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;
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.