Where the input values of stored procedures are decided in adobe? | Community
Skip to main content
P
Poonam_Dogra
Level 4
November 12, 2021
Solved

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

  • November 12, 2021
  • 2 replies
  • 1177 views

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!

 

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by david--garcia

    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;
/

    2 replies

    david--garcia
    david--garciaAccepted solution
    Level 10
    November 13, 2021

    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;
/
      P
      Poonam_DograAuthor
      Level 4
      November 16, 2021

      Thank you so much @david--garcia for help and prompt reply, really grateful for this 🙂

        david--garcia
        david--garcia
        Level 10
        November 15, 2021

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

        Terms & ConditionsAccessibility statement

        Loading footer...