oracle查看某张表内存溢出,oracle - 执行'execute immediate'查询时出错 - 堆栈内存溢出... ╰+哭是因爲堅強的太久メ 2023-01-18 04:25 99阅读 0赞 你的动态SQL似乎逃脱了太多。 即它看起来更正确： 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; 虽然给出了你的例子，我不明白为什么这里不使用静态SQL，因为你没有动态构建SQL。 也就是你为什么不开枪： 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))
还没有评论，来说两句吧...