Stored Procedure in Oracle giving error PLS-00428

Question

I am trying to create the following procedure in oracle:

CREATE OR REPLACE PROCEDURE SPBILL (SPCLIENT_ID VARCHAR2) 
AS
BEGIN
   SELECT C.CLIENT_NAME, B.ROOM_ID, R.ROOM_COST, T.TREAT_NAME, T.TREAT_COST, (ROOM_COST*(B_END_DATE-B_START_DATE)+TREAT_COST) AS INVOICE
   FROM CLIENTS C, ROOMS R, TREATMENTS T, BOOKING B, PRESCRIPTION P
   WHERE C.CLIENT_ID=B.CLIENT_ID
   AND R.ROOM_ID=B.ROOM_ID
   AND B.CLIENT_ID=P.CLIENT_ID
   AND P.TREAT_ID=T.TREAT_ID
   AND C.CLIENT_ID=SPCLIENT_ID;
END SPBILL;

I am getting a "Procedure created with compilation errors" and the errors is PLS-00428, which required an INTO satement, but i do not understand why and where do i need it as my sql statement works just the way i want it without the procedure. But i need to create a procedure so i can call a specific client id and only recieve their data as an output.

possible duplicate of [Get resultset from oracle stored procedure](http://stackoverflow.com/questions/1170548/get-resultset-from-oracle-stored-procedure) — Allan, Dec 09 '14 at 15:16

Rajesh Chamarthi · Accepted Answer · 2014-12-09T15:35:04.037

When you are running the SQL directly using a client (SQL Plus or SQL Developer or Toad) , data is returned to the client. When you run the same query inside PL/SQL, you need to tell oracle what to do with that data. Usually programs store the output in Pl/SQL variables for further processing.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/selectinto_statement.htm

So, in your case, you might need something along these lines..

CREATE OR REPLACE PROCEDURE SPBILL (SPCLIENT_ID VARCHAR2) 
AS
  l_client_name clients.client_name%type;
  l_room_id rooms.room_id%type;
  ...
  l_invoice number(5,2);
BEGIN
   SELECT C.CLIENT_NAME, B.ROOM_ID, R.ROOM_COST, T.TREAT_NAME, T.TREAT_COST, (ROOM_COST*(B_END_DATE-B_START_DATE)+TREAT_COST)
   into l_client_name, l_room_id...l_invoice
   FROM CLIENTS C, ROOMS R, TREATMENTS T, BOOKING B, PRESCRIPTION P
   WHERE C.CLIENT_ID=B.CLIENT_ID
   AND R.ROOM_ID=B.ROOM_ID
   AND B.CLIENT_ID=P.CLIENT_ID
   AND P.TREAT_ID=T.TREAT_ID
   AND C.CLIENT_ID=SPCLIENT_ID;

   --further processing here based on variables above.
   dbms_output.put_line(l_invoice);
END SPBILL;

Once you compile without errors, you can run the procedure..

set serveroutput on; 
SPBILL(100);

Thank you, i have added what you explained and the procedure was created successfully, but when i execute it with a specified attribute it just says "procedure successfuly completed" but nothing is displayed — user3112130, Dec 09 '14 at 15:24
what are you trying to do with this stored procedure? If it is just display the results, you could run the sql directly. — Rajesh Chamarthi, Dec 09 '14 at 15:26

score 0 · Answer 2 · answered Dec 09 '14 at 15:10

0

Maybe you should use IS instead of AS CREATE OR REPLACE PROCEDURE SPBILL (SPCLIENT_ID VARCHAR2) IS ....

answered Dec 09 '14 at 15:10

user3489953

41
3

`is` and `as` are equivalent in this context. – Allan Dec 09 '14 at 15:17

score 0 · Answer 3 · answered Dec 09 '14 at 16:33

It looks like you have experience with MSSQL and expect Oracle would be the same. If so, it isn't true.

It looks like you try to return resultset from procedure as it usual in MSSQL. Oracle have no implicit resultsets at all. If you wish to do this, you should use explicit resultset eighter via returning REF CURSOR (http://www.orafaq.com/wiki/REF_CURSOR) or via TABLE FUNCTION (https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm).

But first of all, you should think if you really need this procedure at all. Generally, SELECT procedures in Oracle is part of doubtful design.

Stored Procedure in Oracle giving error PLS-00428

3 Answers3

Linked

Related