problem with my procedure

[bobby, morkos]

I have written a procedure and I'm trying to run it in my SQL plus console, which is not working. I don't understand, could anyone please tell me what I'm doing wrong. I have attached my code below:

CREATE PROCEDURE SHOW_CUSTOMERS
AS BEGIN
SELECT * 
FROM CUSTOMERS
ORDER BY CUST_ID
END;

[Muhammad Farooq]

Hi Bobby,
Try this procedure in scott/tiger schema:
====================================
CREATE OR REPLACE PROCEDURE SHOW_EMPLOYEES
AS
V_RECORD EMP%ROWTYPE;
BEGIN
SELECT *
INTO V_RECORD
FROM EMP
WHERE EMPNO=7934;
END;
/
====================================
Points to be considered
1. Use of INTO clause while using SELECT as record is being fethced and assigning it to a record.
2. Only ONE record can be fetched through select/into statement in a procedure (use of where clause).
3. First you have to declare a variable (here I have declare a variable of record type as the whole record is in question).
HIH,
[ April 09, 2002: Message edited by: Muhammad Farooq ]

[bobby, morkos]

I tried it and it gives me a compilation error. Could you please tell me what I did wrong. Thanks.

SQL> 
line 10 truncated.
  1  CREATE OR REPLACE PROCEDURE SHOW_CUSTOMERS
  2  AS
  3  V_RECORD EMP%ROWTYPE;
  4  BEGIN
  5  SELECT *
  6  INTO V_RECORD
  7  FROM customers
  8  WHERE CUST_ID=901;
  9* END;
 10  /
Warning: Procedure created with compilation errors.
SQL> SELECT * FROM CUSTOMERS
  2  WHERE CUST_ID=901;
   CUST_ID CUST_NAME                      CUST_PHONE
---------- ------------------------------ ------------------
       901 Ali                            514-875-9812
SQL> 

[Muhammad Farooq]

Line 3, change "emp" with "customers"

[bobby, morkos]

Thanks a lot, it works. How do you call that procedure from a java program. I have something like show below, please tell me if I'm doing it right. Thanks.

CallableStatement cs = con.prepareCall("{call SHOW_CUSTOMERS}");
      rs = cs.executeQuery();

Originally posted by Muhammad Farooq:
Line 3, change "emp" with "customers"