I'm looking around for Oracle 10g CREATE PROCEDURE syntax and looking 
for examples with simple SELECT statements inside.  I see a lot of DML 
but none with simple SELECT statements.

I'm thinking something like

create or replace procedure aSimpleSelect (aLikeValue char(4)) as
begin
select  *
  from  aTableName
 where  aColumn like aLikeValue;
end; 
/

But when I try creating it inside 10g it complains:

PLS-00103: Encountered the symbol "(" when expecting one of the 
following:  :=), default varying character large the symbol ":=" was 
substituted for "(" to continue.

On Mar 17, 9:53=A0pm, Thomas Gagne <TandGandGA...@gmail.com> wrote:
> I'm looking around for Oracle 10g CREATE PROCEDURE syntax and looking
> for examples with simple SELECT statements inside. =A0I see a lot of DML
> but none with simple SELECT statements.
>
> I'm thinking something like
>
> create or replace procedure aSimpleSelect (aLikeValue char(4)) as
> begin
> select =A0*
> =A0 from =A0aTableName
> =A0where =A0aColumn like aLikeValue;
> end;
> /
>
> But when I try creating it inside 10g it complains:
>
> PLS-00103: Encountered the symbol "(" when expecting one of the
> following: =A0:=3D), default varying character large the symbol ":=3D" wa=
s
> substituted for "(" to continue.

Simple SELECT statements aren't allowed in PL/SQL, at least not
without an INTO Clause:

create or replace procedure aSimpleSelect (aLikeValue char(4)) as
     myrec aTableName%ROWTYPE;
begin
select  *
into myrec
  from  aTableName
 where  aColumn =3D aLikeValue
 and rownum < 2;
end;
/

What you tried to do would involve a collection type and those are not
allowed in the INTO clause.  You could also open a ref cursor:

create or replace procedure aSimpleSelect (aLikeValue char(4)) as
     mycur sys_refcursor;
begin
open mycur for select  *  from  aTableName where  aColumn like
aLikeValue;
end;
/

You could then pass the ref cursor to another procedure, fetch from it
and process the data as in this example:

create or replace package my_package is
	type refcursor is ref cursor;

	procedure proc1(p_job in varchar2, p_cur in out refcursor);
end;
/

create or replace package body my_package is
	procedure proc1(p_job in varchar2, p_cur in out refcursor) as
		l_query varchar2(255);

	begin
		l_query :=3D 'select empno, ename, job, mgr, hiredate, sal, comm,
deptno from emp where job =3D '''||p_job||'''';
		open p_cur for l_query;
	end;
end;
/

show errors

set serveroutput on size 1000000

declare
	type rcursor is ref cursor;
	emptab rcursor;
	emprec emp%rowtype;
begin
	my_package.proc1('CLERK',emptab);

	loop
		fetch emptab into emprec;
		exit when emptab%notfound;
		dbms_output.put_line(emprec.ename||' with employee number '||
emprec.empno||' works in department number '||emprec.deptno);
		dbms_output.put_line('Hired on '||emprec.hiredate);
	end loop;

end;
/

To reiterate, you cannot simply slap a SELECT * FROM ... into the
executable section of a PL/SQL block or procedure as it's not valid
syntax.


David Fitzjarrell

On Mar 17, 9:53=A0pm, Thomas Gagne <TandGandGA...@gmail.com> wrote:

snip

# I'm looking around for Oracle 10g CREATE PROCEDURE syntax and
looking
> 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.js=
p

On Wed, 17 Mar 2010 21:53:57 -0400, Thomas Gagne wrote:

> I'm looking around for Oracle 10g CREATE PROCEDURE syntax and looking
> for examples with simple SELECT statements inside.  I see a lot of DML
> but none with simple SELECT statements.
> 
> I'm thinking something like
> 
> create or replace procedure aSimpleSelect (aLikeValue char(4)) as begin
> select  *
>   from  aTableName
>  where  aColumn like aLikeValue;
> end;
> /
> 
> But when I try creating it inside 10g it complains:
> 
> PLS-00103: Encountered the symbol "(" when expecting one of the
> following:  :=), default varying character large the symbol ":=" was
> substituted for "(" to continue.

Oh boy. This is very wrong. First, if you want to do a simple select and 
just change the values of string, you can do it with bind variable. 
Second, a procedure is procedural. It has variables, scope, an entry 
point and an exit point. It is meant to do something. The verb "to do" is 
the key here. If you need just to return value, you need a function. If 
you need to return a query, the proper data type is cursor. This 
procedure of yours is also incorrectly formatted. Camel notation usually 
gets obliterated by the first formatter that gets hold of your code, be 
it SQL*Developer or that amphibian thingy that some people use. PL/SQL is 
not case sensitive like Java, so the camelNotationDoesNotLookGood. 
Everything will get blurred into an enormous unreadable string. Use "_" 
to separate words. Also, use meaningful variable names. Prefix variables 
with "v_".



-- 
http://mgogala.byethost5.com

On Wed, 17 Mar 2010 21:37:44 -0700, ddf wrote:

> create or replace procedure aSimpleSelect (aLikeValue char(4)) as
>      myrec aTableName%ROWTYPE;
> begin
> select  *
> into myrec
>   from  aTableName
>  where  aColumn = aLikeValue
>  and rownum < 2;
> end;
> /

Nope. This is the right answer:

create or replace function aSimpleSelect (a_like_value char(4)) 
return ref cursor as
v_csr ref cursor;
begin
open v_csr for 
  select * 
  from  aTableName
 where  aColumn = a_like_value;
return(v_csr);
end;
/





-- 
http://mgogala.byethost5.com

On Thu, 18 Mar 2010 13:17:23 +0000, Mladen Gogala wrote:

> On Wed, 17 Mar 2010 21:37:44 -0700, ddf wrote:
> 
>> create or replace procedure aSimpleSelect (aLikeValue char(4)) as
>>      myrec aTableName%ROWTYPE;
>> begin
>> select  *
>> into myrec
>>   from  aTableName
>>  where  aColumn = aLikeValue
>>  and rownum < 2;
>> end;
>> /
> 
> Nope. This is the right answer:
> 
> create or replace function aSimpleSelect (a_like_value char(4)) return
> ref cursor as
> v_csr ref cursor;
> begin
> open v_csr for
>   select *
>   from  aTableName
>  where  aColumn = a_like_value;
> return(v_csr);
> end;
> /

This is the right answer that compiles:

create or replace package ddf
as
type refcsr is ref cursor;
function ssel(a_empno number) return refcsr;
end ddf;
/
create or replace package body ddf
as
function ssel(a_empno number) return refcsr
is
v_csr refcsr;
begin
open v_csr for
select * from emp
where empno=a_empno;
return(v_csr);
end;
end ddf;
/





-- 
http://mgogala.byethost5.com

On Mar 18, 12:30=A0pm, Mladen Gogala <n...@email.here.invalid> wrote:
> On Thu, 18 Mar 2010 13:17:23 +0000, Mladen Gogala wrote:
> > On Wed, 17 Mar 2010 21:37:44 -0700, ddf wrote:
>
> >> create or replace procedure aSimpleSelect (aLikeValue char(4)) as
> >> =A0 =A0 =A0myrec aTableName%ROWTYPE;
> >> begin
> >> select =A0*
> >> into myrec
> >> =A0 from =A0aTableName
> >> =A0where =A0aColumn =3D aLikeValue
> >> =A0and rownum < 2;
> >> end;
> >> /
>
> > Nope. This is the right answer:
>
> > create or replace function aSimpleSelect (a_like_value char(4)) return
> > ref cursor as
> > v_csr ref cursor;
> > begin
> > open v_csr for
> > =A0 select *
> > =A0 from =A0aTableName
> > =A0where =A0aColumn =3D a_like_value;
> > return(v_csr);
> > end;
> > /
>
> This is the right answer that compiles:
>
> create or replace package ddf
> as
> type refcsr is ref cursor;
> function ssel(a_empno number) return refcsr;
> end ddf;
> /
> create or replace package body ddf
> as
> function ssel(a_empno number) return refcsr
> is
> v_csr refcsr;
> begin
> open v_csr for
> select * from emp
> where empno=3Da_empno;
> return(v_csr);
> end;
> end ddf;
> /
>
> --http://mgogala.byethost5.com- Hide quoted text -
>
> - Show quoted text -

