1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Ora-02303 - works in one database, doesn't work in other

Discussion in 'SQL PL/SQL' started by NKK, Apr 2, 2019.

  1. NKK

    NKK Newly Initiated

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    uk
    I’m trying to register an existing schema in the database again through a sql script like below.


    The script worked in one oracle database, but the same script failed with an error (ORA-02303) in another oracle database.


    Sql script->


    Xml_ = Is a variable and supply the structure of schema


    Last statement in the sql script ->

    Xml_API.Reg_Schema('http://XYZ/OrderData.xsd', xml_);



    Oracle Xml_API package ->


    PROCEDURE Reg_Schema (
    xml_schema_url_ IN VARCHAR2,
    xml_schema_ IN CLOB)
    IS
    SCHEMA_TO_DELETE_DOESNT_EXIST EXCEPTION;
    PRAGMA EXCEPTION_INIT(SCHEMA_TO_DELETE_DOESNT_EXIST, -31000);
    BEGIN

    BEGIN
    dbms_xmlschema.deleteSchema(xml_schema_url_,dbms_xmlschema.DELETE_CASCADE_FORCE);
    EXCEPTION
    WHEN SCHEMA_TO_DELETE_DOESNT_EXIST THEN NULL;
    END;
    dbms_xmlschema.registerSchema(xml_schema_url_, xml_schema_);
    END Reg_Schema;

    Error in one database once the script is executed->
    [DATABASE][Oracle JDBC Driver][Oracle]ORA-02303: cannot drop or replace a type with type or table dependents
    ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 72
    ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 33
    ORA-06512: at "IFSAPP.XML_API", line 537
    ORA-06512: at line 2240

    But this script can be run multiple times against one database without any issue. However, other database raises the above error (This schema was already deployed in both databases. I tried to update it through the script this time).

    How can I find the root cause for this ORA-02303 issue? I assume that dbms_xmlschema.deleteSchema doesn't work properly in one database. How can I find that?

    Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,666
    Likes Received:
    375
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    First which release or releases are you using? Please report the Oracle release for both databases. There is not much anyone can do to help you without that information.
     
  3. NKK

    NKK Newly Initiated

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    uk
    Working database ->

    Code (SQL):
    SQL> SELECT * FROM v$version;
    BANNER                                                                               CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle DATABASE 12c Release 12.1.0.1.0 - 64bit Production                                 0
    PL/SQL Release 12.1.0.1.0 - Production                                                    0
    CORE    12.1.0.1.0    Production                                                                0
    TNS FOR 64-bit Windows: Version 12.1.0.1.0 - Production                                   0
    NLSRTL Version 12.1.0.1.0 - Production                                                    0

     

    Not working one ->

    Code (SQL):
    SQL> SELECT * FROM v$version;
    BANNER                                                                               CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle DATABASE 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
    PL/SQL Release 12.1.0.1.0 - Production                                                    0
    CORE    12.1.0.1.0    Production                                                                0
    TNS FOR 64-bit Windows: Version 12.1.0.1.0 - Production                                   0
    NLSRTL Version 12.1.0.1.0 - Production                                                    0

     
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    775
    Likes Received:
    148
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    Please,provide here result this query from two your of database :
    Code (SQL):
    SELECT * FROM sys.dba_registry_sqlpatch
     
  5. NKK

    NKK Newly Initiated

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    uk
    This query returns empty result set for both databases.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,666
    Likes Received:
    375
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Run this query in both databases and post the results:

    select parameter from v$option where value = 'TRUE';

    I suspect you have options installed in the non-working database that are not installed in the working version.
     
  7. NKK

    NKK Newly Initiated

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    uk
    Working one ->
    1 Objects
    2 Connection multiplexing
    3 Connection pooling
    4 Database queuing
    5 Incremental backup and recovery
    6 Instead-of triggers
    7 Parallel load
    8 Proxy authentication/authorization
    9 Plan Stability
    10 Coalesce Index
    11 Transparent Application Failover
    12 Sample Scan
    13 Java
    14 OLAP Window Functions
    15 Flashback Data Archive
    16 DICOM
    17 XStream

    Non-working one ->
    1 Objects
    2 Advanced replication
    3 Bit-mapped indexes
    4 Connection multiplexing
    5 Connection pooling
    6 Database queuing
    7 Incremental backup and recovery
    8 Instead-of triggers
    9 Parallel backup and recovery
    10 Parallel execution
    11 Parallel load
    12 Point-in-time tablespace recovery
    13 Fine-grained access control
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    775
    Likes Received:
    148
    Trophy Points:
    830
    Location:
    Russian Federation
    What is returning this query :select * from all_registry_banners l where l.banner like '%XML%' ;
     
    Last edited: Apr 10, 2019
  9. NKK

    NKK Newly Initiated

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    uk
    Both environments return this > Oracle XML Database Version 12.1.0.1.0 - Production