Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2397
  • Last Modified:

Stored procedure and temporary tables.

Hi,
I have a stored procedure that works fine with SQL server 2005.
I am having trouble making the same thing work with Oracle 9i.
The SP was modified to some extent; but I still get errors.
Here's what I am trying to do.
I execute a query and dump the resultset to a temporary table(#comp1).
Then I read the table and execute another query , the results of which are written to another temporary table.(#Details)
Finally, I need to write out the contents of the second temporary table to a report.(Crystal Report).

Can somebody please let me know how to go about this? I have listed the Procedure below.
Thank you.

CREATE OR REPLACE PROCEDURE TEST_PROC
(
            p_p_OWNER IN VARCHAR2,
            p_COMPANY_SEL_METHOD IN NUMBER,
            p_COMPANY_CODE_OP1 IN VARCHAR2,
            p_FIN_YEAR_METHOD IN NUMBER,
            p_FIN_YEAR_BEGIN IN VARCHAR2,
            p_FIN_YEAR_END IN VARCHAR2,
            CUR_USER IN VARCHAR2
)
IS    
             CompanyCode VARCHAR2(3);
             FinYear VARCHAR2(4);
             FinName VARCHAR2(60);
             SQLQuery VARCHAR(255);                      
             SQLWhereClause VARCHAR(255);  
             SQLWhereClause1 VARCHAR(255);
             SQLWhereClause2 VARCHAR(255);
             SQLWhereClause3 VARCHAR(255);
             SQLWhereClause4 VARCHAR(255);
             SQLFinalQuery VARCHAR(255);
                 
BEGIN
                 ETABLE := '#comp';      

               EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE #comp1
                                ( COMP_CODE VARCHAR2(60)         NULL
                                , FIN_YEAR VARCHAR2(4)                NULL
                                , FIN_NAME VARCHAR2(60)            NULL
                                ) ON COMMIT PRESERVE ROWS' ;  

               EXCEPTION

                   WHEN TAB_ALREADY_EXISTS THEN NULL;
                        EXECUTE IMMEDIATE 'TRUNCATE TABLE #comp1' ;
                   WHEN OTHERS THEN
                        m_ERROR_NO   :=  -20010;
                        m_ERROR_TEXT := 'Error while creating temp table #comp1. Error is : ';
                        RAISE;
       --END;
--BEGIN
                -- ETABLE := '#Details';                
               --EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE #Details
                             --   (
                                                --p_OWNER VARCHAR2(8) NULL,
                                                --PROCESSED_DATE DATE NULL,
                                                --FIN_NAME VARCHAR2(60) NULL,
                                                --COMP_CODE VARCHAR2(60) NULL,
                                                --FIN_YEAR VARCHAR2(4) NULL

                               -- ) ON COMMIT PRESERVE ROWS' ;
               --EXCEPTION
                   --WHEN TAB_ALREADY_EXISTS THEN NULL;
                        --EXECUTE IMMEDIATE 'TRUNCATE TABLE #Details' ;
                   --WHEN OTHERS THEN    
                        --m_ERROR_NO   :=  -20010;
                        --m_ERROR_TEXT := 'Error occured while creating temp table #Details . Error is : ';
                        --RAISE;
       --END;
/* To test        
exec TEST_PROC 'xyz',0,'001',0,'2005','2007','user1'
*/

BEGIN
  SQLQuery := 'SELECT FIN.COMP_CODE,
            DFIN.FIN_YEAR,
            FIN.FIN_NAME                  
            FROM
                        FIN
            INNER JOIN
                        DFIN
            ON
                        FIN.YR_CODE=DFIN.YR_CODE
            AND
                        FIN.p_OWNER=DFIN.p_OWNER ';
  SQLWhereClause1 := '           WHERE           FIN.p_OWNER='''+ p_p_OWNER+'''';                        

 

IF p_COMPANY_SEL_METHOD = 1 THEN
              SQLWhereClause2 := ' AND FIN.COMP_CODE = '''+p_COMPANY_CODE_OP1 +'''';

ELSE
             SQLWhereClause2 := '';

END IF;

IF p_FIN_YEAR_METHOD = 1 THEN
             SQLWhereClause3 := ' AND FIN_YEAR between '''+p_FIN_YEAR_BEGIN +''' and '''+p_FIN_YEAR_END +'''';  

ELSEIF p_FIN_YEAR_METHOD = 2 THEN
            SQLWhereClause3 := ' AND FIN_YEAR = '''+p_FIN_YEAR_BEGIN +'''' ;

ELSE
             SQLWhereClause3 := '';

END IF;            

 SQLWhereClause4 :='            ORDER BY  
                        FIN_YEAR,COMP_CODE';
 SQLWhereClause := SQLWhereClause1 + SQLWhereClause2 + SQLWhereClause3 + SQLWhereClause4;
 SQLFinalQuery := SQLQuery + SQLWhereClause;
--exec (SQLFinalQuery)
--END
INSERT INTO #comp1 (                COMP_CODE ,
                              FIN_YEAR,
                        FIN_NAME
             
            )
EXEC (SQLFinalQuery);
--END
DECLARE RPT_CUR CURSOR local fast_forward FOR
SELECT COMP_CODE ,       FIN_YEAR ,  FIN_NAME FROM #comp1
OPEN RPT_CUR
FETCH NEXT FROM RPT_CUR INTO CompanyCode,FinYear ,FinName
    WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #Details                             -- insert records into temporary Details table
SELECT
                          PALC.p_OWNER
                        , REP.PROCESSED_DATE
                        , FIN.DEFN_DESC
                        , FIN.CLOSE_PROC
                        , FIN.COMP_CODE
                        , DFIN.FIN_YEAR

            FROM
                        PALC
            INNER JOIN
                        FIN
            ON
                        PALC.p_OWNER = FIN.p_OWNER
            AND
                        PALC.YR_CODE = FIN.YR_CODE
            INNER JOIN
                        REP
            ON
                        REP.p_OWNER=RLP.p_OWNER
            AND
                  REP.REQUEST_ID=RLP.REQUEST_ID
            AND
                        REP.REQUEST_TYPE=RLP.REQUEST_TYPE
            INNER JOIN  
                        DALC
            ON
                        FIN.YR_CODE=DFIN.YR_CODE
            AND
                        FIN.p_OWNER=DFIN.p_OWNER
            AND
                        DFIN.FIN_YEAR = @FinYear
            WHERE
                        PALC.p_OWNER = p_p_OWNER  
            AND PALC.FIN_YEAR = FinYear        
            AND FIN.COMP_CODE = CompanyCode                      
            AND FIN.FLX_UPDATE_USER =CUR_USER
            and PALC.PER_TYPE = 0
            ORDER BY
                        PALC.QTR_NO,PALC.PER_NO,REP.PROCESSED_DATE
            FETCH NEXT FROM RPT_CUR INTO CompanyCode,FinYear,FinName
END

---fetching from cursor
CLOSE RPT_CUR
DEALLOCATE RPT_CUR
DROP TABLE #comp1
SELECT * FROM #Details
DROP TABLE #Details
--GRANT EXECUTE ON TEST_PROC TO PUBLIC
END
--go

I get the following errors :

LINE/COL ERROR

-------- -----------------------------------------------------------------

95/8     PLS-00103: Encountered the symbol " p_FIN_YEAR_METHOD " when
         expecting one of the following:
         := . ( @ % ;


106/13   PLS-00103: Encountered the symbol "#" when expecting one of the   following:
         ( <an identifier> <a double-quoted delimited-identifier>         table the
         The symbol "#" was ignored.
111/1    PLS-00103: Encountered the symbol "EXEC" when expecting one of
LINE/COL ERROR
-------- -----------------------------------------------------------------

         the following:

         ( select values

         The symbol "select" was substituted for "EXEC" to continue.
114/1    PLS-00103: Encountered the symbol "DECLARE" when expecting one of     the following:
         . ( , * % & - + / at mod rem <an identifier>
         <a double-quoted delimited-identifier> <an exponent (**)> as
         from ||



0
Enid_JP
Asked:
Enid_JP
  • 13
  • 12
  • +2
1 Solution
 
slightwv (䄆 Netminder) Commented:
First: you shouldn't create tables in PL/SQL let alone global temporary tables.

...
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE #comp1
...

In Oracle you create the temp table once and it exists forever.  After you insert/update/delete and end the transaction the rows are automatically removed.

Second:  Oracle won't let you create a table with a '#' in it.

Third:  In PL/SQL it is 'elsif' not 'elseif' ( around line 95 ).
0
 
sujith80Commented:
I doubt you can get what you want using "Oracle Temporary Tables".

The Temporary tables can store the data max only upto the end of the oracle session. So, when you call your procedure thru some means(I dont know how you invoke your procedure), the data get populated. But once you come out of that oracle session the data is no longer there. So, unless the report is also generated in the same session this approach will not work. Moreover, this is not an efficient way of generating a report. Because, everytime you want to generate the report you will have to run this procedure. Ideally what you should do is, run the procedure once, keep the results ready and generate the report from the result data many times.

Consider re-designing using the normal tables.
- Have a DATE column of something to distinguish the current data from history data.
- If you dont need any history data for the reports, then truncate the table and insert. Something like:

procedure <your procedure>
as
begin
-- trucate the table here.
-- insert into <your table> <your select statement>
-- commit;
end;
/
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Enid_JPAuthor Commented:
Suppose I have a variable that contains the SQL Query.Can I use EXECUTE IMMEDIATE as shown below with the cursor?
SQLWhereClause := SQLWhereClause1 || SQLWhereClause2 || SQLWhereClause3;
 SQLFinalQuery := SQLQuery || SQLWhereClause;
BEGIN
CURSOR RPT_CUR_ONE IS
EXECUTE IMMEDIATE (SQLFinalQuery); -- is this allowed?
OPEN RPT_CUR_ONE;
FOR EACH_REC IN RPT_CUR_ONE LOOP
...access data in cursor..
0
 
slightwv (䄆 Netminder) Commented:
>> is this allowed?

no.

an 'execute immediate' executes the command and deosn't return anything.

You can declare a generic ref cursor and open it with a dynamic string.
note:  I don't have any 9i left around to test this but it should work
------------------------------------------------------
drop table tab1;
create table tab1 ( col1 char(1) );

insert into tab1 values('a');
commit

declare
      type generic_cur is ref cursor;
      myCur      generic_cur;
      mySQL      varchar2(200) := 'select col1 from tab1';
      myChar      char(1);

begin
      open myCur for mySQL;
      fetch myCur into myChar;
      close myCur;
      dbms_output.put_line('Got: ' || myChar);
end;
/


0
 
Enid_JPAuthor Commented:
Hi,
I tried tha sample and it works fine.; but in my stored procedure, I get the following error when I compile.
I have declared it like this:
TYPE generic_cur is ref cursor;
     CUR_ONE generic_cur;
BEGIN
.....

LINE/COL ERROR
-------- -----------------------------------------------------------------
106/1    PL/SQL: Statement ignored
106/17   PLS-00221: 'CUR_ONE' is not a procedure or is undefined
0
 
Enid_JPAuthor Commented:
Hi,
I tried creating a package
 CREATE OR REPLACE PACKAGE TEST_REF_CURSOR IS
   TYPE TEST_CUR_TYPE IS REF CURSOR;
END TEST_REF_CURSOR;
/
Then I used this in the SP as
CREATE OR REPLACE PROCEDURE TEST_PROC
(
      CUR_ONE IN OUT  TEST_REF_CURSOR .TEST_CUR_TYPE ,
      PARAM1 IN VARCHAR2,
      PARAM2 IN NUMBER,
      )
IS
etc...Then, I form a query and store it in SQLFinalQuery.I get the error for the following line.
OPEN CUR_ONE FOR SQLFinalQuery;

LINE/COL ERROR
-------- -----------------------------------------------------------------
106/1    PL/SQL: Statement ignored
106/17   PLS-00221: 'CUR_ONE' is not a procedure or is undefined

What step am I missing?
0
 
slightwv (䄆 Netminder) Commented:
I forget when SYS_REFCURSOR came around but I'm thinking it wasn't until 10g.  It made life so much easier.  Check your 9i docs and see if you can find any reference to it.  If you can find it, we'll completely shift gears.  SYS_REFCURSOR is basically a system defined cursor so you don't have to declare your own type.

Back to the current error:
Is the procedure TEST_PROC defined in the package TEST_REF_CURSOR?  If you posted the entire package creation then it isn't and that's causing the error in the second post.

Any particular reason you went with a package over the sample I posted?

I'll need to see a little more of the code if you can post it.
0
 
Enid_JPAuthor Commented:
Hi,
It didn't work when I declared the cursor like in the sample(type generic_cur is ref cursor;
); that's why I used a package.
TEST_PROC is not defined in the package.I have given the SP below.
If I have a VARCHAR2 parameter that I need to use in a query in the stored procedure, how do I do it?
For e.g.
I have a query like:
pStrValue is of type VARCHAR2.
It works fine with SQLServer 2005 but not on Oracle 9i. It compiles, but no rows are returned.
SELECT * from test1
WHERE col1 = pStrValue;
I have tried with SELECT * from test1
WHERE col1 = ''' || pStrValue || ''' ;  -- this does not work too.
When I put in the actual value, it works fine.For e.g.
SELECT * from test1
WHERE col1 = 'user1';
/***************************/
CREATE OR REPLACE PACKAGE TEST_REF_CURSOR IS
   TYPE TEST_CUR_TYPE IS REF CURSOR;
END TEST_REF_CURSOR;
/
/***************/
CREATE OR REPLACE PROCEDURE TEST_PROC
(
      CUR_ONE IN OUT  TEST_REF_CURSOR .TEST_CUR_TYPE,
            p_p_OWNER IN VARCHAR2,
            p_COMPANY_SEL_METHOD IN NUMBER,
            p_COMPANY_CODE_OP1 IN VARCHAR2,
            p_FIN_YEAR_METHOD IN NUMBER,
            p_FIN_YEAR_BEGIN IN VARCHAR2,
            p_FIN_YEAR_END IN VARCHAR2,
            CUR_USER IN VARCHAR2
)
IS    
             CompanyCode VARCHAR2(3);
             FinYear VARCHAR2(4);
             FinName VARCHAR2(60);
             SQLQuery VARCHAR(255);                      
             SQLWhereClause VARCHAR(255);  
             SQLWhereClause1 VARCHAR(255);
             SQLWhereClause2 VARCHAR(255);
             SQLWhereClause3 VARCHAR(255);
             SQLWhereClause4 VARCHAR(255);
             SQLFinalQuery VARCHAR(255);
                 
BEGIN
                           
               EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE #Details
                                (
                                                p_OWNER VARCHAR2(8) NULL,
                                                PROCESSED_DATE DATE NULL,
                                                FIN_NAME VARCHAR2(60) NULL,
                                                COMP_CODE VARCHAR2(60) NULL,
                                                FIN_YEAR VARCHAR2(4) NULL

                                ) ON COMMIT PRESERVE ROWS' ;
               --EXCEPTION
                   --WHEN TAB_ALREADY_EXISTS THEN NULL;
                        --EXECUTE IMMEDIATE 'TRUNCATE TABLE #Details' ;
                   --WHEN OTHERS THEN    
                        --m_ERROR_NO   :=  -20010;
                        --m_ERROR_TEXT := 'Error occured while creating temp table #Details . Error is : ';
                        --RAISE;
       --END;
/* To test        
exec TEST_PROC 'xyz',0,'001',0,'2005','2007','user1'
*/

BEGIN
  SQLQuery := 'SELECT FIN.COMP_CODE,
            DFIN.FIN_YEAR,
            FIN.FIN_NAME                  
            FROM
                        FIN
            INNER JOIN
                        DFIN
            ON
                        FIN.YR_CODE=DFIN.YR_CODE
            AND
                        FIN.p_OWNER=DFIN.p_OWNER ';
  SQLWhereClause1 := '           WHERE           FIN.p_OWNER='''+ p_p_OWNER+'''';                        
IF p_COMPANY_SEL_METHOD = 1 THEN
              SQLWhereClause2 := ' AND FIN.COMP_CODE = '''+p_COMPANY_CODE_OP1 +'''';

ELSE
             SQLWhereClause2 := '';
END IF;

IF p_FIN_YEAR_METHOD = 1 THEN
             SQLWhereClause3 := ' AND FIN_YEAR between '''+p_FIN_YEAR_BEGIN +''' and '''+p_FIN_YEAR_END +'''';  

ELSEIF p_FIN_YEAR_METHOD = 2 THEN
            SQLWhereClause3 := ' AND FIN_YEAR = '''+p_FIN_YEAR_BEGIN +'''' ;

ELSE
             SQLWhereClause3 := '';

END IF;            

 SQLWhereClause4 :='            ORDER BY  
                        FIN_YEAR,COMP_CODE';
 SQLWhereClause := SQLWhereClause1 + SQLWhereClause2 + SQLWhereClause3 + SQLWhereClause4;
 SQLFinalQuery := SQLQuery + SQLWhereClause;

OPEN CUR_ONE FOR SQLFinalQuery;

FOR EACH_REC IN CUR_ONE LOOP

CompanyCode              := EACH_REC.COMP_CODE;
FinYear               := EACH_REC.CAL_YEAR;
FinName              := EACH_REC.DEFN_DESC;
INSERT into TempDetails                             -- insert records into temporary Details table
SELECT
                          PALC.p_OWNER
                        , REP.PROCESSED_DATE
                        , FIN.DEFN_DESC
                        , FIN.CLOSE_PROC
                        , FIN.COMP_CODE
                        , DFIN.FIN_YEAR
            FROM
                        PALC
            INNER JOIN
                        FIN
            ON
                        PALC.p_OWNER = FIN.p_OWNER
            AND
                        PALC.YR_CODE = FIN.YR_CODE
            INNER JOIN
                        REP
            ON
                        REP.p_OWNER=RLP.p_OWNER
            AND
                  REP.REQUEST_ID=RLP.REQUEST_ID
            AND
                        REP.REQUEST_TYPE=RLP.REQUEST_TYPE
            INNER JOIN  
                        DALC
            ON
                        FIN.YR_CODE=DFIN.YR_CODE
            AND
                        FIN.p_OWNER=DFIN.p_OWNER
            AND
                        DFIN.FIN_YEAR = FinYear
            WHERE
                        PALC.p_OWNER = p_p_OWNER  
            AND PALC.FIN_YEAR = FinYear        
            AND FIN.COMP_CODE = CompanyCode                      
            AND FIN.FLX_UPDATE_USER =CUR_USER
            and PALC.PER_TYPE = 0
            ORDER BY
                        PALC.QTR_NO,PALC.PER_NO,REP.PROCESSED_DATE;
           
END LOOP;
END;
---fetching from cursor
--CLOSE RPT_CUR;
--DEALLOCATE RPT_CUR;
--DROP TABLE comp1;
--SELECT * FROM TempDetails;
--DROP TABLE TempDetails;
--GRANT EXECUTE ON TEST_PROC TO PUBLIC;
END TEST_PROC;
/
SET termout  ON
SET feedback ON
show errors


0
 
slightwv (䄆 Netminder) Commented:
First:  You seem to still be creating the global temp table with an execute immediate.

I would change up the way you are building the where clauses but that's a personal preference.  We'll talk about that later.

>>It works fine with SQLServer 2005 but not on Oracle 9i. It compiles, but no rows are returned.
>>WHERE col1 = ''' || pStrValue || ''' ;  -- this does not work too.

I'm confused now.  Is this part of the procedure or kind of a separate question?  I see where some select statements are commented out at the bottom of the procedure.  Are you referring to them?  If so, you can't do native DML/DDL in PL/SQL so simple selects like the one commented out are out.  You will have to 'select' the data in PL/SQL in a cursor or wait until after you've executed the procedure and select it from SQL*Plus or something similar.


Back to the question.  I went out and looked at the 9.0.1 docs and sys_refsursor seems to exist:
http://download.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89856/08_subs.htm#20682


From SQL*Plus try the following and see if this works.  Then we'll go back to your proc and clean it up.
---------------------------------------------------------------------------------------------------

var myOutput refcursor

CREATE OR REPLACE PROCEDURE TEST_PROC
(
      CUR_ONE IN OUT SYS_REFCURSOR
)
IS    
BEGIN

open cur_one for 'select sysdate from dual';
                           
END TEST_PROC;
/

show errors


exec test_proc(:myOutput);

print myOutput


0
 
Enid_JPAuthor Commented:
Hi,
The sample above works fine.
When I declare CUR_ONE as SYS_REFCURSOR, I get the same old error , i.e.

LINE/COL ERROR
-------- --------------------------------------------------------------
108/1    PL/SQL: Statement ignored
108/17   PLS-00221: 'RPT_CUR_ONE' is not a procedure or is undefined
------------------------------------------------------------------------

0
 
Enid_JPAuthor Commented:
Hi,
The sample above works fine.
When I declare CUR_ONE as SYS_REFCURSOR in my Stored procedure, I get the same old error , i.e.

LINE/COL ERROR
-------- --------------------------------------------------------------
108/1    PL/SQL: Statement ignored
108/17   PLS-00221: 'RPT_CUR_ONE' is not a procedure or is undefined
------------------------------------------------------------------------

0
 
slightwv (䄆 Netminder) Commented:
Can you post the failing code in full?

To help can you also post the table definitions involved so I can try compiling on my end?   This will save me some time since I don't have to make up my own tables.
0
 
Enid_JPAuthor Commented:
Hi,
Here's the full SP and the required table definitions.
CREATE OR REPLACE PROCEDURE TEST_PROC
(
            CUR_ONE IN OUT SYS_REFCURSOR,
            p_T_OWNER IN VARCHAR2,
            p_COMPANY_SEL_METHOD IN NUMBER,
            p_COMPANY1 IN VARCHAR2,
            p_FIN_YEAR_METHOD IN NUMBER,
            p_FIN_YEAR_BEGIN IN VARCHAR2,
            p_FIN_YEAR_END IN VARCHAR2,
            CURR_USER IN VARCHAR2
)
IS
             CompanyCode VARCHAR2(3);
             FinYear VARCHAR2(4);
             FinName VARCHAR2(60);
             CompType NUMBER;
             SQLQuery VARCHAR(255);                        
             SQLWhereClause VARCHAR(255);  
             SQLWhereClause1 VARCHAR(255);
             SQLWhereClause2 VARCHAR(255);
             SQLWhereClause3 VARCHAR(255);
             SQLWhereClause4 VARCHAR(255);
             SQLFinalQuery VARCHAR(255);
          --TYPE generic_cur is ref cursor;
     --CUR_ONE generic_cur;
BEGIN
               EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TempDetails
               (
                                                T_OWNER VARCHAR2(8) NULL,
                                                QTR_NO NUMBER NULL,
                                                PER_NO NUMBER NULL,
                                                PER_START_DATE DATE NULL,
                                                PER_END_DATE DATE NULL,
                                                REQ_TYPE NUMBER NULL,
                                                REQ_ID VARCHAR2(15),                      
                                                FIN_NAME VARCHAR2(60) NULL,
                                                COMP_TYPE NUMBER NULL,
                                                COMP_CODE VARCHAR2(60) NULL,
                                                FIN_YEAR VARCHAR2(4) NULL
                ) ON COMMIT PRESERVE ROWS' ;
               --exception handling
/* To test
exec TEST_PROC 'test',0,'001',0,'2005','2007','user1'
*/
BEGIN
  SQLQuery := 'SELECT FIN.COMP_CODE,
            DCAL.FIN_YEAR,
            FIN.FIN_NAME,
            FIN.COMP_TYPE                                
            FROM
                        FIN
            INNER JOIN  
                        DCAL
            ON
                        FIN.YR_CODE=DCAL.YR_CODE
            AND
                        FIN.T_OWNER=DCAL.T_OWNER ';
  SQLWhereClause1 := '           WHERE
                        FIN.T_OWNER='''|| p_T_OWNER||'''';              
IF p_COMPANY_SEL_METHOD = 1 THEN
              SQLWhereClause2 := ' AND FIN.COMP_CODE = ''' || p_COMPANY1 ||'''';
ELSE
             SQLWhereClause2 := '';
END IF;
 IF p_FIN_YEAR_METHOD = 1 THEN
             SQLWhereClause3 := ' AND FIN_YEAR between '''|| p_FIN_YEAR_BEGIN || ''' and '''|| p_FIN_YEAR_END || '''';      
ELSIF p_FIN_YEAR_METHOD = 2 THEN
            SQLWhereClause3 := ' AND FIN_YEAR = ''' || p_FIN_YEAR_BEGIN || '''' ;
ELSE
             SQLWhereClause3 := '';
END IF;
 SQLWhereClause4 :=' ORDER BY FIN_YEAR,COMP_CODE';
 SQLWhereClause := SQLWhereClause1 || SQLWhereClause2 || SQLWhereClause3 || SQLWhereClause4;
 SQLFinalQuery := SQLQuery || SQLWhereClause;

OPEN CUR_ONE FOR SQLFinalQuery;
FOR EACH_REC IN CUR_ONE LOOP
--FETCH NEXT FROM CUR_ONE INTO CompanyCode,FinYear,FinName,CompType
CompanyCode             := EACH_REC.COMP_CODE;
FinYear               := EACH_REC.FIN_YEAR;
FinName               := EACH_REC.FIN_NAME;
CompType      := EACH_REC.COMP_TYPE;
         
INSERT into TempDetails          -- insert records into temporary Details table
SELECT
                          PALC.T_OWNER
                        , PALC.QTR_NO
                        , PALC.PER_NO
                        , PALC.PER_START_DATE
                        , PALC.PER_END_DATE
                        , REP.REQ_TYPE
                        , REP.REQ_ID
                        , FIN.FIN_NAME
                        , FIN.COMP_TYPE
                        , FIN.COMP_CODE
                        , DCAL.FIN_YEAR
            FROM
                        PALC
            INNER JOIN
                        FIN
            ON
                        PALC.T_OWNER = FIN.T_OWNER
            AND
                       PALC.YR_CODE = FIN.YR_CODE
            INNER JOIN
                        RLP
            ON
                        RLP.COMP_CODE=FIN.COMP_CODE
            AND
                        RLP.T_OWNER=FIN.T_OWNER
            AND
                        RLP.PER_NO = PALC.PER_NO
            AND
                        RLP.LAST_DATE >= PALC.PER_END_DATE
            INNER JOIN
                        REP
            ON
                        REP.T_OWNER=RLP.T_OWNER
            AND
                        REP.REQ_ID=RLP.REQ_ID
            AND
                        REP.REQ_TYPE=RLP.REQ_TYPE
            INNER JOIN  
                        DCAL
            ON
                        FIN.YR_CODE=DCAL.YR_CODE
            AND
                        FIN.T_OWNER=DCAL.T_OWNER
            AND
                        DCAL.FIN_YEAR = FinYear
            WHERE
                        PALC.T_OWNER = p_T_OWNER
            AND PALC.FIN_YEAR = FinYear        
            AND FIN.COMP_CODE = CompanyCode                      
            AND FIN.CUR_USER =CURR_USER
            and PALC.PER_TYPE = 0
            ORDER BY
                        PALC.QTR_NO,PALC.PER_NO;
 END LOOP; -- cursor loop
END;
CLOSE CUR_ONE;
DEALLOCATE CUR_ONE
--BEGIN
--SELECT * FROM TempDetails;
--DROP TABLE TempDetails;
--GRANT EXECUTE ON TEST_PROC TO PUBLIC;
--END;

END TEST_PROC;
/
SET termout  ON
SET feedback ON
show errors
/***************/
CREATE TABLE FIN(
      T_OWNER char(8)  NOT NULL,      
      COMP_CODE varchar(60)  NOT NULL,
      YR_CODE char(6)  NULL,
      FIN_NAME char(60)  NULL,      
      COMP_TYPE smallint NULL,
      CUR_USER char(20)  NOT NULL
)

CREATE TABLE RLP(
      T_OWNER char(8)  NOT NULL,      
      REQ_ID char(15)  NOT NULL,
      REQ_TYPE smallint NOT NULL,
      COMP_CODE varchar(60)  NOT NULL,
      PER_NO smallint NOT NULL,
      LAST_DATE date NOT NULL
)

CREATE TABLE REP(
      T_OWNER char(8)  NOT NULL,
      REQ_ID char(15)  NOT NULL,
      REQ_TYPE smallint NOT NULL,
)

CREATE TABLE PALC(
      T_OWNER char(8)  NOT NULL,
      YR_CODE char(6)  NOT NULL,
      FIN_YEAR char(4)  NOT NULL,
      PER_NO smallint NOT NULL,
      PER_START_DATE date NULL,
      PER_END_DATE date NULL,
      QTR_NO smallint NULL,
      PER_TYPE char(3)  NULL,
)
CREATE TABLE DCAL(
      T_OWNER char(8)  NOT NULL,
      YR_CODE  char(6)  NOT NULL,
      FIN_YEAR char(4)  NOT NULL,
)
/**********/
0
 
slightwv (䄆 Netminder) Commented:
First and I'll keep repeating it:  Get rid of the 'Create global temporary table' DDL in the procedure.  Create it outside the procedure once and forget it.  That's how they work in Oracle.  Temp tables in Oracle are a completely different thing than SQL Server.

Second:  The end result still escapes me, you take the time and effort to put all this data into a temporary table then don't seem to do anything with it.

Third:  I'm not an expert on this but I'm pretty sure you can't loop through the entire cursor and close it when it's an OUT parameter and have the receiving program do anything with it.  You'll just need to play with it and see.

Last and most important:  The code problem was with your loop and the DEALLOCATE command.  I've gotten it to compile but You'll need to verify it works.

--------------------------------------------------
CREATE OR REPLACE PROCEDURE TEST_PROC
(
            CUR_ONE IN OUT SYS_REFCURSOR,
            p_T_OWNER IN VARCHAR2,
            p_COMPANY_SEL_METHOD IN NUMBER,
            p_COMPANY1 IN VARCHAR2,
            p_FIN_YEAR_METHOD IN NUMBER,
            p_FIN_YEAR_BEGIN IN VARCHAR2,
            p_FIN_YEAR_END IN VARCHAR2,
            CURR_USER IN VARCHAR2
)
IS
             CompanyCode VARCHAR2(3);
             FinYear VARCHAR2(4);
             FinName VARCHAR2(60);
             CompType NUMBER;
             SQLQuery VARCHAR(255);                        
             SQLWhereClause VARCHAR(255);  
             SQLWhereClause1 VARCHAR(255);
             SQLWhereClause2 VARCHAR(255);
             SQLWhereClause3 VARCHAR(255);
             SQLWhereClause4 VARCHAR(255);
             SQLFinalQuery VARCHAR(255);
          --TYPE generic_cur is ref cursor;
     --CUR_ONE generic_cur;
BEGIN
               --exception handling
/* To test
exec TEST_PROC 'test',0,'001',0,'2005','2007','user1'
*/
  SQLQuery := 'SELECT FIN.COMP_CODE,
            DCAL.FIN_YEAR,
            FIN.FIN_NAME,
            FIN.COMP_TYPE                                
            FROM
                        FIN
            INNER JOIN  
                        DCAL
            ON
                        FIN.YR_CODE=DCAL.YR_CODE
            AND
                        FIN.T_OWNER=DCAL.T_OWNER ';
  SQLWhereClause1 := '           WHERE
                        FIN.T_OWNER='''|| p_T_OWNER||'''';              
IF p_COMPANY_SEL_METHOD = 1 THEN
              SQLWhereClause2 := ' AND FIN.COMP_CODE = ''' || p_COMPANY1 ||'''';
ELSE
             SQLWhereClause2 := '';
END IF;
 IF p_FIN_YEAR_METHOD = 1 THEN
             SQLWhereClause3 := ' AND FIN_YEAR between '''|| p_FIN_YEAR_BEGIN || ''' and '''|| p_FIN_YEAR_END || '''';      
ELSIF p_FIN_YEAR_METHOD = 2 THEN
            SQLWhereClause3 := ' AND FIN_YEAR = ''' || p_FIN_YEAR_BEGIN || '''' ;
ELSE
             SQLWhereClause3 := '';
END IF;
 SQLWhereClause4 :=' ORDER BY FIN_YEAR,COMP_CODE';
 SQLWhereClause := SQLWhereClause1 || SQLWhereClause2 || SQLWhereClause3 || SQLWhereClause4;
 SQLFinalQuery := SQLQuery || SQLWhereClause;

OPEN CUR_ONE FOR SQLFinalQuery;
--FOR EACH_REC IN CUR_ONE LOOP

   LOOP
      FETCH CUR_ONE INTO CompanyCode,FinYear,FinName,CompType;

      EXIT WHEN CUR_ONE%NOTFOUND OR CUR_ONE%NOTFOUND IS NULL;

INSERT into TempDetails          -- insert records into temporary Details table
SELECT
                          PALC.T_OWNER
                        , PALC.QTR_NO
                        , PALC.PER_NO
                        , PALC.PER_START_DATE
                        , PALC.PER_END_DATE
                        , REP.REQ_TYPE
                        , REP.REQ_ID
                        , FIN.FIN_NAME
                        , FIN.COMP_TYPE
                        , FIN.COMP_CODE
                        , DCAL.FIN_YEAR
            FROM
                        PALC
            INNER JOIN
                        FIN
            ON
                        PALC.T_OWNER = FIN.T_OWNER
            AND
                       PALC.YR_CODE = FIN.YR_CODE
            INNER JOIN
                        RLP
            ON
                        RLP.COMP_CODE=FIN.COMP_CODE
            AND
                        RLP.T_OWNER=FIN.T_OWNER
            AND
                        RLP.PER_NO = PALC.PER_NO
            AND
                        RLP.LAST_DATE >= PALC.PER_END_DATE
            INNER JOIN
                        REP
            ON
                        REP.T_OWNER=RLP.T_OWNER
            AND
                        REP.REQ_ID=RLP.REQ_ID
            AND
                        REP.REQ_TYPE=RLP.REQ_TYPE
            INNER JOIN  
                        DCAL
            ON
                        FIN.YR_CODE=DCAL.YR_CODE
            AND
                        FIN.T_OWNER=DCAL.T_OWNER
            AND
                        DCAL.FIN_YEAR = FinYear
            WHERE
                        PALC.T_OWNER = p_T_OWNER
            AND PALC.FIN_YEAR = FinYear        
            AND FIN.COMP_CODE = CompanyCode                      
            AND FIN.CUR_USER =CURR_USER
            and PALC.PER_TYPE = 0
            ORDER BY
                        PALC.QTR_NO,PALC.PER_NO;
 END LOOP; -- cursor loop

CLOSE CUR_ONE;

END TEST_PROC;
/

show errors
0
 
Enid_JPAuthor Commented:
Thank you.
I didn't have the time to try this out yesterday
I need to execute 2 queries here; the first one will query some tables and get a recordset. I need to use data (FinYear,CompanyCode,etc) from thet Recordset and use them in the where clause of the next query.
This final data should be used to generate a report(Crystal Reports).
That is why I had placed these lines in my SP.
--BEGIN
--SELECT * FROM TempDetails;
--DROP TABLE TempDetails;
--GRANT EXECUTE ON TEST_PROC TO PUBLIC;
--END;
0
 
Enid_JPAuthor Commented:
When I try to execute this procedure, I get the following error.
r is the cursor variable.

PL/SQL procedure successfully completed.
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "r"
SQL> /
0
 
slightwv (䄆 Netminder) Commented:
I'm afraid I'll need more information:

How did you execute the procedure?
Did you execute the procedure I posted as-is?



0
 
Enid_JPAuthor Commented:
I removed the code creating the temporary table and executed it separately.
This is how I executed TEST_PROC.

 variable r refcursor;
  exec TEST_PROC
 (CUR_ONE => :r, p_T_OWNER => 'testuser', p_COMPANY_SEL_METHOD => 0,p_COMPANY1 => 'A11' ,p_FIN_YEAR_METHOD => 0, p_FIN_YEAR_BEGIN => '2007',p_FIN_YEAR_END => '2007' ,CURR_USER => 'testuser');
  print :r;

/*********/
PL/SQL procedure successfully completed.
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "r"
SQL> /
0
 
slightwv (䄆 Netminder) Commented:
Not sure how I missed this one:  You can't close the cursor if it is to be returned.

Comment out:  CLOSE CUR_ONE;

For a reference, refer back to my sample code in post ID:19619004.

I also have the same reservation about looping through a cursor that is to be returned.  See my comment labeled: "Third: " in post ID:19637841.
0
 
Enid_JPAuthor Commented:
I still get the following error when I exceute the SP.
I have commented out the line that closes the cursor.

print :r;
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "r"
0
 
slightwv (䄆 Netminder) Commented:
I'm unable to reproduce the problem on my end.

Give this one a try.  I've cleaned up all the 'where clause' stuff a little.  I also remove the loop that inserts into the temp table.  This should be as simple a test that we can get with your data.

If the problem continues, do one final test just to verify the cursor is returning:
replace:
OPEN CUR_ONE FOR SQLQuery;

with
OPEN CUR_ONE FOR 'select sysdate from dual';
0
 
slightwv (䄆 Netminder) Commented:
oops. forgot the code.  It's Monday.....
--------------------------------------------------
CREATE OR REPLACE PROCEDURE TEST_PROC
(
            CUR_ONE IN OUT SYS_REFCURSOR,
            p_T_OWNER IN VARCHAR2,
            p_COMPANY_SEL_METHOD IN NUMBER,
            p_COMPANY1 IN VARCHAR2,
            p_FIN_YEAR_METHOD IN NUMBER,
            p_FIN_YEAR_BEGIN IN VARCHAR2,
            p_FIN_YEAR_END IN VARCHAR2,
            CURR_USER IN VARCHAR2
)
IS
             CompanyCode VARCHAR2(3);
             FinYear VARCHAR2(4);
             FinName VARCHAR2(60);
             CompType NUMBER;
             SQLQuery VARCHAR(4000);                        
          --TYPE generic_cur is ref cursor;
     --CUR_ONE generic_cur;
BEGIN
               --exception handling
/* To test
exec TEST_PROC 'test',0,'001',0,'2005','2007','user1'
*/
  SQLQuery := 'SELECT FIN.COMP_CODE,
            DCAL.FIN_YEAR,
            FIN.FIN_NAME,
            FIN.COMP_TYPE                                
            FROM
                        FIN
            INNER JOIN  
                        DCAL
            ON
                        FIN.YR_CODE=DCAL.YR_CODE
            AND
                        FIN.T_OWNER=DCAL.T_OWNER
                    WHERE
                        FIN.T_OWNER='''|| p_T_OWNER||'''';              


IF p_COMPANY_SEL_METHOD = 1 THEN
              SQLQuery := SQLQuery || ' AND FIN.COMP_CODE = ''' || p_COMPANY1 ||'''';
END IF;

IF p_FIN_YEAR_METHOD = 1 THEN
             SQLQuery := SQLQuery || ' AND FIN_YEAR between '''|| p_FIN_YEAR_BEGIN || ''' and '''|| p_FIN_YEAR_END || '''';      
ELSIF p_FIN_YEAR_METHOD = 2 THEN
             SQLQuery := SQLQuery || ' AND FIN_YEAR = ''' || p_FIN_YEAR_BEGIN || '''' ;
END IF;

SQLQuery := SQLQuery || ' ORDER BY FIN_YEAR,COMP_CODE';

OPEN CUR_ONE FOR SQLQuery;

END TEST_PROC;
/

show errors
0
 
Enid_JPAuthor Commented:
Hi,
OPEN CUR_ONE FOR 'select sysdate from dual'; works fine.
The Stored procedure with the query returns the same error.
0
 
slightwv (䄆 Netminder) Commented:
Sorry for the delay in responding.  I was out of the office all last week.

Can you provide me with a table definition and some sample data?

Verify that the procedure I provided in post ID:19730483 fails and I'll try to reproduce the problem on my end.
0
 
AaronAbendCommented:
Just found this thread in searching for info on MSSQL to Oracle. Very valuable info from slighwv - thanks from me.

I think MSSQL people are used to creating objects on the fly, because mssql lets you compile a procedure that refers to non-existent objects.  I see that to make stuff work, you have to create all objects in advance then truncate them.

My thanks to slighwv. I do not see an accepted solution even though there is a lot of good info already here.

If this gets closed by the moderator, I recommend points to slighwv (I must say, he has already invested a lot of time for only 50 points).

0
 
slightwv (䄆 Netminder) Commented:
Thanks for the kind words.  Glad you found the information helpful.
0
 
slightwv (䄆 Netminder) Commented:
Ah yes.  I remember this question.  I agree with AaronAbend in that it has a lot of useful information.  I guess I never answered the question so I can't ask for the points.

I suggest PAQ/ no refund.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 13
  • 12
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now