Oracle sp kann Count nicht mit data integer verwenden

  • Dieser SP wurde für SQL Server geschrieben und dann für die Verwendung in Oracle "konvertiert". Die App ist jedoch fehlerhaft, weil der Datentyp von Oracle zurückgegeben wird und MSSQL anders ist. Wie kann ich diesen SP anpassen, um für Oracle akzeptable Daten zurückzugeben?

     DECLARE
      CNT PLS_INTEGER;
    BEGIN
      SELECT COUNT(*)
        INTO CNT
        FROM OEN.DBD_C_MODULES;
    
      IF CNT > 0 THEN
        INSERT INTO OEN.DBD_C_MODULES (MODULE_ID, DESCRIPTION, ACTIVE) VALUES (11, 'Medi', 1);
        INSERT INTO OEN.DBD_C_MODULES (MODULE_ID, DESCRIPTION, ACTIVE) VALUES (15, 'Orde', 1);
        COMMIT;
      END IF;
    END;
    
    /
    
    
    CREATE OR REPLACE PROCEDURE OEN.DBD_GET_MEDICINE (
      FACILITYKEY VARCHAR2
    , DATEFROM DATE
    , DATETHRU DATE
    , UNITSTR VARCHAR2
    , INCLUDEDISCH NUMERIC
    , PATNUMBER NUMERIC
    , MEDNAME VARCHAR2
    , OCURSOR OUT SYS_REFCURSOR
    )
    AS
    BEGIN
      OPEN OCURSOR FOR
        SELECT DTR.DATE_ORDER "Date"
             , COUNT(*) "Count"
          FROM ( ( ( ( (
                        SELECT TRUNC(DATEFROM) + ROWNUM DATE_ORDER, ROWNUM + 1 DAY_ORDER FROM OEN.GEN_C_DX WHERE ROWNUM <= DATETHRU - DATEFROM
                       ) DTR
                       INNER JOIN OPTC.ORD_M_ADMIN AM ON DTR.DATE_ORDER + ( AM.ADMIN_TIME - TRUNC(AM.ADMIN_TIME) ) BETWEEN AM.START_DATE AND AM.END_DATE
                     )
                     LEFT OUTER JOIN OPTC.ORD_D_ADMIN_LOG AL ON AM.ORDER_KEY = AL.ORDER_KEY 
                   )
                   LEFT OUTER JOIN OPTC.ORD_C_ADMIN_RESULT AR ON AL.ADMIN_RESULT = AR.RESULT_KEY
                 )
                 INNER JOIN OGEN.GEN_M_PATIENT_MAST M ON AM.PAT_NUMBER = M.PAT_NUMBER AND AM.FACILITY_KEY = M.FACILITY_KEY AND ( UNITSTR IS NULL OR MOD(INSTR(UNITSTR, M.UNIT_CODE), 2) = 1 )
               )
               LEFT OUTER JOIN OGEN.GEN_M_DOCTOR_MAST DR ON M.PRIMARY_DOCTOR_KEY = DR.DOCTOR_KEY AND M.FACILITY_KEY = DR.FACILITY_KEY 
         WHERE AM.FACILITY_KEY IN (SELECT * FROM TABLE(OGEN.COMMA_TO_TABLE(FACILITYKEY)))
           AND ( AM.PAT_NUMBER = PATNUMBER OR PATNUMBER = -1 )
           AND ( MEDNAME IS NULL OR AM.ORDER_NAME LIKE ( '%' || MEDNAME || '%' ) )
           AND AM.PRN = 0
           AND OPTC.ORD_IS_ADMIN(AM.START_DATE, DTR.DATE_ORDER, AM.DAYS_REQ) = 1
           AND NOT EXISTS ( SELECT AL1.ORDER_KEY
                              FROM OPTC.ORD_D_ADMIN_LOG AL1 
                             WHERE AL1.ORDER_KEY = AM.ORDER_KEY
                               AND INSTR('01234', CAST(ABS(AL1.ADMIN_RESULT) AS VARCHAR2(2))) > 0 )
           AND NOT EXISTS ( SELECT OM.ORDER_KEY
                              FROM OPC.ORD_M_ORDER OM
                             WHERE OM.ORDER_STATUS = 0
                               AND OM.HOLD_ON_FROM = AM.ORDER_KEY
                               AND DTR.DATE_ORDER BETWEEN OM.START_DATE AND OM.END_DATE )
           AND ( ( INCLUDEDISCH = 0 ) OR ( 1 = ( CASE WHEN ( INCLUDEDISCH = 1 ) AND M.DISCHARGE_DATE IS NULL THEN 1 WHEN ( INCLUDEDISCH = 2 ) AND ( M.DISCHARGE_DATE IS NOT NULL ) THEN 1 ELSE 0 END ) ) )
          GROUP BY DTR.DATE_ORDER;
    END;
     
    22 November 2011
    skaffmanRolf
1 answer
  • Ihre als NUMERIC definierten Eingabeparameter sollten NUMBER sein, zB:

     CREATE OR REPLACE PROCEDURE OEN.DBD_GET_MEDICINE (
      FACILITYKEY  IN  VARCHAR2,
      DATEFROM     IN  DATE,
      DATETHRU     IN  DATE,
      UNITSTR      IN  VARCHAR2,
      INCLUDEDISCH IN  NUMBER,
      PATNUMBER    IN  NUMBER,
      MEDNAME      IN  VARCHAR2,
      OCURSOR      OUT SYS_REFCURSOR
    )
     
    22 November 2011
    wweicker