Inserting Into A Table Created In A Package

From: Greg Teets <gteets_at_cinci.rr.com>
Date: Fri, 21 Sep 2001 15:53:19 GMT
Message-ID: <3bab5b30.322476948@news-server.cinci.rr.com>

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.

Thanks for your help. Received on Fri Sep 21 2001 - 10:53:19 CDT