Меню

Ошибка ora 06553 pls 306

While I call the procedure, it gives me error:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'UPDATE_LETTER_BODY'

Here is my procedure code:

    CREATE OR REPLACE PROCEDURE FMIS3.UPDATE_LETTER_BODY IS
    body_text varchar2(32767); 
    condition_id integer;
    begin    
        update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY=body_text 
        where FKOL_OFFICEWISE_LETTER_ID=condition_id;
    end;

Here is how I called it:

     CALL UPDATE_LETTER_BODY('test',241);

asked Dec 19, 2012 at 7:09

hsuk's user avatar

3

Check this out : 

       CREATE OR REPLACE PROCEDURE FMIS3.UPDATE_LETTER_BODY  ( body_text IN FMS_K_OFFICEWISE_LETTER.FKOL_LETTER_BODY%type,condition_id in FMS_K_OFFICEWISE_LETTER.FKOL_OFFICEWISE_LETTER_ID%type)IS
       begin
          update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY= body_text
          where FKOL_OFFICEWISE_LETTER_ID=condition_id;
       end;

answered Dec 20, 2012 at 5:46

user1917764's user avatar

0

It should be 

CREATE OR REPLACE PROCEDURE FMIS3.UPDATE_LETTER_BODY(body_text in varchar2,condition_id in number) IS
begin    
    update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY=body_text 
    where FKOL_OFFICEWISE_LETTER_ID=condition_id;
end;

answered Dec 19, 2012 at 7:13

Rahul Vasantrao Kamble's user avatar

CREATE OR REPLACE PROCEDURE FMIS3.UPDATE_LETTER_BODY  ( body_text  IN varchar2,condition_id  in integer ) IS
begin    
    update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY=body_text 
    where FKOL_OFFICEWISE_LETTER_ID=condition_id;
end;

update your proc as above …

answered Dec 19, 2012 at 7:25

Nipun Jain's user avatar

Nipun JainNipun Jain

6224 silver badges6 bronze badges

1

Источник

I’m running the below query in oracle and i m facing the error ORA-06553.

select cast(cbdev.cbchr(utl_raw.substr(4500, 5, 26)) AS DECIMAL (14, 2)) as grant_held 
  from bs_transaction 
 where account_coid = 'TS 0014 T8324J3L2V' 
   and txn_id = 21;

Not sure why substr is throwing error when the syntax seem to be correct.
Can someone please help me with this.

DAIRAV's user avatar

DAIRAV

7291 gold badge8 silver badges29 bronze badges

asked Dec 28, 2018 at 8:22

Satyanvesh D's user avatar

6

Try the following:

select cast(cbdev.cbchr(utl_raw.substr('4500', 5, 26)) AS DECIMAL (14, 2)) as grant_held 
  from bs_transaction 
 where account_coid = 'TS 0014 T8324J3L2V' 
   and txn_id = 21;

Fahmi's user avatar

Fahmi

37.1k5 gold badges21 silver badges29 bronze badges

answered Dec 28, 2018 at 8:26

Joe Love's user avatar

Joe LoveJoe Love

5,3862 gold badges19 silver badges30 bronze badges

5

Источник

I’m running the below query in oracle and i m facing the error ORA-06553.

select cast(cbdev.cbchr(utl_raw.substr(4500, 5, 26)) AS DECIMAL (14, 2)) as grant_held 
  from bs_transaction 
 where account_coid = 'TS 0014 T8324J3L2V' 
   and txn_id = 21;

Not sure why substr is throwing error when the syntax seem to be correct.
Can someone please help me with this.

DAIRAV's user avatar

DAIRAV

7291 gold badge8 silver badges29 bronze badges

asked Dec 28, 2018 at 8:22

Satyanvesh D's user avatar

6

Try the following:

select cast(cbdev.cbchr(utl_raw.substr('4500', 5, 26)) AS DECIMAL (14, 2)) as grant_held 
  from bs_transaction 
 where account_coid = 'TS 0014 T8324J3L2V' 
   and txn_id = 21;

Fahmi's user avatar

Fahmi

37.1k5 gold badges21 silver badges29 bronze badges

answered Dec 28, 2018 at 8:26

Joe Love's user avatar

Joe LoveJoe Love

5,3862 gold badges19 silver badges30 bronze badges

5

Источник

Error Message

When registering with ArcSDE an Oracle table containing an ST_Geometry column or altering the registration of a feature class in Oracle containing an ST_Geometry column, the following error message may occur:

«.ORA-06553: PLS-306: wrong number or types of arguments in call to ‘SDO_AGGR_MBR'»

Note:
This error message is itself an error; spatial type tables do not use Oracle Spatial calls (in this case, SDO_AGGR_MBR).

Cause

This is a known issue in ArcSDE 9.2.

Solution or Workaround

Create a spatial index on the ST_Geometry column in the business table of the feature class before registering the feature class with ArcSDE or altering the feature class’ registration.

Using a SQL client such as SQL*Plus, execute the CREATE INDEX statement. Provide a name for the spatial index, the name of the ST_Geometry column, grid sizes, and spatial reference ID.

The following example creates a spatial index named crm_idx on a table named cult_rcs, which contains an ST_Geometry named shape. One grid level is used (size=10) and the spatial reference ID is 1.

Code:
create index crm_idx on cult_rcs(shape)
indextype is sde.st_spatial_index
parameters('st_grids=10,0,0 st_srid=1');

Once the spatial index exists, proceed with registering the table with ArcSDE using the sdetable -o register command, or altering the table’s registration using the sdetable -o alter_reg command. Consult the ArcSDE Administration Command Reference provided with ArcGIS Server Enterprise for correct syntax and usage of these commands.

Last Published: 5/5/2016

Article ID: 000009084

Источник

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

Источник

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest

0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии

А вот еще интересные материалы:

  • Яшка сломя голову остановился исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного где ошибка
  • Ошибка ora 06550 pls 00201
  • Ошибка ora 06413 соединение не открыто

    • ➤