Re: dynamic sql

Sumit Khanna wrote:

> i have code
> OPEN csr_CDPM for v_ExecuteSql;
> LOOP
> FETCH csr_CDPM INTO Cdpm_eSchedule_rec;
> exit WHEN csr_CDPM%NOTFOUND;
> insert_Ctr := insert_Ctr + 1;
>
> IF (insert_Ctr = 1) THEN
> -- get the next CDPM_OUTpUT ID VALUE
> SELECT cdpm_output_seq.NEXTVAL
> INTO v_CDPM_OUTPUT_ID
> FROM DUAL;
> END IF;
>
> CASE LTRIM(RTRIM(UPPER(v_reportdefrec_tab(1).report_type)))
> WHEN c_rpt_type_escheduled THEN
> FOR j in 1 .. 24
> LOOP
> str_Insert_Sql := '';
> v_DataName := 'HOUR' || TO_CHAR(j);
> v_DataValueName := 'Cdpm_eSchedule_rec.WEIGHTEDHOUR' || TO_CHAR(j);
> v_DataValue := v_DataValueName;
> str_Insert_Sql := 'INSERT INTO CDPM_OUTPUT (ID,
> PJM_SHORT_NAME,
> BASE_CONTRACT,
> AGGR_PSEUDO_SUPPLIER_CODE,
> RFP_TICKET_NO,
> DATA_DATE,
> DATA_NAME,
> DATA_VALUE)
> VALUES ('
> || v_cdpm_output_id
> || ' , ''' || Cdpm_eSchedule_rec.PjmShortName
> || ''' , ''' || Cdpm_eSchedule_rec.ContractId
> || ''' , ''' || Cdpm_eSchedule_rec.PseudoSupplBy
> || ''' , ''' || Cdpm_eSchedule_rec.RfpTicketNo
> || ''' , ' || Cdpm_eSchedule_rec.Load_Date
> || ' , ''' || v_DataName
> || ''' , '|| v_DataValue || ' )';
> EXECUTE IMMEDIATE str_Insert_Sql;
> END LOOP;
> ELSE
> NULL;
> END CASE;
> END LOOP;
> close csr_CDPM;
>
> How do i get the value of v_DataValue to be correctly substituted when
> building my insert statement so that i can execute it correctly.
>
> I am getting
> INSERT INTO CDPM_OUTPUT (ID,
> PJM_SHORT_NAME,
> BASE_CONTRACT,
> AGGR_PSEUDO_SUPPLIER_CODE,
> RFP_TICKET_NO,
> DATA_DATE,
> DATA_NAME,
> DATA_VALUE)
>
> VALUES (19 , 'CPSI10' , '15190' , 'SOSRXXXOPXSECKV' , '' , 01-SEP-03 ,
> 'HOUR24' , Cdpm_eSchedule_rec.WEIGHTEDHOUR24 )
> i want the value of Cdpm_eSchedule_rec.WEIGHTEDHOUR24
>
> Any help will be appreciated.

Please do not cross-post and multipost. One c.d.o. group is sufficient.

My comments on this most horrible of code are where you first posted it.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)