Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

dbms_sql - error ORA-00911

Status
Not open for further replies.

tonyblacks

Technical User
Oct 6, 2002
2
US
I am trying to write a procedure that will allow me to update any table, column. Code is below - Disregard the else part of the code- If I can get the update part to work, I will figure out the rest. I am using autonomous to get around the commit issue. I use the DBMS_OUTPUT.PUT_LINE package to help me debug the code by display the sql statement constructed.
Appreciate any help provided.

***************************************************
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "SCOTT.UNIVERSALUPDATE", line 30
ORA-06512: at line 1
**************************************************

CREATE OR REPLACE PROCEDURE UniversalUpdate(
selection integer,
x varchar2,
y varchar2,
value varchar2,
qualifier varchar2
) IS
name varchar2(20);
columnname varchar2(30);
tablename varchar2(40);
PRAGMA AUTONOMOUS_TRANSACTION;
seq_sql VARCHAR(200);
cursor_handle INTEGER;
execute_ddl INTEGER;
BEGIN
name := 'Select an Option';
tablename := x;
columnname := y;
DBMS_OUTPUT.PUT(chr(10));
DBMS_OUTPUT.PUT_LINE(name);
DBMS_OUTPUT.PUT_LINE('1). Update');
DBMS_OUTPUT.PUT_LINE('2). Delete');
DBMS_OUTPUT.PUT_LINE('=========================================');
if selection = 1 then
DBMS_OUTPUT.PUT_LINE('table name: ' ||tablename);
DBMS_OUTPUT.PUT_LINE('column name: ' ||columnname);
DBMS_OUTPUT.PUT_LINE('Update '|| tablename ||' set '||columnname|| ' = '''|| value ||''''|| ' where '||columnname ||' = '''||qualifier ||''';');
seq_sql := 'Update '|| tablename ||' set '||columnname|| ' = '''|| value ||''''|| ' where '||columnname ||' = '''||qualifier ||''';';
cursor_handle := DBMS_SQL.open_cursor;
DBMS_SQL.parse (cursor_handle, seq_sql, DBMS_SQL.native);
execute_ddl := DBMS_SQL.execute (cursor_handle);
DBMS_SQL.close_cursor (cursor_handle);
elsif selection = 2 then
seq_sql := 'Delete from '''||tablename||' where '|| columnname || ''' = ''' ||value ||'''';
cursor_handle := DBMS_SQL.open_cursor;
DBMS_SQL.parse (cursor_handle, seq_sql, DBMS_SQL.native);
execute_ddl := DBMS_SQL.execute (cursor_handle);
DBMS_SQL.close_cursor (cursor_handle);
else
DBMS_OUTPUT.PUT_LINE('usage < 1.Update 2.Delete > < tablename > < columnname > < value >');
end if;
end;
/
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top