Store Multiple Values using Oracle Stored Procedure
Hello,
We have to develop a standard form of stored procedure to accept multiple values from front end and store it in a table in a database.
Normally, this would be possible using a comma separated value string variable. The split up of string would be carried out on oracle side and values would have been inserted. However, this frequently crops up the trailing "," issue.
Is there any better method to accept multiple values in a stored procedure? Can VARRAYS be used to accomplish this ... knowing that any number of such values can be received from front end for storage?
Can anyone point us to the right code sample or idea?
Thanks in advance.
We have to develop a standard form of stored procedure to accept multiple values from front end and store it in a table in a database.
Normally, this would be possible using a comma separated value string variable. The split up of string would be carried out on oracle side and values would have been inserted. However, this frequently crops up the trailing "," issue.
Is there any better method to accept multiple values in a stored procedure? Can VARRAYS be used to accomplish this ... knowing that any number of such values can be received from front end for storage?
Can anyone point us to the right code sample or idea?
Thanks in advance.
Another idea is to store the values in an Oracle table (temporary or permanent) and to clean this table when apropriate.
The third idea is to use Oracle PL/SQL package and to store the values in global VARARRAY in the package via a procedure in the same package.
The obstacle here is if you can keep the prelimenary steps (constructing and filling the VARARRAY) and the usage phase in the same Oracle session.
The explanation is that for every session the package is delivered in independent "incarnation" of the package.
The obstacle here is if you can keep the prelimenary steps (constructing and filling the VARARRAY) and the usage phase in the same Oracle session.
The explanation is that for every session the package is delivered in independent "incarnation" of the package.
I would probably have the procedure accept a CLOB as the input parameter and pass in XML.
Then you can easily parse any values you wish.
If you design the XML properly inserting is real easy:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb_dbmstore.htm#CACEJGEH
If you can provide a little more about the structure we can provide a working example.
Then you can easily parse any values you wish.
If you design the XML properly inserting is real easy:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb_dbmstore.htm#CACEJGEH
If you can provide a little more about the structure we can provide a working example.
ASKER
schwertner: & slightwv:
Thank you for the responses till date. The following is the scenario for a working model:
Table : User
Fields : First Name, Middle Name, Last Name
Data received from Java Code : IN Parameters is as under:
FIRST_NAME : JASON, BEN, KELLY
MIDDLE_NAME : K, NULL, D
LAST NAME : ANDERSON, ROBERT, GRANT
I desire to avoid the small errors arising out of the trailing comma (if any) attached to the IN parameters received from the JAVA Frontend. This removes the issue of Counting the number of elements in the input received and also the loop when executing a query.
Could you provide us a working code using VARRARY, STRING Input and XML Input.
Greatly appreciated.
Thanks in advance.
Thank you for the responses till date. The following is the scenario for a working model:
Table : User
Fields : First Name, Middle Name, Last Name
Data received from Java Code : IN Parameters is as under:
FIRST_NAME : JASON, BEN, KELLY
MIDDLE_NAME : K, NULL, D
LAST NAME : ANDERSON, ROBERT, GRANT
I desire to avoid the small errors arising out of the trailing comma (if any) attached to the IN parameters received from the JAVA Frontend. This removes the issue of Counting the number of elements in the input received and also the loop when executing a query.
Could you provide us a working code using VARRARY, STRING Input and XML Input.
Greatly appreciated.
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you have the ability to change the output from the Java app?
If so, I would strongly encourage you to go with XML. It is pretty much the standard for exchanging data between apps/systems.
>>Could you provide us a working code using VARRARY, STRING Input and XML Input.
I really don't have time to create three distinct working for you to pick the one you want to use.
Once you do a little research and decide on a model we can try to come up with an example of how it would work.
If you choose XML and generate the ROWSET format there is a working example in the link I provided.
If you want a more customized XML layout, decide on one and I can probably help with the stored procedure to parse and load it.
Just let us know.
If so, I would strongly encourage you to go with XML. It is pretty much the standard for exchanging data between apps/systems.
>>Could you provide us a working code using VARRARY, STRING Input and XML Input.
I really don't have time to create three distinct working for you to pick the one you want to use.
Once you do a little research and decide on a model we can try to come up with an example of how it would work.
If you choose XML and generate the ROWSET format there is a working example in the link I provided.
If you want a more customized XML layout, decide on one and I can probably help with the stored procedure to parse and load it.
Just let us know.
VARRAYS is not standart data type in the programming languages except may be PL/SQL and Oracle Forms environment.
There is a method in PL/SQl (pipelined function) that can use variable delimeter of values. So you can adjust the procedure to use different delimeter: ',' , TAB, etc.