You didn't read my entire response?


David Fitzjarrell

On Thu, 18 Mar 2010 12:01:44 -0700, ddf wrote:


> You didn't read my entire response?

I did. My point is that is a function, not a procedure. 



-- 
http://mgogala.byethost5.com

On Mar 18, 3:06=A0pm, Mladen Gogala <n...@email.here.invalid> wrote:
> On Thu, 18 Mar 2010 12:01:44 -0700, ddf wrote:
> > You didn't read my entire response?
>
> I did. My point is that is a function, not a procedure.
>
> --http://mgogala.byethost5.com

And you read the orignial post where he's asking to create a
procedure ...


David Fitzjarrell

On Thu, 18 Mar 2010 14:01:42 -0700, ddf wrote:


>> I did. My point is that is a function, not a procedure.
>>
>> --http://mgogala.byethost5.com
> 
> And you read the orignial post where he's asking to create a procedure
> ...

He doesn't know what does he want. That's the main problem.



-- 
http://mgogala.freehostia.com

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 
something else.

John Hurley wrote:
> On Mar 17, 9:53 pm, Thomas Gagne <TandGandGA...@gmail.com> wrote:
>
> snip
>
> # I'm looking around for Oracle 10g CREATE PROCEDURE syntax and
> looking
>   
>> 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.

On Mar 18, 10:33=A0pm, Thomas Gagne <TandGandGA...@gmail.com> wrote:

snip

> I've used it before in other RDBs. =A0The example was deliberately
> simplified. =A0My intent would be to allow more complicated processing
> before the final SELECT. =A0In some cases the procedure could do some
> simple parameter checking, call other procedures, and preprocess into
> temporary tables before the final projection. =A0Though 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!

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".



-- 
http://mgogala.byethost5.com

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.

On Fri, 19 Mar 2010 11:51:59 -0700, Thomas Gagne wrote:

> 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.


No, it is not possible. Oracle does not support local temporary tables 
which are alternative to cursors in some other RDBMS systems.


-- 
http://mgogala.byethost5.com

On Fri, 19 Mar 2010 11:51:59 -0700 (PDT), Thomas Gagne
<tggagne@gmail.com> wrote:

>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.

No, it is not possible.
Unlike  'products' like T-SQL, which abusively rapes the PROCEDURE
concept to return a resultset, PL/SQL is a *REAL* programming
language, which doesn't force you to use crutches like temporary
tables.
This also measn the 'wisdom' of Mladen Gogala Oracle 'lacks'
transactional DDL is incorrect. Oracle doesn't *NEED* crap constructs
like 'transactional DDL'. In the Oracle world systems are *designed*
and not 'hacked together'. Maybe Mladen Gogala's applications are, and
in that case he might better switch to craphola RDBMS-es completely.

-- 
Sybrand Bakker
Senior Oracle DBA

On Thu, 18 Mar 2010 22:26:44 -0400, Thomas Gagne
<TandGandGAGNE@gmail.com> wrote:

>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 Oracl

Mickeysoft has never understood the Procedure concept, and ignored the
formal defintiion and abused it to return a result set.
It seems like you belong to the class of sqlserver 'developers' which
is so narrow-minded they automatically reject everything done
differently by Oracle and start bashing Oracle for it.
Luckily sqlserver is incapable of being an enterprise class product,
just because of its poor architecture and vendor lock-in, so your
'objections'  are futile.

-- 
Sybrand Bakker
Senior Oracle DBA

On Fri, 19 Mar 2010 20:33:09 +0100, Sybrand Bakker wrote:

> This also measn the 'wisdom' of Mladen Gogala Oracle 'lacks'
> transactional DDL is incorrect. Oracle doesn't *NEED* crap constructs
> like 'transactional DDL'. In the Oracle world systems are *designed* and
> not 'hacked together'. Maybe Mladen Gogala's applications are, and in
> that case he might better switch to craphola RDBMS-es completely.

Real programmers use cursors? So be it, use them. So, real programmers as 
you see them, would probably never do this:

mgogala@nycwxp2622:~$ PGHOST=lpo-postgres-01
mgogala@nycwxp2622:~$ psql -U scott
Password for user scott: 
Timing is on.
psql (8.4.2)
Type "help" for help.

scott=> create local temporary table dept10 
as select * from emp
where deptno=10;
SELECT
Time: 37.902 ms
scott=> select * from dept10;
 empno | ename  |    job    | mgr  |      hiredate       | sal  | comm | 
deptno 
-------+--------+-----------+------+---------------------+------+------
+--------
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 |      
|     10
  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000 |      
|     10
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 |      
|     10
(3 rows)

Time: 30.546 ms
scott=> \c scott;
psql (8.4.2)
You are now connected to database "scott".
scott=> select * from dept10;
ERROR:  relation "dept10" does not exist
LINE 1: select * from dept10;
                      ^
scott=> 


Big deal, as long as it works. Of course, that particular database also 
knows how to deal with cursors. Local temporary tables or transactional 
DDL are a very useful feature which doesn't exist in Oracle. Oracle has 
many very useful and practical features, but this one is not among them.
BTW, the password for this user is...., oh, I'll let you guess this one.
-- 
http://mgogala.byethost5.com

Sybrand, no need to be insecure.  I'm just trying to figure out how
Oracle programmers do things I've been doing since the early 90s.   At
present my task is to figure out how to do write procedures for Oracle
and am trying to discover what it's capabilities are.  Packages and
cursors add complexity that doesn't exist in other products.  Don't
feel threatened, I'm sure there are features Oracle provides that are
difficult to simulate without complexity in other DBs, like recursive
SELECTs.

But for the purposes of this thread, the topic is stored procedures
and returns data sets either to an interactive user using SQLDeveloper
or an application.

On Fri, 19 Mar 2010 13:56:20 -0700, Thomas Gagne wrote:

> Sybrand, no need to be insecure.

Sybrand may be anything, but insecure he's not. Pop psychology on oracle 
group is a sign of bad taste. I do dislike Sybrand intensely and I would 
use a word starting with "a" and ending in "le" to describe him, but 
insecure he's not.



-- 
http://mgogala.byethost5.com

On Mar 19, 4:56=A0pm, Thomas Gagne <tgga...@gmail.com> wrote:

snip

> Sybrand, no need to be insecure. =A0I'm just trying to figure out how
> Oracle programmers do things I've been doing since the early 90s. =A0 At
> present my task is to figure out how to do write procedures for Oracle
> and am trying to discover what it's capabilities are. =A0Packages and
> cursors add complexity that doesn't exist in other products. =A0Don't
> feel threatened, I'm sure there are features Oracle provides that are
> difficult to simulate without complexity in other DBs, like recursive
> SELECTs.
>
> But for the purposes of this thread, the topic is stored procedures
> and returns data sets either to an interactive user using SQLDeveloper
> or an application.

I would recommend that you buy and read ( probably multiple times )
Tom Kyte's book Expert Oracle Architecture.

Read the first chapter a couple of times.  Do the shampoo song ( rinse
and repeat ).

You need to understand and harness the power of Oracle ... not keep
repeating I would do this if it were SQL server.  That's the road to
purgatory that you are currently traveling on.

On Mar 19, 4:56=A0pm, Thomas Gagne <tgga...@gmail.com> wrote:

> But for the purposes of this thread, the topic is stored procedures
> and returns data sets either to an interactive user using SQLDeveloper
> or an application.

As far as I can tell, your initial question was "Can a procedure
contain only a SELECT statement?", which David Fitzjarrell answered in
the first response.

If you are looking to *return* a data set, you need a function.  If
you want to use a resultset produced from a *procedure*, you need an
out variable of sys_refcursor type, such as:

SQL> create table t(c number);

Table created.

SQL> insert into t select rownum from all_objects where rownum <=3D 10;

9 rows created.

SQL> commit;

Commit complete.

SQL> create or replace procedure p_data(p_data out sys_refcursor) is
  2  begin
  3    open p_data for select * from t;
  4  end;
  5  /

