CODEWITHSUNDEEP
plsql
Arman21
Arman21

Reputation: 103

PL/SQL EXECUTE IMMEDIATE INTO a CURSOR

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;

Upvotes: 1

Views: 29853

Answers (3)

Stepan Kasyanenko
Stepan Kasyanenko

Reputation: 3186

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.

Upvotes: 2

Recoil
Recoil

Reputation: 178

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>

Upvotes: 2

Nitish
Nitish

Reputation: 1736

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;

Upvotes: 1

Related Questions