Re: Can a procedure contain only a SELECT statement?

From: ddf <>
Date: Wed, 17 Mar 2010 21:37:44 -0700 (PDT)
Message-ID: <>

On Mar 17, 9:53 pm, 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.

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;
select *
into myrec
  from aTableName
 where aColumn = aLikeValue
 and rownum < 2;

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;
open mycur for select * from aTableName where aColumn like aLikeValue;

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);

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


show errors

set serveroutput on size 1000000


	type rcursor is ref cursor;
	emptab rcursor;
	emprec emp%rowtype;

		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;


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

Original text of this message