Link to home
Start Free TrialLog in
Avatar of Adwait Chitaley
Adwait ChitaleyFlag for India

asked on

Store Multiple Values using Oracle Stored Procedure


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.
Avatar of schwertner
Flag of Antarctica image

It depends on the programming language your  application uses.
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.
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.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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:

If you can provide a little more about the structure we can provide a working example.
Avatar of Adwait Chitaley


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:


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.
Avatar of flow01
Flag of Netherlands image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.