Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008
    Posts
    7

    Question Unanswered: Urgent Help - Pl/Sql

    Hi all,

    I had a query regarding Pl Sql.

    In a stored procedure, I created a table dyanmically using Execute Immediate.
    I modified the data in that table as per requirement.
    Now, in order to fetch that data to the front end, I use a Ref Cursor,

    When I try to fetch the data from Ref Cursor, it says "Table Doesnot exist".
    Since that table will be created only at the run time, it is showing that error.
    In this case how can i achieve this..

    Pls see the following code..

    EXECUTE IMMEDIATE 'CREATE TABLE TEMP_SPR_DYNA(CALLID NUMBER(10), LOG VARCHAR2(400)';

    ---- did some modification on that table data

    OPEN RC1 FOR
    SELECT * FROM TEMP_SPR_DYNA;

    it says that THE TABLE "TEMP_SPR_DYNA" DOES NOT EXIST.

    on the other hand if I use

    OPEN RC1 FOR
    EXECUTE IMMEDIATE 'SELECT * FROM TEMP_SPR_DYNA';

    IT IS SAYING SELECT STATEMENT IS MISSING..

    Help me with some solution...

    Thanks a lot..

  2. #2
    Join Date
    Aug 2008
    Posts
    9
    Sorry, could you be a little more clear

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    When I try to fetch the data from Ref Cursor, it says "Table Doesnot exist".
    Since that table will be created only at the run time, it is showing that error.
    It is (almost always) bad practice in Oracle to create tables dynamically. Even the stage tables shall be created as GLOBAL TEMPORARY ones statically (as their definition does not change - only data which are private to each session). Study this concept and re-think the need of dynamic table creation.
    In this case how can i achieve this..
    Access the table dynamically too. Find some time to open PL/SQL User's Guide and Reference, located with other Oracle documentation e.g. online on http://tahiti.oracle.com/, to find the correct syntax.

    To your code tries: the first one (static) is not possible to be used; the second one (dynamic) is the way to go, but with the correct syntax.

Posting Permissions

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