PL/SQL - how do i write a function that returnsmore than 1 records from a tablefor example in sql server ther

GeekInterview.com > Tech FAQs > PL/SQL

Go To First | Previous Question | Next Question

PL/SQL | Question 38 of 166

how do i write a function that returns
more than 1 records from a table
for example in sql server there is a
solution as
create function f1()
return table as
select * from emp
how do i do the same in oracle. plz help

Total Answers and Comments: 7

Last Update: November 24, 2008 Asked by: vssandilya

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

July 17, 2006 05:56:20

mukund

RE: how do i write a function that returnsmore t...

Make use of ref cursor.

Declare the set of records as 'type refcur is ref cursor'

Then write the procedure body.

then use execute immediate or open refcur for 'strsql'

where strsql is the select query.

u can have this cursor records in the front end as dataset.

Is this answer useful? Yes | No

September 14, 2006 12:52:54

beginner

RE: how do i write a function that returnsmore t...

Hi Mukund

Could u give an example for returning more than 1 value in procedures/functions.

Thanks in advance.

Is this answer useful? Yes | No

September 14, 2006 20:35:20

V.S.SANDILYA

how do write the vb code in the front end so that i can get all the records into the recordset

dear mukund

thanks a lot. in fact i did all that u told and it is really fast.

how do i write the vb front end coding like

dim con as new adodb.connection

dim rs as new adodb.recordset

dim cmd as new adodb.command

con.open

the rest of the lines are not known to me.plz help and email me to

vssandilya@yahoo.com

with regards

sandilya

Is this answer useful? Yes | No

October 10, 2006 04:36:22

Sivakumar

RE: how do i write a function that returnsmore t...

Hai

There is one option in PL/SQL to return more than one rows.You can do it using Pipeline option.Here is one example which i tried to return dates between from date and todate as dataset

CREATE OR REPLACE FUNCTION DATEBETWEEN(FROMDATE DATE TODATE DATE)RETURN TBDATES PIPELINED IS

TMPDATE1 DATES : DATES(NULL);

TMPDATE DATE;

BEGIN

TMPDATE : FROMDATE;

LOOP

EXIT WHEN TMPDATE > TODATE;

TMPDATE1.DATE1 : TMPDATE;

PIPE ROW(TMPDATE1);

TMPDATE : TMPDATE+1;

END LOOP;

RETURN;

END;

Where

TBDATES is a object of type DATES

And you should query this function as follows

SELECT * FROM TABLE(DATEBETWEEN(TO_DATE('01-APR-2006') TO_DATE('20-APR-2006')));

Is this answer useful? Yes | No

October 10, 2006 12:31:53

v.s.sandilya

RE: how do i write a function that returnsmore t...

thanks a lot.

with regards

sandilya

Is this answer useful? Yes | No

July 20, 2008 10:11:37

pardhu323

Member Since: July 2008 Contribution: 2

RE: how do i write a function that returnsmore than 1 records from a tablefor example in sql server there is a solution as create function f1() return table asselect * from emphow do i do the same in oracle. plz help

create or replace function f1 return sys_refcursor as
r1 sys_refcursor;
begin open r1 for select * from emp;
return(r1);
end;

call the funtion as :

select f1 from dual;

then it returns :

CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 10000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Is this answer useful? Yes | No

November 24, 2008 05:32:29

rajivgupta780184

Member Since: August 2008 Contribution: 32

If we want to return multiple row from a function then we can use ref cursor
function .Example is given below:

create or replace function f1 return sys_refcursor as
r1 sys_refcursor;
begin open r1 for select * from emp;
return(r1);
end;
call the
function as :select f1 from
dual; then it returns :

CURSOR STATEMENT : 1CURSOR STATEMENT : 1 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 10000 10

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank