dynamic sql

From: Sumit Khanna <sumit.khanna_at_bge.com>
Date: 30 Dec 2003 13:41:20 -0800
Message-ID: <74b6bbc8.0312301341.2c989460@posting.google.com>

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. Received on Tue Dec 30 2003 - 15:41:20 CST