Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

opening a refcursor with dynamic SQL

664981Dec 10 2008 — edited Dec 11 2008
Hi,

i need to pass the select statement to the cursor as a dynamic SQL i tried the below and i get runtime error.
create or replace
procedure         prm_sp_charts_db (P_CURSOR OUT SYS_REFCURSOR,CHARTCode VARCHAR)

IS

V_SQLSTATEMENT VARCHAR2(2000);

BEGIN

   V_SQLSTATEMENT := 'open P_CURSOR for SELECT ';
   V_SQLSTATEMENT := V_SQLSTATEMENT || 'PMC' || CHARTCode || '_F'|| CHARTCode || '_CODE Keycode1, PMC' || CHARTCode || '_HOP_CODE HOPCode,PMC'|| CHARTCode || '_EFFECTIVE_FROM Effective_From,PMC' || CHARTCode || '_EFFECTIVE_UPTO Effective_Upto,PMC' || CHARTCode || '_CURRENCY Currency,PMC' || CHARTCode || '_AMOUNT Amount FROM PRM_M_CHART_' || CHARTCode ;
   DBMS_OUTPUT.PUT_LINE(V_SQLSTATEMENT);
    execute immediate v_sqlstatement; --into p_cursor;
     --open p_cursor forinto
 --  DBMS_OUTPUT.PUT_LINE(V_SQLSTATEMENT);
  
end prm_sp_charts_db; 
[\code]

ERROR:
ORA-00900: invalid SQL statement
ORA-06512: at "SALUSER.PRM_SP_CHARTS_DB", line 12
ORA-06512: at line 7
open P_CURSOR for SELECT PMCC_FC_CODE Keycode1, PMCC_HOP_CODE HOPCode,PMCC_EFFECTIVE_FROM Effective_From,PMCC_EFFECTIVE_UPTO Effective_Upto,PMCC_CURRENCY Currency,PMCC_AMOUNT Amount FROM PRM_M_CHART_C
Process exited.
Disconnecting from the database sal.
[\code]

Thanks and regards,
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 8 2009
Added on Dec 10 2008
15 comments
1,296 views