1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Stored Procedure , trouble figuring out the issues

Discussion in 'General' started by newbi, Apr 11, 2014.

  1. newbi

    newbi Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi , I am new to Oracle and cant seem to find the issue in the procedure that i have written

    Its a ASP .NET MVC 4.5 APP with ODAC linked to a Oracle 11 g

    the errors reported are {"ORA-06550: line 18, column 31:\nPL/SQL: ORA-00907: missing right parenthesis\nORA-06550: line 10, column 9:\nPL/SQL: SQL Statement ignored\nORA-06550: line 33, column 7:\nPLS-00103: Encountered the symbol \"end-of-file\" when expecting one of the following:\n\n ( begin case declare end exit for goto if loop mod null\n pragma raise return select update while with <an identifier>\n <a double-quoted delimited\nORA-06512: at \"AXIOM_DP.PKG_UPLOAD_BATCHES\", line 15\nORA-06512: at line 1"}


    and the procedure is this


    create or replace
    PACKAGE BODY PKG_UPLOAD_BATCHES AS
    PROCEDURE USP_BATCHES_INSERT(
    BATCH_SEQUENCE_ID IN NUMBER,
    STATUS IN VARCHAR2,
    PACKAGE_SEQUENCE_ID IN NUMBER,
    IS_ACTIVATED IN VARCHAR2,
    MODIFIED_BY NUMBER,
    MODIFIED_DATE IN DATE,
    resultset IN OUT SYS_REFCURSOR) AS

    BEGIN
    /* TODO implementation required */
    --select ICCIDS from (SYS.dbms_debug_vc2coll ICCID_LIST)

    EXECUTE IMMEDIATE
    'BEGIN
    FOR emp IN
    (
    SELECT dbd_iccid
    FROM DEP_ICCID_TEMP
    WHERE commission_pct is not NULL
    )
    LOOP
    BEGIN
    INSERT INTO DEP_BATCH_DETAILS(dbd_iccid,
    dbd_isactivated,
    dbd_modified_by,
    dbd_modified_on,
    dbd_status,
    dbh_seq,
    dpg_seq
    )
    SELECT (emp.dbd_iccid,
    IS_ACTIVATED,
    MODIFIED_BY,
    MODIFIED_DATE,
    STATUS,
    BATCH_SEQUENCE_ID,
    PACKAGE_SEQUENCE_ID
    )
    FROM dual
    WHERE NOT EXISTS (SELECT NULL
    FROM DEP_BATCH_DETAILS
    WHERE DEP_ICCID = emp.dbd_iccid
    )
    END
    END LOOP;
    END; ';
    COMMIT;
    null;

    END USP_BATCHES_INSERT;

    END PKG_UPLOAD_BATCHES;





    Its used to check whether each column in one table(ICCID_TEMP) exists in another table(DEP_BATCH_DETAILS) if not the column data and some other parameters are entered into the 2nd table.

    The procedure compiled fine , I use sql devloper and dont know how to run it from there so i run the procedure from C# code


    Thanks in Advance
    :confused:
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    Why
    you use execute immediate?
    Why you can't use merge?


    Code (SQL):


    CREATE OR REPLACE PACKAGE BODY PKG_UPLOAD_BATCHES
    AS
        PROCEDURE USP_BATCHES_INSERT
            (
                BATCH_SEQUENCE_ID IN NUMBER,
                STATUS IN VARCHAR2,
                PACKAGE_SEQUENCE_ID IN NUMBER,
                IS_ACTIVATED IN VARCHAR2,
                MODIFIED_BY NUMBER,
                MODIFIED_DATE IN DATE,
                resultset IN OUT SYS_REFCURSOR
            )
        AS

        BEGIN
            /* TODO implementation required */
            --select ICCIDS from (SYS.dbms_debug_vc2coll ICCID_LIST)

            MERGE INTO DEP_BATCH_DETAILS n
            USING DEP_ICCID_TEMP o
              IN (o.commission_pct IS NOT NULL
                  AND
                  n.DEP_ICCID = o.dbd_iccid          
                 )
            WHEN NOT matched THEN
              INSERT
                    (dbd_iccid,
                    dbd_isactivated,
                    dbd_modified_by,
                    dbd_modified_on,
                    dbd_status,
                    dbh_seq,
                    dpg_seq
                    )      
              VALUES
                    (
                    o.dbd_iccid,
                    o.IS_ACTIVATED,
                    o.MODIFIED_BY,
                    o.MODIFIED_DATE,
                    o.STATUS,
                    o.BATCH_SEQUENCE_ID,
                    o.PACKAGE_SEQUENCE_ID            
                    );
            COMMIT;

            END;

    END PKG_UPLOAD_BATCHES;
    /
     
     
    newbi likes this.
  3. newbi

    newbi Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I didn't use merge as both the tables have different structure and also there is no unique constraint in the table


    about execute immediate , well got that from somewhere though ... i am open to suggestions


    Thanks ,
    Aaron.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Can you provide description of the tables : DEP_BATCH_DETAILS and DEP_ICCID_TEMP ?
     
  5. newbi

    newbi Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Sure,

    here you go .


    BATCH_DETAILS

    DBD_SEQ NUMBER(20,0)
    DBH_SEQ NUMBER(20,0)
    DPG_SEQ NUMBER(20,0)
    DBD_ICCID VARCHAR2(30 BYTE)
    DBD_MSISDN VARCHAR2(30 BYTE)
    DBD_STATUS VARCHAR2(20 BYTE)
    DBD_PUK2 VARCHAR2(30 BYTE)
    DBD_MODIFIED_BY NUMBER(20,0)
    DBD_MODIFIED_ON DATE
    ATTRIBUTE1 VARCHAR2(200 BYTE)
    ATTRIBUTE2 VARCHAR2(20 BYTE)
    ATTRIBUTE3 VARCHAR2(20 BYTE)
    ATTRIBUTE4 VARCHAR2(20 BYTE)
    ATTRIBUTE5 VARCHAR2(20 BYTE)
    DBD_REMARKS VARCHAR2(500 BYTE)
    DBD_ISACTIVATED VARCHAR2(20 BYTE)


    ICCID_TEMP
    DBD_ICCID VARCHAR2(30 BYTE)
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Tell who fills in the table ICCID_TEMP.
    If record with value of a key of dbd_iccid isn't present in the table DEP_BATCH_DETAILS, then that shall be added in the table DEP_BATCH_DETAILS:
    insert into DEP_BATCH_DETAILS (DBD_ICCID) of values (
    o.dbd_iccid) ?
     
  7. newbi

    newbi Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    This works fine

    CREATE OR REPLACE PACKAGE BODY PKG_UPLOAD_BATCHES
    AS
    PROCEDURE USP_BATCHES_INSERT
    (
    BATCH_SEQUENCE_ID IN NUMBER,
    STATUS IN VARCHAR2,
    PACKAGE_SEQUENCE_ID IN NUMBER,
    IS_ACTIVATED IN VARCHAR2,
    MODIFIED_BY NUMBER,
    MODIFIED_DATE IN DATE,
    resultset IN OUT SYS_REFCURSOR
    )
    AS

    BEGIN
    /* TODO implementation required */
    --select ICCIDS from (SYS.dbms_debug_vc2coll ICCID_LIST)

    MERGE INTO DEP_BATCH_DETAILS n
    USING DEP_ICCID_TEMP o
    ON (
    n.DBD_ICCID = o.dbd_iccid
    )
    WHEN NOT matched THEN
    INSERT
    (dbd_iccid,
    dbd_isactivated,
    dbd_modified_by,
    dbd_modified_on,
    dbd_status,
    dbh_seq,
    dpg_seq
    )
    VALUES
    (
    o.dbd_iccid,
    IS_ACTIVATED,
    MODIFIED_BY,
    MODIFIED_DATE,
    STATUS,
    BATCH_SEQUENCE_ID,
    PACKAGE_SEQUENCE_ID
    );
    COMMIT;

    END;

    END PKG_UPLOAD_BATCHES;




    ICCID_TEMP is populated by a oraclebulk insert from code


    Thanks you so much for your time and help

    Really owe you one

    Cheers!
    Aaron