Ошибка ora 06553 pls 306

I´m trying a query in oracle 10g. It goes like this:

SELECT
  *
FROM
  h2h_reg reg,
  h2h_cat_estatus est
WHERE
  reg.FECH_APLICACION = SYSDATE
AND REG.ID_EST        = EST.ID_ESTATUS
AND est.tipo_estatus  = "X";

So it runs smootly, but when I try it adding a group by:

SELECT
  reg.id_arch,
  reg.id_prod
FROM
  h2h_reg reg,
  h2h_cat_estatus est
WHERE
  reg.FECH_APLICACION = SYSDATE
AND reg.id_est        = est.id_estatus
AND EST.TIPO_ESTATUS  = "X"
GROUP BY
  reg.id_arch,
  reg.id_prod;

I get the next message:

ora-06553 pls-306 wrong number or types of arguments in call to ‘ogc_x’

Does anyone knows what´s wrong in my query?

Rubens Mariuzzo's user avatar

asked Dec 3, 2012 at 16:41

linker85's user avatar

2

you’ve used double quotes on "X".

this should be 'X'.

the X object is an function in the MDSYS schema, «ogc_x», so when you say est.tipo_estatus = "X" instead of the correct est.tipo_estatus = 'X' it gets translated (as «» is as an identifier so «X» is the same as just typing X) to est.tipo_estatus = mdsys.ogc_x and of course fails.

answered Dec 3, 2012 at 16:50

DazzaL's user avatar

DazzaLDazzaL

21.6k3 gold badges49 silver badges57 bronze badges

0

Try with DISTINCT :

SELECT DISTINCT reg.id_arch, reg.id_prod
  FROM h2h_reg reg, h2h_cat_estatus est
 WHERE reg.FECH_APLICACION = SYSDATE
   AND reg.id_est = est.id_estatus
   AND est.tipo_estatus = 'X'

answered Dec 3, 2012 at 16:43

xlecoustillier's user avatar

xlecoustillierxlecoustillier

16.2k14 gold badges61 silver badges85 bronze badges

I found that this error was generated because I had used Oracle reserved words to name some of my columns, e.g. date, time, comment, etc. Once I renamed the columns the problem disappeared.

answered Sep 15, 2017 at 6:34

John Argus's user avatar

This problem also occured when the column does not exist

answered Jan 29 at 16:23

Andrei Manolache's user avatar

Источник

I´m trying a query in oracle 10g. It goes like this:

SELECT
  *
FROM
  h2h_reg reg,
  h2h_cat_estatus est
WHERE
  reg.FECH_APLICACION = SYSDATE
AND REG.ID_EST        = EST.ID_ESTATUS
AND est.tipo_estatus  = "X";

So it runs smootly, but when I try it adding a group by:

SELECT
  reg.id_arch,
  reg.id_prod
FROM
  h2h_reg reg,
  h2h_cat_estatus est
WHERE
  reg.FECH_APLICACION = SYSDATE
AND reg.id_est        = est.id_estatus
AND EST.TIPO_ESTATUS  = "X"
GROUP BY
  reg.id_arch,
  reg.id_prod;

I get the next message:

ora-06553 pls-306 wrong number or types of arguments in call to ‘ogc_x’

Does anyone knows what´s wrong in my query?

Rubens Mariuzzo's user avatar

asked Dec 3, 2012 at 16:41

linker85's user avatar

2

you’ve used double quotes on "X".

this should be 'X'.

the X object is an function in the MDSYS schema, «ogc_x», so when you say est.tipo_estatus = "X" instead of the correct est.tipo_estatus = 'X' it gets translated (as «» is as an identifier so «X» is the same as just typing X) to est.tipo_estatus = mdsys.ogc_x and of course fails.

answered Dec 3, 2012 at 16:50

DazzaL's user avatar

DazzaLDazzaL

21.6k3 gold badges49 silver badges57 bronze badges

0

Try with DISTINCT :

SELECT DISTINCT reg.id_arch, reg.id_prod
  FROM h2h_reg reg, h2h_cat_estatus est
 WHERE reg.FECH_APLICACION = SYSDATE
   AND reg.id_est = est.id_estatus
   AND est.tipo_estatus = 'X'

answered Dec 3, 2012 at 16:43

xlecoustillier's user avatar

xlecoustillierxlecoustillier

16.2k14 gold badges61 silver badges85 bronze badges

I found that this error was generated because I had used Oracle reserved words to name some of my columns, e.g. date, time, comment, etc. Once I renamed the columns the problem disappeared.

answered Sep 15, 2017 at 6:34

John Argus's user avatar

This problem also occured when the column does not exist

answered Jan 29 at 16:23

Andrei Manolache's user avatar

Источник

Problem:

When trying to upload a file on an AWS RDS instance to an S3 bucket I was getting this error:

SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
      p_bucket_name    =>  ‘db-bucket’,       
      p_directory_name =>  ‘DATA_PUMP_DIR’,
      p_prefix         =>  ‘EXPORT_SCHEMAS.dmp’)
   AS TASK_ID FROM DUAL;

ORA-06553: PLS-306: wrong number or types of arguments in call to ‘UPLOAD_TO_S3’
06553. 00000 —  «PLS-%s: %s»
*Cause:    
*Action:
Error at Line: 2 Column: 7

Solution:

Add the following mandatory argument for subdirectory «even if it’s empty» to the above command:

p_s3_prefix      =>  »,

Final command will be:

SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
      p_bucket_name    =>  ‘db-bucket’,       
      p_directory_name =>  ‘DATA_PUMP_DIR’,
      p_s3_prefix      =>  »,
      p_prefix         =>  ‘EXPORT_SCHEMAS.dmp’)
   AS TASK_ID FROM DUAL; 

Reference:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-s3-integration.html#oracle-s3-integration.using.upload

Источник

Recently i had faced “ORA-06553: PLS-306:” error while taking an export using expdp for table using “query” parameter.

$ expdp directory=TEST_DPUMP_DIR  dumpfile=test_url.dmp logfile=test_url.log tables=anand.test_delete_objects query=\"WHERE processed_flag = 'Y'\"

Export: Release 11.2.0.3.0 - Production on Tue May 29 00:24:25 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=TEST_DPUMP_DIR dumpfile=test_url.dmp logfile=test_url.log tables=anand.test_delete_objects query="WHERE processed_flag = Y"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 112 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "ANAND"."TEST_DELETE_OBJECTS" failed to load/unload and is being skipped due to error:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_Y'
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /exp/kmportal_dpump_dir/test_url.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 00:25:50

The export completes successfully if we use parfile

$ expdp parfile=test_url.lst

Export: Release 11.2.0.3.0 - Production on Tue May 29 00:53:49 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** parfile=test_url.lst
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 112 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ANAND"."TEST_DELETE_OBJECTS"           10.09 KB      38 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /exp/kmportal_dpump_dir/test_url.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 00:54:39


$ more test_url.lst
directory=TEST_DPUMP_DIR
dumpfile=test_url.dmp
logfile=test_url.log
tables=anand.test_delete_objects
query="WHERE processed_flag = 'Y'"

So,i was little confused until i came across Jonathan Lewis’s comments on the below blog

Aliases

Unfortunately, if you’ve installed Spatial, there are two functions (called ogc_x and ogc_y) which has been given public synonyms X and Y respectively that move Oracle into the function-call validity checks – which is where the pls error comes from.

SYS > select owner,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where SYNONYM_NAME='Y';

OWNER      SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
---------- ------------------------------ ------------------------------ -------------------------
PUBLIC     Y                              MDSYS                          OGC_Y

1 row selected.

SYS > select owner,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where SYNONYM_NAME='X';

OWNER      SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
---------- ------------------------------ ------------------------------ -------------------------
PUBLIC     X                              MDSYS                          OGC_X

1 row selected.

So to run it succsfully from the command line

$ expdp directory=TEST_DPUMP_DIR dumpfile=test_url_test.dmp logfile=test_url_test.log tables=anand.test_delete_objects query=\"WHERE processed_flag = \'Y\'\"

Export: Release 11.2.0.3.0 - Production on Tue May 29 01:11:44 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=TEST_DPUMP_DIR dumpfile=test_url_test.dmp logfile=test_url_test.log tables=anand.test_delete_objects query="WHERE processed_flag = 'Y'"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 112 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ANAND"."TEST_DELETE_OBJECTS"           10.09 KB      38 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /exp/kmportal_dpump_dir/test_url_test.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 01:12:24

Источник

Steps:

1. Update OPatch and OMSPatcher in the Middleware Home. To update
OPatch, go to the Patch 6880880, and pick 13.9 release. To update OMSPatcher,
go to the Patch 19999993 and pick the correct Enterprise Manager release. 

2. Download
the patch 24914115 and apply the patch

3.
Restart OMS

1. Update OPatch and OMSPatcher in the Middleware Home

[oracle@cloud ~]$  export ORACLE_HOME=/opt/oracle/middleware_13.2

[oracle@cloud ~]$
/opt/oracle/middleware_13.2/OPatch/opatch version

OPatch Version:
13.8.0.0.0

OPatch succeeded.

[oracle@cloud ~]$
/opt/oracle/middleware_13.2/OMSPatcher/omspatcher version

OMSPatcher
Version: 13.8.0.0.0

OPlan Version:
12.1.0.2.2

OsysModel build:
Mon Dec 21 18:31:52 PST 2015

OMSPatcher
succeeded.

[oracle@cloud ~]$ cd /tmp/

[oracle@cloud tmp]$ mkdir downloadpatch

[oracle@cloud tmp]$ cd downloadpatch

Download Patch 6880880 and Patch 19999993

[oracle@cloud downloadpatch]$ wget —http-user=username
—http-password=pass —no-check-certificate —output-document=filename https://updates.oracle.com/Orion/Services/download/p6880880_139000_Generic.zip?aru=20781116&patch_file=p6880880_139000_Generic.zip

[oracle@cloud downloadpatch]$ wget —http-user=username
—http-password=pass —no-check-certificate —output-document=filename https://updates.oracle.com/Orion/Services/download/p19999993_132000_Generic.zip?aru=20713685&patch_file=p19999993_132000_Generic.zip

[oracle@cloud downloadpatch]$ ls

p19999993_132000_Generic.zip  p6880880_139000_Generic.zip

[oracle@cloud downloadpatch]$ unzip
p6880880_139000_Generic.zip

[oracle@cloud downloadpatch]$ ls

6880880  p19999993_132000_Generic.zip  p6880880_139000_Generic.zip

[oracle@cloud downloadpatch]$ cd 6880880

[oracle@cloud 6880880]$
/opt/oracle/middleware_13.2/oracle_common/jdk/bin/java -jar opatch_generic.jar
-silent ORACLE_HOME=/opt/oracle/middleware_13.2

[oracle@cloud 6880880]$ $OMS_HOME/OPatch/opatch
version

OPatch Version:
13.9.1.0.0

OPatch succeeded.

[oracle@cloud 6880880]$ cd $OMS_HOME

[oracle@cloud middleware_13.2]$ mv OMSPatcher
OMSPatcher.bak

[oracle@cloud middleware_13.2]$ unzip /tmp/downloadpatch
/p19999993_132000_Generic.zip -d $OMS_HOME

Archive:  /tmp/patch/p19999993_132000_Generic.zip

   creating:
/opt/oracle/middleware_13.2/OMSPatcher/

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/omspatcher.bat

   creating:
/opt/oracle/middleware_13.2/OMSPatcher/jlib/

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/jlib/omspatcher.jar

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/jlib/oracle.omspatcher.classpath.jar

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/jlib/oracle.omspatcher.classpath.unix.jar

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/jlib/oracle.omspatcher.classpath.windows.jar

   creating:
/opt/oracle/middleware_13.2/OMSPatcher/scripts/

   creating:
/opt/oracle/middleware_13.2/OMSPatcher/scripts/oms/

   creating: /opt/oracle/middleware_13.2/OMSPatcher/scripts/oms/oms_child_scripts/

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/scripts/oms/oms_child_scripts/omspatcher_jvm_discovery

  inflating: /opt/oracle/middleware_13.2/OMSPatcher/scripts/oms/oms_child_scripts/omspatcher_jvm_discovery.bat

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/scripts/oms/oms_child_scripts/omspatcher_wls

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/scripts/oms/oms_child_scripts/omspatcher_wls.bat

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/scripts/oms/omspatcher

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/scripts/oms/omspatcher.bat

   creating:
/opt/oracle/middleware_13.2/OMSPatcher/wlskeys/

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/wlskeys/createkeys.cmd

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/wlskeys/createkeys.sh

   creating:
/opt/oracle/middleware_13.2/OMSPatcher/oms/

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/oms/generateMultiOMSPatchingScripts.pl

  inflating:
/opt/oracle/middleware_13.2/OMSPatcher/omspatcher

  inflating:
/opt/oracle/middleware_13.2/readme.txt

  inflating:
/opt/oracle/middleware_13.2/PatchSearch.xml

[oracle@cloud middleware_13.2]$ $OMS_HOME/OMSPatcher/omspatcher
version

OMSPatcher
Version: 13.8.0.0.1

OPlan Version:
12.2.0.1.6

OsysModel build:
Tue Nov 08 17:52:30 PST 2016

OMSPatcher
succeeded.

2. Apply Patch 24914115

[oracle@cloud middleware_13.2]$ cd /opt/oracle/patch

[oracle@cloud patch]$ 
wget —http-user=username —http-password=pass —no-check-certificate
—output-document=filename https://updates.oracle.com/Orion/Services/download/p24914115_132100_Generic.zip?aru=20792154&patch_file=p24914115_132100_Generic.zip

[oracle@cloud patch]$ unzip
p24914115_132100_Generic.zip

[oracle@cloud patch]$ cd 24914115/

[oracle@cloud 24914115]$ pwd

/opt/oracle/patch/24914115

[oracle@cloud 24914115]$ $ORACLE_HOME/OMSPatcher/omspatcher apply
-analyze

