1 Reply Latest reply: Apr 11, 2014 7:28 AM by c921f7bc-66c8-411d-9707-9b6fff129587 RSS

    I cant seem to figure out the errors marked here

    c921f7bc-66c8-411d-9707-9b6fff129587

      Hi , I am new to ODAC and i cant seem to figure out the issues in the procedure that i have written , I use 11 g Oracle , its an MVC 4.5 app with a ODAC connection .

       

      The error 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 \n   <a double-quoted delimited\nORA-06512: at \"AXIOM_DP.PKG_UPLOAD_BATCHES\", line 15\nORA-06512: at line 1"}

        • 1. Re: I cant seem to figure out the errors marked here
          c921f7bc-66c8-411d-9707-9b6fff129587

          This is my procedure

           

           

           

           

           

          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