How to display the results of a procedure outside of it in Oracle

Question

I am working on an application and made the decision that all the queries would be procedures. I hope to have gains in performance and ease of maintenance by doing it this way. Our DBA's have also expressed interest in having it done this way.

I have an HR table where operations are performed on it each night, and any changes are recorded in a secondary table. We are not doing auditing, these change records are kept until the next run and show users the changes that have happened.

To keep my question shorter I have reduced the number of columns in HR.

The HR table ID, GROUP_NAME, and GROUP_LEVEL. The Drill table has ID and TYPEVALUE.

CREATE OR REPLACE PROCEDURE DOCSADM.DRILL_RECORD_POSITION (
    RECORD_TYPE IN VARCHAR2,
    OUT_ID OUT VARCHAR2,
    OUT_GROUP_NAME OUT VARCHAR2,
    OUT_GROUP_LEVEL OUT VARCHAR2
) AS
BEGIN
    SELECT  HR.ID,  HR.GROUP_NAME,  HR.GROUP_LEVEL
    INTO    OUT_ID, OUT_GROUP_NAME, OUT_GROUP_LEVEL
    FROM HR_POSITION HR JOIN DRILL_POSITION DP ON (HR.ID = DP.ID) WHERE DP.TYPEVALUE = RECORD_TYPE;
END DRILL_RECORD_POSITION;

The procedure compiles without issue. Before doing all the work in the application to link to the procedure and extract the values which in this case will eventually be displayed in a view or webpage, I wanted to have a quick little script that would call the procedure and then display the results so I can verify in Oracle.

Loops

BEGIN
for t in (DRILL_RECORD_POSITION('D', V1,V5,V6))
loop
    --dbms_output.put_line(t.V1 || t.V5 || t.V6);
    dbms_output.put_line(t.OUT_ID);

end loop;
END;
/

CURSORS

DECLARE
V1 HR_POSITION.ID%TYPE;
V5 HR_POSITION.GROUP_NAME%TYPE;
V6 HR_POSITION.GROUP_LEVEL%TYPE;
CURSOR T_CUR IS DRILL_RECORD_POSITION('D', V1,V5,V6);
BEGIN
OPEN T_CUR;
    DBMS_OUTPUT.PUTLINE('START');
    LOOP
        FETCH T_CUR INTO V1,V5,V6;
        EXIT WHEN T_CUR%NOTFOUND;
        DBMS_OUTPUT.PUTLINE(V1||V5||V6);
    END LOOP;
CLOSE T_CUR;
END;

FOR LOOPS

DECLARE
V1 HR_POSITION.POSITION_ID%TYPE;
V5 HR_POSITION.GROUP_NAME%TYPE;
V6 HR_POSITION.GROUP_LEVEL%TYPE;
BEGIN
    DBMS_OUTPUT.PUTLINE('START');
    FOR INDEX IN (DRILL_RECORD_POSITION('D', V1,V5,V6))
LOOP
        --DBMS_OUTPUT.PUTLINE(INDEX.ID);
        DBMS_OUTPUT.PUTLINE(INDEX.V1||INDEX.V5||INDEX.V6);
    END LOOP;

END;

Note: I edited the column names out and shorted some when transferring here so I might have made a few mistakes.

All the articles I have seen online show me how to display from within the original procedure or by using views, cursors, records. Unless I am wrong, Eclipse wont have any problems using the information in the current form which is why I am passing it that way. So I am not interested in changing the procedure and would like to work with it as is, since thats how the application will be doing it.

As this is the first of the stored procedures I am doing for the application, instead of using adhoc queries from the application, I dont have any existing examples to work from, which is why I believe the results will work fine, because it should be the same format the adhoc ones use.

Update:

In one of the comments, I was pointed to what should have been a solution. This was confirmed by another solution that was under it.

I keep getting the error

ORA-01422: exact fetch returns more than requested number of rows

So Im returning multiple rows, but that is my expectation and what is happening. I just cant seem to figure out how to display the results.

Answer 1

To test the procedure you showed, you would do something like:

declare
  l_id hr_position.id%type;
  l_group_name hr_position.group_name%type;
  l_group_level hr_position.group_level%type;
begin
  drill_record_position('D', l_id, l_group_name, l_group_level);
  dbms_output.put_line(l_id ||':'|| l_group_name ||':'|| l_group_level);
end;
/

But that - or more specifically, your procedure - only works if there is exactly one row in the query's result set for the passed-in value type. It seems you're expecting multiple rows back (which would get too-many-rows), but there could also be non (which would get no-data-found).

So really it seems like your question should be about how to write your procedure so it works with one of the retrieval/test methods you tried.

If your procedure needs to return multiple rows then it can use a ref cursor, e.g.:

create or replace procedure drill_record_position (
  p_record_type in varchar2,
  p_ref_cursor out sys_refcursor
)
as
begin
  open p_ref_cursor for
    select hr.id, hr.group_name, hr.group_level
    from hr_position hr
    join drill_position dp
    on hr.id = dp.id
    where dp.typevalue = p_record_type;
end drill_record_position;
/

which you could then test with something like:

declare
  l_ref_cursor sys_refcursor;
  l_id hr_position.id%type;
  l_group_name hr_position.group_name%type;
  l_group_level hr_position.group_level%type;
begin
  drill_record_position('D', l_ref_cursor);
  loop
    fetch l_ref_cursor into l_id, l_group_name, l_group_level;
    exit when l_ref_cursor%notfound;
    dbms_output.put_line(l_id ||':'|| l_group_name ||':'|| l_group_level);
  end loop;
  close l_ref_cursor;
end;
/

You can also do that as a function, which might be easier to work with from your application:

-- drop procedure drill_record_position;

create or replace function drill_record_position (p_record_type in varchar2)
return sys_refcursor as
  l_ref_cursor sys_refcursor;
begin
  open l_ref_cursor for
    select hr.id, hr.group_name, hr.group_level
    from hr_position hr
    join drill_position dp
    on hr.id = dp.id
    where dp.typevalue = p_record_type;
  return l_ref_cursor;
end drill_record_position;
/

declare
  l_ref_cursor sys_refcursor;
  l_id hr_position.id%type;
  l_group_name hr_position.group_name%type;
  l_group_level hr_position.group_level%type;
begin
  l_ref_cursor := drill_record_position('D');
  loop
    fetch l_ref_cursor into l_id, l_group_name, l_group_level;
    exit when l_ref_cursor%notfound;
    dbms_output.put_line(l_id ||':'|| l_group_name ||':'|| l_group_level);
  end loop;
  close l_ref_cursor;
end;
/

You coudl also do this with collections and a pipelined function, which is more work to set up:

create type t_drill_obj as object (
   -- use your real data types...
  id number,
  group_name varchar2(10),
  group_level number
)
/

create type t_drill_tab as table of t_drill_obj
/

create or replace function drill_record_position (p_record_type in varchar2)
return t_drill_tab pipelined as
begin
  for l_row in (
    select t_drill_obj(hr.id, hr.group_name, hr.group_level) as obj
    from hr_position hr
    join drill_position dp
    on hr.id = dp.id
    where dp.typevalue = p_record_type
  )
  loop
    pipe row (l_row.obj);
  end loop;
  return;
end drill_record_position;
/

but you could call it as part of another query, and even join tot he result if you needed to:

select * from table(drill_record_position('D'));