calling oracle procedure error

[DannyTmoov2]

I'm trying to execute the following oracle procedure from my app:

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;
/

I'm using the following to call the proc:

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

I'm 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

 

[chiph]

Some suggestions:

Set the parameter length for i_order_no. Often Oracle needs you to tell it the length of a column.

Instead of using "#5/3/2005 11:07:44 PM#" as your date variable, use ISO-8601 format (the native format that calling ToString() on a DateTime variable returns). This format (yyyy-mm-ddThh:mm:ss) will always sort correctly, and Oracle understands it without getting confused over US vs. European date formats.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first