Oracle Procedure cursor as a varchar2 variable

Question

I want to pass a string like "select 'a' as alphabet, 1 as number from dual" to procedure and use the same to assign to a cursor.

Here is my code how my proc looks like

CREATE OR REPLACE PROCEDURE ME( sqlstmt in varchar2 )
AS  
  C1 CURSOR IS sqlstmt;

BEGIN  
  FOR REC IN C1 
        LOOP
            DBMS_OUTPUT.PUT_LINE(REC.alphabet);
        END LOOP;
END;

Tried execute immediate and other stuff but nothing helped.

Answer 1

You only need a slightly different syntax to open a cursor based on a select stored in a varchar2:

CREATE OR REPLACE PROCEDURE ME(sqlstmt IN VARCHAR2) AS
    C1                                      SYS_REFCURSOR;
    vAlphabet   varchar2(100);
    vNumber     number;
BEGIN
    OPEN C1 FOR sqlstmt;

    LOOP
        FETCH C1 INTO vAlphabet, vNumber;

        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(vAlphabet);
    END LOOP;
END;

The procedure call, paying attention to avoid reserved words such as 'number' in the statement:

SQL> exec ME('select ''a'' as alphabetValue, 1 as numberValue from dual');
a

PL/SQL procedure successfully completed.

Notice that you have to know in advance the number and type of the columns returned by the cursor to fetch data.