Re: Inserting Into A Table Created In A Package

Thanks.

On Fri, 21 Sep 2001 17:20:21 GMT, Mark Styles <news_at_lambic.co.uk> wrote:

>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:31:09 CDT