An Error Analysis of a mad PLS-00306

When performing databasereplay preprocessing, you may encounter a problem. For the first time, it is an internal (function Stored Procedure) Error in executing the package call. It is a typical parameter passing error. SQLexecdbms_workload_replay.PROCESS_CAPTURE (AAA); BEGINdbms_workload_replay.PROCESS_CAPTURE (AAA); END; * ER

A problem occurs during database replay preprocessing. In the beginning, it is an internal (function/Stored Procedure) error in the execution package call. It is a typical parameter passing error. SQL exec dbms_workload_replay.PROCESS_CAPTURE ('aaa'); BEGIN dbms_workload_replay.PROCESS_CAPTURE ('aaa'); END; * ER

A problem occurs during database replay preprocessing. In the beginning, it is an internal (function/Stored Procedure) error in the execution package call. It is a typical parameter passing error.

SQL> exec dbms_workload_replay.PROCESS_CAPTURE('AAA');BEGIN dbms_workload_replay.PROCESS_CAPTURE('AAA'); END; *ERROR at line 1:ORA-06550: line 1, column 15:PLS-00306: wrong number or types of arguments in call to 'ADD_CAPTURE'ORA-06550: line 1, column 9:PL/SQL: Statement ignoredORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY", line 2301ORA-06512: at line 1

This problem is generally caused by calling the function by 10046 trace. From the trace, we can see that dbms_wrr_internal.add_capture is running.

BEGIN   :1 := dbms_wrr_internal.add_capture( :name,                                        :db_id,                                        :db_name,                               :db_version,                                        'tmp',                                        '/tmp',                   'TRUE',                                        :status,                                        NULL, NULL,                                        :stime,                                        NULL,                                        :sscn,                            :dflt_action,                                        NULL,                                        NULL,              :wid,                                        :internal_stime                                      ); END; END OF STMT PARSE #11529215044979176000:c=0,e=540,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=434859905841 ===================== PARSING IN CURSOR #11529215044979172808 len=8 dep=1 uid=0 oct=45 lid=0 tim=434859906846 hv=2761672982 ad='0' sqlid='8sst43uk9rk8q' ROLLBACK END OF STMT PARSE #11529215044979172808:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=434859906844 XCTEND rlbk=1, rd_only=1, tim=434859906921 EXEC #11529215044979172808:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=434859906939 CLOSE #11529215044979172808:c=0,e=2,dep=1,type=3,tim=434859906960 PARSE #11529215044979172808:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=434859907039 XCTEND rlbk=1, rd_only=1, tim=434859907062 EXEC #11529215044979172808:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=434859907074 CLOSE #11529215044979172808:c=0,e=1,dep=1,type=3,tim=434859907091 EXEC #11529215044981457272:c=10000,e=77698,p=0,cr=60,cu=29,mis=0,r=0,dep=0,og=1,plh=0,tim=434859907137 ERROR #11529215044981457272:err=6550 tim=434859907154SQL> desc dbms_wrr_internal FUNCTION ADD_CAPTURE RETURNS NUMBER Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- NAME                           VARCHAR2                IN DB_ID                          NUMBER                  IN DB_NAME                        VARCHAR2                IN DB_VERSION                     VARCHAR2                IN DIR                            VARCHAR2                IN DPATH                          VARCHAR2                IN DPATH_SHARED                   VARCHAR2                IN STATUS                         VARCHAR2                IN ECODE                          NUMBER                  IN EMSG                           VARCHAR2                IN STIME                          DATE                    IN ETIME                          DATE                    IN SSCN                           NUMBER                  IN DEFAULT_ACTION                 VARCHAR2                IN AWR_DB_ID                      NUMBER                  IN AWR_BSNAP                      NUMBER                  IN WID                            VARCHAR2                INSQL> select NAME,DATATYPE_STRING,VALUE_STRING from v$sql_bind_capture where sql_id='3h55aw49j63g8'; no rows selected

I thought that 10046 could capture the bound variable values, but it does not exist here. v $ SQL _bind_capture could not capture the value of the bound variable. I suspect it has something to do with the internal package. Therefore, we cannot determine which parameter has a problem with the call type. There is no good way to deal with such a problem. I used to perform database replay in my own Linux environment, and it was successful, so the idea is to migrate this package from the Linux environment to the Hp environment. So we use grep to search for the specific file. I found the file named "prvtwrr. plb", which contains a bunch of encrypted items. Upload the file to HP for running. After running the file, the girl still reports the previous error. Depressing: Why is my Linux environment okay? I cannot do it in HP environment. When there was no way to go, my colleague executed the following command under the $ ORACLE_HOME directory.

w4sd23pa#[/home/oracle]find $ORACLE_HOME -name "prvtwrr.plb" /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/prvtwrr.plb /oracle/app/oracle/product/11.2.0/db_1/.patch_storage/17411249_Apr_21_2014_22_29_21/files/rdbms/admin/prvtwrr.plb w4sd23pa#[/home/oracle] w4sd23pa#[/home/oracle]ls -l /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/prvtwrr.plb -rw-r--r--   1 oracle     oinstall    151611 Apr 22 13:29 /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/prvtwrr.plb w4sd23pa#[/home/oracle]ls -l /oracle/app/oracle/product/11.2.0/db_1/.patch_storage/17411249_Apr_21_2014_22_29_21/files/rdbms/admin/prvtwrr.plb -rw-r--r--   1 oracle     oinstall    149850 Sep 29  2013 /oracle/app/oracle/product/11.2.0/db_1/.patch_storage/17411249_Apr_21_2014_22_29_21/files/rdbms/admin/prvtwrr.plb

Execute the find command to find two files, and the other file is obviously the patch file 17411249. Use opatch to check the patch. The patch was recently installed.

w4sd23pa#[/home/oracle]opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.6 Copyright (c) 2013, Oracle Corporation.  All rights reserved. Oracle Home       : /oracle/app/oracle/product/11.2.0/db_1 Central Inventory : /oracle/app/oraInventory    from           : /oracle/app/oracle/product/11.2.0/db_1/oraInst.loc OPatch version    : 11.2.0.3.6 OUI version       : 11.2.0.4.0 Log file location : /oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-05-27_23-23-16PM_1.log Lsinventory Output file location : /oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-05-27_23-23-16PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g                                                  11.2.0.4.0 There are 1 product(s) installed in this Oracle Home. Interim patches (18) : Patch  17411249     : applied on Tue May 20 18:41:12 GMT+08:00 2014 Unique Patch ID:  17551033    Created on 21 Apr 2014, 22:29:21 hrs PST8PDT    Bugs fixed:      17411249

After we see this information, we suspect that the patch has a problem. If yes, we will roll back and try again, so we have made a rollback for this patch. In readme, we also need to run a postinstall. SQL script. After the rollback is completed, the preprocessing operation is successfully performed. Then we installed the patch again and ran the postinstall. SQL script. We found that the preprocessing was successful. Later I asked my colleagues who installed the patch, and finally found that he forgot to execute the postinstall. SQL script. In general, this story is still very sad. If you don't execute a find command, you won't find any errors caused by installing new patches.

Original article address: A mad PLS-00306 error analysis, thanks to the original author to share.