Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Texas
    Posts
    2

    Question Unanswered: Trouble creating a temp_table

    I haven't worked with temp_tables before, so I am new to this. I created a package and am using a temp_table and think there is some conflict with using a temp_table inside a package. Here is my simplified code. Thanks in advance.

    CREATE OR REPLACE PACKAGE BODY "ES_SUP" IS

    FUNCTION CLEAN_INACT_PROV (x1 NUMBER) RETURN VARCHAR2 IS
    totProv NUMBER;

    BEGIN
    create #temp_table(prov_id number);
    insert into #temp_table (select pri_prov_id from chrt);
    insert into test1 (itemid) values ((select prov_id from temp_table));
    drop temp_table;

    RETURN totProv;
    END CLEAN_INACT_PROV;

    --> Error at line "create #temp_table(prov_id number); "
    Encountered the symbol "CREATE" when expecting one of the following: begin case declare exit for goto if loop mod null pragma

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you either need to use "execute_immediate" (look it up)
    OR
    I normally create the temp tables and indexes before I run the procedure script I need to run, then after the procedure I drop the temp tables.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    Atlanta
    Posts
    8

    Re: Trouble creating a temp_table

    one cannot directly fire DDL in procedures.
    they have to be fired using Dynamic SQL.
    refer Oracle Documentation "PL/SQL Users Guide" for further details

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •