deployed app ignoring field from oracle stored proc

D

DannyT

Active member
Joined
Apr 14, 2005
Messages
36
I hope someone can shed some light on this its 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:
Code: 
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:
Code: 
-- 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:
Code: 
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:

Code: 
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.
Code: 
  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, id be extremely grateful. If you need any more info please dont hesitate to ask.

Cheers,

Dan
 
Back
Top