CODEWITHSUNDEEP
oracle-database
Bit_hunter
Bit_hunter

Reputation: 819

Getting error while executing 'execute immediate ' query

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.

Upvotes: 0

Views: 790

Answers (1)

DazzaL
DazzaL

Reputation: 21993

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

Upvotes: 2

Related Questions