Procedure and SYS_REFCURSOR problem!

[orgilhp]

Hi guys

here is a sample procedure that i have used:
----------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE spGetCards
(
cv_1 IN OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN cv_1 FOR
SELECT * FROM tCards
END;
----------------------------------------------------------------------------------------

And I call this procedure from C# code using System.Data.OracleClient
namespace. Like below:
----------------------------------------------------------------------------------------
OracleCommand Cmd = new OracleCommand("spGetCards", Conn);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("cv_1", OracleType.Cursor).Direction =
ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(Cmd);
da.Fill(ds, "tcard");
-----------------------------------------------------------------------------------------
But ds.Tables["tcard"] has nothing to show, there is no data loaded.

What I trying to ask is "Can I do selection using stored procedure
with sys_refcursor and show the result on an application using the
code above?"

Please help!
Any suggestion will be highly appreciated!

... Orgil

[Michael Moore]

Why are you using "IN OUT" instead of just "OUT" ?

Mike

--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[Michael Moore]

Also, I don't know c#, but here is a page that might help you.

http://www.oracle.com/technology/pub/articles/mastering_dotnet_oracle/williams_refcursors.html

[ddf]

On Aug 10, 2:00 am, orgilhp <orgi...@gmail.com> wrote:
> Hi guys
>
> here is a sample procedure that i have used:

> ---------------------------------------------------------------------------­-------------

> CREATE OR REPLACE PROCEDURE spGetCards
> (
>   cv_1 IN OUT SYS_REFCURSOR
> )
> AS
> BEGIN
>    OPEN cv_1 FOR
>       SELECT * FROM tCards
> END;

> ---------------------------------------------------------------------------­-------------

>
> And I call this procedure from C# code using System.Data.OracleClient
> namespace. Like below:

> ---------------------------------------------------------------------------­-------------

>             OracleCommand Cmd = new OracleCommand("spGetCards", Conn);
>             Cmd.CommandType = CommandType.StoredProcedure;
>             Cmd.Parameters.Add("cv_1", OracleType.Cursor).Direction =
> ParameterDirection.Output;
>             OracleDataAdapter da = new OracleDataAdapter(Cmd);
>             da.Fill(ds, "tcard");

> ---------------------------------------------------------------------------­--------------

> But ds.Tables["tcard"] has nothing to show, there is no data loaded.
>
> What I trying to ask is "Can I do selection using stored procedure
> with sys_refcursor and show the result on an application using the
> code above?"
>
> Please help!
> Any suggestion will be highly appreciated!
>
> ... Orgil

Where is the semicolon that should be at the end of your cursor query?


David Fitzjarrell

[orgilhp]

thanks all of you suggested me!