Replacing DBMS_OUTPUT.PUT_LINE with SELECT

sdstuber thanks for that. I don't think I can use temp tables but any code samples or links I can refer to for other solutions?

I am a little bit confused as to the composition of your data.

These are all PRODUCT_NAME values?

DELL BLACK 537 - DESKTOP ONLY D250S1
DELL V305 ALL IN ONE PRINTER + INK
DELL BLACK 537 - DESKTOP ONLY D250S1

Whereas
250GB
2GB RAM
3 YR LIMITED WARRANTY
...
7 DAYS FROM RECEIPT, UNOPENED
DELL V305 ALL-IN-ONE PRINTER

are all ELEMENT values? If not, what does your data actually look like?

If yes, how does your code achieve the ordering shown? Why are the two entries marked DELL BLACK 537 - DESKTOP ONLY D250S1 not listed consecutively?

There are several ways, depending on how much output there is:

You could turn your procedure into a Function and return all the output through a single variable, replace the DBMS_OUTPUT with a simple concat.

Alternatively if the DB and Application are on the same server. return the DBMS_OUTPUT file name, and open and read the file.

gatorvip, Sorry I think it was repeated in the DBMS_OUTPUT screen. it has to be shown only like,

Product Name
 - Bundle Info 1
 - Bundle Info 2

Ex:
DELL BLACK 537 - DESKTOP ONLY D250S1
 - 250GB
 - 2GB RAM
 - 3 YR LIMITED WARRANTY
 - 7 DAYS FROM RECEIPT, UNOPENED
 - ATI RADEON 3200
 - BLU-RAY COMBO
 - DELL 22IN LCD
 - DELL AX210
 - INCLUDED
 - INSPIRON 537 SLIM
 - INTEGRATED
 - INTEL DUAL CORE E5200 2.5GHz
 - MS WINDOWS VISTA HOME BASIC
 - MS WORKS 9.0
 - NIC, 56K
 - NORTON 360 ALL-IN-ONE SECURITY
 - NOT INCLUDED
 - NOT INCLUDED
 - NOT INCLUDED
DELL V305 ALL IN ONE PRINTER + INK
 - 1 YR LIMITED WARRANTY
 - 7 DAYS FROM RECEIPT, UNOPENED
 - DELL V305 ALL-IN-ONE PRINTER

See: http://msdn.microsoft.com/en-us/library/ms971506.aspx

The function given by sdstuber is doing exactly what I need! But I trying to call it from C# datareader, but get this error.

Parameter 'Parameter1': No size set for variable length data type: String.

please see code below and advice.

OracleConnection conn = new OracleConnection();
        conn.ConnectionString = "---";
        OracleCommand cmd = new OracleCommand() ;
        cmd.Connection=conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "fngetproductbundle";
        OracleParameter pInOrderId = new OracleParameter();
        pInOrderId.ParameterName = "strorderid";
        pInOrderId.OracleType = OracleType.VarChar;
        pInOrderId.Direction = ParameterDirection.Input;
        pInOrderId.Value = "RITTAYL476186";
        OracleParameter pOutBundleInfo= new OracleParameter();
        pInOrderId.OracleType = OracleType.VarChar;
        pOutBundleInfo.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(pInOrderId);
        cmd.Parameters.Add(pOutBundleInfo);
 
        try
        {
            conn.Open();
            OracleDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
                while (dr.Read())
                    Response.Write(dr.FieldCount);
            else
                Response.Write("No rows returned.");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            conn.Close();
        }

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:

the size will be 4000

actually no, that's not the problem.  sorry, answered faster than I was thinking.


Don't use a StoredProcedure  command type,  it's just a sql query.

yes, a function call is involved but you don't want .net to make that call for you, you want the function call to be part of the sql

sdstuber I changed the size of the parameters to 4000 and CommandType to Text,
I still get "Parameter 'Parameter1': No size set for variable length data type: String. "

 OracleCommand cmd = new OracleCommand() ;
        cmd.Connection=conn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "fngetproductbundle";
        OracleParameter pInOrderId = new OracleParameter();
        pInOrderId.ParameterName = "strorderid";
        pInOrderId.OracleType = OracleType.VarChar;
        pInOrderId.Direction = ParameterDirection.Input;
        pInOrderId.Size = 4000;
        pInOrderId.Value = "RITTAYL476186";
        OracleParameter pOutBundleInfo= new OracleParameter();
        pInOrderId.OracleType = OracleType.VarChar;
        pOutBundleInfo.Direction = ParameterDirection.ReturnValue;
        pOutBundleInfo.Size = 4000;
        cmd.Parameters.Add(pInOrderId);
        cmd.Parameters.Add(pOutBundleInfo);
 
        try
        {
            conn.Open();
            OracleDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
                while (dr.Read())
                    Response.Write(dr.FieldCount.ToString());
            else
                Response.Write("No rows returned.");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

get rid of the second parameter and put the query into the command

sdstuber - I appreciate you help, but you need to understand you are talking to a dumb a--- :)
What query are we talking about, the function name or this
SELECT * FROM TABLE(CAST(LEGACY_WEBUSER.FNGETPRODUCTBUNDLE('RITTAYL476186') AS VCARRAY))

that select query, you shouldn't need the CAST unless you're on an old version of 9i

but it's ok to leave it in

sdstuber is a superstar!

He was patient in answering all my questions. Thank you!