From: Thomas Gagne on 18 Mar 2010 22:26
Thank you all for your help.
My background is Sybase & SqlServer. On both, due I'm sure to a common
heritage, a stored procedure is capable of being as simple or complex as
the programmer wants. Sometimes, all that is needed is a select
statement. Sometimes even simple projections may require multiple steps
to prepare the last SELECT. Additionally, stored procedures are capable
of returning multiple result sets. I assumed, incorrectly, such a thing
was not so complicated that it couldn't be easily done inside Oracle.
I'm curious about packages and the procedures inside them. I take it
the cursor must be treated as a cursor inside the application rather
than as a result set? I'm using .Net, OracleDataAdapter, and DataSet.
It seems as though result sets from stored procedures will require
From: Thomas Gagne on 18 Mar 2010 22:33
John Hurley wrote:
> On Mar 17, 9:53 pm, Thomas Gagne <TandGandGA...(a)gmail.com> wrote:
> # I'm looking around for Oracle 10g CREATE PROCEDURE syntax and
>> for examples with simple SELECT statements inside.
> Why exactly would you want to do that in the first place?
> Have you looked at something like this? http://www.oradev.com/ref_cursor.jsp
I've used it before in other RDBs. The example was deliberately
simplified. My intent would be to allow more complicated processing
before the final SELECT. In some cases the procedure could do some
simple parameter checking, call other procedures, and preprocess into
temporary tables before the final projection. Though I haven't had the
need inside MySQL, I understand MySQL has provided this since 5.1.
From: John Hurley on 19 Mar 2010 05:40
On Mar 18, 10:33 pm, Thomas Gagne <TandGandGA...(a)gmail.com> wrote:
> I've used it before in other RDBs. The example was deliberately
> simplified. My intent would be to allow more complicated processing
> before the final SELECT. In some cases the procedure could do some
> simple parameter checking, call other procedures, and preprocess into
> temporary tables before the final projection. Though I haven't had the
> need inside MySQL, I understand MySQL has provided this since 5.1.
You rarely and really want to stay away from using temporary tables
when processing work in Oracle.
Really this is a technique that for the most part can be avoided in
almost any relational dbms.
Using temporary tables kinda/sorta looks like a crutch for most
experienced DBAs and developers. Do it all in 1 SQL statement and
harness the power of the database engine. Give it as much work as you
can in 1 SQL statement!
From: Mladen Gogala on 19 Mar 2010 11:01
On Fri, 19 Mar 2010 02:40:47 -0700, John Hurley wrote:
> You rarely and really want to stay away from using temporary tables when
> processing work in Oracle.
Not really. There are databases that support local temporary tables very
well, SQL Server, Postgres and DB2 are among them. For those database,
using local temporary table is something that comes naturally, a very
useful feature. Oracle doesn't support transactional DDL, which is the
basis for local temporary tables, but that is a lack in Oracle features,
not a basis for conclusion that "temporary tables are for wimps because
real programmers(TM) use cursors".
From: Thomas Gagne on 19 Mar 2010 14:51
So is there a way to use packaged procedures to select data without
using cursors? It would be great if I could use the same syntax
inside SQLDeveloper as I might inside a program, and process the
results similarly to how normal selects are handled.