Skip to main content
A
Anonymous
June 13, 2010
Question

Passing an array of structures to an Oracle stored procedure (CFMX)

  • June 13, 2010
  • 1 reply
  • 587 views

I'm looking to write a Oracle stored procedure where I would pass in an array of structures and loop over each iteration to insert the bits and pieces within the structures to the DB.,

I haven't written this type of procedure / package before.  I am planning to do an sp / package similar to what is sketched out in the second reply to this thread: http://forums.oracle.com/forums/thread.jspa?threadID=1078772

Assuming I do, how can I call the procedure from ColdFusion (I'm using MX) and pass in my array?  As far as I can see, none of the CF_SQL_Types make sense.

    This topic has been closed for replies.

    1 reply

    W
    wrkoch
    Inspiring
    June 28, 2010

    Let me know if you make any progress.  I'm fighting the same battle.  What I've done so far is to convert my array of struct into a delimited CLOB that looks like this:

    prop1;prop2;prop3|prop1;prop2;prop3|prop1;prop2;prop3|prop1;prop2;prop3|

    Then I wrote a stored proc to suck it up using a pipelined function.  It's not to bad but parsing the CLOB on the ORACLE side is somewhat time consuming.

    I've also converted the array to XML and used dbms_xmlstore to convert but, on large arrays, it is very slow and the CLOB gets huge fast.

    I was hoping to use the cf_sql_refcursor but I can't figure out how.

    Warren

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices