I'm trying to use EXECUTE IMMEDIATE into a CURSOR.
I get the error code PLS-103
Is there any Chance to do this?
My Cursor
CURSOR myCursor(var IN VARCHAR2)
IS
v_query_string := 'SELECT Name FROM myDB WHERE Name LIKE '||var||';';
EXECUTE IMMEDIATE v_query_string;
My Loop
FOR c1 IN curquestanswersicd_hd_2(v_name)
LOOP
HTP.P(c1.name);
END LOOP;
You can't execute DML or PL/SQL code insight of a CURSOR declaration.
Only a SELECT Statement is valid for a CURSOR declaration.
Please refer to http://docs.oracle.com/database/121/LNPLS/explicit_cursor.htm#LNPLS01313
For your simple case it's not neccessary to use an explicit cursor and for performance reasons you should use an implicit cursor, wrapped in a FOR-LOOP:
Example code:
SET SERVEROUTPUT ON;
SET FEEDBACK OFF;
CLEAR;
-- Create a test table
CREATE TABLE MYDB (
ID NUMBER,
NAME VARCHAR2(255)
);
-- Insert some test values
INSERT INTO mydb (id,name) VALUES(1,'Oracle 11g Enterprise');
INSERT INTO mydb (id,name) VALUES(2,'Oracle 11g XP');
INSERT INTO mydb (id,name) VALUES(3,'Oracle 12c Enterprise');
INSERT INTO mydb (id,name) VALUES(4,'Oracle 12c XP');
INSERT INTO mydb (id,name) VALUES(5,'MongoDB');
INSERT INTO mydb (id,name) VALUES(6,'Postgres');
-- and commit.
COMMIT;
-- Create the procedure
CREATE OR REPLACE PROCEDURE MYDBNAMES(P_NAME_SNIPPET IN VARCHAR2) IS
V_NAME VARCHAR2(256);
BEGIN
FOR C1 IN (SELECT NAME
FROM MYDB
WHERE NAME LIKE P_NAME_SNIPPET) LOOP
DBMS_OUTPUT.PUT_LINE(C1.NAME); -- <- HTP.P(C1.NAME);
END LOOP;
END;
/
SHOW ERRORS;
--Execute procedure
EXECUTE MYDBNAMES('%11%');
-- Clean up
DROP PROCEDURE MYDBNAMES;
DROP TABLE MYDB;
Output:
Oracle 11g Enterprise
Oracle 11g XP
SQL>
If you need run loop by different query string to cab do next:
open for 'select 1 from dual' for my_cursor;
Loop
exit when my_cursor%NOTFOUND
end loop;
Sorry for formatting, I'm posting from mobile phone.
Instead of having only the select statement in v_query_string, you need to have the whole FOR loop block inside it.
DECLARE
V_STRING VARCHAR2(1000) := 'BEGIN
FOR C1 IN (SELECT * FROM TAB) LOOP
--DO SOMETHING
END LOOP;
END;';
BEGIN
EXECUTE IMMEDIATE V_STRING;
END;
