Home » Developer & Programmer » JDeveloper, Java & XML » Require help on Array of Nested tables and Oracle Object type (Oracle9i Enterprise Edition Release 9.2.0.8.0)
Require help on Array of Nested tables and Oracle Object type [message #580924] Fri, 29 March 2013 14:15 Go to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
Hi All,
I have a scenario where I have some millions of records received from a flat file and the record is stored in Table as below:

Tablename: FILE_RECORD

Rows:
FILE_REG_ID = 1
RECORD_NBR = 1
PROCESSED_IND = U
RECORD= 00120130326006A

FILE_REG_ID = 1
RECORD_NBR = 2
PROCESSED_IND = U
RECORD= 00120130326003

1) I have to read these records at once and
a) Split the RECORD column to get various other data Eg: Fld1=001, Fld2=20130326, Fld3 = 003
b) send as an Array to Java.

2) Java will format this into XML and sent to other application.

3) The other application returns a response as Successful or Failure to Java in XML

4) Java will send RECORD_NBR and the corresponding response as Success or Failure back to PLSQL

5) PLSQL should match the RECORD_NBR and update the PROCESSED_IND = P.

I 'm able to achieve this using SQL Table type by creating a TYPE for Each of the fields (Flds) however the problem is Java cannot Access the parameters as the TYPE are of COLUMN Types
Eg: For RECORD_NBR
SUBTYPE t_record_nbr IS FILE_RECORD.T010_RECORD_NBR%TYPE;

Can you please let me know how I can achieve this to support Java, I know one way that is by creating an OBJECT TYPE and a TABLE of the OBJECT TYPE.
Eg: T_FILE_RECORD_REC IS OBJECT
FILE_REG_ID number(Cool, RECORD_NBR number (10), PROCESSED_IND varchar2(1), RECORD varchar(20)
Create type T_FILE_RECORD_TAB IS TABLE OF T_FILE_RECORD_REC

However I'm facing a problem to populate an Array of records, I know I'm missing something important. Hence please help.

It would be helpful to provide some guidelines and suggestions or Pseudo or a Code to achieve this. Rest all I can take up further.

Thanks in advance,
Re: Require help on Array of Nested tables and Oracle Object type [message #580925 is a reply to message #580924] Fri, 29 March 2013 14:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the table description?
Where is the problem? In details.
Why are you using object type if you don't how to use them?
Post code, FORMATTED (see your previous topics).

Regards
Michel
Re: Require help on Array of Nested tables and Oracle Object type [message #580926 is a reply to message #580925] Fri, 29 March 2013 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All your previous topics show that you don't know how to use object type and collections.
Do what has been advised you.
Stay on what you know and forget what you don't and is worse that what you know.

Regards
Michel
Re: Require help on Array of Nested tables and Oracle Object type [message #580928 is a reply to message #580924] Fri, 29 March 2013 14:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please see if the example in the following thread helps.

http://www.orafaq.com/forum/mv/msg/17246/580563/0/#msg_580563
Re: Require help on Array of Nested tables and Oracle Object type [message #580929 is a reply to message #580928] Fri, 29 March 2013 15:19 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing
Previous Topic: how to pass any array from java to Oracle
Next Topic: ORA-00932: inconsistent datatypes:
Goto Forum:
  


Current Time: Thu Mar 28 05:44:50 CDT 2024