Error(4,5): PLS-00428: an INTO clause is expected in this SELECT statement

Question

create or replace PROCEDURE find_Doctor (p_SSN in number) AS

    BEGIN
    select drName, drPhone
    from clients
    where SSN = p_SSN;

END find_Doctor;

I've got this stored procedure and I just want to output the resulting table from that call. Is there an easy way to do this without declaring a temporary table? I can't just make is a normal SQL query because I have to call it from a java program.

The problem is that I need the output to go to a java console. — WMS, Nov 20 '17 at 10:35
Why can't you call a normal SQL query from your Java program, using a `preparedStatenent` and `resultSet` ? — Alex Poole, Nov 20 '17 at 10:48

score 1 · Answer 1 · edited Nov 20 '17 at 10:48

1

In a Procedure you would need variable to hold the result output of the SQL query. You can then use the variable. Use this:

CREATE OR REPLACE PROCEDURE find_Doctor (p_SSN IN NUMBER)
AS
   var_nm   VARCHAR2 (100);
   var_ph   NUMBER;
BEGIN
   SELECT drName, drPhone
     INTO var_nm, var_ph
     FROM clients
    WHERE SSN = p_SSN;

 DBMS_OUTPUT.put_line ('Doc Name - ' || var_nm || 'Doc Ph. No-' || var_ph);
END find_Doctor;

Edit:

I can't just make is a normal SQL query because I have to call it from a java program.

You can then use SYS_REFCUSOR to return results, which can be mapped to a JDBC ResultSet.

CREATE OR REPLACE PROCEDURE find_Doctor (p_SSN   IN     NUMBER,
                                         VAR        OUT SYS_REFCURSOR)
AS
BEGIN
   OPEN VAR FOR
      SELECT drName, drPhone
        FROM clients
       WHERE SSN = p_SSN;
END find_Doctor;

edited Nov 20 '17 at 10:48

APC

144,005
19
170
281

answered Nov 20 '17 at 10:35

XING

9,608
4
22
38

Any reason for downvote. Please feel free to expression your views here. – XING Nov 20 '17 at 10:39
How do I map var to a ResultSet? is it not `ResultSet rs = call.executeQuery()`? – WMS Nov 20 '17 at 11:23
@WMS You can read this https://stackoverflow.com/questions/3572626/calling-stored-procedure-from-java-jpa or google down ,, You find many solutions – XING Nov 20 '17 at 11:35

score 0 · Answer 2 · answered Nov 20 '17 at 10:37

You should define out parameters drName and drPhone:

create or replace PROCEDURE find_Doctor (p_SSN in number, p_drName OUT 
VARCHAR2, p_drPhone OUT VARCHAR2) AS
BEGIN
  select drName, drPhone
  into p_drName, p_drPhone
  from clients
  where SSN = p_SSN;
END find_Doctor;

score 0 · Answer 3 · answered Nov 20 '17 at 10:38

Easy way is to learn tool you're used and best practices for it.

For me, best way is remove senseless procedure and just selects data you need. But if you adherent of 'SP only' approach you can use ref cursor to retrive required data:

create or replace function find_Doctor (p_SSN in number) 
  return sys_refcursor as

  v_result sys_refcursor;

BEGIN
  open v_result for 
    select drName, drPhone
    from clients
    where SSN = p_SSN;
  return v_result;
END find_Doctor;

Error(4,5): PLS-00428: an INTO clause is expected in this SELECT statement

3 Answers3