CODEWITHSUNDEEP
sqloracle-databaseviewplsql
Luis perez
Luis perez

Reputation: 132

Return values of select in function/procedure

I have a SQL sentence where I make a minus of two tables to search the differences. As I use frequently I would like to create a function or procedure to make these and get output by screen. Someone could explain me how is the best way to make these, could you put me some example?

Upvotes: 0

Views: 82

Answers (2)

mrkovec
mrkovec

Reputation: 376

Maybe this is the what you're looking for, if you're using Oracle 11g Release 2:

create or replace procedure prnt_my_view(my_view in varchar2, separator in varchar2 default ',') is
    type myrefcur is ref cursor;
    type rowtext is table of varchar2(256);
    rowdef varchar2(256);
    rows_cv myrefcur;
    text rowtext;
begin
    select listagg(column_name,'||'''||separator||'''||')  within group (order by column_id) into rowdef from user_tab_columns where lower(table_name) = lower(my_view);
    open rows_cv for 'select '||rowdef||' from '||my_view;
    fetch rows_cv bulk collect into text;
    for i in text.first..text.last loop
        dbms_output.put_line(text(i));
    end loop;
    close rows_cv;
exception when others then
    dbms_output.put_line('something is wrong:'||sqlerrm);
end;

edit: if you can't use listagg, check other solutions for example here: alternative to listagg in Oracle?

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

If you frequently use the MINUS query, then it is better to create a view on the query. To fetch the resultset, you just need to select from the view.

For example,

CREATE OR REPLACE VIEW my_view AS
   SELECT column_list FROM table1
     MINUS
   SELECT column_list FROM table2

And to fetch the result,

SELECT * FROM my_view;

Read the documentation for more details on CREATE VIEW

Upvotes: 1

Related Questions