Home » SQL & PL/SQL » SQL & PL/SQL » ora-00900 invalid sql statement while using open cursor for variable
ora-00900 invalid sql statement while using open cursor for variable [message #641985] |
Fri, 28 August 2015 13:41 |
|
bhardkap
Messages: 2 Registered: August 2015
|
Junior Member |
|
|
Hi Tech Gurus,
I have one requirement in which i have to fetch the select query from a table which i'll use to generate a report using pl/sql stored procedure.
i tried doing it using ref cursor as follows:( please note that the below code is just a sample code of my actual code)
Create or replace PROCEDURE PROC1
AS
TYPE T_CURSOR IS REF CURSOR;
v_query patent.query%type;
Proc_nm patent.procedure_name%type;
VID patent.ID%type;
a varchar:= '';
b varchar:='';
CURSOR C
IS
SELECT Query,
FROM patent where flag=1;
cur_ref T_CURSOR;
BEGIN
open C;
loop
fetch c into V_query;
exit when c%notfound;
end loop;
OPEN cur_ref FOR v_query ; --------- error statement of executable code
LOOP
FETCH cur_ref bulk INTO a,b,c;
EXIT
WHEN cur_ref%NOTFOUND;
END LOOP;
close c;
END PROC1;
-- query is the column of the patent table and will contain a generic query like select x,y,z from sample;
The above code compiled successfully. but while executing the code i got an error ::ora-00900 invalid sql statement" .
my question is :can we use v_query for fetching the rows as mentioned above ? as i can't directly hardcode the query in place of v_query as it should come from the table itself.
Please provide your suggestions.
|
|
|
|
|
|
|
Re: ora-00900 invalid sql statement while using open cursor for variable [message #641997 is a reply to message #641990] |
Sat, 29 August 2015 02:50 |
|
bhardkap
Messages: 2 Registered: August 2015
|
Junior Member |
|
|
Hi,
Thanks for the response. but as i already mentioned in my last post that i just showed you my sample code and not the actual code .Please find below the code with minor correction and do reply for the errors which i got while executing it. my question is can i use a variable (v_query) for opening the cursor instead of writing the full query in the procedure itself as the actual query which is coming the this variable from the patent table.
Patent table is as follows:
id query procedure_name flag
1 select a,b,c from sample; proc1 1 -- where a is number,b is varchar2,c is varchar2
2 select a,b,c from sample1; proc2 0
Create or replace PROCEDURE PROC1
AS
TYPE T_CURSOR IS REF CURSOR;
v_query patent.query%type;
Proc_nm patent.procedure_name%type;
VID patent.ID%type;
a number:= '';
b varchar2(500):='';
c varchar2(500):='';
CURSOR C
IS
SELECT Query
FROM patent where flag=1;
cur_ref T_CURSOR;
BEGIN
open C;
loop
fetch c into V_query;
exit when c%notfound;
end loop;
OPEN cur_ref FOR v_query ; --------- error statement of executable code
LOOP
FETCH cur_ref INTO a,b,c;
EXIT
WHEN cur_ref%NOTFOUND;
END LOOP;
close c;
END PROC1;
|
|
|
|
|
|
Re: ora-00900 invalid sql statement while using open cursor for variable [message #642168 is a reply to message #642143] |
Fri, 04 September 2015 05:49 |
|
aspire
Messages: 18 Registered: September 2015 Location: TVN
|
Junior Member |
|
|
CREATE TABLE EMP_1 (EMPNO NUMBER PRIMARY KEY, ENAME VARCHAR2(90), DEPTNO NUMBER REFERENCING DEPT_1(DEPTNO));
CREATE TABLE DEPT_1 (DEPTNO NUMBER PRIMARY KEY, DEPT_NAME VARCHAR2(90));
CREATE TABLE SALARY_1 (EMPNO NUMBER REFERENCING EMP_1(EMPNO), SALARY NUMBER);
INSERT INTO EMP_1 VALUES (101, 'ADAMN', 1);
INSERT INTO EMP_1 VALUES (102, 'RICKY', 2);
INSERT INTO EMP_1 VALUES (103, 'CLARK', 3);
INSERT INTO EMP_1 VALUES (104, 'PATIL', 2);
INSERT INTO DEPT_1 VALUES (1, 'MANAGER');
INSERT INTO DEPT_1 VALUES (2, 'ACCOUNT');
INSERT INTO DEPT_1 VALUES (3, 'HR');
INSERT INTO SALARY_1 VALUES (101, 10000);
INSERT INTO SALARY_1 VALUES (102, 2400);
INSERT INTO SALARY_1 VALUES (103, 5000);
INSERT INTO SALARY_1 VALUES (104, 1500);
CREATE TABLE PATENT (ID NUMBER, QUERY VARCHAR2(1000), PROC VARCHAR2(100),FLG VARCHAR2(1));
DROP TABLE PATENT;
INSERT INTO PATENT VALUES (10,'SELECT EMPNO, ENAME, DEPTNO FROM EMP_1','PRC1','1');
INSERT INTO PATENT VALUES (20,'SELECT DEPTNO, DEPT_NAME FROM DEPT_1','PRC2','1');
INSERT INTO PATENT VALUES (30,'SELECT EMPNO, SALARY FROM SALARY_1','PRC3','1');
/
create or replace PROCEDURE PROC1 AS
TYPE T_CURSOR IS REF CURSOR;
CUR_REF T_CURSOR;
TYPE REC_EMP IS RECORD (EMPNO EMP_1.EMPNO%TYPE, ENAME EMP_1.ENAME%TYPE, DEPTNO EMP_1.DEPTNO%TYPE);
LV_REC_EMP REC_EMP;
TYPE REC_DEPT IS RECORD (DEPTNO DEPT_1.DEPTNO%TYPE, DEPT_NAME DEPT_1.DEPT_NAME%TYPE );
LV_REC_DEPT REC_DEPT;
TYPE REC_SAL IS RECORD (EMPNO SALARY_1.EMPNO%TYPE, SALARY SALARY_1.SALARY%TYPE);
LV_REC_SAL REC_SAL;
CURSOR C1 IS SELECT QUERY,PROC FROM PATENT WHERE FLG='1';
V_QUERY PATENT.QUERY%TYPE;
PROC PATENT.PROC%TYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V_QUERY,PROC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Using normal cursor : '||V_QUERY);
OPEN CUR_REF FOR V_QUERY;
LOOP
IF PROC ='PRC1' THEN
FETCH CUR_REF INTO LV_REC_EMP;
EXIT WHEN CUR_REF%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Using Refcursor : '||LV_REC_EMP.EMPNO||' | '||LV_REC_EMP.ENAME||' | '||LV_REC_EMP.DEPTNO);
ELSIF PROC ='PRC2' THEN
FETCH CUR_REF INTO LV_REC_DEPT;
EXIT WHEN CUR_REF%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Using Refcursor : '||LV_REC_DEPT.DEPTNO||' | '||LV_REC_DEPT.DEPT_NAME);
ELSIF PROC ='PRC3' THEN
FETCH CUR_REF INTO LV_REC_SAL;
EXIT WHEN CUR_REF%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Using Refcursor : '||LV_REC_SAL.EMPNO||' | '||LV_REC_SAL.SALARY);
END IF;
END LOOP;
END LOOP;
CLOSE C1;
END PROC1;
/
--Output for the above procedure
Using normal cursor : SELECT EMPNO, ENAME, DEPTNO FROM EMP_1
Using Refcursor : 101 | ADAMN | 1
Using Refcursor : 102 | RICKY | 2
Using Refcursor : 103 | CLARK | 3
Using Refcursor : 104 | PATIL | 2
Using normal cursor : SELECT DEPTNO, DEPT_NAME FROM DEPT_1
Using Refcursor : 1 | MANAGER
Using Refcursor : 2 | ACCOUNT
Using Refcursor : 3 | HR
Using normal cursor : SELECT EMPNO, SALARY FROM SALARY_1
Using Refcursor : 101 | 10000
Using Refcursor : 102 | 2400
Using Refcursor : 103 | 5000
Using Refcursor : 104 | 1500
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 12:27:11 CDT 2024
|