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!

• dynamic
• plsql
• oracle10g
• ref-cursor

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