Call Oracle Procedure from IBM Campaign (Unica) process boxes
I had a requirement to run the DBMS Stats on Oracle table after the table is truncated and loaded via snapshot process boxes. without new stats the tables were not returning the data. So I added a new dummy select process box "Select_Run_Stats" after the snapshots (connected with dotted lines)
Then Enable the Raw SQL for the select box and enter a dummy SQL which produces the output matching the Audience. Here in the example, the audience was numeric, so I used "select 1 from dual".
Now add the statement like below tin the Pre-Processing or Post-Processing section of the configuration. Now as this is a dummy select box, it doesn't matter whether you put it in pre or post section. But remember, pre-processing statements are run before the actual select statement you entered in the select process box (here "select 1 from dual") and post-processing runs after the actual select statement.
BEGIN
dbms_stats.gather_table_stats('schema_name','table_name');
END;
Also, you can add multiple calls to different stored procedures here in one statement or via mutiple pre/post processing statements. Note the semicolon after "END", it is needed.
Alternate Approach:
You can do the same thing using Custom Macro as well
Create a custom macro say Run_Ora_Stat(Owner,TableName)
expression Type: Raw SQL Selecting ID List
Expression:
BEGIN
dbms_stats.gather_table_stats('<Owner>','<Table_Name>');
END;
Notice the macro expression has the single quote (') needed for the Oracle Function. So while calling it, don't put the quote
Create a similar select process box to run raw sql and call the macro as shown below. Here we are calling the same macro twice to run the stats for 2 tables. and no single quote while calling the macro.
This is applicable for calling any Oracle stored procedure. I believe similar statement will work with SQL server and DB2 also with minor tweak as per database standard. Will test if I get the opportunity.
Then Enable the Raw SQL for the select box and enter a dummy SQL which produces the output matching the Audience. Here in the example, the audience was numeric, so I used "select 1 from dual".
Now add the statement like below tin the Pre-Processing or Post-Processing section of the configuration. Now as this is a dummy select box, it doesn't matter whether you put it in pre or post section. But remember, pre-processing statements are run before the actual select statement you entered in the select process box (here "select 1 from dual") and post-processing runs after the actual select statement.
BEGIN
dbms_stats.gather_table_stats('schema_name','table_name');
END;
Also, you can add multiple calls to different stored procedures here in one statement or via mutiple pre/post processing statements. Note the semicolon after "END", it is needed.
Alternate Approach:
You can do the same thing using Custom Macro as well
Create a custom macro say Run_Ora_Stat(Owner,TableName)
expression Type: Raw SQL Selecting ID List
Expression:
BEGIN
dbms_stats.gather_table_stats('<Owner>','<Table_Name>');
END;
Create a similar select process box to run raw sql and call the macro as shown below. Here we are calling the same macro twice to run the stats for 2 tables. and no single quote while calling the macro.
This is applicable for calling any Oracle stored procedure. I believe similar statement will work with SQL server and DB2 also with minor tweak as per database standard. Will test if I get the opportunity.
Comments
Post a Comment