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

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
  • 5
  • 4
LVL 76

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 48

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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 76

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 76

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 76

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

839 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