Enid_JP
asked on
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','20
*/
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
AND
REP.REQUEST_TYPE=RLP.REQUE
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,RE
FETCH NEXT FROM RPT_CUR INTO CompanyCode,FinYear,FinNam
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 ||
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','20
*/
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
AND
REP.REQUEST_TYPE=RLP.REQUE
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,RE
FETCH NEXT FROM RPT_CUR INTO CompanyCode,FinYear,FinNam
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 ||
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 ).
...
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 ).
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;
/
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;
/
ASKER
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..
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..
>> 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:
end;
/
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:
end;
/
ASKER
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
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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','20
*/
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
AND
REP.REQUEST_TYPE=RLP.REQUE
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,RE
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
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','20
*/
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
AND
REP.REQUEST_TYPE=RLP.REQUE
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,RE
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
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
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
ASKER
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
--------------------------
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
--------------------------
ASKER
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
--------------------------
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
--------------------------
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.
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.
ASKER
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','2
*/
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,FinNam
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_COD
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,
)
/**********/
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','2
*/
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,FinNam
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_COD
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,
)
/**********/
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','2
*/
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,FinNam
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_COD
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
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','2
*/
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,FinNam
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_COD
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
ASKER
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;
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;
ASKER
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> /
r is the cursor variable.
PL/SQL procedure successfully completed.
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "r"
SQL> /
I'm afraid I'll need more information:
How did you execute the procedure?
Did you execute the procedure I posted as-is?
How did you execute the procedure?
Did you execute the procedure I posted as-is?
ASKER
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> /
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> /
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.
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.
ASKER
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"
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"
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';
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';
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','2
*/
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
--------------------------
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','2
*/
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
ASKER
Hi,
OPEN CUR_ONE FOR 'select sysdate from dual'; works fine.
The Stored procedure with the query returns the same error.
OPEN CUR_ONE FOR 'select sysdate from dual'; works fine.
The Stored procedure with the query returns the same error.
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.
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.
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).
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).
Thanks for the kind words. Glad you found the information helpful.
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.
I suggest PAQ/ no refund.
http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=c2001157&sliceId=&dialogID=5022787&stateId=1%200%205024431
mlmcc