Re: Inserting Into A Table Created In A Package

On Fri, 21 Sep 2001 15:53:19 GMT, gteets_at_cinci.rr.com (Greg Teets) wrote:

>I have created a table in a package as follows:
>
>CREATE OR REPLACE PACKAGE BODY TEST AS
>
>PROCEDURE create_table
>IS
> cur integer;
> rc integer;
>BEGIN
> cur := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cur, 'CREATE TABLE TEST_IT
> (Y DATE NOT NULL, X NUMBER)',
>DBMS_SQL.NATIVE);
>
> rc := DBMS_SQL.EXECUTE(cur);
> DBMS_SQL.CLOSE_CURSOR(cur);
>END;
>
>PROCEDURE insert_data
>IS
>BEGIN
> insert into test_it values(TO_DATE('31-JUL-01'), 3);
>END;
>
>PROCEDURE create_test_table
>IS
>BEGIN
> create_table;
> insert_data;
>END;
>
>END;
>
>When I compile this package body, I get the following message:
>16/5 PL/SQL: SQL Statement ignored
>16/17 PLS-00201: identifier 'TEST_IT' must be declared
>
>If I take out the insert_data procedure and the call to it, the
>package compiles fine and creates the table when executed.
>
>How should I declare the table test_it so that it is recognized by the
>insert_data procedure? I had assumed that by placing create_table
>ahead of insert_data the table name would be available to insert_data.

You need to perform the insert using dynamic SQL too, something like:

CREATE OR REPLACE PACKAGE BODY TEST AS FUNCTION dynamic (v_statement IN VARCHAR2) RETURN NUMBER IS

    cur integer;
    rc integer;
BEGIN
    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, v_statement, DBMS_SQL.NATIVE);     rc := DBMS_SQL.EXECUTE(cur);
    DBMS_SQL.CLOSE_CURSOR(cur);
    RETURN rc;
END; PROCEDURE create_table IS

    rc integer;
BEGIN
    rc:= dynamic('CREATE TABLE TEST_IT (Y DATE NOT NULL, X NUMBER)'); END; PROCEDURE insert_data IS

    rc integer;
BEGIN
    rc := dynamic('insert into test_it values(TO_DATE('31-JUL-01'), 3)');
END; PROCEDURE create_test_table
IS
BEGIN
    create_table;
    insert_data;
END; END; Received on Fri Sep 21 2001 - 12:20:21 CDT