1 Reply Latest reply on Jul 26, 2005 11:17 AM by 416900

    java.sql.SQLException: invalid name pattern

    448054
      Hi,
      I am using FTP adapter and DB adapter to pass my messages from a flat file to the database. I have created a D3L to process the message and have imported the D3L file for the common view. Created the FTPApp for publishing and DBapp for subscribing. Again have used the D3L file in the FTPApp and have mapped to the common view and also have used import common view for the DB adapter which is the same D3L file stored as the common view. When we first wanted to create application view for the DB adapter we by mistake have choosen ObjectCopy in the transformation and a script with a object B2BAPP_BOMCSV.STRUCT_OAI_V_ARR was created, but we deleted the subscribe and then created again using the CopyFields option and have written the procedure and have complied it in the database. Now whenever we are passing the file it comes to DB adapter and throws the following error:

      java.sql.SQLException: invalid name pattern: B2BAPP_BOMCSV.STRUCT_OAI_V_ARR
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:161)
           at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:411)
           at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:326)
           at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1299)
           at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:155)
           at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:112)
           at oracle.oai.agent.adapter.database.DBMessageWriter.createOracleArray(DBMessageWriter.java:1028)
           at oracle.oai.agent.adapter.database.DBMessageWriter.createOracleObject(DBMessageWriter.java:1089)
           at oracle.oai.agent.adapter.database.DBMessageWriter.createOracleObject(DBMessageWriter.java:1056)
           at oracle.oai.agent.adapter.database.DBMessageWriter.addInArguments(DBMessageWriter.java:550)
           at oracle.oai.agent.adapter.database.DBMessageWriter.getPushInfo(DBMessageWriter.java:374)
           at oracle.oai.agent.adapter.database.DBMessageWriter.pushToDatabase(DBMessageWriter.java:272)
           at oracle.oai.agent.adapter.database.DBMessageWriter.messageReceived(DBMessageWriter.java:224)
           at oracle.oai.agent.client.SmartQueueDispatcher.run(SmartQueueDispatcher.java:315)
           at java.lang.Thread.run(Thread.java:534)
      We have checked that B2BAPP_BOMCSV.STRUCT_OAI_V_ARR does not exist as a object in the database. Wanted to know from where is the db adapter picking up the B2BAPP_BOMCSV.STRUCT_OAI_V_ARR.
        • 1. Re: java.sql.SQLException: invalid name pattern
          416900
          From memory, the deletion of objects (Application Data Types and Common Data Types) can leave orphaned objects all over the place. Likewise, mapping from XML or D3L to a flat table structure for me is a little scary. iStudio creates "Subscribe" procedures, and if necessary any other objects you will need to consume the message.

          When you run the Subscribe Wizard, and get to the Define Stored Procedure screen, you will augment the iStudio code to perform some inserts into tables etc.
          Once you've done this, you need then to go to
          File...Export PL/SQL
          Expand on the DB Application name, the Subscribe Events and highlight the event.
          Complete the File Prefix, and select the destination of the output files.
          iStudio will create two files. One called [App_Name]_[Event_Name].sql and another called [App_Name]_[Event_Name]TYPES.sql

          Is the B2BAPP_BOMCSV.STRUCT_OAI_V_ARR object creation in the [App_Name]_[Event_Name]TYPES.sql file?

          iStudio has never been (IMHO) and still is not the "sharpest tool" in the OAI toolkit.

          In my experience, I would tend to start again. Delete ALL Events, and Data Types for your message. i.e. the FTP side, the DB side and then the Common View. Make sure that ALL objects are deleted. Start deleting the child attributes first from the Application Data Types and Common Data Types.

          I've had the "fun" in the past of deleting the top or middle parts of the array data types structure, and then getting an error message.

          Once you have removed all evidence of your transaction, stop and restart the repository service.

          When recreating the Common View, use the Import...XML (using your dtd). Likewise, use Import..XML again to create the FTP side too (do not use the "Import...Common Data Type").

          In the past I have closely based my target DB table on the XML or D3L object, choosing to create array objects as columns within the table. This makes the mapping of objects so much easier.

          e.g. If my XML looks like this.....

          <?xml version="1.0"?>
          <EXAMPLE_DATA>
          <message_type>Example Message</message_type>
          <message_desc>An example of array</message_desc>
          <level_one>
          <level_one_id>One</level_one_id>
          <level_one_desc>Level One Desc</level_one_desc>
          <level_two>
          <level_two_id>Two</level_two_id>
          <level_two_desc>Level Two Desc</level_two_desc>
          <level_three>
          <level_three_id>3</level_three_id>
          <level_three_desc>Level Three Desc</level_three_desc>
          <level_four>
          <level_four_id>Four</level_four_id>
          <level_four_desc>Level Four Desc</level_four_desc>
          <level_five>
          <level_five_data>Lev5.1</level_five_data>
          <level_five_data>Lev5.2</level_five_data>
          <level_five_data>Lev5.3</level_five_data>
          </level_five>
          </level_four>
          </level_three>
          </level_two>
          </level_one>
          </EXAMPLE_DATA>

          I create my table using this set of scripts.....

          --
          -- Create Objects Used by the EXAMPLE_OAI table
          --

          --
          -- Create type level_five
          --
          CREATE OR REPLACE TYPE level_five
          IS OBJECT (level_five_data     VARCHAR2(6));
          /

          --
          -- Create array of level_five (level_five_arr)
          --
          CREATE OR REPLACE TYPE level_five_arr
          IS VARRAY(5000) OF level_five;
          /

          --
          -- Create type level_four_type
          --
          CREATE OR REPLACE TYPE level_four_type
          IS OBJECT (level_four_id VARCHAR2(4)
          ,level_four_desc VARCHAR2(40)
          ,level_five level_five_arr );
          /

          --
          -- Create array of level_four_type (level_four_arr)
          --
          CREATE OR REPLACE TYPE level_four_arr
          IS VARRAY(5000) OF level_four_type;
          /

          --
          -- Create type level_three_typ
          --
          CREATE OR REPLACE TYPE level_three_typ
          IS OBJECT (level_three_id VARCHAR2(3)
          ,level_three_desc VARCHAR2(40)
          ,level_four level_four_arr );
          /

          --
          -- Create array of level_three_typ (level_three_arr)
          --
          CREATE OR REPLACE TYPE level_three_arr
          IS VARRAY(5000) OF level_three_typ;
          /

          --
          -- Create type level_two_typ
          --
          CREATE OR REPLACE TYPE level_two_typ
          IS OBJECT (level_two_id VARCHAR2(3)
          ,level_two_desc VARCHAR2(40)
          ,level_three level_three_arr );
          /

          --
          -- Create array of level_two_typ (level_two_arr)
          --
          CREATE OR REPLACE TYPE level_two_arr
          IS VARRAY(5000) OF level_two_typ;
          /

          -- Create type tlb
          CREATE OR REPLACE TYPE level_one_typ
          IS OBJECT (level_one_id VARCHAR2(3)
          ,level_one_desc VARCHAR2(40)
          ,level_two level_two_arr );
          /

          --
          -- Create array of level_two_typ (level_two_arr)
          --
          CREATE OR REPLACE TYPE level_one_arr
          IS VARRAY(5000) OF level_one_typ;
          /

          --
          -- Create EXAMPLE_OAI table
          --


          CREATE TABLE example_oai (
          message_type VARCHAR2(20)
          ,message_desc VARCHAR2(20)
          ,level_one level_one_arr
          ,oai_correlation_id VARCHAR2(70)
          ,processed_flag VARCHAR2(1)
          ,last_update_date DATE -- Standard Who Column
          ,last_update_by NUMBER(15) -- Standard Who Column
          ,creation_date DATE -- Standard Who Column
          ,created_by NUMBER(15) -- Standard Who Column
          ,last_update_login NUMBER(15) -- Standard Who Column
          );

          **Note**
          My DB Adapter usually connects to the APPS schema. My staging / message tables are created in a custom XXOAI schema. Therefore I need to GRANT to APPS all the objects and tables created using the above script.

          I then base my DB Application View on this EXAMPLE_OAI table, and map between the Common View and the DB Application View.

          In iStudio, when you get to the "Defined Stored Procedure" screen in the Subscribe Wizard, add the insert statement to the EXAMPLE_OAI table....

          PROCEDURE sub_Example_OAI_V1(
          MESSAGE_TYPE IN LONG,
          MESSGE_DESC IN LONG,
          LEVEL_ONE IN XXOAI.LEVEL_ONE_ARR,
          LAST_UPDATE_DATE IN DATE,
          LAST_UPDATE_BY IN NUMBER,
          CREATION_DATE IN DATE,
          CREATED_BY IN NUMBER,
          LAST_UPDATE_LOGIN IN NUMBER
          )
          AS
          -- Set Runtime Variables
          lv_date_time DATE := sysdate;
          lv_last_update_date DATE := lv_date_time; -- Standard Who Column
          lv_last_update_by NUMBER(15) := fnd_global.user_id; -- Standard Who Column
          lv_creation_date DATE := lv_date_time; -- Standard Who Column
          lv_created_by NUMBER(15) := fnd_global.user_id; -- Standard Who Column
          lv_last_update_login NUMBER(15) := fnd_global.login_id; -- Standard Who Column

          BEGIN
          -- Insert Example Data into the XXOAI.EXAMPLE_OAI staging table
          INSERT INTO XXOAI.EXAMPLE_OAI
          (MESSAGE_TYPE
          ,MESSAGE_DESC
          ,LEVEL_ONE
          ,LAST_UPDATE_DATE
          ,LAST_UPDATE_BY
          ,CREATION_DATE
          ,CREATED_BY
          ,LAST_UPDATE_LOGIN
          )
          VALUES
          (MESSAGE_TYPE
          ,MESSAGE_DESC
          ,LEVEL_ONE
          ,lv_last_update_date
          ,lv_last_update_by
          ,lv_creation_date
          ,lv_created_by
          ,lv_last_update_login
          );
          COMMIT;
          END sub_Example_OAI_V1;

          To get at the data in the EXAMPLE_OAI table, use this script....

          SELECT
          eo.MESSAGE_TYPE "Message Type"
          ,eo.MESSAGE_DESC "Message Desc"
          ,l1.level_one_id "Level 1 ID"
          ,l1.level_one_desc "Level 1 Desc"
          ,l2.level_two_id "Level 2 ID"
          ,l2.level_two_desc "Level 2 Desc"
          ,l3.level_three_id "Level 3 ID"
          ,l3.level_three_desc "Level 3 Desc"
          ,l4.level_four_id "Level 4 ID"
          ,l4.level_four_desc "Level 4 Desc"
          ,l5.level_five_data "Level 5 Data"
          FROM
          example_oai eo
          ,TABLE(eo.level_one) l1
          ,TABLE(l1.level_two) l2
          ,TABLE(l2.level_three) l3
          ,TABLE(l3.level_four) l4
          ,TABLE(l4.level_five)(+) l5

          I know that this reply does not automatically answer your question regarding the "java.sql.SQLException: invalid name pattern: B2BAPP_BOMCSV.STRUCT_OAI_V_ARR" exception you are getting. But using the above technique avoids having to import additional iStudio created objects, like arrays, as they are managed and controlled by you.

          Hope this helps.
          Yan