Calling a StoredProcedure

[nebulom]

I need help on calling this "what they call" StoredProcedure. They assume my code on front end is invalid so I'm going to test it on SQL. This is Oracle though.

CREATE OR REPLACE FUNCTION "FNC_ZUP22221" (
  p_ImportType IN NUMBER
) 
RETURN NUMBER IS

/*
 * The possible return values are the following:
 *  1 = Insert and Delete executed successfully
 *  2 = One or more rows in TBL_TEMP_ZUP22221 exist in TBL_VEHICLE
 *  3 = p_ImportType is neither 1, 2 nor 3
 *  4 = An unhandled exception is thrown
 */

v_Result    NUMBER;
v_RowCount  NUMBER;

BEGIN
  SAVEPOINT SP;

  IF p_ImportType = 1 THEN
    SELECT COUNT(A.PART_NUMBER)
    INTO v_RowCount
    FROM TBL_TEMP_ZUP22221 A
    INNER JOIN TBL_VEHICLE B
    ON A.PART_NUMBER = B.PART_NUMBER
      AND A.PS = B.PS
      AND A.START_DATE = B.START_DATE
      AND A.END_DATE = B.END_DATE
      AND A.NAMC = B.NAMC;
  
    IF v_RowCount > 0 THEN
      v_Result := 2;
    ELSE
      INSERT INTO TBL_PLAN_STORE(
        PART_NUMBER,
        PS,
        START_DATE,
        END_DATE,
        WRKG_DAYS,
        QTY_PER_CONT,
        TQN,
        NAMC,
        COC,
        C_OR_D,
        TP,
        CUST_ORDER_QTY,
        DOCK,
        KANBAN,
        PART_DESCRIPTION,
        STORE_ADDRESS,
        HMM,
        SUPPLIER_NAME,
        MAIN_ROUTE_NO,
        VALID,
        NAME_OF_TMM,
        TEL,
        CROSS_D1,
        EDI,
        UPD_TIMESTAMP,
        USER_NAME,
        ORDER_TYPE)
      SELECT 
        PART_NUMBER,
        PS,
        START_DATE,
        END_DATE,
        WRKG_DAYS,
        QTY_PER_CONT,
        TQN,
        NAMC,
        COC,
        C_OR_D,
        TP,
        CUST_ORDER_QTY,
        DOCK,
        KANBAN,
        PART_DESCRIPTION,
        STORE_ADDRESS,
        HMM,
        SUPPLIER_NAME,
        MAIN_ROUTE_NO,
        VALID,
        NAME_OF_TMM,
        TEL,
        CROSS_D1,
        EDI,
        UPD_TIMESTAMP,
        USER_NAME,
        'V'
      FROM TBL_TEMP_ZUP22221;
      
      v_Result := 1;
    END IF;
  ELSIF p_ImportType = 2 THEN
    INSERT INTO TBL_PLAN_STORE(
      PART_NUMBER,
      PS,
      START_DATE,
      END_DATE,
      WRKG_DAYS,
      QTY_PER_CONT,
      TQN,
      NAMC,
      COC,
      C_OR_D,
      TP,
      CUST_ORDER_QTY,
      DOCK,
      KANBAN,
      PART_DESCRIPTION,
      STORE_ADDRESS,
      HMM,
      SUPPLIER_NAME,
      MAIN_ROUTE_NO,
      VALID,
      NAME_OF_TMM,
      TEL,
      CROSS_D1,
      EDI,
      UPD_TIMESTAMP,
      USER_NAME,
      ORDER_TYPE)
    SELECT 
      A.PART_NUMBER,
      A.PS,
      A.START_DATE,
      A.END_DATE,
      A.WRKG_DAYS,
      A.QTY_PER_CONT,
      A.TQN,
      A.NAMC,
      A.COC,
      A.C_OR_D,
      A.TP,
      A.CUST_ORDER_QTY,
      A.DOCK,
      A.KANBAN,
      A.PART_DESCRIPTION,
      A.STORE_ADDRESS,
      A.HMM,
      A.SUPPLIER_NAME,
      A.MAIN_ROUTE_NO,
      A.VALID,
      A.NAME_OF_TMM,
      A.TEL,
      A.CROSS_D1,
      A.EDI,
      A.UPD_TIMESTAMP,
      A.USER_NAME,
      'V'
    FROM TBL_TEMP_ZUP22221 A
    INNER JOIN TBL_VEHICLE B
    ON A.PART_NUMBER != B.PART_NUMBER
      AND A.PS != B.PS
      AND A.START_DATE != B.START_DATE
      AND A.END_DATE != B.END_DATE
      AND A.NAMC != B.NAMC;
    
    INSERT INTO TBL_PLAN_STORE(
      PART_NUMBER,
      PS,
      START_DATE,
      END_DATE,
      WRKG_DAYS,
      QTY_PER_CONT,
      TQN,
      NAMC,
      COC,
      C_OR_D,
      TP,
      CUST_ORDER_QTY,
      DOCK,
      KANBAN,
      PART_DESCRIPTION,
      STORE_ADDRESS,
      HMM,
      SUPPLIER_NAME,
      MAIN_ROUTE_NO,
      VALID,
      NAME_OF_TMM,
      TEL,
      CROSS_D1,
      EDI,
      UPD_TIMESTAMP,
      USER_NAME,
      ORDER_TYPE)
    SELECT 
      PART_NUMBER,
      PS,
      START_DATE,
      END_DATE,
      WRKG_DAYS,
      QTY_PER_CONT,
      TQN,
      NAMC,
      COC,
      C_OR_D,
      TP,
      CUST_ORDER_QTY,
      DOCK,
      KANBAN,
      PART_DESCRIPTION,
      STORE_ADDRESS,
      HMM,
      SUPPLIER_NAME,
      MAIN_ROUTE_NO,
      VALID,
      NAME_OF_TMM,
      TEL,
      CROSS_D1,
      EDI,
      UPD_TIMESTAMP,
      USER_NAME,
      'V'
    FROM TBL_VEHICLE;        
          
    v_Result := 1;
  ELSIF p_ImportType = 3 THEN
    INSERT INTO TBL_PLAN_STORE(
      PART_NUMBER,
      PS,
      START_DATE,
      END_DATE,
      WRKG_DAYS,
      QTY_PER_CONT,
      TQN,
      NAMC,
      COC,
      C_OR_D,
      TP,
      CUST_ORDER_QTY,
      DOCK,
      KANBAN,
      PART_DESCRIPTION,
      STORE_ADDRESS,
      HMM,
      SUPPLIER_NAME,
      MAIN_ROUTE_NO,
      VALID,
      NAME_OF_TMM,
      TEL,
      CROSS_D1,
      EDI,
      UPD_TIMESTAMP,
      USER_NAME,
      ORDER_TYPE)
    SELECT 
      PART_NUMBER,
      PS,
      START_DATE,
      END_DATE,
      WRKG_DAYS,
      QTY_PER_CONT,
      TQN,
      NAMC,
      COC,
      C_OR_D,
      TP,
      CUST_ORDER_QTY,
      DOCK,
      KANBAN,
      PART_DESCRIPTION,
      STORE_ADDRESS,
      HMM,
      SUPPLIER_NAME,
      MAIN_ROUTE_NO,
      VALID,
      NAME_OF_TMM,
      TEL,
      CROSS_D1,
      EDI,
      UPD_TIMESTAMP,
      USER_NAME,
      'V'
    FROM TBL_TEMP_ZUP22221;
  
    INSERT INTO TBL_PLAN_STORE(
      PART_NUMBER,
      PS,
      START_DATE,
      END_DATE,
      WRKG_DAYS,
      QTY_PER_CONT,
      TQN,
      NAMC,
      COC,
      C_OR_D,
      TP,
      CUST_ORDER_QTY,
      DOCK,
      KANBAN,
      PART_DESCRIPTION,
      STORE_ADDRESS,
      HMM,
      SUPPLIER_NAME,
      MAIN_ROUTE_NO,
      VALID,
      NAME_OF_TMM,
      TEL,
      CROSS_D1,
      EDI,
      UPD_TIMESTAMP,
      USER_NAME,
      ORDER_TYPE)
    SELECT 
      A.PART_NUMBER,
      A.PS,
      A.START_DATE,
      A.END_DATE,
      A.WRKG_DAYS,
      A.QTY_PER_CONT,
      A.TQN,
      A.NAMC,
      A.COC,
      A.C_OR_D,
      A.TP,
      A.CUST_ORDER_QTY,
      A.DOCK,
      A.KANBAN,
      A.PART_DESCRIPTION,
      A.STORE_ADDRESS,
      A.HMM,
      A.SUPPLIER_NAME,
      A.MAIN_ROUTE_NO,
      A.VALID,
      A.NAME_OF_TMM,
      A.TEL,
      A.CROSS_D1,
      A.EDI,
      A.UPD_TIMESTAMP,
      A.USER_NAME,
      'V'
    FROM TBL_VEHICLE A
    INNER JOIN TBL_TEMP_ZUP22221 B
    ON A.PART_NUMBER != B.PART_NUMBER
      AND A.PS != B.PS
      AND A.START_DATE != B.START_DATE
      AND A.END_DATE != B.END_DATE
      AND A.NAMC != B.NAMC;
    
    v_Result := 1;  
  ELSE
    v_Result := 3;
  END IF;
  
  IF v_Result = 1 THEN
    DELETE FROM TBL_TEMP_ZUP22221;
    DELETE FROM TBL_VEHICLE;
  END IF;
  COMMIT;
