Search results matching tags 'Oracle' and 'Parameters'

Getting multiple rows from oracle stored procedure and binding it to grid view

yeotumitsu@sify.com — Tue, 15 Apr 2008 04:00:00 GMT

To get this stuff you need to create a Ref Cursor to return recordset.

create or replace PACKAGE Types AS

TYPE cursor_type IS REF CURSOR;

END Types;

Now create a stored procedure to return multiple rows .

create or replace

PROCEDURE getAllCity(p_recordset OUT types.cursor_type) AS

BEGIN

OPEN p_recordset FOR

select * from tbl_country;

END getAllCity;

Now put this code in your event.

string strconn = myDynconnStr;

OracleConnection conn = new OracleConnection(strconn);

OracleCommand objCmd = new OracleCommand();

objCmd.Connection = conn;

objCmd.CommandText = "getAllCity";

objCmd.CommandType = CommandType.StoredProcedure;

objCmd.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;

OracleDataAdapter odr = new OracleDataAdapter(objCmd);

DataSet ds = new DataSet();

odr.Fill(ds);

GridView1.DataSource = ds.Tables[0];

GridView1.DataBind();

Re: Return "Record Added" string from an Oracle Stored Procedure

John Happy — Tue, 22 Jan 2008 05:00:00 GMT

Discovered the problem ...

Made a change to the VB Stored Procedure calling code on my ASP.Net page.

My incorrect output parameter code was as follows:

Dim objStatusParam As New OracleParameter("Status", OracleDbType.Varchar2)

The output parameter code that worked looks like this:

Dim objStatusParam As New OracleParameter("Status", OracleDbType.Varchar2, 50)

Note that I had to supply the Varchar2 max. size number, in this case, 50.

My help came from a post I found on an Oracle.com forum.

Now my insert works and my status string appears in the label on my asp.net web page.

column datatype in oracle and dataset table

nomercy007 — Thu, 01 Nov 2007 04:00:00 GMT

Hi all,

I have an Oracle table with column of datatype NUMBER(10). When I add a tabledapter in dataset designer, it creates a table with a column of datatype: system.decimal

I change it to system.int32, but when I use an insert query, the parameter is of decimal format. I changed the type in designer.vb (although it is not recommended) and it worked fine. but after time, it revoked back to decimal.

Am I missing something, or it is just another oracle, .NET misunderstanding?

I would appreciate any feedback,

Thanks,

Re: SqlDataSource, ORA-01008, 01036, and GridView

sajala — Thu, 12 Jul 2007 04:00:00 GMT

Prashant,

Thanks for trying to help me. Removing the DeleteParameters did not work. What I ended up doing was removing the OldValuesParameterFormatString. Now it works. Only one or two people are going to be using this application and it's not going to get a lot of modifications once the tables are filled, so I don't need to do a lot of conflict checking.

Thanks.

sajala

Data Inserting problem

sthauttam — Wed, 16 May 2007 04:00:00 GMT

Hi!

I have two tables Address(address_id, street) and Employee(emp_id,address_id,name). // address_id in Employee table is foreign key

Here is my stored procedure.

CREATE OR REPLACE PROCEDURE address_proc
       (street in varchar2,
        name in varchar2)
is
BEGIN
     INSERT INTO address
         VALUES (address_id_seq.nextval,street);
     INSERT INTO employee
         VALUES (emp_id_seq.nextval,address_id_seq.currval,name);
     commit;
END;

Now, how can I insert data from a webpage that has input text field for street and name? I mean to say that a webpage has two input fields street and name. Now I have to save those two fields in separate tables with using the single stored procedure as given above. I am using Oracle 9i and ASP.NET 2.0

Any suggestions are highly appreciated.

Re: Help, a big problem worried me for week!!

Sohnee — Mon, 14 May 2007 04:00:00 GMT

Here are a few possible reasons:

1) One of your parameter names might be more than 32 characters long - which causes this error.

2) You've ommitted the : from the front of the parameter names in the parameter collection.

3) You've got a [space] at the end of one of your parameter names

4) You need to clear the parameters from previous use before you set these (i.e. an old parameter from your select is still hanging about)

I've included all of these to help out other people that come across the same problem. You're particular problem should be solved with this (Oracle is a bit different to MSSQL in this respect):

this.cmd.Parameters.Add(":dydm",OracleType.VarChar,4).Value = dydm;
this.cmd.Parameters.Add(":ny",OracleType.VarChar,6).Value = ny;