Re: Simple Create Procedure Problem..

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:41ac00bc$0$17542$afc38c87_at_news.optusnet.com.au...
| Arijit Chatterjee wrote:
| > RESPECTED FACULTIES,
| > I AM TRYING TO WRITE A PROCEDURE CODE IS GIVEN BELOW
| > ===========================================
| > CREATE PROCEDURE PROC(ACCNO IN NUMBER)
| > AS
| > BEGIN
| > SELECT * FROM ACCOUNTMASTER WHERE ACCOUNTCODE=ACCNO;
| > END;
| > ============================================
| > aND I AM GETTING THE ERROR
| >
| > --------------------------------------------
| > Warning: Procedure created with compilation errors.
| > --------------------------------------------
| > I HAVE CONNECTED WITH SCOTT/TIGER AND SCOTT HAS GIVEN THE
| > PRIVILEGES TO CREATE PROCEDURE.
| >
| > NOW WHAT SHOULD BE THE REASON FOR THIS ERROR?
| >
| > REGARDS
| > ARIJIT CHATTERJEE
|
|
| How about learning how to type "show errors" when you receive that
message?
|
| If you did, maybe you'd see something like this:
|
| SQL> create procedure proc (accno in number)
| 2 as
| 3 begin
| 4 select * from emp where empno=accno;
| 5 end;
| 6 /
|
| Warning: Procedure created with compilation errors.
|
| SQL> show errors
| Errors for PROCEDURE PROC:
|
| LINE/COL ERROR
| -------- -----------------------------------------------------------------
| 4/1 PLS-00428: an INTO clause is expected in this SELECT statement
|
| Which kind of gives you the clue as to where the error lies. And one
| possible solution would be:
|
| SQL> create or replace procedure proc (accno in number)
| 2 as
| 3 begin
| 4 execute immediate 'select * from emp where empno=' || accno;
| 5 end;
| 6 /
|
| Procedure created.
|
|
| But this is extremely basic stuff, so you'd best start reading up on how
| to write PL/SQL.
|
| Regards
| HJR
slow down, howard....

your fix steps backwards a bit by introducing execute immediate where not necessary, and still not providing an INTO clause

perhaps you meant something more like this:

create or replace procedure proc (accno in number) as
  r_emp emp%rowtype;
begin

     select *
     into   r_emp
     from emp where empno = accno;

     -- then do something with r_emp

Arijit, study up on Oracle's PL/SQL Users guide -- do you have one or know where to get it?

++ mcs Received on Tue Nov 30 2004 - 04:34:34 CST