8 Replies Latest reply on Feb 13, 2020 6:59 AM by Nico Heinze

How to populate global oracle temp tables from power center.

suresh bandaru Mar 18, 2019 5:13 AM

Hi All,

Have this use case where in i need to invoke stored procedure which in turn insert the data into global oracle temp tables

and then read the data from global temp table and insert into final target table.am able to call the proc using stored procedure or sql transformation in etl mapping but dont see the data in global oracle temp tables though etl job got succeeded . Guess that etl uses separate db session while running the job and close the session after the job success.so global oracle temp tables dont preserve the data.

Could anyone please tell me on how to do this in etl? can i create another dummy proc on the top of original procedure and execute the dummy from etl.

Thanks

Suresh

1. Re: How to populate global oracle temp tables from power center.

Nico Heinze Mar 20, 2019 11:40 AM (in response to suresh bandaru)

Temporary tables are bound to one single Oracle session, as far as I understand it. You cannot access their contents from a different Oracle session.
Each SQ, each LKP, each SQL transformation, each SP gets its own DB session when running a PowerCenter session. So indeed you cannot access the data in a different Oracle session which you inserted e.g. via a SP.

Why is it necessary to load the data into a temp table first and then write them to the "real" target table later? To ensure that data sets are complete?
If that's the reason, there are better ways to ensure that. It MAY look as if these other ways are more complex than your idea (which might run fine under normal circumstances), but in the end maintenance efforts probably become easier by other means.

Regards,
Nico
Like Show 0 Likes(0)

Actions
2. Re: How to populate global oracle temp tables from power center.

suresh bandaru Mar 21, 2019 2:50 AM (in response to Nico Heinze)

Thanks Nico for the explanation. As you mentioned, each SQ, each LKP, each SQL transformation, each SP gets its own DB session when running a Power Center session.

am calling the proc and inserting the data into target table from temp tables and all these are done in SQL transformation (script mode) as I had given 2 statements in single sql file and passed it to the script name column in SQL transformation. Guess its same db session that power center uses to run the proc and insert the data from temp to target table. Please correct me if am wrong.

Thanks
Suresh
Like Show 0 Likes(0)

Actions
3. Re: How to populate global oracle temp tables from power center.

Vlad Ponomarenko Mar 21, 2019 4:04 AM (in response to suresh bandaru)

Hi,
Your sql file must be like this
Begin
insert into TABLE_TEMP ...;
insert into TABLE_TGT select ... from TABLE_TEMP ... ;
commit;
end;

If you put additional commit between inserts then your DDL for TABLE_TEMP must contain
ON COMMIT PRESERVE ROWS;

Regards, Vlad
Like Show 0 Likes(0)

Actions
4. Re: How to populate global oracle temp tables from power center.

Nico Heinze Mar 21, 2019 2:59 PM (in response to suresh bandaru)

Correct; if you execute the statements from a SQL Transformation, they are executed via the same Oracle session.
Please beware of two points:
First - as mentioned by Vlad - the temp table must be created "correctly".
Second you have to make sure that the undo / redo logs are large enough for the whole load (and trust me, quite a few DBAs will want to kill you for such a design).

Regards,
Nico
Like Show 0 Likes(0)

Actions
5. Re: How to populate global oracle temp tables from power center.

suresh bandaru Mar 22, 2019 6:23 AM (in response to Nico Heinze)

Hi Vlad,

Here is the sql statement that gets executed from SQL Transformation.

call ESP_CR_ALTERED_ROWS(i_TableNm => table_nm, i_StartDate => start_date,i_EndDate => end_date, i_NoDuplicates => true);
insert into TABLE_TGT select * from TABLE_TEMP;
Commit;

The first statement invoke the procedure ESP_CR_ALTERED_ROWS which in turn insert the data into global temp table 'TABLE_TEMP' (ON COMMIT PRESERVE ROWS) .

second statement reads the data from 'TABLE_TEMP' and load into TABLE_TGT.

When I verified the data for 'TABLE_TEMP' in data base after running the workflow ,its empty.

when I ran the proc directly in the data base , this temp table has data. Don't have any control on these all these global temp tables as they are owned by different team and cant change them to normal tables. Please advice.

Thanks
Suresh
Like Show 0 Likes(0)

Actions
6. Re: How to populate global oracle temp tables from power center.

Vlad Ponomarenko Mar 22, 2019 7:15 AM (in response to suresh bandaru)

Hi Suresh,

You can never view data in GTT (global temporary table) generated in a workflow in another DB tool (sqlplus, SQL developer, etc.). It is the one of the main feature of GTT.

If TGT_Table has no new records add some debug rows to procedure (or it's clone) to be sure that it works correctly without error or exceptions. May be the reason in datatype of input parameters may be in privilegies and so on.
Regards, Vlad
Like Show 0 Likes(0)

Actions
7. Re: How to populate global oracle temp tables from power center.

Jothimurugan Vadivel Feb 11, 2020 8:27 PM (in response to suresh bandaru)

Hi Suresh,
We do have the same problem. Can you let me know how you solved this issue
Like Show 0 Likes(0)

Actions
8. Re: How to populate global oracle temp tables from power center.

Nico Heinze Feb 13, 2020 6:59 AM (in response to Jothimurugan Vadivel)

With all due respect, but isn't it all available in this thread already?
What information do you miss?
There is no "resolution", you have to redesign your approach if you want to do it with PowerCenter and you insist on using a temp table. Vlad has explained how to do it.

Regards,
Nico
Like Show 0 Likes(0)

Actions