- Joined
- Oct 28, 2006
- Messages
- 36,581
- Likes received
- 3,172
- Thanks received
- 1,186
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
Code:
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.


