How to view/verify a procedure result?

Question

Can someone explain how to see the results of a procedure, everything is working fine and the code is valid, executed and compiled with no errors. Now how can I see the results as Query or anything.

The ex procedure is about sum of salary.

CREATE OR REPLACE PROCEDURE HR.TOTAL_SALARY AS    
   total_salary NUMBER(12,2);    
BEGIN    

  SET TRANSACTION READ ONLY;    

  SELECT SUM (salary) 
    INTO total_salary 
    FROM employees;    

  DBMS_OUTPUT.PUT_LINE('Total salary 1: ' || total_salary);     
  COMMIT;    

END;

I don't understand what you mean by wanting to "see the results as query or anything". The procedure populates a variable, and prints it to screen--what more do you need to see? The `COMMIT` is unnecessary; you're not inserting, updating or deleting data. — OMG Ponies, Sep 23 '10 at 02:30
But how can I see if it works? "Prints it to screen"? I just want to see the total sum of salary which should be 691400... — , Sep 23 '10 at 02:37
Yes, the `DBMS_OUTPUT.PUT_LINE` will print "Total salary 1: 691400" to screen. IIRC, DBMS_OUTPUT was under a different tab in PLSQLDeveloper... — OMG Ponies, Sep 23 '10 at 02:45
As a query I mean as when you use SQL Worksheet and select a table or specific column and so on, it shows you directly a "view", result. — , Sep 23 '10 at 02:46
I believe that the cursor solution is what you want, if you want to display things as a view or table would display. By using a cursor you could, for example, make the proc a data source for a report just as if it is a table or view. — user158017, Sep 23 '10 at 03:03

score 2 · Answer 1 · answered Sep 23 '10 at 02:45

2

Are you running this in SQL*Plus? Have you "set serveroutput on;"?

answered Sep 23 '10 at 02:45

Jon Heller

34,999
6
74
132

1

You mentioned "SQL Worksheet" above. Does this mean you're using Oracle SQL Developer? If so, click on the DBMS_OUTPUT tab, click the Enable DBMS Output button, and run the procedure again. Make sure you don't have other worksheets open with DBMS Output enabled or they can steal each others output. – Jon Heller Sep 23 '10 at 04:03
No, im using Oracle 11g Enterprise Manager, but managed to apply dbms_output.enable (1000000); and trying to access it in "SQL Worksheet" by by entering SELECT * FROM HR.TOTAL_SALARY WHERE NAME = 'TOTAL_SALARY' ORDER BY LINE; But I get error: ora-04044 procedure function package or type is not allowed here – Sep 23 '10 at 04:08

score 1 · Answer 2 · answered Sep 23 '10 at 09:15

I recommend for this a function

CREATE OR REPLACE FUNCTION HR.TOTAL_SALARY return number AS    
   total_salary NUMBER(12,2);    
BEGIN    

  SELECT SUM (salary) 
    INTO total_salary 
    FROM employees;    

return total_salary;

END;

The usage for this is like:

select hr.TOTAL_SALARY() as total_sal from dual.

score 0 · Accepted Answer · answered Sep 23 '10 at 02:42

To output the results of a select statement in a procedure you need to use a cursor.

create procedure myproc 
(in_variable IN number, out_records OUT sys_refcursor)
as
begin
open out_records for

select * from mytable
where column = in_variable;

end;

then to use it, declare the cursor, execute the proc, and output the results.

variable records refcursor;
exec myproc(1, :records);
print :records;

(no promises that the above is syntactically perfect - I'm away from the DB right now. But it should be close enough to get you in the right direction.)

Oh - and you can use a user-defined cursor type inside of a package, if that is appropriate for your environment.

How to view/verify a procedure result?

3 Answers3