oracle查看某张表内存溢出,oracle - 执行'execute immediate'查询时出错 - 堆栈内存溢出...

╰+哭是因爲堅強的太久メ 2023-01-18 04:25 136阅读 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))

oracle查看某张表内存溢出,oracle - 执行'execute immediate'查询时出错 - 堆栈内存溢出...

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle内存溢出：诊断和预防策略

相关 Oracle内存溢出：原因及解决策略

相关 Oracle表空间管理问题：内存溢出示例

相关 Java内存溢出问题：堆栈溢出示例

相关 linux查看was jvm内存溢出,was manage内存溢出

相关 oracle查看某张表内存溢出,oracle - 执行'execute immediate'查询时出错 - 堆栈内存溢出...

相关 oracle ldr 到处,oracle - 从SQLoader到外部表[Oracle] - 堆栈内存溢出

相关 android内存溢出错误,android - Android Timer出现错误 - 堆栈内存溢出

相关查看服务器文件内存溢出,查看服务器日志内存溢出

相关 jvm 内存溢出-直接内存溢出