I have written one program with dynamic SQL and piece of code is follows.
sql_stmt := 'SELECT '||CBID(i)||',BID,'||CBEID(i)||',''NA'',''NA'',''NA'' FROM DIM_ORGNISATION WHERE BID in(select PARENT_B_ID from ORG_DIM_LOD where CHILD_B_ID ='||CBID(i)||') and to_Date(start_Date,''DD/MM/YYYY'') = TO_DATE ( trunc('||Cstart_date_type(i)||'),''DD-MON-YY'',''NLS_DATE_LANGUAGE=ENGLISH'')';
EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO tempBID,tempSBD,tempLBD,tempL3BD,tempL4BD,tempSABD
And , when i'm executing dynamic SQL gives the error as follows.
ORA-00904: "JAN": invalid identifier
ORA-06512: at "LWNER.SHY_CREATE_MAPING", line 184
ORA-06512: at line 2
when displaying with using DBMS_OUTPUT
DBMS_OUTPUT.PUT_LINE('Cstart_date_type(i)'||Cstart_date_type(i)||));
It's diaplaying it as "01-JAN-70".
Is there any one can help on this please ?