deployed app ignoring field from oracle stored proc

[DannyT]

I hope someone can shed some light on this it's driving me potty:

 

I have a vb.net app running on an oracle 9i back end. The issue is with populating one datatable, on my dev machine, connecting to same db it works fine (even when running installed, deployed package), on another machine it completely ignores a field as follows:

 

Table:

CREATE TABLE tbl_order_status 
(
order_status_id		NUMBER(10)	NOT NULL,
order_status 		NVARCHAR2(2) DEFAULT '00',
order_status_name	VARCHAR2(20),
CONSTRAINT 	pk_order_status PRIMARY KEY(order_status) 
);

data:

-- NB there is a trigger which inserts an autonumber type primary key into the first field
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('00', 'status00');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('0', 'status0');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('01', 'status01');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('02', 'status02');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('03', 'status03');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('04', 'status04');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('05', 'status05');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('5W', 'status5W');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('06', 'status06');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('07', 'status07');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('08', 'status08');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('09', 'status09');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('10', 'status10');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('11', 'status11');
INSERT INTO tbl_order_status(order_status, order_status_name)
VALUES('99', 'status99');

 

stored procedure:

CREATE OR REPLACE PACKAGE pkg_order_status
AS
 TYPE rc_order_status IS REF CURSOR;
END;
/
show errors

CREATE OR REPLACE  PROCEDURE sp_select_order_status
   (c_order_status OUT pkg_order_status.rc_order_status)
IS
BEGIN

OPEN c_order_status FOR
SELECT DISTINCT order_status_id, order_status, order_status_name
FROM tbl_order_status;
END;
/

When i run the procedure from SQL Plus (on either machine) I get the results as expected:

 

SQL> VAR c1 REFCURSOR
SQL> execute sp_select_order_status(:c1);

PL/SQL procedure successfully completed.

SQL> print c1

ORDER_STATUS_ID OR ORDER_STATUS_NAME
--------------- -- --------------------
             1 00 Status00
             2 0  Status0
             3 01 Status01
             4 02 Status02
             5 03 Status03
             6 04 Status04
             7 05 Status05
             8 5W Status5W
             9 06 Status06
            10 07 Status07
            11 08 Status08

ORDER_STATUS_ID OR ORDER_STATUS_NAME
--------------- -- --------------------
            12 09 Status09
            13 10 Status10
            14 11 Status11
            15 99 Status99

15 rows selected.

 

The problem is when i populate a dataset table with the results from that procedure it populates fine on my dev machine, but on the live machine the 2nd field (order_status) is always blank.

' get order statuses to dataset
       With dbadpt
           ' set command object properties
           With cmdOrderStatus
               .Connection = getCn()
               .CommandText = "sp_select_order_status"
               .CommandType = CommandType.StoredProcedure
               .Parameters.Add("c_order_status", OracleType.Cursor).Direction = ParameterDirection.Output
           End With

           ' select command
           .SelectCommand = cmdOrderStatus
           .TableMappings.Add("Table", "orderStatus")

           ' fill dataset
           .Fill(ds)
           .Dispose()
       End With
...
Dim dr1 As DataRow
       For Each dr1 In ds.Tables("orderStatus").Select
           TextBox1.Text = TextBox1.Text & ", " & dr1(1)
       Next
'dev outputs: "00, 0, 01, 02, 03 .. etc" 
'live outputs: ",,,,,,,, etc"

 

This is obviously a pain in the arse to debug as it works fine on my machine! If anyone can offer any guidance or even a better way to debug and narrow down the problem, i'd be extremely grateful. If you need any more info please dont hesitate to ask.

 

Cheers,

 

Dan