Procedure created.

SQL> variable b refcursor
SQL> exec p_data(:b)

PL/SQL procedure successfully completed.

SQL> print b

	 C
----------
	 1
	 1
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9

10 rows selected.

SQL>

You can use this with any modern language such as java, python, C#,
etc.

HTH,

Steve

On Fri, 19 Mar 2010 16:48:43 -0700, Steve Howard wrote:


> You can use this with any modern language such as java, python, C#, etc.

You forgot Perl.



-- 
http://mgogala.byethost5.com

On Mar 19, 11:31=A0pm, Mladen Gogala <gogala.mla...@gmail.com> wrote:
> On Fri, 19 Mar 2010 16:48:43 -0700, Steve Howard wrote:
> > You can use this with any modern language such as java, python, C#, etc=
..
>
> You forgot Perl.
>
> --http://mgogala.byethost5.com

No, that's what I meant by etc. :)

Python has been my language of choice recently.  I know you are a big
perl guy based on your past posts, but I absolutely love the python
model.

On 03/20/2010 01:56 PM, Steve Howard wrote:
> On Mar 19, 11:31 pm, Mladen Gogala <gogala.mla...@gmail.com> wrote:
>> On Fri, 19 Mar 2010 16:48:43 -0700, Steve Howard wrote:
>>> You can use this with any modern language such as java, python, C#, etc.
>> You forgot Perl.
>>
>> --http://mgogala.byethost5.com
> 
> No, that's what I meant by etc. :)
> 
> Python has been my language of choice recently.  I know you are a big
> perl guy based on your past posts, but I absolutely love the python
> model.

You should come and see Ruby. ;-)

Cheers

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On 03/19/2010 08:39 PM, Sybrand Bakker wrote:
> On Thu, 18 Mar 2010 22:26:44 -0400, Thomas Gagne
> <TandGandGAGNE@gmail.com> wrote:
> 
>> 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 Oracl
> 
> Mickeysoft has never understood the Procedure concept, and ignored the
> formal defintiion and abused it to return a result set.
> It seems like you belong to the class of sqlserver 'developers' which
> is so narrow-minded they automatically reject everything done
> differently by Oracle and start bashing Oracle for it.
> Luckily sqlserver is incapable of being an enterprise class product,
> just because of its poor architecture and vendor lock-in, so your
> 'objections'  are futile.

I would not be too sure of that.  SQL Sever isn't as bad as people are 
trying to make it look - and it's gaining ground, especially in the area 
of dealing with larger data sets.  Maybe it's not as "enterprise class" 
as Oracle is (or is claimed to be) but the management tools with good 
graphical user interface were there before Oracle had Grid Control. 
Yes, I know - real DBA's use command line, but there are situations 
where a graphical visualization can greatly help.

Cheers

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Thomas Gagne <TandGandGAGNE@gmail.com> writes:

> 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
> something else.

The fact that a procedure can also return a resultset doesn't really fit
the model of what a procedure is intended for.  Most SQLServer/Sybase
guys, first coming into Oracle look for it and are quite frustrated that
the concept does not exist because it was "easy" in other environments,
and I know because I was one of them many moons ago.  But, now, after
having programmed in Oracle for a long time, I very much like the clear
distinction.

As others have said, use a function, or have your procedure fill an
output parameter with a ref cursor.

-- 
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

Robert Klemme <shortcutter@googlemail.com> writes:

> On 03/19/2010 08:39 PM, Sybrand Bakker wrote:
>> On Thu, 18 Mar 2010 22:26:44 -0400, Thomas Gagne
>> <TandGandGAGNE@gmail.com> wrote:
>>
>>> 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 Oracl
>>
>> Mickeysoft has never understood the Procedure concept, and ignored the
>> formal defintiion and abused it to return a result set.
>> It seems like you belong to the class of sqlserver 'developers' which
>> is so narrow-minded they automatically reject everything done
>> differently by Oracle and start bashing Oracle for it.
>> Luckily sqlserver is incapable of being an enterprise class product,
>> just because of its poor architecture and vendor lock-in, so your
>> 'objections'  are futile.
>
> I would not be too sure of that.  SQL Sever isn't as bad as people are
> trying to make it look - and it's gaining ground, especially in the
> area of dealing with larger data sets.  Maybe it's not as "enterprise
> class" as Oracle is (or is claimed to be) but the management tools
> with good graphical user interface were there before Oracle had Grid
> Control. Yes, I know - real DBA's use command line, but there are
> situations where a graphical visualization can greatly help.

One of the biggest winning arguments for Oracle, is that it run on
almost all platforms, MS products only run on one.

-- 
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

On Sat, 20 Mar 2010 05:56:06 -0700, Steve Howard wrote:

> Python has been my language of choice recently.  I know you are a big
> perl guy based on your past posts, but I absolutely love the python
> model.

I am, actually, rather unhappy because the company I worked for decided 
to adopt Python as its scripting language. All those ad-hoc scripts that 
I used to write in Perl will from now on have to be written in Python. 
Having worked with Perl for a decade, I am quite familiar with it and I 
like it a lot. The developers, however, claim that Perl is hard to learn, 
something that I bitterly dispute, and that it has a lousy object model, 
which I must agree with. 
A have bought a book about Python and will have to start reading it one 
of these days. I will try keeping my Perl skills current, though. I am 
writing a schema comparison tool that will be completely open source, in 
Perl. It will include Postgres, too.

-- 
http://mgogala.byethost5.com

Galen Boyer wrote:
> <snip>
>
> The fact that a procedure can also return a resultset doesn't really fit
> the model of what a procedure is intended for.
Can you elaborate on "... what a procedure is intended for?"

On Mar 21, 7:21=A0am, Thomas Gagne <TandGandGA...@gmail.com> wrote:

snip

> > The fact that a procedure can also return a resultset doesn't really fi=
t
> > the model of what a procedure is intended for.
>
> Can you elaborate on "... what a procedure is intended for?"

Really please take the time to buy and read a couple of Tom Kyte's
books.  It would probably be a better use of your time and you would
have something permanent.

The first thing you need to understand is that you want to harness the
power of Oracle not do things in some kind of database neutral type of
interfacing.

Start by tossing out all your preconceived ideas of how you would do
it in some other environment.

Good luck!

On 20.03.2010 23:20, Galen Boyer wrote:
> Robert Klemme<shortcutter@googlemail.com>  writes:
>
>> On 03/19/2010 08:39 PM, Sybrand Bakker wrote:
>>> On Thu, 18 Mar 2010 22:26:44 -0400, Thomas Gagne
>>> <TandGandGAGNE@gmail.com>  wrote:
>>>
>>>> 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 Oracl
>>>
>>> Mickeysoft has never understood the Procedure concept, and ignored the
>>> formal defintiion and abused it to return a result set.
>>> It seems like you belong to the class of sqlserver 'developers' which
>>> is so narrow-minded they automatically reject everything done
>>> differently by Oracle and start bashing Oracle for it.
>>> Luckily sqlserver is incapable of being an enterprise class product,
>>> just because of its poor architecture and vendor lock-in, so your
>>> 'objections'  are futile.
>>
>> I would not be too sure of that.  SQL Sever isn't as bad as people are
>> trying to make it look - and it's gaining ground, especially in the
>> area of dealing with larger data sets.  Maybe it's not as "enterprise
>> class" as Oracle is (or is claimed to be) but the management tools
>> with good graphical user interface were there before Oracle had Grid
>> Control. Yes, I know - real DBA's use command line, but there are
>> situations where a graphical visualization can greatly help.
>
> One of the biggest winning arguments for Oracle, is that it run on
> almost all platforms, MS products only run on one.

Why is that an argument pro Oracle?

Kind regards

	robert


-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

"Thomas Gagne" <TandGandGAGNE@gmail.com> wrote in message 
news:tqadnRsfhsIjGjzWnZ2dnUVZ_qudnZ2d@wow.com...
> I'm looking around for Oracle 10g CREATE PROCEDURE syntax and looking for 
> examples with simple SELECT statements inside.  I see a lot of DML but 
> none with simple SELECT statements.
>
> I'm thinking something like
>
> create or replace procedure aSimpleSelect (aLikeValue char(4)) as
> begin
> select  *
>  from  aTableName
> where  aColumn like aLikeValue;
> end; /
>
> But when I try creating it inside 10g it complains:
>
> PLS-00103: Encountered the symbol "(" when expecting one of the 
> following:  :=), default varying character large the symbol ":=" was 
> substituted for "(" to continue.
>


