Array of Key/Value Pairs as input to Oracle function

Posted on 2016-10-21
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 100 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.
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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:
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 400 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

726 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