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,