Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Array of Key/Value Pairs as input to Oracle function

Posted on 2016-10-21
Medium Priority
Last Modified: 2016-10-26
I need to know how to write an Oracle function that will take an array of key/value pairs and insert items into a table.

The parameters need to be: FileName (VARCHAR2), Keywords(array of key/value pairs)

I will insert a record into a table called FileDir using the FileName parameter using this code:
select seq_fd_rid.nextval into pfd_rid from dual;
insert into filedirectory (fd_rid, fd_filename, fd_lastmodified, fd_eve_rid, fd_old)
    values (pfd_rid, vfd_filename, nvl(vfd_lastmodified,sysdate), veve_rid, 'N')

Open in new window

then I will loop through the array and insert a record into another table called FileKeyPair for each of the keywords
insert into cic3.filekeypair (fkp_fd_rid, fkp_keyword, fkp_value) values (pfd_rid, keyword, value)

Open in new window

How would I write a function to take an array of key/value pairs and do this?

Would I use something like this as input and iterate over it like this?

FOR indx IN 
    kvp.FIRST .. kvp.LAST
    insert into cic3.filekeypair (fkp_fd_rid, fkp_keyword, fkp_value) values (pfd_rid, kvp[indx].KEYWORD_, kvp[indx].VALUE_)   ;

Open in new window

Question by:EddieShipman
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41854745
Sorry but your requirements are a little confusing.

If I'm guessing correctly, you want to load the contents of a file into some table?

You can pass in an array as long as it is an Oracle declared data type.  It also depends on what language you are using to call the procedure.  Look at associative arrays.

Personally, I would look at passing in XML and parsing it once received.

Please provide some sample data and expected results or at least some more information about the exact requirements.
LVL 49

Assisted Solution

PortletPaul earned 400 total points
ID: 41854801
array of key/value pairs
               e.g.  a/b,b/c,c/d,d/e

The problem is you have simply assumed we know what/how you are passing that "array". As slightwv suggest one (good) way would be to use XML but there is an almost infinite set of possibilities.

fnKeepArray('my_file', 'a/b,b/c,c/d,d/e')

& is the expectation that results in rows of 3 columns, like this?

filename      array_key   array_value
my_file         a                   b
my_file         b                   c
my_file         c                   d
my_file         d                   e

what is the table name? and the names of each column?
LVL 26

Author Comment

ID: 41855317
@portlet Paul,
The filename is the same for all entries in the Key/Value Pair so there is no need to put it in the array.

I put the table names and columns in the code I posted, did you not see that?

No, I will be passing these as arguments to the function, not loading a file.

We do have some functions where we pass JSON and parse it, and that may be the way it has to be done.
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41855678
12c has some JSON parsing ability but I haven't played with any of it.

Parsing a string as input into a procedure is pretty straight forward.  The level of effort is directly related to the format being passed in.

This is where sample data and expected results will help.  Personally I would vote for XML over JSON.  Then I don't have to write the parsing code.  Oracle has done that for me.
LVL 26

Author Comment

ID: 41857294

What I need to do is pass an array of Key/Value Pairs to a function that will build some dynamic SQL to run and return the records.

There is some demo data and a better explanation of the tables in this post: https://www.experts-exchange.com/questions/28965919/Oracle-query-tuning-help-required.html
I tried to tune the SQL but it still isn't working so I'm going a different route by modifying the function that returns the document records.

The main table filekeypair has fields:
fkp_fd_rid -- pointer to fd_rid in filedirectory table
fkp_keyword -- keyword
fkp_value -- value for this keyword record
fkp_insdate --- insert date for this record, set by a trigger

The other related table, filedirectory, has these fields:
fd_rid -- id of record
fd_filename -- path to the file on our server
fd_lastmodified -- last modified date of the document
fd_eve_rid -- id of event document associated with
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41857341
I would suggest making the input parameter XML or JSON and passing in the multiple values that way.

Parsing the XML using Oracle would be easier since you can leverage build in functions.
LVL 26

Author Comment

ID: 41857410
But it's more difficult to pass from .Net. This is going to be called from a web service and I have all data going in and out as JSON.
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 1600 total points
ID: 41857426
I'm not following.

I generate XML with .Net code and call Oracle procedures with it all the time.

You can use JSON as a CLOB/VARCHAR2 parameter to the Oracle code but unless you are running 12c, you will have to write your own parser.

Since the JSON likely won't be all that complex, it should be pretty simple to parse.  There are several examples here on the site.

If you can post some example JSON that you might use, we can probably help with the parsing at the Oracle level.
LVL 26

Author Comment

ID: 41861136
The JSON required would be something like this:
         "VALUE":"My Document"
         "VALUE":"\\Myserver\Share\File Name.txt"

Open in new window

I'm going to use this to create dynamic SQL to retrieve documents from the filekeypair/filedirectory tables.
The original post, concerning inserts, is irrelevant due to my finding a function to do that already.
Let me close this question and open another.
LVL 26

Author Closing Comment

ID: 41861138
Closing and going to ask another question

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question