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

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

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