Procedure Question

Toad for Oracle
1

In Oracle is possible create a Procedure that return is a table ?

Ex:

CREATE OR REPLACE PROCEDURE TESTE
(
NOME IN VARCHAR,
NEMPRESTIMO OUT INTEGER,
NAGENCIA OUT VARCHAR,
QUANTIA OUT NUMBER
)
IS
BEGIN
SELECT
NUMERO_EMPRESTIMO,
NOME_AGENCIA,
QUANTIA
INTO
NEMPRESTIMO,
NAGENCIA,
QUANTIA
FROM EMPRESTIMO
WHERE NOME_AGENCIA = NOME;
END;
/

I tried a most forms, but i could not.

Thanks

2

Yes, what you're interested in is a function that returns piped row output.

The usage would be something like:

Select * from table(my_function(variable_1, variable_2));

Then whatever you've specified in your function and whatever data you're choosing to pipe through it, will be displayed in a table.

On Nov 6, 2013, at 1:03 PM, "Eduardo Azevedo" bounce-eduardo971@toadworld.com wrote:

Procedure Question

Thread created by Eduardo Azevedo
In Oracle is possible create a Procedure that return is a table ?

Ex:

CREATE OR REPLACE PROCEDURE TESTE
(
NOME IN VARCHAR,
NEMPRESTIMO OUT INTEGER,
NAGENCIA OUT VARCHAR,
QUANTIA OUT NUMBER
)
IS
BEGIN
SELECT
NUMERO_EMPRESTIMO,
NOME_AGENCIA,
QUANTIA
INTO
NEMPRESTIMO,
NAGENCIA,
QUANTIA
FROM EMPRESTIMO
WHERE NOME_AGENCIA = NOME;
END;
/

I tried a most forms, but i could not.

Thanks

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Oracle notifications altogether.

Toad World - Oracle Discussion Forum

Flag this post as spam/abuse.

3

Then, correct form is use the function, not procedure ?

4

Eduardo, this can be done using a function or a procedure. I routinely use a ‘reference cursor’ which is essentially a temporary result table. Here are examples of both:

CREATE OR REPLACE PACKAGE SOME_PACKAGE IS

TYPE TYPE_REFCUR IS REF CURSOR;

FUNCTION F_RETURN_A_CURSOR(

IN_KEY IN INTEGER

) RETURN TYPE_REFCUR IS

OUT_REF_CUR TYPE_REFCUR;

BEGIN

OPEN OUT_REF_CUR FOR

SELECT *

FROM SOME_TABLE

WHERE SOME_KEY_ID = IN_KEY;

RETURN OUT_REF_CUR;

END F_RETURN_A_CURSOR;

PROCEDURE P_RETURN_A_CURSOR(

IN_KEY IN INTEGER,

OUT_REF_CUR OUT TYPE_REFCUR,

)

IS

BEGIN

OPEN OUT_REF_CUR FOR

SELECT *

FROM SOME_TABLE

WHERE SOME_KEY_ID = IN_KEY;

END;

5

Thanks a lot !