Oracle procedure error

D

DannyT

Active member
Joined
Apr 14, 2005
Messages
36
Im trying to execute the following oracle procedure from my app:

Code: 
CREATE OR REPLACE PROCEDURE sp_manual_order
		(i_order_no		IN	tbl_orders.order_no%TYPE,
		 o_order_id		OUT	tbl_orders.order_id%TYPE,
		 i_xfactory		IN	DATE)
IS
BEGIN

--insert order
INSERT INTO tbl_orders(order_no, client_name, postcode, picked_date)
SELECT i_order_no, Manual Order, MAN01, to_date(i_xfactory)
FROM dual
WHERE i_order_no NOT IN (SELECT order_no FROM tbl_orders);

-- get just added order_id
SELECT order_id
INTO o_order_id
FROM tbl_orders
WHERE order_no = i_order_no;
	
END;
/

Im using the following to call the proc:
Code: 
With cmdOrder
            .Connection = getCn()
            .CommandText = "sp_manual_order"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Add("i_order_no", OracleType.VarChar).Value = i_order_no  valid value E.g. "TEST"
            .Parameters.Add("i_xfactory", OracleType.DateTime).Value = i_xfactory  valid date break point shows "#5/3/2005 11:07:44 PM#" (originally from a dateTimePicker control
            .Parameters.Add("o_order_id", OracleType.Number).Direction = ParameterDirection.Output
        End With

        openDb()
         execute the function
        cmdOrder.ExecuteNonQuery()  ********errors here*********
         MsgBox(intRowsAffected)
        closeDb()
        intOrderId = cmdOrder.Parameters("o_order_id").Value

Im getting the error:
"An unhandled exception of type System.Data.OracleClient.OracleException occurred in system.data.oracleclient.dll

Additional information: System error."

With the associated oracle error as:
ORA-01458: invalid length inside variable character string

This is my current bugbare driving me nuts so as always, any help much appreciated!

Cheers,
Dan
 
Back
Top