Getting error while executing 'execute immediate ' query

Question

I have to insert data of one table to another one. I have create a procedure for. In procedure one varchar2 variable Code is below-

create or replace procedure proc_OneTimeLoad

 as
  query varchar2(5000);

begin

  OneTimeLoad_query := 'INSERT INTO trgt_table
              (Originterminal,
               Destinationterminal,
               Scheduledepartdate,
               Scheduledeparttime,
               Runtypenbr,
               DOW,
               Sched_Slip_Arr_Tm,
               DAY,
               Sched_Arr_Tm,
               Drivercode,
               Puprunflag,
               Sched_Cd,
               Status_Cd)

             SELECT Orgn_Term_Cd,
   Dest_Term_Cd,
   sysdate,
   Sched_Dep_Tm,
   Run_Nbr,
   DOW,
   to_date(
   LPAD(nvl(substr(tn.Sched_Slip_Arr_Tm,1,length(Sched_Slip_Arr_Tm)- 2),0),2,0)
   ||'''':''''||
   RPAD(nvl(TRUNC(TO_NUMBER(substr(Sched_Slip_Arr_Tm,3,length(Sched_Slip_Arr_Tm) - 2)) * .60,0),0),2,0)      
   ||'''':00''''
   ,''''HH24:MI:SS'''')
   as Sched_Slip_Arr_Tm,
   Day,
   to_date(
   LPAD(nvl(substr(tn.sched_arr_tm,1,length(sched_arr_tm)- 2),0),2,0)
   ||'''':''''||
   RPAD(nvl(TRUNC(TO_NUMBER(substr(sched_arr_tm,3,length(sched_arr_tm) - 2)) * .60,0),0),2,0)      
   ||'''':00''''
   ,''''HH24:MI:SS'''') 
   as Sched_Arr_Tm,
   Drvr_Cd,
   Pup_Run_Flg,
   ''''B'''',
   ''''1P''''
FROM src_table tn
WHERE tn.dow = to_char(sysdate - 1, ''''D'''')
AND tn.sched_dep_tm between
       TO_NUMBER(SUBSTR(TO_CHAR(sysdate, ''''HH24:MI:SS''''), 1, 2) ||
                 TRUNC(SUBSTR(TO_CHAR(sysdate, ''''HH24:MI:SS''''), 4, 2) * 100/60,0)) 
  AND
       TO_NUMBER(TO_NUMBER(SUBSTR(TO_CHAR(sysdate, ''''HH24:MI:SS''''), 1, 2)) + 12 ||
                 TRUNC(SUBSTR(TO_CHAR(sysdate, '''' HH24 :MI :SS ''''), 4, 2) * 100 / 60, 0))';

  execute immediate query;


  dbms_output.put_line(' Data loaded successfully');

end;

Here i am getting error while executing this procedure 'Missing right parenthesis. I think there is some problem of [']. I want to user' ' in query to concatenate string. just like for getting time

where time=to_date(HH||':'||mi||':'||ss,''HH24:MI:SS')

Please help me somebody.

When you're stuck you should simplify your problem as much as possible. That process will usually solve the problem for you. In this case, you are using 4 quotation marks when you should use 2. Or to simplify things even more, use the alternative quoting mechanism. For example, query := q'!select date '2000-01-01' ...!'. Also, you should accept some of the answers to your previous questions, to let other people know the answer is correct. — Jon Heller
– Jon Heller, Commented Feb 1, 2013 at 4:09
if i am using only '' then getting error that these are not valid bind variables. — Bit_hunter
– Bit_hunter, Commented Feb 1, 2013 at 6:24
@user1965381 in your sql i see no reason for dynamic SQL here as you dont appear to be altering the string at all. but all of your quotes are doubled up e.g. ||'''':''''|| should be ||'':''|| etc. — DazzaL
– DazzaL, Commented Feb 1, 2013 at 7:55

DazzaL · Accepted Answer · 2013-02-01 08:06:34Z

your dynamic SQL seems escaped too much. i.e. it looks more correct as:

   OneTimeLoad_query := 'INSERT INTO trgt_table
               (Originterminal,
                Destinationterminal,
                Scheduledepartdate,
                Scheduledeparttime,
                Runtypenbr,
                DOW,
                Sched_Slip_Arr_Tm,
                DAY,
                Sched_Arr_Tm,
                Drivercode,
                Puprunflag,
                Sched_Cd,
                Status_Cd)    
              SELECT Orgn_Term_Cd,
    Dest_Term_Cd,
    sysdate,
    Sched_Dep_Tm,
    Run_Nbr,
    DOW,
    to_date(
    LPAD(nvl(substr(tn.Sched_Slip_Arr_Tm,1,length(Sched_Slip_Arr_Tm)- 2),0),2,0)
    ||'':''||
    RPAD(nvl(TRUNC(TO_NUMBER(substr(Sched_Slip_Arr_Tm,3,length(Sched_Slip_Arr_Tm) - 2)) * .60,0),0),2,0)
    ||'':00''
    ,''HH24:MI:SS'')
    as Sched_Slip_Arr_Tm,
    Day,
    to_date(
    LPAD(nvl(substr(tn.sched_arr_tm,1,length(sched_arr_tm)- 2),0),2,0)
    ||'':''||
    RPAD(nvl(TRUNC(TO_NUMBER(substr(sched_arr_tm,3,length(sched_arr_tm) - 2)) * .60,0),0),2,0)
    ||'':00''
    ,''HH24:MI:SS'')
    as Sched_Arr_Tm,
    Drvr_Cd,
    Pup_Run_Flg,
    ''B'',
    ''1P''
 FROM src_table tn
 WHERE tn.dow = to_char(sysdate - 1, ''D'')
 AND tn.sched_dep_tm between
        TO_NUMBER(SUBSTR(TO_CHAR(sysdate, ''HH24:MI:SS''), 1, 2) ||
                  TRUNC(SUBSTR(TO_CHAR(sysdate, ''HH24:MI:SS''), 4, 2) * 100/60,0))
   AND
        TO_NUMBER(TO_NUMBER(SUBSTR(TO_CHAR(sysdate, ''HH24:MI:SS''), 1, 2)) + 12 ||
                  TRUNC(SUBSTR(TO_CHAR(sysdate, ''HH24:MI:SS ''), 4, 2) * 100 / 60, 0))';

   execute immediate OneTimeLoad_query;

although given your example i don't see why static SQL isn't used here, as your not dynamically building the SQL. ie why don't you just fire:

INSERT INTO trgt_table
              (Originterminal,
               Destinationterminal,
               Scheduledepartdate,
               Scheduledeparttime,
               Runtypenbr,
               DOW,
               Sched_Slip_Arr_Tm,
               DAY,
               Sched_Arr_Tm,
               Drivercode,
               Puprunflag,
               Sched_Cd,
               Status_Cd)
             SELECT Orgn_Term_Cd,
   Dest_Term_Cd,
   sysdate,
   Sched_Dep_Tm,
   Run_Nbr,
   DOW,
   to_date(
   LPAD(nvl(substr(tn.Sched_Slip_Arr_Tm,1,length(Sched_Slip_Arr_Tm)- 2),0),2,0)
   ||':'||
   RPAD(nvl(TRUNC(TO_NUMBER(substr(Sched_Slip_Arr_Tm,3,length(Sched_Slip_Arr_Tm) - 2)) * .60,0),0),2,0)
   ||':00'
   ,'HH24:MI:SS')
   as Sched_Slip_Arr_Tm,
   Day,
   to_date(
   LPAD(nvl(substr(tn.sched_arr_tm,1,length(sched_arr_tm)- 2),0),2,0)
   ||':'||
   RPAD(nvl(TRUNC(TO_NUMBER(substr(sched_arr_tm,3,length(sched_arr_tm) - 2)) * .60,0),0),2,0)
   ||':00'
   ,'HH24:MI:SS')
   as Sched_Arr_Tm,
   Drvr_Cd,
   Pup_Run_Flg,
   'B',
   '1P'
FROM src_table tn
WHERE tn.dow = to_char(sysdate - 1, 'D')
AND tn.sched_dep_tm between
       TO_NUMBER(SUBSTR(TO_CHAR(sysdate, 'HH24:MI:SS'), 1, 2) ||
                 TRUNC(SUBSTR(TO_CHAR(sysdate, 'HH24:MI:SS'), 4, 2) * 100/60,0))
  AND
       TO_NUMBER(TO_NUMBER(SUBSTR(TO_CHAR(sysdate, 'HH24:MI:SS'), 1, 2)) + 12 ||
                 TRUNC(SUBSTR(TO_CHAR(sysdate, 'HH24:MI:SS '), 4, 2) * 100 / 60, 0))

Collectives™ on Stack Overflow

Getting error while executing 'execute immediate ' query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related