Home » SQL & PL/SQL » SQL & PL/SQL » HELP! writing back in table
HELP! writing back in table [message #35839] Thu, 18 October 2001 07:36 Go to next message
Marcus Peter
Messages: 1
Registered: October 2001
Junior Member
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 script does the following: read substrings out of two rows of the table, changes them and SHOULD write them back.
Unfortunately writing back fails. I tried already an ref cursor to fix, but that did not help.

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;

----------------------------------------------------------------------
Re: HELP! writing back in table [message #35840 is a reply to message #35839] Thu, 18 October 2001 11:26 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
interesting proble... I suspect you are destroying the cursor when you execute the update statement.

I'd eaither read the PK or ROWID in the SELECT cursor and use that PK or ROWID to locate the rows to be updated (rather than the "where current of" which implicitly refers to your current select cursor.

Maybe I'm missing something, but I don't see any reason why you can't simply write this as a single update statement using a form something like this:
update t1 a
set a.col1 = (select ... from t1 b
where a.pk=b.pk)

Also on 8i you may try "execute immediate" than all the dbms_sql assuming that the define column is no longer required.

and exists (select null from t1 where...);

----------------------------------------------------------------------
Re: HELP! writing back in table [message #35844 is a reply to message #35839] Fri, 19 October 2001 00:59 Go to previous message
Hans
Messages: 42
Registered: September 2000
Member
try this
 
create or replace procedure gauss_replace (table_name in varchar2, input_text in varchar2, output_text in varchar2)
as
 
   find varchar2(50) := input_text;
   repl varchar2(50) := output_text;
   
   sql_string varchar2(500);
 
   my_cursor integer;
   num_rows_processed integer;
   
begin
 
   sql_string := 
      'update '||table_name || chr(10) ||
         'set path = replace( path,' || '''' || find || '''' || ',' || '''' || repl || '''' || '),'  || chr(10) ||
             'http = replace( http,' || '''' || find || '''' || ',' || '''' || repl || '''' || ')';
         
   my_cursor := dbms_sql.open_cursor;
   dbms_sql.parse(my_cursor, sql_string, DBMS_SQL.V7);  
   num_rows_processed := dbms_sql.execute (my_cursor);   
   dbms_sql.close_cursor(my_cursor);
   
   commit;
exception
   when others then
      if dbms_sql.is_open(my_cursor) then
         dbms_sql.close_cursor(my_cursor);
      end if;
      raise;   
end;
/
show errors
 
drop table t;
create table t (
   path  varchar2(60),
   http  varchar2(60)
);
 
insert into t values ( 'aaaa', 'aaaa' );
insert into t values ( 'bbbb', 'aaaa' );
insert into t values ( 'aaaa', 'bbbb' );
insert into t values ( 'cccc', 'dddd' );
insert into t values ( 'aaab', 'abbb' );
commit;
 
 
begin
   gauss_replace( 't', 'aaa', 'qqq' );
end;
/
 
select * from t;


----------------------------------------------------------------------
Previous Topic: how to find the top 5 biggest value from a query
Next Topic: (Urgent !!!) Row to column
Goto Forum:
  


Current Time: Thu Mar 28 17:32:57 CDT 2024