RE: Dynamic PL/SQL

From: larry elkins <elkinsl_at_flash.net>
Date: Sat, 23 Dec 2000 16:02:57 -0600
Message-Id: <10719.125304@fatcity.com>

Viktor,

I just did a quick test case *without* the proposed changes and I got the ORA-00917: missing comma error. Sorry about that. It has been a while since I encountered this problem, and, for some reason I was thinking it was the ORA-01756 error that I had encountered.

Here is the test case I used for the various things. The table STRIP is composed of a single column and one row with the value O'Dell. I commented out the section where I did a replace on the local variable, and, it did indeed give the ORA-00917. Sorry for the confusion.

  1 Declare

  2     ls_select_statement varchar2(2000);
  3     ls_insert_statement varchar2(2000);
  4     li_cursor_id        pls_integer;
  5     li_cursor_id1       pls_integer;
  6     li_rc               pls_integer;
  7     v_strip             varchar2(30);

  8 Begin
  9 -- ls_select_statement := 'SELECT REPLACE(STRIP,'''''''','' '') FROM STRIP';

 10  --   ls_select_statement := 'SELECT
REPLACE(STRIP,'''''''','''''''''''') FROM STRIP';
 11  --   ls_select_statement := 'SELECT REPLACE(STRIP,chr(39),'' '') FROM

STRIP';
 12 -- ls_select_statement := 'SELECT REPLACE(STRIP,chr(39),chr(39)||chr(39)) FROM STRIP';

 13     ls_select_statement := 'SELECT STRIP FROM STRIP';
 14     li_cursor_id := DBMS_SQL.OPEN_CURSOR;
 15     DBMS_SQL.parse(li_cursor_id, ls_select_statement,1);
 16     DBMS_SQL.DEFINE_COLUMN(li_cursor_id, 1, v_strip,30);
 17     li_rc := DBMS_SQL.EXECUTE(li_cursor_id);
 18     LOOP
 19       EXIT WHEN DBMS_SQL.FETCH_ROWS(li_cursor_id) = 0;
 20       DBMS_SQL.COLUMN_VALUE(li_cursor_id, 1,v_strip);
 21  --     v_strip := replace(v_strip,'''',' ');
 22       ls_insert_statement := 'INSERT INTO STRIP VALUES(';
 23       ls_insert_statement := ls_insert_statement

||''''||v_strip||''''||')';
 24 -- dbms_output.put_line(ls_insert_statement);

 25       li_cursor_id1 := DBMS_SQL.OPEN_CURSOR;
 26       DBMS_SQL.PARSE(li_cursor_id1, ls_insert_statement, 1);
 27       li_rc := DBMS_SQL.EXECUTE(li_cursor_id1);
 28       DBMS_SQL.CLOSE_CURSOR(li_cursor_id1);
 29     END LOOP;
 30     DBMS_SQL.CLOSE_CURSOR(li_cursor_id);

 31* end;
SQL> /
 Declare
*
ERROR at line 1:

ORA-00917: missing comma
ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at line 26

Regards,

Larry G. Elkins
elkinsl_at_flash.net
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Viktor Sent: Saturday, December 23, 2000 3:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: Dynamic PL/SQL

Larry,
Thanks a lot for taking the time to go over this. I really appreciate it.
I should've indicated the error:
I am getting ORA-00917: missing comma. Received on Sat Dec 23 2000 - 16:02:57 CST