Re: Can a procedure contain only a SELECT statement?

From: ddf <oratune_at_msn.com>
Date: Wed, 17 Mar 2010 21:37:44 -0700 (PDT)
Message-ID: <144c8a21-c47f-413b-9ede-c61c87694a6c_at_t34g2000prm.googlegroups.com>

On Mar 17, 9:53 pm, Thomas Gagne <TandGandGA..._at_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 Received on Wed Mar 17 2010 - 23:37:44 CDT