Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

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

PoonamBhardwaj
Level 2
Level 2

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
David__Garcia
Correct answer by
Level 9
Level 9

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
David__Garcia
Correct answer by
Level 9
Level 9

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

PoonamBhardwaj
Level 2
Level 2

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

David__Garcia
Level 9
Level 9

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