Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> inconsistency sql and dbms_sql?
would someone take a look at this and tell me what i am doing wrong, here is the background, i am writing a procedure that takes 2 parms, source_table_name and destination_table_name, using dbms_sql, i am going to take all columns and copy to new table, converting long to CLOB and long raw to BLOB on the fly. When i am doing the dbms_sql.parse and specifying to get a locator for my CLOB in an anonymous pl/sql block it works fine, but same sql when passed to dbms_sql.parse comes up with a ORA-905 missing keyword, could it be that I have been working on it too long and cant see the forest for the trees. here is the code i used( it is set echo on and i spooled it all). thanx in advance, joe
SQL> set echo on SQL> @c:\tmp\x.x SQL> set serveroutput on size 100000; SQL> SQL> drop table z;
Table dropped.
SQL> create table z (x1 varchar2(10) not null,
2 x2 number not null, 3 xnumber number not null, 4 x3 char(10) not null, 5 xdate date not null, 6 x4 clob , 7 x6 raw(10), 8 x7 varchar(5), 9 x8 number(10,2), 10 x9 number(5));
Table created.
SQL> SQL> SQL> SQL> declare 2 2 q1 VARCHAR2(10):='ABCDEF'; 3 q2 NUMBER:=20; 4 q3 NUMBER:=30; 5 q4 CHAR(10):='ZYX'; 6 q5 DATE:='01-jan-99'; 7 q6 CLOB; 8 q7 RAW(10); 9 q8 VARCHAR2(5); 10 q9 NUMBER(10,2); 11 q10 NUMBER(5); 12 clob_x clob; 13 13 13 begin14 14 insert into Z( X1,X2,XNUMBER,X3,XDATE,X4,X6,X7,X8,X9) values (q1,q2,q3,q4,q5, 15 empty_clob(),q7,q8,q9,q10) returning X4 into clob_x; 16 end; 17 /
PL/SQL procedure successfully completed.
SQL> SQL> declare 2 2 q1 VARCHAR2(10):='ABCDEF'; 3 q2 NUMBER:=20; 4 q3 NUMBER:=30; 5 q4 CHAR(10):='ZYX'; 6 q5 DATE:='01-jan-99'; 7 q6 CLOB; 8 q7 RAW(10); 9 q8 VARCHAR2(5); 10 q9 NUMBER(10,2); 11 q10
NUMBER(5); 12 clob_x clob; 13 13 sql_string varchar2(2000); 14 cursor_handle integer; 15 15 begin 16 16 sql_string:='insert into Z( X1,X2,XNUMBER,X3,XDATE,X4,X6,X7,X8,X9) values (q1,q2,q3,q4,q5, 17 empty_clob(),q7,q8,q9,q10) returning X4 into clob_x'; 18 18cursor_handle:=dbms_sql.open_cursor; 19 dbms_sql.parse(cursor_handle,sql_String,dbms_sql.native); 20 dbms_sql.close_cursor(cursor_handle); 21 21 end; 22 / declare * ERROR at line 1: ORA-00905: missing keyword ORA-06512: at "SYS.DBMS_SYS_SQL", line 491 ORA-06512: at "SYS.DBMS_SQL", line 32 ORA-06512: at line 19
SQL> SQL> SQL> spool off -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to UsenetReceived on Tue Jan 06 1998 - 00:00:00 CST