Can't run DDL statement in an Oracle query

Question

I need to pull a list of distinct formatted source names for a query. I wrote a function to do this and it returns the data I want, however, because it truncates and writes to a table it cannot be used within a query in Oracle. So I am stumped on how to make this work within a query. Here is the code.

FUNCTION GET_POD_SOURCE_FROM_APP_NO
     (I_APPL_ID_SEQ IN WRD_APPLICATIONS.APPL_ID_SEQ%TYPE,
      I_DELIMITER IN VARCHAR2 DEFAULT ';')

   RETURN VARCHAR2
   IS

   CURSOR C1 IS
    SELECT DISTINCT SOUR.SOUR_ID_SEQ,
           SRNM.SRNM_NM,
           SOUR.FORK_NM,
           DECODE(POD.UNNAMED_TRIBUTARY,
                  'N',
                  NULL,
                  'Y',
                  'UNNAMED TRIBUTARY') POD_UT,
           MRTP.DESCR POD_MINORTYPE,
           DECODE(POD.MAJOR_TYPE,
                  'S',
                  'SURFACE WATER',
                  'G',
                  'GROUNDWATER',
                  NULL,
                  NULL) POD_MAJORTYPE
      FROM WRD_SOURCES             SOUR,
           WRD_SOURCE_NAMES        SRNM,
           WRD_POINT_OF_DIVERSIONS POD,
           WRD_MINOR_TYPES         MRTP,
           WRD_VERSION_APPLICATION_XREFS VAX
     WHERE SOUR.SOUR_ID_SEQ = POD.SOUR_ID_SEQ
       AND SOUR.SRNM_ID_SEQ = SRNM.SRNM_ID_SEQ
       AND POD.MRTP_CD = MRTP.MRTP_CD(+)
       AND POD.WRGT_ID_SEQ = VAX.WRGT_ID_SEQ
       AND POD.VERS_ID_SEQ = VAX.VERS_ID_SEQ
       AND VAX.APPL_ID_SEQ = I_APPL_ID_SEQ;


   CURSOR C2 IS
    SELECT DISTINCT TS.SOURCE_FULL
    FROM WRD.TEMP_SOURCE TS
    ORDER BY TS.SOURCE_FULL;



  C1_R    C1%ROWTYPE;
  C2_R    C2%ROWTYPE;
  ROW_CNT PLS_INTEGER := 0;
  RTN_VAL VARCHAR2(4000);
  SRC_NAME VARCHAR2(400);

BEGIN

  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_SOURCE';

  FOR C1_R IN C1 LOOP

    IF C1_R.POD_UT IS NOT NULL THEN
      SRC_NAME := C1_R.POD_UT || ' OF ';
    END IF;

    SRC_NAME := SRC_NAME || C1_R.SRNM_NM;

    IF C1_R.FORK_NM IS NOT NULL THEN
      SRC_NAME := SRC_NAME ||', '|| C1_R.FORK_NM;
    END IF;

    IF C1_R.POD_MINORTYPE IS NOT NULL THEN
      SRC_NAME := C1_R.POD_MINORTYPE || ', ' || SRC_NAME;
    END IF;

    EXECUTE IMMEDIATE 'INSERT INTO WRD.TEMP_SOURCE VALUES (SRC_NAME)';
    EXECUTE IMMEDIATE 'COMMIT';

    SRC_NAME := '';
  END LOOP;


  FOR C2_R IN C2 LOOP
    ROW_CNT := ROW_CNT + 1;

    IF (ROW_CNT < 2) THEN
      RTN_VAL := C2_R.SOURCE_FULL;
    ELSE
      RTN_VAL := SUBSTR(RTN_VAL, 1, 3600) || I_DELIMITER || ' ' || C2_R.SOURCE_FULL;
    END IF;
  END LOOP;

  RETURN(TRIM(RTN_VAL));

END GET_POD_SOURCE_FROM_APP_NO;

Thank you.

Answer 1

Here is a pure SQL version which does away with the need for temp tables and unnecessary DDL.

with C1_R as ( 
   SELECT DISTINCT SOUR.SOUR_ID_SEQ,
           SRNM.SRNM_NM,
           SOUR.FORK_NM,
           DECODE(POD.UNNAMED_TRIBUTARY,
                  'N',
                  NULL,
                  'Y',
                  'UNNAMED TRIBUTARY') POD_UT,
           MRTP.DESCR POD_MINORTYPE,
           DECODE(POD.MAJOR_TYPE,
                  'S',
                  'SURFACE WATER',
                  'G',
                  'GROUNDWATER',
                  NULL,
                  NULL) POD_MAJORTYPE
      FROM WRD_SOURCES             SOUR,
           WRD_SOURCE_NAMES        SRNM,
           WRD_POINT_OF_DIVERSIONS POD,
           WRD_MINOR_TYPES         MRTP,
           WRD_VERSION_APPLICATION_XREFS VAX
     WHERE SOUR.SOUR_ID_SEQ = POD.SOUR_ID_SEQ
       AND SOUR.SRNM_ID_SEQ = SRNM.SRNM_ID_SEQ
       AND POD.MRTP_CD = MRTP.MRTP_CD(+)
       AND POD.WRGT_ID_SEQ = VAX.WRGT_ID_SEQ
       AND POD.VERS_ID_SEQ = VAX.VERS_ID_SEQ
       AND VAX.APPL_ID_SEQ = I_APPL_ID_SEQ;
 ) , fmt as (
 select  distinct  nvl2(C1_R.POD_MINORTYPE, C1_R.POD_MINORTYPE || ', ', null)
            || nvl2(C1_R.POD_UT  C1_R.POD_UT || ' OF ', null)
            || C1_R.SRNM_NM
            || nvl2(C1_R.FORK_NM IS, ', '|| C1_R.FORK_NM, null)
           as SRC_NAME;
from C1_R
)
select listagg(src_name, '|') within group (order by source_name) as rtn_val
from fmt
/

