Oracle - new

Re: new

From:

Date:

Time:

Hi

May be this code can help you.

====================================================================
Using Ref Cursors To Return Recordsets
Since Oracle 7.3 REF CURSORS have been available which allow recordsets to
be returned from stored procedures, functions and packages. The example
below uses a ref cursor to return a subset of the records in the EMP table.

First, a package definition is needed to hold the ref cursor type:

CREATE OR REPLACE PACKAGE Types AS
TYPE cursor_type IS REF CURSOR;
END Types;
/
Next a procedure is defined to use the ref cursor:

CREATE OR REPLACE
PROCEDURE GetEmpRS (p_deptno IN emp.deptno%TYPE,
p_recordset OUT Types.cursor_type) AS
BEGIN
OPEN p_recordset FOR
SELECT ename,
empno,
deptno
FROM emp
WHERE deptno = p_deptno
ORDER BY ename;
END GetEmpRS;
/
The resulting cursor can be referenced from PL/SQL as follows:

SET SERVEROUTPUT ON 1000000
DECLARE
v_cursor Types.cursor_type;
v_ename emp.ename%TYPE;
v_empno emp.empno%TYPE;
v_deptno emp.deptno%TYPE;
BEGIN
GetEmpRS (p_deptno => 30,
p_recordset => v_cursor);

LOOP
FETCH v_cursor
INTO v_ename, v_empno, v_deptno;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_ename || ' | ' || v_empno || ' | ' || v_deptno);
END LOOP;
CLOSE v_cursor;
END;
/
====================================================================

-----Original Message-----
From: Gustavo Guitera V.I.A.
To: Oracle List
Sent: 12/16/03 12:26 PM
Subject: Re: [Oracle] new

what do I do wrong?

CREATE OR REPLACE FUNCTION GETUSERS( V_USER IN NCHAR) RETURN CURSOR

AS

V_TORETURN CURSOR;

BEGIN

SELECT *

INTO V_TORETURN

FROM USERS

WHERE USR_ID=V_USER;

RETURN RESULT;

END;

Warning: Function created with compilation errors

----- Original Message -----
From: Gustavo Guitera V.I.A. <mailto:gguitera@vai-ingdesi.com>
To: Oracle List <mailto:oracle-list@digitalpoint.com>
Sent: Tuesday, December 16, 2003 11:10 AM
Subject: Re: [Oracle] new

ok, i will prove it, thanks a lot.

----- Original Message -----
From: Fabien Sanglard <mailto:fabien@pcn.fr>
To: Oracle List <mailto:oracle-list@digitalpoint.com>
Sent: Tuesday, December 16, 2003 10:48 AM
Subject: Re: [Oracle] new

In this case, i think you can return a cursor ( just declare V_TORETURN
in my exemple as cursor)..

CU

----- Original Message -----
From: Gustavo Guitera V.I.A. <mailto:gguitera@vai-ingdesi.com>
To: Oracle List <mailto:oracle-list@digitalpoint.com>
Sent: Tuesday, December 16, 2003 2:24 PM
Subject: Re: [Oracle] new

thanks for the answer, but I need to return all the records of one
table in which records have the same where conditions.

for example

IN parameter = Wuser_country
select * from users where usr_country=Wuser_country

thanks, again

----- Original Message -----
From: Fabien Sanglard <mailto:fabien@pcn.fr>
To: Oracle List <mailto:oracle-list@digitalpoint.com>
Sent: Tuesday, December 16, 2003 9:55 AM
Subject: Re: [Oracle] new

Yes, it is possible with stored function, this is an example:

CREATE OR REPLACE FUNCTION GETMEDIAPATH( V_MEDIAID IN NUMBER) RETURN
VARCHAR2
AS

V_TORETURN VARCHAR2(255);

BEGIN
SELECT MYFIELD
INTO V_TORETURN
FROM YOURTABLE
WHERE blablabla;

You can here process any test or string treatment,

RETURN RESULT;
END;

Cordialement,

Fabien Sanglard

----- Original Message -----
From: Gustavo Guitera V.I.A. <mailto:gguitera@vai-ingdesi.com>
To: oracle-list@digitalpoint.com <mailto:oracle-list@digitalpoint.com>
Sent: Tuesday, December 16, 2003 1:49 PM
Subject: [Oracle] new

Hi , I'm new using Oracle , and I have a lot of questions.

1- In a MSSQL I have store procedures whos return data like a simple
query(select * from bla bla bla) , is that posible in Oracle?

Which is the better way to solve that kind of troubles?

thank in advance,
Gustavo Guitera

VAI - INGDESI Automation
Albarellos 2620 - (C1419FSQ)
Buenos Aires - Argentina
Tel: +54-11-45732233 / Fax: +54-11-45732255
www.vai-ingdesi.com <http://www.vai-ingdesi.com>

----------------------------------------------------------
To subscribe, unsubscribe or to search list archive
please visit http://www.digitalpoint.com/lists/oracle.html
----------------------------------------------------------

Sales:
888/4-DPOINT

Support:
858/452-3696