Home » RDBMS Server » Server Administration » URGENT Syntax question.
URGENT Syntax question. [message #370637] Thu, 13 January 2000 05:09 Go to next message
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 #370638 is a reply to message #370637] Thu, 13 January 2000 07:05 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Laert,
You can use the DBMS_SQL package functions in PL/SQL to accomplish this.
Paul
Re: URGENT Syntax question. [message #370641 is a reply to message #370638] Thu, 13 January 2000 09:37 Go to previous message
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
Previous Topic: Y2K Date Format Issues with Oracle 7.3.4
Next Topic: Re: First day of the FINNISH week
Goto Forum:
  


Current Time: Thu Mar 28 19:54:06 CDT 2024