0 Replies Latest reply on Dec 1, 2003 1:49 PM by 251531

    OraParamArray Processing with PL/SQL Procedure

    251531
      Hello,
      I keep getting the following error when trying to execute my code (in an asp page). I have attached code snippets below. It works fine when I use an Update statement rather than a call to the procedure. My data types do not give me an error when I make a call to the procedure using single params rather than OraParamArrays with the AddTable. When I use CreateSQL instead of ExecuteSQL it doesn't give any errors but it also doesn't change the data. Any Ideas?

      Error:
      Oracle Automation error '800a01b8'

      SQL execution error, ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CHANGE' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CHANGE' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CHANGE' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CHANGE' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CHANGE' ORA-06550: line 1, column 7: PLS-003

      Asp page call:
      Set OraParameters = OraDatabase.Parameters
      OraDatabase.Parameters.AddTable "P_TELEPHONE_LIST_ID", ORAPARM_INPUT, 2, CLNG(request("rowlength")+1)
      OraParameters.AddTable "P_TELEPHONE_NO", ORAPARM_INPUT, 1, CLNG(request("rowlength")+1), 20
      OraParameters.AddTable "P_COMPANY_TX", ORAPARM_INPUT, 1, CLNG(request("rowlength")+1), 30
      OraParameters.AddTable "P_RSID_CD", ORAPARM_INPUT, 1, CLNG(request("rowlength")+1), 10
      OraParameters.AddTable "P_TYPE_CD", ORAPARM_INPUT, 96, CLNG(request("rowlength")+1), 1
      OraParameters.AddTable "P_HUNT_CD", ORAPARM_INPUT, 1, CLNG(request("rowlength")+1), 3
      OraParameters.AddTable "P_USER_LOGON_TX", ORAPARM_INPUT, 1, CLNG(request("rowlength")+1), 40

      FOR i = 0 to request("rowlength")
      OraParameters("P_TELEPHONE_LIST_ID").put_value nvl(request("p_telephone_id_" & i),0),i
      OraParameters("P_TELEPHONE_NO").put_value nvl(trim(request("P_TELEPHONE_TX_" & i)),0),i
      OraParameters("P_COMPANY_TX").put_value replace(nvl(trim(request("P_COMPANY_TX_" & i)),0),"'","''"),i
      OraParameters("P_RSID_CD").put_value nvl(request("P_RSID_CD_" & i),0),i
      OraParameters("P_TYPE_CD").put_value nvl(request("P_TYPE_CD_" & i),0),i
      OraParameters("P_HUNT_CD").put_value nvl(request("P_HUNT_CD_" & i),""),i
      OraParameters("P_USER_LOGON_TX").put_value USER_LOGON,i
      NEXT
      OraDatabase.ExecuteSQL "Begin TELEPHONE_LIST_PCK.CHANGE(:P_TELEPHONE_LIST_ID, :P_TELEPHONE_NO, :P_COMPANY_TX, :P_RSID_CD, :P_TYPE_CD, :P_HUNT_CD, :P_USER_LOGON_TX); end;"
      OraParameters.REMOVE "P_TELEPHONE_LIST_ID"
      OraParameters.REMOVE "P_TELEPHONE_NO"
      OraParameters.REMOVE "P_COMPANY_TX"
      OraParameters.REMOVE "P_RSID_CD"
      OraParameters.REMOVE "P_TYPE_CD"
      OraParameters.REMOVE "P_HUNT_CD"
      OraParameters.REMOVE "P_USER_LOGON_TX"


      Package procedure:
      PROCEDURE CHANGE (
      P_TELEPHONE_LIST_ID TELEPHONE_LIST.TELEPHONE_LIST_ID%TYPE,
      P_TELEPHONE_NO TELEPHONE_LIST.TELEPHONE_NO%TYPE,
      P_COMPANY_TX TELEPHONE_LIST.COMPANY_TX%TYPE,
      P_RSID_CD TELEPHONE_LIST.RSID_CD%TYPE,
      P_TYPE_CD TELEPHONE_LIST.TYPE_CD%TYPE,
      P_HUNT_CD TELEPHONE_LIST.HUNT_CD%TYPE,
      P_USER_LOGON_TX USERS.USER_LOGON_TX%TYPE) IS

      V_RECORD TELEPHONE_LIST%ROWTYPE;

      BEGIN

      SELECT * INTO V_RECORD FROM TELEPHONE_LIST WHERE TELEPHONE_LIST_ID= P_TELEPHONE_LIST_ID;

      IF NVL(P_TELEPHONE_NO,'.') <> NVL(V_RECORD.TELEPHONE_NO,'.') THEN
      UPDATE TELEPHONE_LIST SET TELEPHONE_NO = replace(replace(replace(replace(P_TELEPHONE_NO,'-',''),')',''),'(',''),'.','')
      WHERE TELEPHONE_LIST_ID= P_TELEPHONE_LIST_ID;
      END IF;
      -------------------
      IF NVL(P_COMPANY_TX,'.') <> NVL(V_RECORD.COMPANY_TX,'.') THEN
      UPDATE TELEPHONE_LIST SET COMPANY_TX = UPPER(P_COMPANY_TX)
      WHERE TELEPHONE_LIST_ID= P_TELEPHONE_LIST_ID;
      END IF;
      -------------------
      IF NVL(P_RSID_CD,'.') <> NVL(V_RECORD.RSID_CD,'.') THEN
      UPDATE TELEPHONE_LIST SET RSID_CD = UPPER(P_RSID_CD)
      WHERE TELEPHONE_LIST_ID= P_TELEPHONE_LIST_ID;
      END IF;
      -------------------
      -------------------
      IF NVL(P_TYPE_CD,'.') <> NVL(V_RECORD.TYPE_CD,'.') THEN
      UPDATE TELEPHONE_LIST SET TYPE_CD = UPPER(P_TYPE_CD)
      WHERE TELEPHONE_LIST_ID= P_TELEPHONE_LIST_ID;
      END IF;
      -------------------
      -------------------
      IF NVL(P_HUNT_CD,'.') <> NVL(V_RECORD.HUNT_CD,'.') THEN
      UPDATE TELEPHONE_LIST SET HUNT_CD = UPPER(P_HUNT_CD)
      WHERE TELEPHONE_LIST_ID= P_TELEPHONE_LIST_ID;
      END IF;
      -------------------
      -------------------MAKE HISTORY RECORD
      INSERT INTO HISTORY(RSID_CD,TELEPHONE_TX,USER_ID,ACTION_TX)
      VALUES(P_RSID_CD,P_TELEPHONE_NO,P_USER_LOGON_TX,'CHANGE');

      COMMIT;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      ADD_RECORD (
      P_TELEPHONE_NO,
      P_COMPANY_TX,
      P_RSID_CD,
      P_TYPE_CD,
      P_HUNT_CD,
      P_USER_LOGON_TX);
      WHEN OTHERS THEN
      LOG_ERROR('TELEPHONE_LIST.',-20000,SQLERRM);
      RAISE_APPLICATION_ERROR(-20000,SQLERRM);
      END CHANGE;

      Tables:

      Telephone_list
      Name Null? Type
      ----------------------- -------- -------------
      TELEPHONE_LIST_ID NOT NULL NUMBER
      TELEPHONE_NO VARCHAR2(20)
      RSID_CD VARCHAR2(10)
      MOD_DT DATE
      TYPE_CD CHAR(1)
      COMPANY_TX VARCHAR2(30)
      HUNT_CD VARCHAR2(3)

      users
      Name Null? Type
      ----------------------- -------- -------------
      USER_ID NOT NULL NUMBER
      FIRST_NAME_TX VARCHAR2(50)
      LAST_NAME_TX VARCHAR2(50)
      SSN_TX CHAR(9)
      ACCESS_LEVEL_ID NUMBER
      STATUS_TX CHAR(1)
      MOD_DT DATE
      USER_LOGON_TX VARCHAR2(40)