CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle11ginline
Victor Zakharov
Victor Zakharov

Reputation: 26434

Oracle equivalent of stored procedure that returns an inline table?

Example in T-SQL (SQL Server - taken from here):

CREATE PROC proc_authors
    @au_lname VARCHAR(40)
AS
    SELECT  
        au_id, au_fname, au_lname, city, state
    FROM authors
    WHERE au_lname = @au_lname
go

Is it possible in Oracle to create a stored procedure that returns an inline table (without declaring a type - like the above)? If not, what would be the closest alternative? i.e. declare inline type, then use it. The idea is to minimize number of DB permissions that are granted.

Please include sample code as part of your answer.

Reasoning behind using stored procedure vs function - we have legacy software that can only execute stored procedures, or raw queries. It appears that only stored procedures in there have support for parameterized execution, which is what we are after.

Upvotes: 0

Views: 1048

Answers (2)

vishnu sable
vishnu sable

Reputation: 358

try this with ref cursor

PROCEDURE proc_get_tada(ip_user IN VARCHAR2, 
                        op_error_code OUT NUMBER, 
                        op_cursor OUT SYS_REFCURSOR,) AS

BEGIN

  OPEN op_cursor FOR
    SELECT * FROM your_table yt where yt.user = ip_user;

EXCEPTION
  WHEN OTHERS THEN
    op_error_code := -1;
END proc_get_tada;

you will get collection of all data from you table you can iterate in java or calling program.

Upvotes: 1

bernhard.weingartner
bernhard.weingartner

Reputation: 505

Maybe you are searching for something like this:

create table author
(
    au_id number,
    au_name varchar2(100)
);

insert into author (au_id, au_name) values(1, 'ME');

create or replace function getAuthor(auName varchar2)
return author%rowtype
is
    retval author%rowtype;
begin
    select *  into retval from author where au_name=auName;
    return retval;
end;

declare
 auth author%rowtype;
begin
    auth := getAuthor('ME');
    dbms_output.put_line(auth.au_id);
end;

Upvotes: 1

Related Questions