Unanswered: Creating Temporary Table in Stored Procedures.
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.
We was faced the same kind of problem using TEMPORARY Table, most probably we find solution for that!
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>
EXECUTE IMMEDIATE ' CREATE GLOBAL TEMPORARY TABLE <TABLE NAME> ( ... )';
EXECUTE <EMPORARY PROCEDURE NAME>
CREATE OR REPLACE PROCEDURE <ACTUAL PROCEDURE NAME>