Avatar of Adwait Chitaley
Adwait Chitaley
Flag for India asked on

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.
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
schwertner

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.
schwertner

Another idea is to store the values in an Oracle table (temporary or permanent) and to clean this table when apropriate.
schwertner

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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:
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.
Adwait Chitaley

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.
ASKER CERTIFIED SOLUTION
flow01

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
slightwv (䄆 Netminder)

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.