From: Thomas Gagne on
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.

From: ddf on
On Mar 17, 9:53 pm, Thomas Gagne <TandGandGA...(a)gmail.com> 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.

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 = 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 := 'select empno, ename, job, mgr, hiredate, sal, comm,
deptno from emp where job = '''||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
From: John Hurley on
On Mar 17, 9:53 pm, Thomas Gagne <TandGandGA...(a)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
From: Mladen Gogala on
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
From: Mladen Gogala on
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
 |  Next  |  Last
Pages: 1 2 3 4 5 6 7 8 9 10 11
Prev: semvmx
Next: 10.2.0.5 Patchset