help with "execute immediate" in oracle

[cmuraz]

hi
i need to insert values in a temporary teble which i dynamically create the columns like tbl_01, tbl_02....
for eg.,
execute immediate 'INSERT INTO TBL_MONTH_LEAVE_DETAIL(TBL_ADM_NO,TBL_MONTH,'TBL_' ||TO_CHAR(SYSDATE,'DD'))'
||'VALUES (TRSTBD_ADM_NO,TO_CHAR(SYSDATE,'MMYY'),1)';

HERE THE COLUMNS ARE TBL_ADM_NO,TBL_MONTH,TBL_01,.....TBL_30
ACCORDING TO THE DAY THE COLUMN WILL BE SELECTED AND A VALUE 1 WILL BE INSERTED.

[amitpatel66]

So what is the error that your dynamic insert statement is displaying?

[amitpatel66]

Try this statement:

 
execute immediate 'INSERT INTO TBL_MONTH_LEAVE_DETAIL(TBL_ADM_NO,TBL_MONTH,TBL_'||TO_CHAR(SYSDATE,'DD'))||'VALUES (TRSTBD_ADM_NO,TO_CHAR(SYSDATE,''MMYY''),1)';
 
 

[cmuraz]

the error says column not allowed here

[cmuraz]

can we concatenate the column names in the execute immedite statement?

[Pilgrim333]

Hi,

Check you parenthesis and quotes, i think you are making a mistake with that.

Pilgrim.

[debasisdas]

@cmuraz
why you need to concatenate the column names in insert statement ?

[cmuraz]

thank u guys n sorry for the trouble..
ive done a mistake in the paranthesis and quotation mark.
i ve misplaced them in a wrong place...
i got it working now..
thanks a lot

[Pilgrim333]

@cmuraz
Hi,

Good to see that your problem is solved.

Pilgrim.