RETURN v_Result;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO SP;
    RETURN 4;

END "FNC_ZUP22221";
/

So calling it exec FNC_ZUP22220(1); is valid? Or am I missing something here? I don't need to edit that procedure, it's from someone I don't know.

[brucevde]

I don't use Oracle that much and have never called a function using ADO but the first thing I noticed is that the function name is FNC_ZUP22221 not FNC_ZUP22220 - or is that just a typo.

What error are you getting?

[nebulom]

Opps, I'm sorry about that. This is my call. I think I'm not doing it right.

exec FNC_ZUP22221(1);

and I get the message at the image attached.

[Edit]Does being a Function or a Procedure matter? If so, how do I call a Function (coz I guess I'm supposed to call a Function)?

[fret]

In SQL this would be.

exec StoredProcedure1 @n = 1

[Merrion]

The error message is quite right - the "FNC_ZUP22221" object is not a stored procedure, it is a function.

In sql you would execute it thus:

SELECT FNC_ZUP22221(1)

I think you can do this with an OraclCommand object thusly:-

Private Function GetZup2221(byval inNumber As Integer) As Integer
    Dim myConnection As New OracleConnection(myConnString)
    Dim myCommand As New OracleCommand("SELECT FNC_ZUP22221(:inNumber)", myConnection)
    myCommand.Parameters.Add("inNumber", OracleType.Number).Value = inNumber 
    Return myCommand.ExecuteScalar

[nebulom]

Thanks people. I guess the function thing is resolved. One more thing (just not wanting to start a new thread)... How do I call a procedure with Out arguments.

CREATE OR REPLACE PROCEDURE "ZUP22231" (
    I_nu_OutDay     IN  NUMBER,                       --Ship Date
    I_v2_UserName   IN  TBL_ERRLOG.USER_NAME%TYPE,    --UserName
    I_v2_pcName     IN  TBL_ERRLOG.PC_NAME%TYPE,      --PC Name

    O_nu_SyoriCount     OUT NUMBER,     --Process Count
    O_nu_ErrCount       OUT NUMBER,     --Error Count
    O_nu_SyoriStatus    OUT NUMBER,     --Process Status
    O_nu_sqlCord        OUT NUMBER,     --SQL Cord
    O_v2_sqlMessage     OUT VARCHAR2    --SQL Message
    ) IS

. . .

For example, that's my procedure declaration. Again, thanks guys.