I think the thing that looks odd to the Oracle professional is as follows:
In SQL Server you can do something like this (apologies for incorrect
table and column names, I don't have a copy of the software handy):

create procedure jpl
as

set notcount on
select name from sys.schemas;

select name, physical_name
from sys.master_files;

set nocount off
go

From (say) sqlcmd you can now type:

> jpl
> go

This effectively executes and displays the results of the
two queries in the procedure - and this seems "viable"
in something like a lightweight tool supplied by the
people who produced the database software.

BUT -
The procedure seems to have taken on the responsibility of knowing
how to output the data to the front-end.

So, from the viewpoint of the Oracle developer, what do you have to
do in the application code to know that when you call the procedure
you're going to get two result sets which are different shapes. 
(Presumably
you want to see two sets of data, rather than one set of data which is just
a single column very wide string.)  And how does the front-end code know
that it might, or might not, get some "data" which is actually row counts
depending on whether the procedure "set nocount on" or not ?

Does your application call to the procedure have to know about all the 
result
sets that could be produced in the procedure and call the procedure passing
in references to some sort of cursor handle ?

Please bear in mind that this question is being asked from a perspective of 
total
ignorance of how you are expected to use procedures in application code 
written
for SQL Server.



-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

"Galen Boyer" <galen_boyer@yahoo.com> wrote in message 
news:u4okahbya.fsf@www.yahoo.com...
>
> One of the biggest winning arguments for Oracle, is that it run on
> almost all platforms, MS products only run on one.
>
>

On the other hand, many businesses only want to run on
a single platform.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

"Robert Klemme" <shortcutter@googlemail.com> wrote in message 
news:80mfq8Fo7U1@mid.individual.net...
> On 20.03.2010 23:20, Galen Boyer wrote:
>> Robert Klemme<shortcutter@googlemail.com>  writes:
>>
>>> On 03/19/2010 08:39 PM, Sybrand Bakker wrote:
>>>> On Thu, 18 Mar 2010 22:26:44 -0400, Thomas Gagne
>>>> <TandGandGAGNE@gmail.com>  wrote:
>>>>
>>>>> 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 Oracl
>>>>
>>>> Mickeysoft has never understood the Procedure concept, and ignored the
>>>> formal defintiion and abused it to return a result set.
>>>> It seems like you belong to the class of sqlserver 'developers' which
>>>> is so narrow-minded they automatically reject everything done
>>>> differently by Oracle and start bashing Oracle for it.
>>>> Luckily sqlserver is incapable of being an enterprise class product,
>>>> just because of its poor architecture and vendor lock-in, so your
>>>> 'objections'  are futile.
>>>
>>> I would not be too sure of that.  SQL Sever isn't as bad as people are
>>> trying to make it look - and it's gaining ground, especially in the
>>> area of dealing with larger data sets.  Maybe it's not as "enterprise
>>> class" as Oracle is (or is claimed to be) but the management tools
>>> with good graphical user interface were there before Oracle had Grid
>>> Control. Yes, I know - real DBA's use command line, but there are
>>> situations where a graphical visualization can greatly help.
>>
>> One of the biggest winning arguments for Oracle, is that it run on
>> almost all platforms, MS products only run on one.
>
> Why is that an argument pro Oracle?
>

Very simple, choices. That is a major advantage. 

On 03/21/2010 04:49 PM, Bob Jones wrote:
> "Robert Klemme" <shortcutter@googlemail.com> wrote in message 
> news:80mfq8Fo7U1@mid.individual.net...
>> On 20.03.2010 23:20, Galen Boyer wrote:
>>> Robert Klemme<shortcutter@googlemail.com>  writes:
>>>
>>>> On 03/19/2010 08:39 PM, Sybrand Bakker wrote:
>>>>> On Thu, 18 Mar 2010 22:26:44 -0400, Thomas Gagne
>>>>> <TandGandGAGNE@gmail.com>  wrote:
>>>>>
>>>>>> 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 Oracl
>>>>> Mickeysoft has never understood the Procedure concept, and ignored the
>>>>> formal defintiion and abused it to return a result set.
>>>>> It seems like you belong to the class of sqlserver 'developers' which
>>>>> is so narrow-minded they automatically reject everything done
>>>>> differently by Oracle and start bashing Oracle for it.
>>>>> Luckily sqlserver is incapable of being an enterprise class product,
>>>>> just because of its poor architecture and vendor lock-in, so your
>>>>> 'objections'  are futile.
>>>> I would not be too sure of that.  SQL Sever isn't as bad as people are
>>>> trying to make it look - and it's gaining ground, especially in the
>>>> area of dealing with larger data sets.  Maybe it's not as "enterprise
>>>> class" as Oracle is (or is claimed to be) but the management tools
>>>> with good graphical user interface were there before Oracle had Grid
>>>> Control. Yes, I know - real DBA's use command line, but there are
>>>> situations where a graphical visualization can greatly help.
>>> One of the biggest winning arguments for Oracle, is that it run on
>>> almost all platforms, MS products only run on one.
>> Why is that an argument pro Oracle?
>>
> 
> Very simple, choices. That is a major advantage. 

I beg to differ.  Some do not need the choice, some are only looking for 
MS based products, some don't care about the OS...  On the other hand, 
if you need to support multiple platforms you either need to make 
compromises to be able to adjust your product to all of them - or you 
need significant more development resources.  The sheer number of 
supported OS to choose from is not a value in itself.

Actually I believe "choice" is often overrated.  You certainly cannot 
look at it alone and make that a major selling point for one product or 
another.  If you are building a product for a wide range of application 
cases from very small to very large then it's probably a good idea to 
look at the platforms a database product can be run on.  But even then 
you should closely look at the details - it may turn out that supporting 
one DB product on multiple platforms turns out as expensive as having to 
support different products altogether.  If you do not need to support 
such a variety of environments then the platform may be totally 
irrelevant and other aspects such as customer requirements, reusing 
existing code base etc. might be paramount.  Many companies today use 
more than one OS so the aspect of additional cost for maintaining 
another platform often is non existent.

Kind regards

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

"Robert Klemme" <shortcutter@googlemail.com> wrote in message 
news:80n5p6F596U1@mid.individual.net...
> On 03/21/2010 04:49 PM, Bob Jones wrote:
>> "Robert Klemme" <shortcutter@googlemail.com> wrote in message 
>> news:80mfq8Fo7U1@mid.individual.net...
>>> On 20.03.2010 23:20, Galen Boyer wrote:
>>>> Robert Klemme<shortcutter@googlemail.com>  writes:
>>>>
>>>>> On 03/19/2010 08:39 PM, Sybrand Bakker wrote:
>>>>>> On Thu, 18 Mar 2010 22:26:44 -0400, Thomas Gagne
>>>>>> <TandGandGAGNE@gmail.com>  wrote:
>>>>>>
>>>>>>> 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 Oracl
>>>>>> Mickeysoft has never understood the Procedure concept, and ignored 
>>>>>> the
>>>>>> formal defintiion and abused it to return a result set.
>>>>>> It seems like you belong to the class of sqlserver 'developers' which
>>>>>> is so narrow-minded they automatically reject everything done
>>>>>> differently by Oracle and start bashing Oracle for it.
>>>>>> Luckily sqlserver is incapable of being an enterprise class product,
>>>>>> just because of its poor architecture and vendor lock-in, so your
>>>>>> 'objections'  are futile.
>>>>> I would not be too sure of that.  SQL Sever isn't as bad as people are
>>>>> trying to make it look - and it's gaining ground, especially in the
>>>>> area of dealing with larger data sets.  Maybe it's not as "enterprise
>>>>> class" as Oracle is (or is claimed to be) but the management tools
>>>>> with good graphical user interface were there before Oracle had Grid
>>>>> Control. Yes, I know - real DBA's use command line, but there are
>>>>> situations where a graphical visualization can greatly help.
>>>> One of the biggest winning arguments for Oracle, is that it run on
>>>> almost all platforms, MS products only run on one.
>>> Why is that an argument pro Oracle?
>>>
>>
>> Very simple, choices. That is a major advantage.
>
> I beg to differ.  Some do not need the choice, some are only looking for 
> MS based products, some don't care about the OS...

Saying I don't care about OS is like saying I don't care about cost, 
features, scalability, and so forth. I have no doubt some companies think 
Windows is the best solution for everything - at least M$ is one of them.

> On the other hand, if you need to support multiple platforms you either 
> need to make compromises to be able to adjust your product to all of 
> them - or you need significant more development resources.

Why? Oracle has already done that. You talk to the databases the same way 
across all platforms.

>  The sheer number of supported OS to choose from is not a value in itself.
>

Of course, choice has no value. Scalability has no value. Security has no 
value. Nothing has value. 

On Sun, 21 Mar 2010 19:10:41 +0100, Robert Klemme wrote:

> Actually I believe "choice" is often overrated.

To paraphrase Benjamin Franklin, those who would sacrifice choice for 
reliability deserve neither.



-- 
http://mgogala.freehostia.com

On 03/21/2010 08:08 PM, Bob Jones wrote:
> "Robert Klemme" <shortcutter@googlemail.com> wrote in message 
> news:80n5p6F596U1@mid.individual.net...
>> On 03/21/2010 04:49 PM, Bob Jones wrote:
>>> "Robert Klemme" <shortcutter@googlemail.com> wrote in message 
>>> news:80mfq8Fo7U1@mid.individual.net...
>>>> On 20.03.2010 23:20, Galen Boyer wrote:
>>>>> Robert Klemme<shortcutter@googlemail.com>  writes:
>>>>>
>>>>>> On 03/19/2010 08:39 PM, Sybrand Bakker wrote:
>>>>>>> On Thu, 18 Mar 2010 22:26:44 -0400, Thomas Gagne
>>>>>>> <TandGandGAGNE@gmail.com>  wrote:
>>>>>>>
>>>>>>>> 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 Oracl
>>>>>>> Mickeysoft has never understood the Procedure concept, and ignored 
>>>>>>> the
>>>>>>> formal defintiion and abused it to return a result set.
>>>>>>> It seems like you belong to the class of sqlserver 'developers' which
>>>>>>> is so narrow-minded they automatically reject everything done
>>>>>>> differently by Oracle and start bashing Oracle for it.
>>>>>>> Luckily sqlserver is incapable of being an enterprise class product,
>>>>>>> just because of its poor architecture and vendor lock-in, so your
>>>>>>> 'objections'  are futile.
>>>>>> I would not be too sure of that.  SQL Sever isn't as bad as people are
>>>>>> trying to make it look - and it's gaining ground, especially in the
>>>>>> area of dealing with larger data sets.  Maybe it's not as "enterprise
>>>>>> class" as Oracle is (or is claimed to be) but the management tools
>>>>>> with good graphical user interface were there before Oracle had Grid
>>>>>> Control. Yes, I know - real DBA's use command line, but there are
>>>>>> situations where a graphical visualization can greatly help.
>>>>> One of the biggest winning arguments for Oracle, is that it run on
>>>>> almost all platforms, MS products only run on one.
>>>> Why is that an argument pro Oracle?
>>>>
>>> Very simple, choices. That is a major advantage.
>> I beg to differ.  Some do not need the choice, some are only looking for 
>> MS based products, some don't care about the OS...
> 
> Saying I don't care about OS is like saying I don't care about cost, 
> features, scalability, and so forth. I have no doubt some companies think 
> Windows is the best solution for everything - at least M$ is one of them.

Not at all.  Saying one does not care about the OS can also mean that 
one is willing to pick the OS after the product one wants to use.  With 
Windows, Solaris and Linux administrators in house the cost of 
maintaining an additional machine with either OS might be identical. 
But the cost of the application or the choice of applications available 
might differ dramatic.  For such a company it is a totally reasonable 
approach to not look at the platforms something can run on in the first 
place but rather on the choice of applications.

>> On the other hand, if you need to support multiple platforms you either 
>> need to make compromises to be able to adjust your product to all of 
>> them - or you need significant more development resources.
> 
> Why? Oracle has already done that. You talk to the databases the same way 
> across all platforms.

Do you also administer Oracle the same way on all platforms?  Does it 
have the same performance characteristics on all platforms and the same 
bugs?  Do all features work the same way on all platforms?  Is it 
sufficient to test an application against Oracle on a single platform?

>>  The sheer number of supported OS to choose from is not a value in itself.
> 
> Of course, choice has no value. Scalability has no value. Security has no 
> value. Nothing has value. 

I never said that choice or any of the other things you mention has no 
value.  Why do I get the impression that you intentionally misquote me?

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Galen Boyer wrote:
> <snip>
>
> One of the biggest winning arguments for Oracle, is that it run on
> almost all platforms, MS products only run on one.
>
>   
That argument had traction in the 80s and 90s when hardware was 
expensive--especially minicomputers.  Hardware prices have come down so 
much that being able to run on multiple operating systems (back when 
manufacturers had their own OSes) was a big plus.

In the 21st century, the most expensive component of a system is its 
software, and the most expensive component of that is developer time, so 
"enterprise" had better find a new meaning.

Programmer appeal is a strength of MySQL.  Except for its screwy user 
scheme, it's easier to install and manage than either Oracle or Sybase, 
and includes many features near-and-dear to programmers.  More and more, 
programmers are the ones sneaking technologies like PHP, Python, Linux, 
and now MySQL thru the IT back door.  I haven't heard or read of anyone 
sneaking Oracle into a project because it's easy to install, use, 
administer, or program.

My observation regarding Oracle, DB2, Sybase, and SqlServer is the need 
to compete for programmer mindshare, 'cause "enterprise" software is 
increasingly more about "enterprise" cost than "enterprise" features, 
which if not available today will be tomorrow for much less.

Op 22-3-2010 22:25, Thomas Gagne schreef:
> Galen Boyer wrote:
>> <snip>
>>
>> One of the biggest winning arguments for Oracle, is that it run on
>> almost all platforms, MS products only run on one.
>>
> That argument had traction in the 80s and 90s when hardware was
> expensive--especially minicomputers. Hardware prices have come down so
> much that being able to run on multiple operating systems (back when
> manufacturers had their own OSes) was a big plus.
>
> In the 21st century, the most expensive component of a system is its
> software, and the most expensive component of that is developer time, so
> "enterprise" had better find a new meaning.
>
> Programmer appeal is a strength of MySQL. Except for its screwy user
> scheme, it's easier to install and manage than either Oracle or Sybase,
> and includes many features near-and-dear to programmers. More and more,
> programmers are the ones sneaking technologies like PHP, Python, Linux,
> and now MySQL thru the IT back door. I haven't heard or read of anyone
> sneaking Oracle into a project because it's easy to install, use,
> administer, or program.

Well, I'm one of the sneaky Oracle pushers, pushing a standard edition 
one of XE environment with Apex wherever I can. Installing XE is running 
an executable on Windows or rpm on Linux. That's all. SE One is also a 
point and click installation and runs 'out of the box'. Even SE One 
Application Server runs out of the box after little more than 20 
minutes, (at least, without patching..), including an Oracle Internet 
Directory LDAP Server and Oracle Portal.


> My observation regarding Oracle, DB2, Sybase, and SqlServer is the need
> to compete for programmer mindshare, 'cause "enterprise" software is
> increasingly more about "enterprise" cost than "enterprise" features,
> which if not available today will be tomorrow for much less.

Yep. That's why there is also Standard Edition and Standard Edition One.

Shakespeare

On Mar 23, 8:25=A0am, Thomas Gagne <TandGandGA...@gmail.com> wrote:

> Programmer appeal is a strength of MySQL. =A0Except for its screwy user
> scheme, it's easier to install and manage than either Oracle or Sybase,
> and includes many features near-and-dear to programmers. =A0More and more=
,
> programmers are the ones sneaking technologies like PHP, Python, Linux,
> and now MySQL thru the IT back door. =A0I haven't heard or read of anyone
> sneaking Oracle into a project because it's easy to install, use,
> administer, or program.

Hmm.

I don't wish to offend anyone, by big beef with programmers is that
they tend to regard the DB as "part of the application", and make
their decisions accordingly. That may well be why they choose products
like MySQL when it's simply not appropriate to do so. (Sometimes it
is, sometimes it isn't.)

Frankly, I think decisions like "which RDBMS do we use?" are best left
to people with a better appreciation of the big picture. They might be
an architect, technical manager, DBA, or some combination. But
programmers? Sorry, no. There's more to that decision than how it
looks to the programmers.

-- Phil

>>>> Very simple, choices. That is a major advantage.
>>> I beg to differ.  Some do not need the choice, some are only looking for 
>>> MS based products, some don't care about the OS...
>>
>> Saying I don't care about OS is like saying I don't care about cost, 
>> features, scalability, and so forth. I have no doubt some companies think 
>> Windows is the best solution for everything - at least M$ is one of them.
>
> Not at all.  Saying one does not care about the OS can also mean that one 
> is willing to pick the OS after the product one wants to use.  With 
> Windows, Solaris and Linux administrators in house the cost of maintaining 
> an additional machine with either OS might be identical.

Isn't that the benefit of having choices?

> But the cost of the application or the choice of applications available 
> might differ dramatic.  For such a company it is a totally reasonable 
> approach to not look at the platforms something can run on in the first 
> place but rather on the choice of applications.
>

Ok, another benefit of having choices.

>>> On the other hand, if you need to support multiple platforms you either 
>>> need to make compromises to be able to adjust your product to all of 
>>> them - or you need significant more development resources.
>>
>> Why? Oracle has already done that. You talk to the databases the same way 
>> across all platforms.
>
> Do you also administer Oracle the same way on all platforms?

Yes for the most part.

> Does it have the same performance characteristics on all platforms?

No, it may not even on the same platform. Does it need to?

> Do all features work the same way on all platforms?

Yes, Oracle is platform-independent. I can't say the same about SQL server 
though.

> Is it sufficient to test an application against Oracle on a single 
> platform?
>

Err, I will need a crystal ball to answer that one.

>>>  The sheer number of supported OS to choose from is not a value in 
>>> itself.
>>
>> Of course, choice has no value. Scalability has no value. Security has no 
>> value. Nothing has value.
>
> I never said that choice or any of the other things you mention has no 
> value.  Why do I get the impression that you intentionally misquote me?
>

Ok, let me quote you exactly.

"The sheer number of supported OS to choose from is not a value in itself."

On Mar 23, 8:25=A0am, Thomas Gagne <TandGandGA...@gmail.com> wrote:

> In the 21st century, the most expensive component of a system is its
> software, and the most expensive component of that is developer time, so
> "enterprise" had better find a new meaning.

Or had better find cheaper developers, because gthe current crop is
waaaaay over-priced...


> Programmer appeal is a strength of MySQL. =A0Except for its screwy user
> scheme, it's easier to install and manage than either Oracle or Sybase,
> and includes many features near-and-dear to programmers.

Ease of install has nothing to do with "programmer appeal".


> More and more,
> programmers are the ones sneaking technologies like PHP, Python, Linux,
> and now MySQL thru the IT back door.

More and more programmers are being identified as sneaky people.

>=A0I haven't heard or read of anyone
> sneaking Oracle into a project because it's easy to install, use,
> administer, or program.

You won't hear of anyone "sneaking" Oracle mostly because it is used
in places where responsibility and professionalism are the determinant
factors, as opposed to "sneakyness".


> My observation regarding Oracle, DB2, Sybase, and SqlServer is the need
> to compete for programmer mindshare,

Not for "sneaky" developers?

>  'cause "enterprise" software is
> increasingly more about "enterprise" cost than "enterprise" features,
> which if not available today will be tomorrow for much less.

What's that got to do with "programmer mindshare" and "sneakyness"?

Do you even have a consistent point without changing universe of
discourse halfway through a sentence?
On second thought, don't bother answering: I'm not interested in your
sneaky thoughts!

1.
Half of the uses of autonomous transactions I see when enabling Oracle 
applications wrap DDL statements.

2.
If Oracle programmers don't need local temp tables then what is BULK 
COLLECT, FORALL and INDEX BY all about?
They are everywhere!

-- 
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

On Tue, 23 Mar 2010 08:49:19 -0400, Serge Rielau wrote:

> If Oracle programmers don't need local temp tables then what is BULK
> COLLECT, FORALL and INDEX BY all about? They are everywhere!

Well, we do need them, but we have fancy names for them and a proper dose 
of disdain for the users of other databases which did not come up with 
such fancy names for the local temporary tables. Such a common and 
ordinary name like "local temp tables" shows a worrying lack of 
imagination and creativity.



-- 
http://mgogala.freehostia.com

On Mon, 22 Mar 2010 18:36:52 -0700, Noons wrote:


> More and more programmers are being identified as sneaky people.

Gee, I started feeling like Wylie E. Coyote, pure genius, already.



-- 
http://mgogala.freehostia.com

On Mar 23, 6:21=A0am, Mladen Gogala <gogala.mla...@gmail.com> wrote:
> On Mon, 22 Mar 2010 18:36:52 -0700, Noons wrote:
> > More and more programmers are being identified as sneaky people.
>
> Gee, I started feeling like Wylie E. Coyote, pure genius, already.
>
> --http://mgogala.freehos.com

But what do geniuses do when they finally succeed?
http://en.wikipedia.org/wiki/Soup_or_Sonic

I'm struck by the parallel between that ending and scalability issues
coming from developer-think, particularly those coming from the MS
world.  I think that may be a message of the Effective Oracle by
Design book, since so many "architects" are just developers who moved
up in the world.

jg
--
@home.com is bogus.
=93What a terrible, terrible man.=94 Judge goes off about Dr. Phil
http://www.signonsandiego.com/news/2010/mar/23/shoplifters-who-were-on-apos=
dr-philapos-headed-to/

On Mar 23, 8:49=A0am, Serge Rielau <srie...@ca.ibm.com> wrote:

snip

> If Oracle programmers don't need local temp tables then what is BULK
> COLLECT, FORALL and INDEX BY all about?
> They are everywhere!

That's stuff inside PLSQL or special proprietary extensions of SQL by
Oracle for more processing power.

The basic mantra of scalable Oracle performance is to take advantage
of the features provided and don't try to do stuff in a database
agnostic/neutral manner.

Logically I guess some of those features kinda/sorta smell like
temporary tables.  That's the kind of thing that Thomas Gagne needs to
take the time to learn about if he wants to harness the power of
Oracle.  The learning curve takes a while though and you need to throw
out some of the preconceived ideas.

On Mar 22, 9:36=A0pm, Noons <wizofo...@gmail.com> wrote:

snip

> > More and more,
> > programmers are the ones sneaking technologies like PHP, Python, Linux,
> > and now MySQL thru the IT back door.
>
> More and more programmers are being identified as sneaky people.

Bingo!  He shoots he scores!  Ouch ... someone call a medic!

> >=A0I haven't heard or read of anyone
> > sneaking Oracle into a project because it's easy to install, use,
> > administer, or program.
>
> You won't hear of anyone "sneaking" Oracle mostly because it is used
> in places where responsibility and professionalism are the determinant
> factors, as opposed to "sneakyness".

Another valid point ... no arguing that responsibility and
professionalism seem to be factors on the decline in IT these days.

Who wants to design an architecture or consider implications when you
can bring in some open source database on your laptop and just start
coding?

> > =A0'cause "enterprise" software is
> > increasingly more about "enterprise" cost than "enterprise" features,
> > which if not available today will be tomorrow for much less.

Since Thomas knows the features of Oracle so well he probably has a
very good perspective on all sorts of enterprise features in general!

On Tue, 23 Mar 2010 12:20:30 -0700, John Hurley wrote:

>> If Oracle programmers don't need local temp tables then what is BULK
>> COLLECT, FORALL and INDEX BY all about? They are everywhere!
> 
> That's stuff inside PLSQL or special proprietary extensions of SQL by
> Oracle for more processing power.

Oh boy, those are local temp tables. You can even do them explicitly by 
using /*+ MATERIALIZE */ in a  "WITH" clause. And you must admit that the 
local temporary tables can be rather handy when you need them.



-- 
http://mgogala.byethost5.com

Robert Klemme <shortcutter@googlemail.com> writes:

>> One of the biggest winning arguments for Oracle, is that it run on
>> almost all platforms, MS products only run on one.
>
> Why is that an argument pro Oracle?

Hm... So, on any OS you can run Oracle?  How is that not an argument
that is pro Oracle?

-- 
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

On 25 Mar, 01:55, Galen Boyer <galen_bo...@yahoo.com> wrote:
> Robert Klemme <shortcut...@googlemail.com> writes:
> >> One of the biggest winning arguments for Oracle, is that it run on
> >> almost all platforms, MS products only run on one.
>
> > Why is that an argument pro Oracle?
>
> Hm... So, on any OS you can run Oracle? =A0How is that not an argument
> that is pro Oracle?
>
> --
> Galen Boyer

At the very least we can agree that it's a limit of SQL Server :)

-g

"Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> wrote in message 
news:RM6dnTmUA8fLkjvWnZ2dnUVZ8mqdnZ2d@bt.com...
>
> I think the thing that looks odd to the Oracle professional is as 
> follows:
> In SQL Server you can do something like this (apologies for incorrect
> table and column names, I don't have a copy of the software handy):
>
> create procedure jpl
> as
>
> set nocount on
> select name from sys.schemas;
>
> select name, physical_name
> from sys.master_files;
>
> set nocount off
> go
>
> From (say) sqlcmd you can now type:
>
>> jpl
>> go
>
> This effectively executes and displays the results of the
> two queries in the procedure - and this seems "viable"
> in something like a lightweight tool supplied by the
> people who produced the database software.
>
> BUT -
> The procedure seems to have taken on the responsibility of knowing
> how to output the data to the front-end.
>
> So, from the viewpoint of the Oracle developer, what do you have to
> do in the application code to know that when you call the procedure
> you're going to get two result sets which are different shapes. 
> (Presumably you want to see two sets of data, rather than one set of data 
> which is just a single column very wide string.)  And how does the 
> front-end code know that it might, or might not, get some "data" which is 
> actually row counts depending on whether the procedure "set nocount on" 
> or not ?
>
> Does your application call to the procedure have to know about all the 
> result sets that could be produced in the procedure and call the 
> procedure passing in references to some sort of cursor handle ?
>
> Please bear in mind that this question is being asked from a perspective 
> of total ignorance of how you are expected to use procedures in 
> application code written for SQL Server.
>
>

Would anyone care to make a technical comment on
my earlier comment

I'm still interested to hear how the front-end code can handle the
output from a procedure when it doesn't have any information
about what that output might look like.


-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

On Thu, 25 Mar 2010 13:01:58 +0000, Jonathan Lewis wrote:

> Would anyone care to make a technical comment on my earlier comment
> 
> I'm still interested to hear how the front-end code can handle the
> output from a procedure when it doesn't have any information about what
> that output might look like.

Well, there must be some information. If the output type is a cursor, one 
can describe the cursor. In Perl, which is my front end of choice, a 
statement handle has the following properties which describe the output 
rather well: NUM_OF_FIELDS,NAME,TYPE,PRECISION and SCALE. I can always 
construct my output of choice, based on the handle description, even if I 
don't know what the original statement looks like, which is frequently 
the case with the dynamic SQL. However, I have to know that the output 
type is a cursor, not a scalar variable. So, if my output is a statement 
handle called $sth, I would do the following:

my $nf = $sth->{NUM_OF_FIELDS};
 for ( my $i = 0; $i < $nf; $i++ ) {
    my $name = $sth->{NAME}[$i];
    my $type = $sth->{TYPE}[$i];
    my $prec = $sth->{PRECISION}[$i];
    my $scle = $sth->{SCALE}[$i];
    my $tn=$db->type_info($type)->{TYPE_NAME};
    print
        "Field number $i: name $name of type $tn". 
        "with precision $prec,$scle\n";
}

That would give me the exact description of the output. One cannot know 
nothing of the output, that would make any programming impossible. 
Executing a procedure which you know nothing about is like the Russian 
roulette: you never know when the chamber will be loaded and what will 
come out of the barrel.

-- 
http://mgogala.byethost5.com

"Mladen Gogala" <no@email.here.invalid> wrote in message 
news:pan.2010.03.25.15.10.07@email.here.invalid...
> On Thu, 25 Mar 2010 13:01:58 +0000, Jonathan Lewis wrote:
>
>> Would anyone care to make a technical comment on my earlier comment
>>
>> I'm still interested to hear how the front-end code can handle the
>> output from a procedure when it doesn't have any information about what
>> that output might look like.
>
> Well, there must be some information.

One would hope so - but in the Oracle world your front-end code has
a formal link with the datbaase through the input types for a procedure
and the output  type for a function, and it's a little bit difficult to 
change the
(database) code in a way that changes the input and output types without
realising that the front-end code might need to be modified.

On the other hand, in the SQL Server world you can have a procedure
that has no apparent feature for a formal definition of what it's going to
output - and therefore no formal mechanism for the front-end to associate
what it's expecting with what the database code might deliver.


-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

On 3/25/2010 11:38 AM, Jonathan Lewis wrote:
> On the other hand, in the SQL Server world you can have a procedure
> that has no apparent feature for a formal definition of what it's going to
> output - and therefore no formal mechanism for the front-end to associate
> what it's expecting with what the database code might deliver.
Actually I think that is an outdated statement. AFAIK TSQL does at least 
have the concept of table parameters (which is roughly equivalent to an 
associative array of records).

Another key difference of TSQL is that these select statements within a 
batch or procedure flow back to the client immediately. That is while 
the TSQL script is still executing.
So when you invoke a procedure result are coming back long before the 
procedure completes. PRINT is a special case of that.

This makes for a different approach to track progress compared to 
APPLICATION_INFO. Quite interesting, actually.

Cheers
Serge
-- 
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

"Serge Rielau" <srielau@ca.ibm.com> wrote in message 
news:811vmcFklmU1@mid.individual.net...
> On 3/25/2010 11:38 AM, Jonathan Lewis wrote:
>> On the other hand, in the SQL Server world you can have a procedure
>> that has no apparent feature for a formal definition of what it's going 
>> to
>> output - and therefore no formal mechanism for the front-end to 
>> associate
>> what it's expecting with what the database code might deliver.
> Actually I think that is an outdated statement. AFAIK TSQL does at least 
> have the concept of table parameters (which is roughly equivalent to an 
> associative array of records).
>

There are all sorts of parameters that a procedure can have, and
lots of data types that a function can return - but the point I was
making was that you can write a procedure that has NO formal
parameter declaration that can still "return" data. In the absence
of  a formal declaration, how does the front end know what to do
with the stream of data that gets generated.


> Another key difference of TSQL is that these select statements within a 
> batch or procedure flow back to the client immediately.

That, of course, is nearly the problem I was highlighting. The results of
a select statement "flow back" to the client - but how is the client 
supposed
to know how to process that stream of data. The procedure may not have
any formal declaration that the client can use to decide how to handle that 
data.




-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

On Mar 25, 8:38=A0am, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> "Mladen Gogala" <n...@email.here.invalid> wrote in message
>
> news:pan.2010.03.25.15.10.07@email.here.invalid...
>
> > On Thu, 25 Mar 2010 13:01:58 +0000, Jonathan Lewis wrote:
>
> >> Would anyone care to make a technical comment on my earlier comment
>
> >> I'm still interested to hear how the front-end code can handle the
> >> output from a procedure when it doesn't have any information about wha=
t
> >> that output might look like.
>
> > Well, there must be some information.
>
> One would hope so - but in the Oracle world your front-end code has
> a formal link with the datbaase through the input types for a procedure
> and the output =A0type for a function, and it's a little bit difficult to
> change the
> (database) code in a way that changes the input and output types without
> realising that the front-end code might need to be modified.
>
> On the other hand, in the SQL Server world you can have a procedure
> that has no apparent feature for a formal definition of what it's going t=
o
> output - and therefore no formal mechanism for the front-end to associate
> what it's expecting with what the database code might deliver.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com

I work in several languages that have implicit data conversions.  This
is generally considered "bad form," in that it leads to its own class
of errors.  I'd say the same applies to the current subject.  Some
things appear to become easier and more straightforward in the short
term.  It's a false economy.

jg
--
@home.com is bogus.
http://www.sfgate.com/cgi-bin/article.cgi?file=3D/c/a/2007/09/30/MNDTSEMSJ.=
DTL&tsp=3Dbusiness

On 3/25/2010 5:05 PM, Jonathan Lewis wrote:
> "Serge Rielau"<srielau@ca.ibm.com>  wrote in message
> news:811vmcFklmU1@mid.individual.net...
>> On 3/25/2010 11:38 AM, Jonathan Lewis wrote:
>>> On the other hand, in the SQL Server world you can have a procedure
>>> that has no apparent feature for a formal definition of what it's going
>>> to
>>> output - and therefore no formal mechanism for the front-end to
>>> associate
>>> what it's expecting with what the database code might deliver.
>> Actually I think that is an outdated statement. AFAIK TSQL does at least
>> have the concept of table parameters (which is roughly equivalent to an
>> associative array of records).
>>
>
> There are all sorts of parameters that a procedure can have, and
> lots of data types that a function can return - but the point I was
> making was that you can write a procedure that has NO formal
> parameter declaration that can still "return" data. In the absence
> of  a formal declaration, how does the front end know what to do
> with the stream of data that gets generated.
>
>
>> Another key difference of TSQL is that these select statements within a
>> batch or procedure flow back to the client immediately.
>
> That, of course, is nearly the problem I was highlighting. The results of
> a select statement "flow back" to the client - but how is the client
> supposed
> to know how to process that stream of data. The procedure may not have
> any formal declaration that the client can use to decide how to handle that
> data.
How does Oracle handle an OUT parameter of ANYDATA?
(As seen e.g. in DBMS_AQ)
How is it different from a weakly typed refcursor?
Clearly the resultset has to be self describing.

I agree with you and Joel, that this is bad form btw.
All I'm doing is pointing out that within themselves these features do 
have value and rarely ever is any vendor alone in allowing the user to 
shoot themselves in the foot.

Cheers
Serge
-- 
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

... can't resist an NRA quote here:
It's not features that kill applications, it's programmers. ;-)

-- 
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

"Serge Rielau" <srielau@ca.ibm.com> wrote in message 
news:8123rpFe2uU1@mid.individual.net...
> On 3/25/2010 5:05 PM, Jonathan Lewis wrote:
>> "Serge Rielau"<srielau@ca.ibm.com>  wrote in message
>>> Another key difference of TSQL is that these select statements within a
>>> batch or procedure flow back to the client immediately.

>>
>> That, of course, is nearly the problem I was highlighting. The results 
>> of
>> a select statement "flow back" to the client - but how is the client
>> supposed
>> to know how to process that stream of data. The procedure may not have
>> any formal declaration that the client can use to decide how to handle 
>> that
>> data.

> How does Oracle handle an OUT parameter of ANYDATA?
> (As seen e.g. in DBMS_AQ)
> How is it different from a weakly typed refcursor?
> Clearly the resultset has to be self describing.
>

I'm not being judgemental (yet) - I just want to know how it works.

From what you're saying it seems the programmer who
calls the procedure has to write the code to unpack the
data stream correctly, potentially writing some very generic
code to handle a stream that says things like:
    The next 10 records are column names
        name1, name2, ...
    The next 10 records are column types
        date, varchar2(10) ...
    The next data stream is an array of rows terminated by end-of-data
        etc.

Is this vaguely correct ? I assume there are some library routines
(like the Oracle pl/sql obind, odesc, etc.) to eliminate the drudge
work of connecting the stream to the local arrays, of course.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

I'm not a SQL Server user, but some Googling turned up this page that
discusses a generic handler for SQL Server result sets:

http://stackoverflow.com/questions/1807899/c-function-to-return-generic-objects-entities

Clearly, there are some library calls involved, but it looks possible,
at least with C#.


-- Phil

"Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> writes:

> Would anyone care to make a technical comment on
> my earlier comment
>
> I'm still interested to hear how the front-end code can handle the
> output from a procedure when it doesn't have any information
> about what that output might look like.

From the java perspective, a procedure executes, and because you called
it, you know whether it returns a result set or not, so, in java you ask
the result set object for the result set, so, to the java coder, the API
works as expected.  But, the issue seems to be exposed by the error
checking that is forced upon the java codebase.  Upon execution of a
procedure within Sybase (and I guess in SQL Server) you have to iterate
a stack of things.  Each thing is tested as to whether it is a return
code or not, and if it is a return code, then a boolean of yes or no
tells you whether the procedure has errored or was successful.  Within
the procedure, an error does not actually bubble to the top as it does
in Oracle, but, instead gets put on this stack of things.

So, on this return stack is, exactly as you are questioning, multiple
types of things.  How is the client supposed to interpret these
different things. My guess is that the implementation of the jdbc
drivers iterate the same stack I just described looking for result sets,
and then, return those things.

That is what I know and am surmising from the java perspective.  I would
guess its the same in other client codebases

-- 
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

<phil_herring@yahoo.com.au> wrote in message 
news:f7f782cf-52db-4a34-9661-a9f0d52404f7@n20g2000prh.googlegroups.com...
> I'm not a SQL Server user, but some Googling turned up this page that
> discusses a generic handler for SQL Server result sets:
>
> http://stackoverflow.com/questions/1807899/c-function-to-return-generic-objects-entities
>
> Clearly, there are some library calls involved, but it looks possible,
> at least with C#.
>
>
> -- Phil



Phil,

Thanks for passing on an interesting link.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

"Galen Boyer" <galen_boyer@yahoo.com> wrote in message 
news:uwrwzd92d.fsf@www.yahoo.com...
> "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> writes:
>
>> Would anyone care to make a technical comment on
>> my earlier comment
>>
>> I'm still interested to hear how the front-end code can handle the
>> output from a procedure when it doesn't have any information
>> about what that output might look like.
>
> From the java perspective, a procedure executes, and because you called
> it, you know whether it returns a result set or not, so, in java you ask
> the result set object for the result set, so, to the java coder, the API
> works as expected.  But, the issue seems to be exposed by the error
> checking that is forced upon the java codebase.  Upon execution of a
> procedure within Sybase (and I guess in SQL Server) you have to iterate
> a stack of things.  Each thing is tested as to whether it is a return
> code or not, and if it is a return code, then a boolean of yes or no
> tells you whether the procedure has errored or was successful.  Within
> the procedure, an error does not actually bubble to the top as it does
> in Oracle, but, instead gets put on this stack of things.
>
> So, on this return stack is, exactly as you are questioning, multiple
> types of things.  How is the client supposed to interpret these
> different things. My guess is that the implementation of the jdbc
> drivers iterate the same stack I just described looking for result sets,
> and then, return those things.
>
> That is what I know and am surmising from the java perspective.  I would
> guess its the same in other client codebases
>
> -- 
> Galen Boyer
>
> --- news://freenews.netfront.net/ - complaints: news@netfront.net ---


Galen

Thanks for that.

I think your comments match the degree of complexity that I had
thought might be necessary.  I wonder if this means that people
tend to write "simple" procedures that only output one type of
record set, rather than the multiple types that the OP was asking
about.


-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

On Mar 26, 5:10=A0pm, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:

snip

> Thanks for that.
>
> I think your comments match the degree of complexity that I had
> thought might be necessary. =A0I wonder if this means that people
> tend to write "simple" procedures that only output one type of
> record set, rather than the multiple types that the OP was asking
> about.

I have a funny feeling the OP may not be answering these questions.

It is easy asking why Oracle doesn't have something that a different
database apparently has.  It gets more involved showing how exactly
one would use those strange features in a practical manner.

Maybe those types of procedures in SQL Server are used in some batch
manner and are just putting out data.  In that case maybe the OP does
not understand the spool command in sqlplus?