Home Trying to execute a simple stored procedure – Help! Can't find an answer that works here

Questions

Trying to execute a simple stored procedure – Help! Can't find an answer that works here

June 8, 2024

I’m completely new to SQL and am trying to execute a stored procedure that I’m not even sure I wrote correctly. It’s supposed to give the total number of patients for a given dr, and this is the stored procedure I managed to compile in Oracle SQL Developer:

CREATE PROCEDURE Totalptcount
    (drid IN INTEGER,
     totalpts OUT INTEGER)
IS
BEGIN
    SELECT COUNT(pts) AS totalpts
    INTO totalpts
    FROM ptlist
    WHERE drid = '1';
END Totalptcount;

I’m trying to input the value ‘1’ and get out the total pt count (row count) for dr w/the ID ‘1’.

How do I execute this? Thanks so much in advance

Tried a number of answers I found here for similar questions but none worked. I also don’t know how to use cursors or declare but I don’t think they’d be needed for my stored procedure (I think). I’ve tried:

var totalpts NUMBER
EXEC totalptcount (:totalpts);
SELECT totalpts; 
- This gave the INTO clause requirement error

BEGIN
    Exec totalptcount
END; 
- I don't really know what I'm doing and just tried this

BEGIN
    Exec totalptcount('1')
END;
- Trying to input value '1' to get out the count
- Unknown command error

Thank you in advance!

>Solution :

Calling a stored procedure (named PL/SQL unit) is done within another PL/SQL block. SQL*Plus however has a shortcut, EXEC which simply wraps the command in a PL/SQL BEGIN .... END; wrapper. You can retrieve the value into a bind variable or a local PL/SQL variable and print it to your display in various ways:

In SQL*Plus:

var totalpts NUMBER

EXEC Totalptcount(1,:totalpts);

print totalpts

var totalpts NUMBER

BEGIN
  Totalptcount(1,:totalpts);
END;

print totalpts

Or:

set serveroutput on;

DECLARE
  totalpts number;
BEGIN
  Totalptcount(1,totalpts);
  dbms_output.put_line(totalpts);
END;

Or even:

var totalpts NUMBER

DECLARE
  totalpts number;
BEGIN
  Totalptcount(1,var_totalpts);
  :totalpts  := var_totalpts;
END;

print totalpts

Lots of options, and this is just for SQLPlus client. How you call a procedure and retrieve the result depends on the client technology stack you are using (in this case, SQLPlus, but it could be many things).

Keep in mind, however, that for most basic SQL needs, you don’t need procedures at all. Simply write SQL that obtains the result you need and execute it directly. We use PL/SQL when we have more complex needs that require full programmatic flow control that SQL by itself doesn’t provide.