0 Replies Latest reply: Apr 12, 2012 7:13 AM by Mikail RSS

    No statement parsed error

    Mikail
      I have to first mention that this thing called ORACLE is totally headache. Don't really understand why people choose it. One of those stupid companies is the one where I work.It's just like an "I don't like this, I don't like that"girl. So many problems, so many things you have to coonsider, so many weird exceptions.
      Ok my problem is I have a stored procedure. It receives bunch of parameters one of which is V_CUR OUT SYS_REFCURSOR and another is a V_TYPE IN NUMBER. In the body of my procedure I check for the value of the V_TYPE, if its 0 then the procedure opens V_CUR for a query, if its 1 then the procedure inserts the values passed with rest of the parameters to a table.here are the problems I fase:

      1. If I run this procedure on SQL Developer it executes as expected, but if I do it by using ODP.NET it just takes whatever the default value is for V_TYPE parameter and just ignores the value I pass from the application.
      2. Now the wirdest thing is then if I remove the DEFAULT value from the V_TYPE I get "No statement parsed error".

      Here's my procedure just in case someone will have patience to review it:


      create or replace
      PROCEDURE "SP_IU_AGPENSIONERS"(
      V_PENSIONERID IN NUMBER DEFAULT NULL,
      V_DEPARTID IN NUMBER DEFAULT NULL,
      V_PENSIONTYPE IN NUMBER DEFAULT NULL,
      V_PLASTNAME IN VARCHAR2 DEFAULT NULL,
      V_PNAME IN VARCHAR2 DEFAULT NULL,
      V_PFATHERNAME IN VARCHAR2 DEFAULT NULL,
      V_PERIDTYPE IN NUMBER DEFAULT NULL,
      V_PERIDSER IN VARCHAR2 DEFAULT NULL,
      V_PERIDNO IN VARCHAR2 DEFAULT NULL,
      V_PGENDER IN NUMBER DEFAULT NULL,
      V_PBIRTHDAY IN DATE DEFAULT NULL,
      V_NATIONALITYID IN NUMBER DEFAULT NULL,
      V_PROTOCOLNO IN VARCHAR2 DEFAULT NULL,
      V_ORDERDATE IN DATE DEFAULT NULL,
      V_APPLYDATE IN DATE DEFAULT NULL,
      V_WEYEAR2006 IN NUMBER DEFAULT NULL,
      V_WEMONTH2006 IN NUMBER DEFAULT NULL,
      V_WEDAY2006 IN NUMBER DEFAULT NULL,
      V_ST IN NUMBER DEFAULT NULL,
      V_PFIELD IN NUMBER DEFAULT NULL,
      V_CUR OUT SYS_REFCURSOR,
      V_TYPE IN NUMBER DEFAULT 0)
      IS
      TYPE_SELECT CONSTANT NUMBER:=0;
      TYPE_INSERT CONSTANT NUMBER:=1;
      TYPE_UPDATE CONSTANT NUMBER:=2;
      TYPE_DELETE CONSTANT NUMBER:=3;
      BEGIN
      IF V_TYPE=TYPE_SELECT THEN
      OPEN V_CUR FOR SELECT PENSIONERID, DEPARTID, PENSIONTYPE, PLASTNAME, PNAME,
      PFATHERNAME, PERIDTYPE, PERIDSER, PERIDNO, PGENDER, PBIRTHDAY,
      NATIONALITYID, PROTOCOLNO, ORDERDATE, APPLYDATE, WEYEAR2006, WEMONTH2006,
      WEDAY2006, ST FROM AGAPUS.AGPENSIONERS WHERE ROWNUM<101 ORDER BY
      PENSIONERID;
      ELSIF V_TYPE=TYPE_INSERT THEN
      INSERT
      INTO
      AGPENSIONERS
      (
      PENSIONERID,
      DEPARTID,
      PENSIONTYPE,
      PLASTNAME,
      PNAME,
      PFATHERNAME,
      PERIDTYPE,
      PERIDSER,
      PERIDNO,
      PGENDER,
      PBIRTHDAY,
      NATIONALITYID,
      PROTOCOLNO,
      ORDERDATE,
      APPLYDATE,
      WEYEAR2006,
      WEMONTH2006,
      WEDAY2006,
      PFIELD
      )
      VALUES
      (
      V_PENSIONERID,
      V_DEPARTID,
      V_PENSIONTYPE,
      V_PLASTNAME,
      V_PNAME,
      V_PFATHERNAME,
      V_PERIDTYPE,
      V_PERIDSER,
      V_PERIDNO,
      V_PGENDER,
      V_PBIRTHDAY,
      V_NATIONALITYID,
      V_PROTOCOLNO,
      V_ORDERDATE,
      V_APPLYDATE,
      V_WEYEAR2006,
      V_WEMONTH2006,
      V_WEDAY2006,
      V_PFIELD
      );
      END IF;
      END;

      Edited by: Mikail on Apr 12, 2012 5:12 AM