| URGENT Syntax question. [message #370637] |
Thu, 13 January 2000 05:09  |
Laert
Messages: 20
Registered: January 2000
|
Junior Member |
|
|
Hi.
How can I insert the value of variable into SQL PL/SQL delete (for example) string in following example:
DECLARE
:tr_table varchar2(30);
BEGIN
:table_name := 'My_table';
DELETE :table_name WHERE ID = 1;
END;
/
Thanx in advance.
|
|
|
|
|
|
| Re: URGENT Syntax question. [message #370641 is a reply to message #370638] |
Thu, 13 January 2000 09:37  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
Laert,
Expanding on my previous answer, here is an example of how it works (this example code is from Oracle's OTN PL/SQL documentation and drops a table, but the principle is the same).
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
cid INTEGER;
BEGIN
/* Open new cursor and return cursor ID. */
cid := DBMS_SQL.OPEN_CURSOR;
/* Parse and immediately execute dynamic SQL statement built by
concatenating table name to DROP TABLE command. */
DBMS_SQL.PARSE(cid, 'DROP TABLE ' || table_name, dbms_sql.v7);
/* Close cursor. */
DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
/* If an exception is raised, close cursor before exiting. */
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
RAISE; -- reraise the exception
END drop_table;
Hope this helps,
Paul
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
