Oracle dynamic sql

[TheFaQQer]

And I tried the suggestion of building the string and it gives me compilation errors when trying to use it in conjunction with cursor open from. Comment out that line and it compiles.

I ran the second code sample I wrote above in Oracle 11g via SQL*Plus and got no errors. If you copy the exact code, does it run OK for you?

Here's the spool from SQL*Plus

APPS@MYDB > create table ffs ( col1 varchar2(10), col2 varchar2(10))
  2  /

Table created.

Elapsed: 00:00:00.36
APPS@MYDB > insert into ffs values ('Muppet','Man');

1 row created.

Elapsed: 00:00:00.04
APPS@MYDB > insert into ffs values ('Manly','Muppet');

1 row created.

Elapsed: 00:00:00.01
APPS@MYDB > commit
  2  /

Commit complete.

Elapsed: 00:00:00.01
APPS@MYDB > select * from ffs;

COL1       COL2
---------- ----------
Muppet     Man
Manly      Muppet

Elapsed: 00:00:00.04
APPS@MYDB > ed
Wrote file afiedt.buf

  1  DECLARE
  2    TYPE myCurTyp IS REF CURSOR;
  3    myCursor MyCurTyp;
  4    theTable VARCHAR2(100);
  5  BEGIN
  6    theTable := 'FFS';
  7    OPEN mycursor FOR 'SELECT col1, col2 FROM '||theTable;
  8    CLOSE mycursor;
  9* END;
APPS@MYDB > /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

APPS@MYDB > select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Elapsed: 00:00:00.04
APPS@MYDB > spool off

If that runs OK, and your code doesn't, then it would suggest that the error is when you build the SQL statement up. Try dbms_outputting it out in your error handler, so that you can at least see what you were trying to run.

[suityou01]

Faqqer you are very kind. I will try these things out and post the results here.

[suityou01]

OK so when I tried it and it didn't work. The error was

Invalid SQL Statement, on the Open Cursor line.

When I comment this line out and chuck the values out to the debugger and then paste the output into a new SQL pane the "invalid SQL Statement" runs perfectly.

[suityou01]

So I played spot the difference. The only difference I could find was that I had declared my strings as NVARCHAR2 and not VARCHAR2.

(The dev environment is locked down, no web access and I cannot easily ship files to it)

I changed the types from NVARCHAR2 to VARCHAR2 and it worked perfectly.

[TheFaQQer]

So I played spot the difference. The only difference I could find was that I had declared my strings as NVARCHAR2 and not VARCHAR2.

(The dev environment is locked down, no web access and I cannot easily ship files to it)

I changed the types from NVARCHAR2 to VARCHAR2 and it worked perfectly.

The datatypes that you use need to be either CHAR or VARCHAR2 for dynamic SQL.

If you need it to accept NLS characters, then you could possibly get away with using a CLOB and letting the database do an implicit conversion for you, or use DBMS_SQL instead, which is more clunky but should work. Alternatively, have a play around with UNISTR and ASCIISTR which will convert special characters for you and see if you can get the right result that way.

[suityou01]

The datatypes that you use need to be either CHAR or VARCHAR2 for dynamic SQL.

If you need it to accept NLS characters, then you could possibly get away with using a CLOB and letting the database do an implicit conversion for you, or use DBMS_SQL instead, which is more clunky but should work. Alternatively, have a play around with UNISTR and ASCIISTR which will convert special characters for you and see if you can get the right result that way.

Then I thank you most sincerely for your time and expertise. Perhaps at a CUK bash I can furnish you with a half dozen fermented beverages of your choice.

[Bunk]

Then I thank you most sincerely for your time and expertise. Perhaps at a CUK bash I can furnish you with a half dozen fermented beverages of your choice.

Or Diet Coke

[suityou01]

Or Diet Coke

Yes or that.

[TheFaQQer]

Or Diet Coke

More likely, yes. Or maybe push the boat out and have a Pepsi Max

[Bunk]

More likely, yes. Or maybe push the boat out and have a Pepsi Max

Ah, the decadence of a CUK meet.