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.

I'm rying to be constructive but ...

Don't know what version of Oracle you are using but your SQL indicates something over 10 years old while EXECUTE IMMEDIATE indicates 8i? What is it you are working with?

Assuming pre-9i you can not use CASE WHEN as you are doing it. It must be in native dynamic SQL. But I can't imaging the syntax you have there working no matter what version of Oracle.

Your statement using v_ExecuteSql is intended to do what? v_ExecuteSql has no value.

You might want to rewrite your cursor using an implicit cursor FOR loop.

And your statement
SELECT cdpm_output_seq.NEXTVAL
INTO v_CDPM_OUTPUT_ID
FROM DUAL;
does nothing that couldn't be done by by putting cdpm_output_seq.NEXTVAL into your insert statement: It is wasted work.

And please please stop the LTRIM(RTRIM( ... the syntax is TRIM().

My overall impression of the code you provided is that you tried a bunch of things ... put them all together ... like making a stew. But never stopped along the way to see if any of it actually worked.

I'd suggest you throw away what you did and start over ... incrementally trying things and only using them after proving that they work.

-- 
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)