Re: dynamic SQL error messages

On 18 Oct 2001 07:45:21 -0700, marcus.peter_at_gmx.de (Marcus Peter) wrote:

>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;
Solution:
You can't use where current of with cursor handles. Please read the documentation carefully.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Thu Oct 18 2001 - 13:08:58 CDT