inconsistency sql and dbms_sql?

From: <joseph_testa_at_aep.com>
Date: 1998/01/06
Message-ID: <884094084.167051275_at_dejanews.com>#1/1

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> _at_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  begin

 14 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  18 

cursor_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 Usenet

Received on Tue Jan 06 1998 - 00:00:00 CET