expression is of wrong type from a weak reference cursor

Question

I am attempting to get a ref cursor to run a dynamic query and return the results. here is an example of what I'm trying to do:

DECLARE
    TYPE CUR_DATA IS REF CURSOR;
    OUT_DATA CUR_DATA;
    SQL_Statement       NVARCHAR2(8000);
BEGIN
    SQL_Statement := ' SELECT * FROM dual ';        
    OPEN OUT_DATA FOR SQL_Statement;
END;

why does this give me an error saying : expression is of wrong type? This ref cursor is weakly typed isn't it? Help!

Answer 1

It is mentioned in the Oracle document that the Select Statement support CHAR, VARCHAR2, or CLOB (not NCHAR or NVARCHAR2). If you want to implement with NVARCHAR then the only solution i know is to translate USING CHAR_CS argument converts char into the database character set. The output datatype is VARCHAR2.

DECLARE
    TYPE CUR_DATA IS REF CURSOR;
    OUT_DATA CUR_DATA;
    SQL_Statement       NVARCHAR2(4000); --declare this as VARCHAR2
    SQL_Statement_var       VARCHAR2(4000);
BEGIN
    SQL_Statement := N'SELECT * FROM dual ';        
    SQL_Statement_var := TRANSLATE(SQL_Statement USING CHAR_CS);
    OPEN OUT_DATA FOR SQL_Statement_var;
END;

No errors.

If NVARCHAR2 is not mandatory ,then try to create with basic data types provided.

DECLARE
    TYPE CUR_DATA IS REF CURSOR;
    OUT_DATA CUR_DATA;
    SQL_Statement       VARCHAR2(4000); --declare this as VARCHAR2
BEGIN
    SQL_Statement := ' SELECT * FROM dual ';        
    OPEN OUT_DATA FOR SQL_Statement;
END; 

References:

Translate...USING

Open For Statement