Returning result of a simple "select * " from a stored procedure without using cursor

[Database Administrator]

Hi All,

I am very novice in oracle and I do not know whether I am asking a
stupid question.

I want to create a stored procedure that returns all the rows from a
table say, 'Employee'. The stored procedure should return the same
set of rows as a simple statement "select * from Employee" returns.

In SQL Server this can be done as follows

create procedure Test1
as
select * from Employee

How can I do this in Oracle in a similar way? I do not want to use
cursors to do a simple select.

Thanks in advance,
Sreenish

[Database Administrator]

Sreenish wrote:

> Hi All,
>
> I am very novice in oracle and I do not know whether I am asking a
> stupid question.
>
> I want to create a stored procedure that returns all the rows from a
> table say, 'Employee'. The stored procedure should return the same
> set of rows as a simple statement "select * from Employee" returns.
>
> In SQL Server this can be done as follows
>
> create procedure Test1
> as
> select * from Employee
>
> How can I do this in Oracle in a similar way? I do not want to use
> cursors to do a simple select.
>
> Thanks in advance,
> Sreenish

Hi, no it's not a stupid question, and the answer is "you can't". Oracle's
procedures cannot return 'inline' results like a simple query. You must
use the non-standard JDBC getting of a result set from output parameters.
Joe Weinstein at BEA

[Database Administrator]

Joseph Weinstein wrote in message news:<3F8D6C8B.3E823BAB@bea.com>...
> Sreenish wrote:
>
> > Hi All,
> >
> > I am very novice in oracle and I do not know whether I am asking a
> > stupid question.
> >
> > I want to create a stored procedure that returns all the rows from a
> > table say, 'Employee'. The stored procedure should return the same
> > set of rows as a simple statement "select * from Employee" returns.
> >
> > In SQL Server this can be done as follows
> >
> > create procedure Test1
> > as
> > select * from Employee
> >
> > How can I do this in Oracle in a similar way? I do not want to use
> > cursors to do a simple select.
> >
> > Thanks in advance,
> > Sreenish
>
> Hi, no it's not a stupid question, and the answer is "you can't". Oracle's
> procedures cannot return 'inline' results like a simple query. You must
> use the non-standard JDBC getting of a result set from output parameters.
> Joe Weinstein at BEA

Sreenish, depending on which version of Oracle you are on and what
your front-end application is there are ways to return a row set from
a procedure. With Oracle 9 you can select from a pipelined procedure
like it was a table. Prior to this you can use such features as
Reference Cursors, object types, and pl/sql tables to accomplish the
task though I am not sure any of these methods meet your simple select
rule.

Look at the following url for a possible solution for both versions 9i
and 8i.
http://asktom.oracle.com/pls/ask/f?=...:2270447621346

HTH -- Mark D Powell --

[Database Administrator]

"Sreenish" wrote in message
news:ab2bce66.0310150724.a6f35bb@posting.google.co m...
> Hi All,
>
> I am very novice in oracle and I do not know whether I am asking a
> stupid question.
>
> I want to create a stored procedure that returns all the rows from a
> table say, 'Employee'. The stored procedure should return the same
> set of rows as a simple statement "select * from Employee" returns.
>
> In SQL Server this can be done as follows
>
> create procedure Test1
> as
> select * from Employee
>
> How can I do this in Oracle in a similar way? I do not want to use
> cursors to do a simple select.
>
> Thanks in advance,
> Sreenish

Use an out parameter and make the parameter a ref cursor. then you just
call the stored proc. See the documentation.
Jim

[Database Administrator]

Jim Kennedy wrote:

> "Sreenish" wrote in message
> news:ab2bce66.0310150724.a6f35bb@posting.google.co m...
> > Hi All,
> >
> > I am very novice in oracle and I do not know whether I am asking a
> > stupid question.
> >
> > I want to create a stored procedure that returns all the rows from a
> > table say, 'Employee'. The stored procedure should return the same
> > set of rows as a simple statement "select * from Employee" returns.
> >
> > In SQL Server this can be done as follows
> >
> > create procedure Test1
> > as
> > select * from Employee
> >
> > How can I do this in Oracle in a similar way? I do not want to use
> > cursors to do a simple select.
> >
> > Thanks in advance,
> > Sreenish
>
> Use an out parameter and make the parameter a ref cursor. then you just
> call the stored proc. See the documentation.
> Jim