The one thing this doesn't do nicely is handle concatenated strings exceeding the 4000 char limit. In 12cR2 Oracle gave us ON OVERFLOW TRUNCATE for the listagg() clause but hardly anybody is yet on 12cR2; Stew Ashton has a workaround for earlier versions. Check it out.

Answer 2

If Wernfried's solution doesn't work, I just wanted to point out that you can do simple formatting like that without resorting to a temporary table. I didn't want to mess with your formatting, so I left your second loop in place, but normally I'd just use LISTAGG for that too, and not use PLSQL for this at all.

FUNCTION GET_POD_SOURCE_FROM_APP_NO
     (I_APPL_ID_SEQ IN WRD_APPLICATIONS.APPL_ID_SEQ%TYPE,
      I_DELIMITER IN VARCHAR2 DEFAULT ';')

   RETURN VARCHAR2
   IS

   CURSOR C1 IS
    SELECT 
        CASE WHEN POD_MINORTYPE is not null 
            THEN POD_MINORTYPE || ', ' 
            ELSE NULL END 
        || CASE WHEN POD_UT is not null 
            THEN POD_UT || ' OF ' 
            ELSE NULL END 
        || SRNM_NM
        || CASE WHEN FORK_NM is not null 
            THEN ', '|| FORK_NM
            ELSE NULL END 
        AS SOURCE_FULL
    FROM (
    SELECT DISTINCT SOUR.SOUR_ID_SEQ,
               SRNM.SRNM_NM,
               SOUR.FORK_NM,
               DECODE(POD.UNNAMED_TRIBUTARY,
                      'N',
                      NULL,
                      'Y',
                      'UNNAMED TRIBUTARY') POD_UT,
               MRTP.DESCR POD_MINORTYPE,
               DECODE(POD.MAJOR_TYPE,
                      'S',
                      'SURFACE WATER',
                      'G',
                      'GROUNDWATER',
                      NULL,
                      NULL) POD_MAJORTYPE
          FROM WRD_SOURCES             SOUR,
               WRD_SOURCE_NAMES        SRNM,
               WRD_POINT_OF_DIVERSIONS POD,
               WRD_MINOR_TYPES         MRTP,
               WRD_VERSION_APPLICATION_XREFS VAX
         WHERE SOUR.SOUR_ID_SEQ = POD.SOUR_ID_SEQ
           AND SOUR.SRNM_ID_SEQ = SRNM.SRNM_ID_SEQ
           AND POD.MRTP_CD = MRTP.MRTP_CD(+)
           AND POD.WRGT_ID_SEQ = VAX.WRGT_ID_SEQ
           AND POD.VERS_ID_SEQ = VAX.VERS_ID_SEQ
           AND VAX.APPL_ID_SEQ = I_APPL_ID_SEQ
    ) TS;

  C1_R    C1%ROWTYPE;
  ROW_CNT PLS_INTEGER := 0;
  RTN_VAL VARCHAR2(4000);
  SRC_NAME VARCHAR2(400);

BEGIN

  FOR C1_R IN C1 LOOP
    ROW_CNT := ROW_CNT + 1;

    IF (ROW_CNT < 2) THEN
      RTN_VAL := C1_R.SOURCE_FULL;
    ELSE
      RTN_VAL := SUBSTR(RTN_VAL, 1, 3600) || I_DELIMITER || ' ' || C1_R.SOURCE_FULL;
    END IF;
  END LOOP;

  RETURN(TRIM(RTN_VAL));

END GET_POD_SOURCE_FROM_APP_NO;
/

Answer 3

These two command don't need to be dynamic:

EXECUTE IMMEDIATE 'INSERT INTO WRD.TEMP_SOURCE VALUES (SRC_NAME)';
EXECUTE IMMEDIATE 'COMMIT';

Just run INSERT INTO WRD.TEMP_SOURCE VALUES (SRC_NAME);

I think the main problem is your TRUNCATE ... and COMMIT. Both commands ends a transaction, this is not allowed within a query. I assume table TEMP_SOURCE is a GLOBAL TEMPORARY Table.

Change your TRUNCATE into DELETE FROM WRD.TEMP_SOURCE; and remove the COMMIT. I am not sure but then it should work (I did not test it).