Expand my Community achievements bar.

The 5th edition of the Campaign Community Lens newsletter is out now!
SOLVED

Where the input values of stored procedures are decided in adobe?

Avatar

Level 4

Hi All,

 

Could someone please help me understand where in adobe campaign iPercent integer is being defined in below stored procedure:

Background: We got an error "nlmodule WDB-2000001 SQL statememt call up_UpdateTableStats('abc', 'tableName', '10')" could not be executed in the clean up workflow.

This is a stored procedure in adobe defined as "up_UpdateTableStats(sSchema varchar2, sTable varchar2, iPercent INTEGER)but the query is where in adobe campaign iPercent is being defined, for example in the above error shown the value of this Integer [iPercent] is 10, but where this value is being decided?

 

Any related hints will also be helpful.

 

Thank you in advance!

 

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

Looking at this procedure, it seems like is estimated/derived from the database based on usage statistics?

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- stored procedure called to update table statistics
-- can be changed accoring to your preferences as a DBA
CREATE OR REPLACE PROCEDURE up_UpdateTableStats(sSchema varchar2, sTable varchar2, iPercent INTEGER) IS
BEGIN

  IF UPPER(sTable) IN ('TMPBROADCAST', 'TMPBROADCASTPAPER') THEN
    DBMS_STATS.SET_TABLE_STATS(USER, 'TMPBROADCAST', numrows=> 2000000, numblks=>40000);
  ELSE
    DBMS_STATS.GATHER_TABLE_STATS(ownname => sSchema,
                                  tabname => sTable,
                                  estimate_percent => iPercent,
                                  cascade => true);
  END IF;
END;
/
3 Replies

Avatar

Correct answer by
Employee Advisor

Looking at this procedure, it seems like is estimated/derived from the database based on usage statistics?

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- stored procedure called to update table statistics
-- can be changed accoring to your preferences as a DBA
CREATE OR REPLACE PROCEDURE up_UpdateTableStats(sSchema varchar2, sTable varchar2, iPercent INTEGER) IS
BEGIN

  IF UPPER(sTable) IN ('TMPBROADCAST', 'TMPBROADCASTPAPER') THEN
    DBMS_STATS.SET_TABLE_STATS(USER, 'TMPBROADCAST', numrows=> 2000000, numblks=>40000);
  ELSE
    DBMS_STATS.GATHER_TABLE_STATS(ownname => sSchema,
                                  tabname => sTable,
                                  estimate_percent => iPercent,
                                  cascade => true);
  END IF;
END;
/

Avatar

Level 4

Thank you so much @David__Garcia for help and prompt reply, really grateful for this 🙂

Avatar

Employee Advisor

@Poonam_Dogradid your question get resolved? could you please close the question or are there any oustanding queries.

page footer