0

I am looking for a way to create collection that would have one additional column that the source table/view columns referred by %ROWTYPE for simplicity.

TYPE my_type IS RECORD ( mode varchar2(10), some_table%ROWTYPE );

Goal:

I want to compare a table and its source view with a two-way MINUS operation and read source view once:

WITH src AS ( select ... from aView )
select * from (
select 'INSERT' as mode, a.* from (
select ... from src
MINUS
select ... from aTable ) a
UNION ALL
select 'DELETE' as mode, b.* from (
select ... from aTable
MINUS
select ... from src) b
)

( where ... is a <fixed_columns_list> )

I am starting to think of creating global temporary tables (GTTs) for INSERT and DELETE mode performed with INSERT ALL, but I have 25 tables, so that will add 50 GTTs :-/

Another con with GTTs is that whenever aTable is changed (new columns, renames, drops), I will also have to change respective GTT.

However, because GTTs will serve for temporary storage in this scenario, I can re-create them on demand automatically, so no issue.

1 Answer 1

0

Put the query in a CURSOR and use the %ROWTYPE of that cursor.

Declare
  CURSOR C is select * from something;

  C_nt is table of C%rowtype;

...

NOTE From my experience, those a.* don't always match up to those b.*. I've always had to declare the columns.

1
  • I am having column list from metadata for any purpose where column order is risky. %ROWTYPE on a declared cursor doesn't help here, becuase from something has to be declared in cursor declaration.
    – Jakub P
    Oct 25, 2021 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.