"Chad" <chaddokmanovich@comcast.netnet> wrote in message
news:NcCdnYMOhc36x_jZ4p2dnA@comcast.com...[color=blue]
> Hi, I am a SQL Server programmer using Oracle for the first time.
>
> In our .NET client apps which use a SQL Server back end, we would use[/color]
Stored[color=blue]
> Procedure exclusively for all database access for increased security as[/color]
well[color=blue]
> as the pre-compiled advantage.
>
> To minimize trips to the db server, we would often call SPs that return[/color]
more[color=blue]
> than one recordset. For example:
>
> CREATE Stored Procedure HelloWorld As
> BEGIN
> SELECT * FROM HELLO
> SELECT * FROM WORLD
> END
>
> Using ADO.NET, the results would be returned as 2 tables within a dataset
> object.
>
> We are using Oracle 10g with version 10.2 ODP.NET (data provider) on the
> client. Is there a *PRODUCTION* version of software available which allows
> me to do this simple task?
>
> If Multiple returned Recordsets are not an option in Oracle SPs, can[/color]
someone[color=blue]
> please show me the latest and greatest (excluding beta software) for[/color]
writing[color=blue]
> a baby Oracle SP to return a query result for use by a client app? I am
> found one example that utilizes TYPEs and CURSORs and it looks ugly as[/color]
hell[color=blue]
> and surprisingly complicated for what it is doing and involves looping
> through a cursor and populating what looks like an array of TYPES which[/color]
are[color=blue]
> defined as output params. Please tell me that there is a better way of[/color]
doing[color=blue]
> this in Oracle.
>
> We could construct the query as a string on the client but I would really
> like to put the DB access in the database as compiled queries.
>
> It would be great if someone could write the Oracle equivalent of the 5[/color]
line[color=blue]
> SQL Server proc above for me, it would help a lot. If two returned queries
> is too much for it, a one query example would still be useful.
>
> Thanks
>
>[/color]
Hi,
Oracle!=SQLServer. They are two very different products. In Oracle you
don't have to use a stored procedure to get the advantage of "compiled" SQL.
(the explain plan) You do have to use bind variables and that is a good
practice in any RDBMs to get reuse of the execution plan.
That said it is a good idea to use stored procedures for what you are trying
to do. In your case you can use ref cursors as out parameters. You should
also use packages. asktom.oracle.com has some great examples:
http://asktom.oracle.com/pls/ask/f?p...10369303150185
The select statement gets returned into a ref cursor. If you want to return
two sets then return each one into a ref cursor. (thus you would have 2 out
parameters.)
Don't construct a string on the client even with stored procedures. Use
bind variables and call the stored procedures with bind variables. You will
get better scaling and better performance.
Jim
