OraFAQ Forum: Server Administration » URGENT Syntax question.

Home » RDBMS Server » Server Administration » URGENT Syntax question.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

Re: URGENT Syntax question. [message #370638 is a reply to message #370637]

Thu, 13 January 2000 07:05

Paul
Messages: 164
Registered: April 1999

Senior Member

Laert,
You can use the DBMS_SQL package functions in PL/SQL to accomplish this.
Paul

Report message to a moderator

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

Report message to a moderator

Previous Topic:	Y2K Date Format Issues with Oracle 7.3.4
Next Topic:	Re: First day of the FINNISH week

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 22:01:51 CDT 2025