Urgent HELP ME --- i am new to ORACLE
 

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

Re: Urgent HELP ME --- i am new to ORACLE
 

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

Re: Urgent HELP ME --- i am new to ORACLE
 

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

Re: Urgent HELP ME --- i am new to ORACLE
 

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