一段insert的sql 直接执行12分钟完成 放到pro里面跑90分钟然后报错，求解

Question

附上sql吧  create table FTTH_PHOTO_QD_TEMP as
    SELECT (SELECT O.ORGNAME
              FROM DW.ZD_BAF_ORG_ORGANIZATION O
             WHERE O.TREECODE = SUBSTR(G.TREECODE, 1, 8)) DEPT1,
           (SELECT O.ORGNAME
              FROM DW.ZD_BAF_ORG_ORGANIZATION O
             WHERE O.TREECODE = SUBSTR(G.TREECODE, 1, 12)) DEPT2,
           (SELECT O.ORGNAME
              FROM DW.ZD_BAF_ORG_ORGANIZATION O
             WHERE O.TREECODE = SUBSTR(G.TREECODE, 1, 16)) DEPT3,
           (SELECT O.OBJECTNAME
              FROM DW.ZD_BAF_ORG_TREE O
             WHERE O.OBJECTID = C.DEAL_OBJECT_GROUP
               AND ROWNUM = 1) POSITION,
           (SELECT O.OBJECTNAME
              FROM DW.ZD_BAF_ORG_TREE O
             WHERE O.OBJECTID = C.DEAL_OBJECT_ID
               AND ROWNUM = 1) PERSON,
           C.GROUPID,
           TO_CHAR(WM_CONCAT(CASE
                               WHEN C.ATTACH_TYPE = 'ROOM_PIC' THEN
                                (SELECT A.ITEMNAME
                                   FROM DW.ZD_BAF_SYS_DICTIONARY_ITEM A
                                  WHERE A.DICTIONARYID = '0E8E88856222F4ADB945BD88AA5CBC6F'
                                    AND UPPER(SUBSTR(ITEMCODE, 1, INSTR(ITEMCODE, '|') - 1)) =
                                        UPPER(REPLACE(B.ATTACH_TYPE, '_'))
                                    AND UPPER(SUBSTR(ITEMCODE, INSTR(ITEMCODE, '|') + 1)) =
                                        B.REVIEW_QUESTION)
                               ELSE
                                ''
                             END)) ROOM_PIC,
           TO_CHAR(WM_CONCAT(CASE
                               WHEN C.ATTACH_TYPE = 'TERM_PIC' THEN
                                (SELECT A.ITEMNAME
                                   FROM DW.ZD_BAF_SYS_DICTIONARY_ITEM A
                                  WHERE A.DICTIONARYID = '0E8E88856222F4ADB945BD88AA5CBC6F'
                                    AND UPPER(SUBSTR(ITEMCODE, 1, INSTR(ITEMCODE, '|') - 1)) =
                                        UPPER(REPLACE(B.ATTACH_TYPE, '_'))
                                    AND UPPER(SUBSTR(ITEMCODE, INSTR(ITEMCODE, '|') + 1)) =
                                        B.REVIEW_QUESTION)
                               ELSE
                                ''
                             END)) TERM_PIC,
           TO_CHAR(WM_CONCAT(CASE
                               WHEN C.ATTACH_TYPE = 'JUMP_PIC' THEN
                                (SELECT A.ITEMNAME
                                   FROM DW.ZD_BAF_SYS_DICTIONARY_ITEM A
                                  WHERE A.DICTIONARYID = '0E8E88856222F4ADB945BD88AA5CBC6F'
                                    AND UPPER(SUBSTR(ITEMCODE, 1, INSTR(ITEMCODE, '|') - 1)) =
                                        UPPER(REPLACE(B.ATTACH_TYPE, '_'))
                                    AND UPPER(SUBSTR(ITEMCODE, INSTR(ITEMCODE, '|') + 1)) =
                                        B.REVIEW_QUESTION)
                               ELSE
                                ''
                             END)) JUMP_PIC,
           MAX(CASE
                 WHEN EXISTS (SELECT 1
                         FROM JUMP_ROOM_TERM E
                        WHERE C.ATTACH_TYPE = E.ATTACH_TYPE
                          AND E.ATTACH_TYPE = 'ROOM_PIC') THEN
                  'ROOM_PIC有照片'
               END) ROOM_PIC有照片,
           MAX(CASE
                 WHEN EXISTS (SELECT 1
                         FROM JUMP_ROOM_TERM E
                        WHERE C.ATTACH_TYPE = E.ATTACH_TYPE
                          AND E.ATTACH_TYPE = 'TERM_PIC') THEN
                  'TERM_PIC有照片'
               END) TERM_PIC有照片,
           MAX(CASE
                 WHEN EXISTS (SELECT 1
                         FROM JUMP_ROOM_TERM E
                        WHERE C.ATTACH_TYPE = E.ATTACH_TYPE
                          AND E.ATTACH_TYPE = 'JUMP_PIC') THEN
                  'JUMP_PIC有照片'
               END) JUMP_PIC有照片,
           MAX(CASE
                 WHEN C.ATTACH_TYPE = 'ROOM_PIC' THEN
                  C.REVIEW_REMARK
                 ELSE
                  ''
               END) ROOM_PIC_MARK,
           MAX(CASE
                 WHEN C.ATTACH_TYPE = 'TERM_PIC' THEN
                  C.REVIEW_REMARK
                 ELSE
                  ''
               END) TERM_PIC_MARK,
           MAX(CASE
                 WHEN C.ATTACH_TYPE = 'JUMP_PIC' THEN
                  C.REVIEW_REMARK
                 ELSE
                  ''
               END) JUMP_PIC_MARK,
           MAX(C.REVIEW_TIME) REVIEW_TIME,
           MAX(C.REVIEWER) REVIEWER,
           TO_CHAR(WM_CONCAT(CASE
                               WHEN C.ATTACH_TYPE = 'ROOM_PIC' THEN
                                (SELECT A.ITEMNAME
                                   FROM DW.ZD_BAF_SYS_DICTIONARY_ITEM A
                                  WHERE A.DICTIONARYID = '0E8E88856222F4ADB945BD88AA5CBC6F'
                                    AND UPPER(SUBSTR(ITEMCODE, 1, INSTR(ITEMCODE, '|') - 1)) =
                                        UPPER(REPLACE(F.ATTACH_TYPE, '_'))
                                    AND UPPER(SUBSTR(ITEMCODE, INSTR(ITEMCODE, '|') + 1)) =
                                        F.REVIEW_QUESTION)
                               ELSE
                                ''
                             END)) SGS_ROOM_PIC,
           TO_CHAR(WM_CONCAT(CASE
                               WHEN C.ATTACH_TYPE = 'TERM_PIC' THEN
                                (SELECT A.ITEMNAME
                                   FROM DW.ZD_BAF_SYS_DICTIONARY_ITEM A
                                  WHERE A.DICTIONARYID = '0E8E88856222F4ADB945BD88AA5CBC6F'
                                    AND UPPER(SUBSTR(ITEMCODE, 1, INSTR(ITEMCODE, '|') - 1)) =
                                        UPPER(REPLACE(F.ATTACH_TYPE, '_'))
                                    AND UPPER(SUBSTR(ITEMCODE, INSTR(ITEMCODE, '|') + 1)) =
                                        F.REVIEW_QUESTION)
                               ELSE
                                ''
                             END)) SGS_TERM_PIC,
           TO_CHAR(WM_CONCAT(CASE
                               WHEN C.ATTACH_TYPE = 'JUMP_PIC' THEN
                                (SELECT A.ITEMNAME
                                   FROM DW.ZD_BAF_SYS_DICTIONARY_ITEM A
                                  WHERE A.DICTIONARYID = '0E8E88856222F4ADB945BD88AA5CBC6F'
                                    AND UPPER(SUBSTR(ITEMCODE, 1, INSTR(ITEMCODE, '|') - 1)) =
                                        UPPER(REPLACE(F.ATTACH_TYPE, '_'))
                                    AND UPPER(SUBSTR(ITEMCODE, INSTR(ITEMCODE, '|') + 1)) =
                                        F.REVIEW_QUESTION)
                               ELSE
                                ''
                             END)) SGS_JUMP_PIC,
           TO_CHAR(WM_CONCAT(CASE
                               WHEN C.ATTACH_TYPE = 'ROOM_PIC' THEN
                                (SELECT A.ITEMNAME
                                   FROM DW.ZD_BAF_SYS_DICTIONARY_ITEM A
                                  WHERE A.DICTIONARYID = '0E8E88856222F4ADB945BD88AA5CBC6F'
                                    AND UPPER(SUBSTR(ITEMCODE, 1, INSTR(ITEMCODE, '|') - 1)) =
                                        UPPER(REPLACE(H.ATTACH_TYPE, '_'))
                                    AND UPPER(SUBSTR(ITEMCODE, INSTR(ITEMCODE, '|') + 1)) =
                                        H.REVIEW_QUESTION)
                               ELSE
                                ''
                             END)) ZZ_ROOM_PIC,
           TO_CHAR(WM_CONCAT(CASE
                               WHEN C.ATTACH_TYPE = 'TERM_PIC' THEN
                                (SELECT A.ITEMNAME
                                   FROM DW.ZD_BAF_SYS_DICTIONARY_ITEM A
                                  WHERE A.DICTIONARYID = '0E8E88856222F4ADB945BD88AA5CBC6F'
                                    AND UPPER(SUBSTR(ITEMCODE, 1, INSTR(ITEMCODE, '|') - 1)) =
                                        UPPER(REPLACE(H.ATTACH_TYPE, '_'))
                                    AND UPPER(SUBSTR(ITEMCODE, INSTR(ITEMCODE, '|') + 1)) =
                                        H.REVIEW_QUESTION)
                               ELSE
                                ''
                             END)) ZZ_TERM_PIC,
           TO_CHAR(WM_CONCAT(CASE
                               WHEN C.ATTACH_TYPE = 'JUMP_PIC' THEN
                                (SELECT A.ITEMNAME
                                   FROM DW.ZD_BAF_SYS_DICTIONARY_ITEM A
                                  WHERE A.DICTIONARYID = '0E8E88856222F4ADB945BD88AA5CBC6F'
                                    AND UPPER(SUBSTR(ITEMCODE, 1, INSTR(ITEMCODE, '|') - 1)) =
                                        UPPER(REPLACE(H.ATTACH_TYPE, '_'))
                                    AND UPPER(SUBSTR(ITEMCODE, INSTR(ITEMCODE, '|') + 1)) =
                                        H.REVIEW_QUESTION)
                               ELSE
                                ''
                             END)) ZZ_JUMP_PIC,
           F.REVIEWER SGS_REVIEWER,
           F.REVIEW_TIME SGS_REVIEWTIME
      FROM DW.ZD_BAF_ORG_ORGANIZATION G, DW.ATTACH_REVIEW_RESULT_1 C
      LEFT JOIN DW.REVIEW_DERST1 B
        ON B.ATTACH_ID = C.ATTACH_ID
       AND B.REVIEW_RESULT = 'Y'
      LEFT JOIN DW.REVIEW_DERST3 F
        ON F.ATTACH_ID = C.ATTACH_ID
       AND F.REVIEW_RESULT = 'Y'
      LEFT JOIN DW.REVIEW_DERST2 H
        ON H.ATTACH_ID = C.ATTACH_ID
       AND H.REVIEW_RESULT = 'Y'
     WHERE C.DEAL_OBJECT_GROUP = G.ORGID
     GROUP BY G.TREECODE,
              C.DEAL_OBJECT_GROUP,
              C.DEAL_OBJECT_ID,
              C.GROUPID,
              F.REVIEWER,
              F.REVIEW_TIME

Answer 1

ORA-06512: at "DW.SP_FTTH_PHOTO", line 691
之前我遇到类似问题。目测是权限问题，sql语句执行对表的操作的权限和过程里面对表的操作的权限不一样。
过程中对表的操作权限需要显示授权。
用dw用户登陆，然后grant  select on SP_FTTH_PHOTO to xxx;(xxx代表当前执行过程的用户)

Answer 2

不对吧 SP_FTTH_PHOTO这就是个存储过程名字而已啊 赋select权限干啥？

Answer 3

不对吧 SP_FTTH_PHOTO这就是个存储过程名字而已啊 赋select权限干啥？
虽然我说错了，但是思路没错。是权限问题，具体要你自己找了。
虽然只是粗略地看了下你的过程，但是你用的表都是dw用户下，是不是和pro不是一个用户？
一般这种直接执行可以，但是放到pro不能执行；
或者是pro可以执行，建成job不能执行，就是权限问题，不是有报错多少行，检查下对应句子里面涉及的权限，
或者是对表没有权限，或者是别的什么权限不够。
昨天我刚还刚处理一个过程，pro可以执行，但是建成job执行报错。

Answer 4

SP_FTTH_PHOTO并没出现在你给的sql语句中
错误不是出在这里吧