CODEWITHSUNDEEP
sqloracle-database
Tim Clotworthy
Tim Clotworthy

Reputation: 95

Create Populate, Then Delete a Table from Within a View (Oracle)

I have a view that compares the data in two tables (tableYesterday and tableToday) and outputs a result based on the comparison. Lets call this view: ViewComp.

I have a need to do more than this. WIthin a single view, I actually need to:

  1. create or replace tableToday and pre-populate it.
  2. execute the ViewComp query.
  3. replace tableYesterday with tableToday.

I have researched things like nested views but I could not find a way to perform 1 and 3 from within the view. I would be grateful for any ideas. Thank you.

Upvotes: 1

Views: 123

Answers (2)

Jon Heller
Jon Heller

Reputation: 36922

This is almost certainly a bad idea. Views shouldn't "do" anything.

For those extremely rare cases where this is required, it can be done with the tricks below. You will definitely want to document this code, to explain to other people what you're doing and why.

Sample schema and objects

--Create table.
create table tableYesterday(a number, b number);

--Create abstract data type to hold one row of data to be returned by the view.
create or replace type ViewComp_type is object
(
    a number,
    b number
);

--Create nested table to hold multiple rows of data to be returned by the view.
create or replace type ViewComp_nt is table of ViewComp_type;

Function to return results

--Create function to return data.
create or replace function ViewComp_function return ViewComp_nt authid current_user as
    --This pragma is necessary for a function that will perform DML.
    pragma autonomous_transaction;

    v_results ViewComp_nt;

    v_name_already_exists exception;
    pragma exception_init(v_name_already_exists, -955);
begin
    --Try to create today's table.  Ignore errors if it exists.
    begin
        execute immediate 'create table tableToday(a number, b number)';
    exception when v_name_already_exists then
        execute immediate 'truncate table tableToday';
    end;

    --Populate today's table.
    execute immediate 'insert into tableToday values(1,1)';

    --Get the difference.
    execute immediate q'[
        select cast(collect(ViewComp_type(a,b)) as ViewComp_nt)
        from
        (
            select * from tableToday
            minus
            select * from tableYesterday
        )
    ]' into v_results;

    --Remove yesterday's data.
    execute immediate 'truncate table tableYesterday';

    --Replace it with today's data.
    execute immediate 'insert into tableYesterday select * from tableToday';
    commit;

    --Return the difference.
    return v_results;
end;
/

Create the view to return the function's data

create or replace view ViewComp as
select * from table(ViewComp_function);

Test Run

--First execution:
select * from ViewComp;

A   B
-   -
1   1

--Second execution:
select * from ViewComp;

A   B
-   -

Upvotes: 1

Kacper
Kacper

Reputation: 4818

You can't do such things in view. You can either create PL/SQL procedure to perform your steps or create materialised view.

Upvotes: 0

Related Questions