Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    36

    Unanswered: temporary tables hlp

    Please suggest some solution, if possible on the
    following :-

    I have a problem in the procedures and functions which have been migrated from sybase to oracle.

    The problem is something like this :-

    In some procedures I have some code which inserts some
    data into a temporary table which has not got created
    because in its creation statement they are accessing a
    variable from the same procedure which is in invalid
    status bcoz its not getting compiled due to the absence
    of the temporary table. Its like a loop/cycle.
    For example:
    Let A be the procedure in which we are accessing
    temporary table B. This temporary Table B is not
    getting created Bcoz it is accessing a local variable
    in procedure A which is in invalid state/not compiled
    state.



    CREATE GLOBAL TEMPORARY TABLE B
    ON COMMIT PRESERVE ROWS AS
    SELECT
    RPAD(SP_A.sid_, 64, ' ') id, cusip, order_id, status,
    created, settles, ordered, price, principal, order_type, updated_time,
    updated, description, accrued, total, ytc, ytm, coupon, maturity, created_time
    FROM
    B
    WHERE 1 = 2 ;


    THE ABOVE ONE IF I AM TRYING TO CREATE IT IS GIVING AN ERROR OF INVALIDSTATE/INVALID COLUMN
    SP_A.SID_

    THIS TEMPORARY TABLE WE ARE ACCESSING INTHE PROCEDURE TO INSERT ROWS.



    Could you suggest some idea on this.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: temporary tables hlp

    The beauty of Oracle's global temporary tables is that you don't have to create them at runtime. The table is created ONCE like any other table; only the data in the table is temporary.

    So for your example:

    1) Do this ONCE (not in the application):

    CREATE GLOBAL TEMPORARY TABLE B
    ON COMMIT PRESERVE ROWS
    (id, cusip, order_id, status,
    created, settles, ordered, price, principal, order_type, updated_time,
    updated, description, accrued, total, ytc, ytm, coupon, maturity, created_time); -- Add datatypes, primary key etc. as required

    2) In the application:
    INSERT INTO B
    (id, cusip, order_id, status,
    created, settles, ordered, price, principal, order_type, updated_time,
    updated, description, accrued, total, ytc, ytm, coupon, maturity, created_time)
    SELECT RPAD(SP_A.sid_, 64, ' ') id, cusip, order_id, status,
    created, settles, ordered, price, principal, order_type, updated_time,
    updated, description, accrued, total, ytc, ytm, coupon, maturity, created_time
    FROM
    B
    WHERE 1 = 2 ;

    Because you said ON COMMIT PRESERVE ROWS, the data you insert will persist for the duration of your SESSION. Alternatively you could have said ON COMMIT DELETE ROWS, which does what it says.

Posting Permissions

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