Expand my Community achievements bar.

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.