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.
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

765 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