See the post! ;-) He already said he didn't want to use a cursor. If an oracle procedure
could act exactly like a simple query, then he could stick to standard JDBC, but they
don't so he can't....
Joe

[Database Administrator]

"Joseph Weinstein" wrote in message
news:3F8E1AC0.FE95D9FD@bea.com...
>
>
> Jim Kennedy wrote:
>
> > "Sreenish" wrote in message
> > news:ab2bce66.0310150724.a6f35bb@posting.google.co m...
> > > Hi All,
> > >
> > > I am very novice in oracle and I do not know whether I am asking a
> > > stupid question.
> > >
> > > I want to create a stored procedure that returns all the rows from a
> > > table say, 'Employee'. The stored procedure should return the same
> > > set of rows as a simple statement "select * from Employee" returns.
> > >
> > > In SQL Server this can be done as follows
> > >
> > > create procedure Test1
> > > as
> > > select * from Employee
> > >
> > > How can I do this in Oracle in a similar way? I do not want to use
> > > cursors to do a simple select.
> > >
> > > Thanks in advance,
> > > Sreenish
> >
> > Use an out parameter and make the parameter a ref cursor. then you just
> > call the stored proc. See the documentation.
> > Jim
>
> See the post! ;-) He already said he didn't want to use a cursor. If an
oracle procedure
> could act exactly like a simple query, then he could stick to standard
JDBC, but they
> don't so he can't....
> Joe
>
>
I did read the post. He can use a cursor. He just thinks he can't because
of concurrency problems in sqlServer which you don't have in Oracle. (read
novice Oracle user) It doesn't hurt to read the documentation.
Jim

[Database Administrator]

sreenish@hotmail.com (Sreenish) wrote

> How can I do this in Oracle in a similar way? I do not want to use
> cursors to do a simple select.

As for not wanting to use a cursor. A cursor is by default what is
created on the server as the result of a SELECT and what is used on
the client. Period.

And this applies to SQL-Server as well. Read the dblib API.

Thus, not wanting to use a cursor makes no sense at all. What
requirement are you trying to satisfy by saying that you do not want
to use a cursor?

If you're complaint is that Oracle does not allow the Transact SQL way
of doing things, then think again.

Do not use Oracle wearing a SQL-Server hat. And vice versa.

Oracle is considered the top db platform for *exactly* that reason.
'cause it aint nothing like SQL-Server when you take the hood off.

Products do not sell because of "Me Too!" features. They sell because
they are *different*. Marketing 101.



--
Billy

[Database Administrator]

"Joseph Weinstein" wrote in message
news:3F8E1AC0.FE95D9FD@bea.com...
>
>
> Jim Kennedy wrote:
>
> > "Sreenish" wrote in message
> > news:ab2bce66.0310150724.a6f35bb@posting.google.co m...
> > > Hi All,
> > >
> > > I am very novice in oracle and I do not know whether I am asking a
> > > stupid question.
> > >
> > > I want to create a stored procedure that returns all the rows from a
> > > table say, 'Employee'. The stored procedure should return the same
> > > set of rows as a simple statement "select * from Employee" returns.
> > >
> > > In SQL Server this can be done as follows
> > >
> > > create procedure Test1
> > > as
> > > select * from Employee
> > >
> > > How can I do this in Oracle in a similar way? I do not want to use
> > > cursors to do a simple select.
> > >
> > > Thanks in advance,
> > > Sreenish
> >
> > Use an out parameter and make the parameter a ref cursor. then you just
> > call the stored proc. See the documentation.
> > Jim
>
> See the post! ;-) He already said he didn't want to use a cursor. If an
oracle procedure
> could act exactly like a simple query, then he could stick to standard
JDBC, but they
> don't so he can't....

Couple of things.

1. What has JDBC got to do with it? Given that he is coming to Oracle from
MSSQL I'd be guessing almost any other connectivity technology. Yes he'd
have to use parameters with ADO/ADO.Net as well but really who cares - its
not rocket science after all.

2. That aside, what do you want us to say? No Oracle can't do what you want.
Not the most helpful of answers really is it, when just by dropping a self
imposed restriction you get the exact same functionality. I mean suppose I
said to an mssql group "I want to join two tables in an sql query using the
(+) outer join syntax" "you can't" is not nearly as helpful as "you can't
use that syntax but here is the syntax you should be using".


