?
Solved

Array of Key/Value Pairs as input to Oracle function

Posted on 2016-10-21
10
Medium Priority
?
252 Views
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?
TYPE kvp IS RECORD (KEYWORD_ VARCHAR2(5), VALUE_ VARCHAR2(50))

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

Open in new window

0
Comment
Question by:Eddie Shipman
  • 5
  • 4
10 Comments
 
LVL 78

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.
0
 
LVL 49

Assisted Solution

by:PortletPaul
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?
0
 
LVL 26

Author Comment

by:Eddie Shipman
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?

@Netminder,
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 78

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.
0
 
LVL 26

Author Comment

by:Eddie Shipman
ID: 41857294
@Netminder,

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
0
 
LVL 78

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.
0
 
LVL 26

Author Comment

by:Eddie Shipman
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.
0
 
LVL 78

Accepted Solution

by:
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.
0
 
LVL 26

Author Comment

by:Eddie Shipman
ID: 41861136
The JSON required would be something like this:
{
   "Keywords":[
      {
         "Keyword":"CLI_RID",
         "VALUE":1158341
      },
      {
         "Keyword":"EVENT_CODE",
         "VALUE":"20161026EECC"
      },
      {
         "Keyword":"DOC_NAME",
         "VALUE":"My Document"
      },
      {
         "Keyword":"FILENAME",
         "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.
0
 
LVL 26

Author Closing Comment

by:Eddie Shipman
ID: 41861138
Closing and going to ask another question
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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", 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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month16 days, 9 hours left to enroll

864 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