OMSPatcher
Automation Tool

Copyright
(c) 2016, Oracle Corporation.  All rights
reserved.

OMSPatcher
version : 13.8.0.0.1

OUI
version        : 13.8.0.0.0

Running
from       : /opt/oracle/middleware_13.2

Log
file location  :
/opt/oracle/middleware_13.2/cfgtoollogs/omspatcher/opatch2017-03-07_16-26-41PM_1.log

 OMSPatcher
log file:
/opt/oracle/middleware_13.2/cfgtoollogs/omspatcher/24914115/omspatcher_2017-03-07_16-26-45PM_analyze.log

Please enter OMS weblogic
admin server URL(t3s://cloud2.ipx.com:7102):> t3s://cloud.ipx.com:7102

Please
enter OMS weblogic admin server username(weblogic):>

Please
enter OMS weblogic admin server password:>

Configuration Validation:
Success

Running apply prerequisite
checks for sub-patch(es) «24914115» and Oracle Home
«/opt/oracle/middleware_13.2″…

Sub-patch(es)
«24914115» are successfully analyzed for Oracle Home
«/opt/oracle/middleware_13.2»

Complete Summary

================

All log file names referenced
below can be accessed from the directory
«/opt/oracle/middleware_13.2/cfgtoollogs/omspatcher/2017-03-07_16-26-41PM_SystemPatch_24914115_1»

 Prerequisites
analysis summary:

——————————-

The following sub-patch(es)
are applicable:

Featureset                            Sub-patches                                     Log file

———-                              ————                                     ———

oracle.sysman.top.oms      24914115  
24914115_opatch2017-03-07_16-26-45PM_1.log 

Log file location: /opt/oracle/middleware_13.2/cfgtoollogs/omspatcher/24914115/omspatcher_2017-03-07_16-26-45PM_analyze.log

OMSPatcher succeeded. 

[oracle@cloud 24914115]$
$ORACLE_HOME/OMSPatcher/omspatcher apply

OMSPatcher
Automation Tool

Copyright (c)
2016, Oracle Corporation.  All rights
reserved.

OMSPatcher version
: 13.8.0.0.1

OUI version        : 13.8.0.0.0

Running from       : /opt/oracle/middleware_13.2

Log file
location  :
/opt/oracle/middleware_13.2/cfgtoollogs/omspatcher/opatch2017-03-07_16-29-58PM_1.log

OMSPatcher log
file:
/opt/oracle/middleware_13.2/cfgtoollogs/omspatcher/24914115/omspatcher_2017-03-07_16-30-02PM_deploy.log

Please enter OMS
weblogic admin server URL(t3s://cloud2.ipx.com:7102):> t3s://cloud.ipx.com:7102

Please enter OMS
weblogic admin server username(weblogic):>

Please enter OMS
weblogic admin server password:>

Configuration
Validation: Success

Running apply
prerequisite checks for sub-patch(es) «24914115» and Oracle Home
«/opt/oracle/middleware_13.2″…

Sub-patch(es)
«24914115» are successfully analyzed for Oracle Home
«/opt/oracle/middleware_13.2»

To continue,
OMSPatcher will do the following:

[Patch and deploy
artifacts]   :

Do you want to
proceed? [y|n] y

User Responded
with: Y

Applying
sub-patch(es) «24914115»

Please monitor log
file:
/opt/oracle/middleware_13.2/cfgtoollogs/opatch/opatch2017-03-07_16-30-02PM_1.log

Complete Summary

================

All log file names
referenced below can be accessed from the directory
«/opt/oracle/middleware_13.2/cfgtoollogs/omspatcher/2017-03-07_16-29-58PM_SystemPatch_24914115_1»

Patching summary:

——————

Binaries of the
following sub-patch(es) have been applied successfully:

Featureset                                                   Sub-patches                                     Log file

———-                                                                ————                                     ———

  oracle.sysman.top.oms_13.2.0.0.0      24914115  
24914115_opatch2017-03-07_16-30-02PM_1.log

Log file location:
/opt/oracle/middleware_13.2/cfgtoollogs/omspatcher/24914115/omspatcher_2017-03-07_16-30-02PM_deploy.log

OMSPatcher
succeeded.

3. Restart  OMS

[oracle@cloud 24914115]$
/opt/oracle/middleware_13.2/bin/emctl stop oms –all

[oracle@cloud 24914115]$
/opt/oracle/middleware_13.2/bin/emctl start oms

To get more
information please check Oracle Support Doc ID 2199723.1

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка ora 06508
  • Ошибка ora 06503
  • Ошибка lci на стиральной машинке самсунг
  • Ошибка err 00001 счетчик цэ2726а
  • Ошибка ora 06413 соединение не открыто