Re: Simple Create Procedure Problem..

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 Received on Mon Nov 29 2004 - 23:10:19 CST