Member Avatar for g.prabu

i have to call a stored procedure that shoud return all data in a table.

so how to create procedure and how to return data from procedure.

kindly send me a small example program .........

  • 4 Contributors
  • 3 Replies
  • 129 Views
  • 5 Months Discussion Span
  • Latest Post Latest Post by Musta

Dani AI

Generated

For returning all rows from a table to a caller, the cleanest pattern is an OUT REF CURSOR. pointed this out; it is the right approach when the caller (SQL*Plus, JDBC, ODP.NET, PHP, etc.) needs a result set. The cursor-loop/DBMS_OUTPUT examples from and are useful for learning or for writing into other tables, but DBMS_OUTPUT only writes to the session buffer and is not a result set that client code can iterate.

A minimal, idiomatic procedure uses SYS_REFCURSOR as an OUT parameter and opens it for your SELECT. For example:

CREATE OR REPLACE PROCEDURE get_all_rows(p_rc OUT SYS_REFCURSOR) AS
BEGIN
  OPEN p_rc FOR SELECT * FROM your_table;
END;
/

From SQL*Plus you can fetch and display it with:

VARIABLE rc REFCURSOR
EXEC get_all_rows(:rc);
PRINT rc

Alternatives and practical notes:

  • Use a pipelined table function if you want to SELECT * FROM table_function(...) directly in SQL or need row-by-row streaming inside SQL.
  • SYS_REFCURSOR is weakly typed (flexible). Use a strongly typed ref cursor or SQL object/collection types when you want compile-time type checks.
  • Do not close the ref cursor inside the procedure — leave it open for the caller to fetch and close.
  • Ensure the executing user has SELECT rights on the target table. Avoid doing heavy client-side materialization in PL/SQL; stream results when possible for large datasets.

Driver tips (brief): in JDBC register the OUT param as Types.REF_CURSOR (or OracleTypes.CURSOR with older drivers) and cast the returned object to a ResultSet. In ODP.NET use OracleDbType.RefCursor. These are the common patterns for getting an Oracle result set back to application code.

Recommended Answers

All 3 Replies

Member Avatar for Memento

i have to call a stored procedure that shoud return all data in a table.

so how to create procedure and how to return data from procedure.

kindly send me a small example program .........

You need to return a ref cursor. See http://www.oradev.com/ref_cursor.jsp for code examples specific to your version of Oracle.

Thanks,
Dan

Member Avatar for manoshailu

hi,


set serveroutput on \\ to procedures
create or replace procedure alldata as \\create procedure

declare variables to select the fields
eid varchar(10);
ename varchar(25);

create cursor
cursor c is select empid,empname from emp;

procedure starts
begin

open the cursor to move records
open c;

create a loop to move records
loop

store the values to the declared variables
fetch c into eid,ename;

check the records available
exit when c%notfound;

select and store the values to the variables
select empid,empname into eid,ename from emp where empid=eid;

to display the result use the below statement
dbms_output.put_line(eid || ' ' || ename);

end the loop
end loop;

close the cursor
close c;

procedure ends
end;

after the completing the press enter and displays the message like this
Procedure created.

to execute the procedure use exec keyword along with the procedure name
exec alldata;

Result
e100 Dinesh.R
e101 hema
e103 sha
1001 asdf
e102 kavi
emp3 siva
emp200 sha's
1002 asdf
1003 asdf

PL/SQL procedure successfully completed.


regards
shailu

Member Avatar for Musta

--------------------------------------------------
Assume :
table_name1 is a table with a,b,c are fields in it.
table_name2 is a table with l,m,n are fields in it.
proc_name insert from table_name1 to table_name2, showing the list.
---------------------------------------------------
CREATE OR REPLACE PROCEDURE proc_name AS
x number;
y varchar2(30);
z date;
CURSOR c IS SELECT a,b,c
FROM table_name1;
BEGIN
OPEN c;
LOOP
FETCH c into x,y,z;
DBMS_OUTPUT.PUT_LINE(x||'/'||y||'/'||z); -- to see your results.
INSERT INTO table_name2(l,m,n) VALUES (z,y,z);
-- to insert into a table.
EXIT WHEN c%NOTFOUND;
END LOOP;
COMMIT;
CLOSE c;
END;
---------------------------------------------------
set serveroutput ON;
execute proc_name;

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.