Expand my Community achievements bar.

Adobe Campaign User Groups are live now. Join our Adobe Campaign User Groups and connect with your local leaders!
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
Community 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;
/

View solution in original post

3 Replies

Avatar

Correct answer by
Community 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

Community Advisor

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