Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40

    Unanswered: Creating Temporary Table in Stored Procedures.

    HI there,
    Well while migrating the database from SQL Server to Oracle 8i using the Oracle Migration Workbench, We get encountered with some problems.
    1> In the SQL Server procedures we had used the concept of creating the temporary tables using them and at the last returning a recordset to the VB Application, Now since we are shifting our work to ORACLE so here during the migration it gives an error in the above scenerio.
    I had find the follwing method.
    -------
    execute immediate 'create global temporary table A <definition follows> ';
    -------
    but using this global temporary table I have some confusions, firstly since it preserves the defination of the temporary table hence with each execution of the same procedure in which we r using this statement, how will it work???
    Secondly it creates the temporary table for the session, but I want to create it for valid only in the stored procedures.

    2> Second a very strange problem occurs, that this utility is unable to convert the following simple query-----
    --------
    SELECT * from discom_consumer as i
    where i.name_e like ltrim(@name) and
    i.address1 like ltrim(@add1) and
    i.address2 like ltrim(@add2) and
    i.address3 like ltrim(@add3)
    ---------
    where the @name, @add1, @add2, @add3 are simply IN variables in the stored procedures.
    Please anyone help.
    Thanking you in anticipation.
    RGDS
    Amit Kanodia

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi Amit,

    We was faced the same kind of problem using TEMPORARY Table, most probably we find solution for that!

    STEPS :
    1. Before you CREATING TEMPORARY Table in your ACTUAL STORED PROCEDURE, create the same TEMPORARY TABLE in temporary Procedure and execute that temporary PROCEDURE.
    2. Delete all the VALUES before creating the TEMPORARY TABLE STRUCTUE IN ACTUAL PROCEDURE.

    Note: If you didn't do the Step 1 Oracle will show the RUN TIME ERROR.

    CREATE OR REPLACE PROCEDURE <TEMPORARY PROCEDURE NAME>
    AS
    BEGIN
    EXECUTE IMMEDIATE ' CREATE GLOBAL TEMPORARY TABLE <TABLE NAME> ( ... )';
    END;
    /

    EXECUTE <EMPORARY PROCEDURE NAME>
    /


    CREATE OR REPLACE PROCEDURE <ACTUAL PROCEDURE NAME>
    AS
    BEGIN

    DELETE <TEMPORARY TABLE NAME>
    EXECUTE IMMEDIATE ' CREATE GLOBAL TEMPORARY TABLE <TABLE NAME> ( ... )';
    END;
    /

    Hey I found some pretty good Migration tool for SQLServer to Oracle plz do visit the www.swissql.com

Posting Permissions

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