INSERT using dynamic sql

Hi there,

I'm trying to use dynamic SQL to insert some data in a table that could be different every time, in a Pro*C program. I'm coding on Unix and accessing to an Oracle 8 db. Here's what I do
(a bit long):

      sprintf(sqlText, "INSERT into %s VALUES
(:cdrCurRec.tccs_seq_id,:cdrCurRec.zip_file_name_id:cdrCurRec.ama_file_n
ame_id,:cdrCurRec.cdrv_asc_file_name_id,:cdrCurRec.cdrv_bin_file_name_id ,:cdrCurRec.gmt_load_dt_time,:cdrCurRec.gmt_prcss_dt_time,:cdrCurRec.ser v_type,:cdrCurRec.rject_reasn,:cdrCurRec.dup_orgin_seq_id,:cdrCurRec.rje ct_actn_cd,:cdrCurRec.rject_reprcss_flag,:cdrCurRec.call_id,:cdrCurRec .swtch_seq_id,:cdrCurRec.orgin_line_dig,:cdrCurRec.cic,:cdrCurRec.auth_c d_swtch_trunk,:cdrCurRec.callg_no,:cdrCurRec.acct_cd,:cdrCurRec.call_typ e,:cdrCurRec.bill_type,", tableName);

sprintf(buffer,":cdrCurRec.trnsl_no,:cdrCurRec.orgin_trunk_grp,:cdrCurRe c.term_trunk_grp,:cdrCurRec.bill_no,:cdrCurRec.orgin_dt,:cdrCurRec.dur,: cdrCurRec.orgin_time,:cdrCurRec.pay_phone_ind,:cdrCurRec.cntry_cd,:cdrCu rRec.ldwc_fl,:cdrCurRec.div_cd_fl,:cdrCurRec.swtch_2_char,:cdrCurRec.fil e_name,:cdrCurRec.osda_fl,:cdrCurRec.term_swtch,:cdrCurRec.final_term_tr unk_grp,:cdrCurRec.re_orgin_fl,:cdrCurRec.filler)");

      strcat(sqlText,buffer);

      EXEC SQL WHENEVER SQLERROR DO sqlError("INSERT CDR in dest table");

      EXEC SQL EXECUTE
        DECLARE
           cid INTEGER;
           ignore INTEGER;
        BEGIN
           cid := DBMS_SQL.OPEN_CURSOR;
           DBMS_SQL.PARSE(cid, :sqlText, DBMS_SQL.NATIVE);
           ignore := DBMS_SQL.EXECUTE(cid);
           DBMS_SQL.CLOSE_CURSOR(cid);
        END;
      END-EXEC;

All right. Basically, I put my query in a char variable (sqlText), declared as char sqlText[3500]. I also use a buffer, same declaration, and a concatenation simply to split that long query on 2 lines. All the data to be inserted is part of the same structure (cdrCurRec). The values are of correct types, and in proper order.

I always get the Oracle error "missing comma", even though I am sure that there is no comma missing anywhere.

What am I doing wrong? Anyone has an idea?

Thanks a lot!

Etienne

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Sep 07 2000 - 15:51:16 CDT