dynamic SQL error messages

From: Marcus Peter <marcus.peter_at_gmx.de>
Date: 18 Oct 2001 07:45:21 -0700
Message-ID: <155d6c3c.0110180645.31ab33e3@posting.google.com>

Hi,
currently I am writing an PL/SQL (runs on Oracle)script for replacing the name of rows in a Table. It also contains dynamic SQL.

The following errors occur:

ORA-00936: missing expression
ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "MPETER.GAUSS_REPLACE", line 46
ORA-06512: at line 1

I suppose it has something to do with the update of the dynamic cursor, because currently I can not write on the server.

Who can help?
Thanks in advance,
Marcus

CREATE OR REPLACE PROCEDURE gauss_replace (table_name IN varchar2, input_text IN varchar2, output_text IN varchar2) as

path_var varchar2(500);
http_var varchar2(500);

find varchar2(50) := input_text;
repl varchar2(50) := output_text;
sql_string varchar2(200);
count number;

my_cursor INTEGER;
NUM_ROWS_PROCESSED INTEGER; BEGIN sql_string := 'SELECT path, http FROM '||table_name; dbms_output.put_line(sql_string);
my_cursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE (my_cursor, sql_string, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN (my_cursor, 1, path_var, 500);
DBMS_SQL.DEFINE_COLUMN (my_cursor, 2, http_var, 500);

NUM_ROWS_PROCESSED := DBMS_SQL.EXECUTE (my_cursor);

LOOP
IF DBMS_SQL.FETCH_ROWS (my_cursor) > 0 THEN

/* Read out the value of the cursor */

DBMS_SQL.COLUMN_VALUE (my_cursor, 1, path_var);
DBMS_SQL.COLUMN_VALUE (my_cursor, 2, http_var);
dbms_output.put_line('vorher:');
dbms_output.put_line(path_var);
dbms_output.put_line(http_var);

/* Replacing the text in the column */

path_var := REPLACE(path_var, find, repl);
http_var := REPLACE(http_var, find, repl);
dbms_output.put_line('nachher:');
dbms_output.put_line(path_var);
dbms_output.put_line(http_var); 

sql_string := 'UPDATE '||table_name||' SET path='''||path_var||''', http='''||http_var||''' WHERE CURRENT OF '||my_cursor; dbms_output.put_line(sql_string);

dbms_sql.parse(my_cursor, sql_string, DBMS_SQL.V7); dbms_output.put_line('parsing complete'); count := dbms_sql.execute(my_cursor);

ELSE
EXIT;
END IF;
END LOOP; Received on Thu Oct 18 2001 - 09:45:21 CDT