Re: 'create procedure' problem

From: Alexander Romanov <sashar_at_comita.spb.ru>
Date: Fri, 9 Apr 1999 15:50:33 +0400
Message-ID: <7ekpju$bc1$1@dragon.infopro.spb.su>

G. Kakisina ïèøåò â ñîîáùåíèè <370DDF8C.611237F2_at_hvision.nl> ...
>Hi,
>I'm trying to create a procedure in Oracle 8 that looks like this:

RTFM

• CUT HERE --- Some Limitations However, this design imposes some limitations. For example, the p-code includes references to schema objects such as tables and stored procedures. The PL/SQL compiler can resolve such references only if the schema objects are known at compile time. In the following example, the compiler cannot process the procedure because the table is undefined until the procedure is executed at run time:

CREATE PROCEDURE create_table AS
BEGIN
   CREATE TABLE dept (deptno NUMBER(2), ...); -- illegal    ...
END; In the next example, the compiler cannot bind the table reference in the DROP TABLE statement because the table name is unknown until the procedure is executed:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS BEGIN
   DROP TABLE table_name; -- illegal
   ...
END;
Overcoming the Limitations
However, the package DBMS_SQL, which is supplied with Oracle, allows PL/SQL to execute SQL data definition and data manipulation statements dynamically at run time. For example, when called, the following stored procedure drops a specified database table:

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;

For more information about package DBMS_SQL, see Oracle8 Application Developer's Guide.
--- CUT HERE --- Best regards
Всех Благ
---
Origin ... Просто надо очень верить этим синим маякам... Romanov Alexander
Alex_Romanov_at_mail.ru
ICQ 6220754 Received on Fri Apr 09 1999 - 06:50:33 CDT