--
Niall Litchfield
Oracle DBA
Audit Commission UK

[Database Administrator]

Jim Kennedy wrote:

> "Joseph Weinstein" wrote in message
> news:3F8E1AC0.FE95D9FD@bea.com...
> >
> >
> > Jim Kennedy wrote:
> >
> > > "Sreenish" wrote in message
> > > news:ab2bce66.0310150724.a6f35bb@posting.google.co m...
> > > > Hi All,
> > > >
> > > > I am very novice in oracle and I do not know whether I am asking a
> > > > stupid question.
> > > >
> > > > I want to create a stored procedure that returns all the rows from a
> > > > table say, 'Employee'. The stored procedure should return the same
> > > > set of rows as a simple statement "select * from Employee" returns.
> > > >
> > > > In SQL Server this can be done as follows
> > > >
> > > > create procedure Test1
> > > > as
> > > > select * from Employee
> > > >
> > > > How can I do this in Oracle in a similar way? I do not want to use
> > > > cursors to do a simple select.
> > > >
> > > > Thanks in advance,
> > > > Sreenish
> > >
> > > Use an out parameter and make the parameter a ref cursor. then you just
> > > call the stored proc. See the documentation.
> > > Jim
> >
> > See the post! ;-) He already said he didn't want to use a cursor. If an
> oracle procedure
> > could act exactly like a simple query, then he could stick to standard
> JDBC, but they
> > don't so he can't....
> > Joe
> >
> >
> I did read the post. He can use a cursor. He just thinks he can't because
> of concurrency problems in sqlServer which you don't have in Oracle. (read
> novice Oracle user) It doesn't hurt to read the documentation.
> Jim

Your powers of interpretation may be better than mine... My assumption is
that he has a standard JDBC application which works now, and he wants it
to work with Oracle too, and it won't. I am not councilling that he abandon
Oracle, but rather to simple know that he will have to do some oracle-specific
coding.
Joe

[Database Administrator]

Niall Litchfield wrote:

> "Joseph Weinstein" wrote in message
> news:3F8E1AC0.FE95D9FD@bea.com...
> >
> >
> > Jim Kennedy wrote:
> >
> > > "Sreenish" wrote in message
> > > news:ab2bce66.0310150724.a6f35bb@posting.google.co m...
> > > > Hi All,
> > > >
> > > > I am very novice in oracle and I do not know whether I am asking a
> > > > stupid question.
> > > >
> > > > I want to create a stored procedure that returns all the rows from a
> > > > table say, 'Employee'. The stored procedure should return the same
> > > > set of rows as a simple statement "select * from Employee" returns.
> > > >
> > > > In SQL Server this can be done as follows
> > > >
> > > > create procedure Test1
> > > > as
> > > > select * from Employee
> > > >
> > > > How can I do this in Oracle in a similar way? I do not want to use
> > > > cursors to do a simple select.
> > > >
> > > > Thanks in advance,
> > > > Sreenish
> > >
> > > Use an out parameter and make the parameter a ref cursor. then you just
> > > call the stored proc. See the documentation.
> > > Jim
> >
> > See the post! ;-) He already said he didn't want to use a cursor. If an
> oracle procedure
> > could act exactly like a simple query, then he could stick to standard
> JDBC, but they
> > don't so he can't....
>
> Couple of things.
>
> 1. What has JDBC got to do with it? Given that he is coming to Oracle from
> MSSQL I'd be guessing almost any other connectivity technology. Yes he'd
> have to use parameters with ADO/ADO.Net as well but really who cares - its
> not rocket science after all.

You are correct to ask. I have to admit my absolutely unfounded assumption of
JDBC, simply from my very specialized backround! Mea culpa, you are correct!

> 2. That aside, what do you want us to say? No Oracle can't do what you want.
> Not the most helpful of answers really is it, when just by dropping a self
> imposed restriction you get the exact same functionality. I mean suppose I
> said to an mssql group "I want to join two tables in an sql query using the
> (+) outer join syntax" "you can't" is not nearly as helpful as "you can't
> use that syntax but here is the syntax you should be using".

Agreed, but there may be some gravitas to the self-imposed limitation, such as
"I have a working application, built laboriously to standard APIs, and want it
to work with Oracle (or MS)". It may be the appropriate thing to starkly state
that for the migration they intend, they will either fail, or will have to alter the
application to do some vendor-specific things.
Joe

>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK