Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

JDeveloper Studio 12c R2 (12.2.1.3.0) and XSLT Mapper?

Bob ParisJun 26 2019

How to Customize XSL files in JDeveloper Studio 12c R2?

Background:

Have JDeveloper Studio 12c R2 (12.2.1.3.0). All features installed.

Task:

Edit / customize existing xsl, xsd and xsd.

Searches:

JDeveloper Get Started documentation - https://docs.oracle.com/middleware/12213/jdev/index.html

Online documentation (11.1.1.5.0) references an XSLT Mapper.  https://docs.oracle.com/cd/E25054_01/dev.1111/e10224/bp_xslt_mpr.htm#BABHGACE

Expect to find this capability in JDeveloper Studio 12c R2.  Assistance appreciated in advance.

Comments

gdarling - oracle
Answer
Hi,

Have you already taken a look at the example on your hard drive.. %ORACLE_HOME%\ODP.NET\samples\2.x\AssocArray . It has IN, IN OUT, and OUT Associative Array Parameters.

I'd think your problem probably though is that you've failed to include "INDEX BY BINARY_INTEGER" in the type declaration. Try changing it to
TYPE OutParamArray IS TABLE OF VARCHAR2(200) index by binary_integer;
Here's a small complete sample I had laying around, and I can easily reproduce your complaint if I leave off " index by binary_integer"

Hope it helps,
Greg
/*

create or replace package mypack5 as
TYPE v2array is table of varchar2(4000) index by binary_integer;
PROCEDURE test_it(thearray out v2array);
END;
/
CREATE or replace PACKAGE BODY MYPACK5 AS
PROCEDURE test_it(thearray out v2array) IS
 begin
    thearray(1):='hello';
    thearray(2):='world'; 
    thearray(3):='from the stored procedure';
 END; 
END;
/
*/

using System;
using System.Data;
using Oracle.DataAccess.Client;
using System.Text;

public class out_v2_tab
{
    public static void Main()
    {

        using (OracleConnection con = new OracleConnection("data source=orcl;user id=scott;password=tiger;"))
        {
            con.Open();

            using (OracleCommand cmd = new OracleCommand("mypack5.test_it", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter Param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2);
                Param1.Direction = ParameterDirection.Output;
                Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                Param1.Size = 3;
                Param1.ArrayBindSize = new int[]{4000,4000,4000};

                cmd.ExecuteNonQuery();
                for (int i = 0; i < 3; i++)
                    Console.WriteLine("Param1[{0}] = {1} ", i,
                      (cmd.Parameters[0].Value as Array).GetValue(i));
            }
        }
    }
}
Marked as Answer by 721872 · Sep 27 2020
721872
Greg:

Thank You. That is what I needed in the stored procedure.

But one thing I cannot find, is how to convert an OracleString[] to a .Net string[] once this Stored Procedure is returned.

I mean, I know I could technically loop through the OracleString[] and do it one at a time, but I would think there is a more efficient way to accomplish this. The example doesn't have this in it, and unable to find something online (as of yet, but still looking).

Any efficient way of doing this without a massive loop?

Thanks
Andy
gdarling - oracle
Param1.OracleDbTypeEx = OracleDbType.Varchar2;
works when I add it to my example above at least, and causes Param1.Value to return string[] instead of OracleString[].

http://download.oracle.com/docs/html/E15167_01/OracleParameterClass.htm#CHDJHDGE

Hope it helps,
Greg
721872
Greg:

Thank you so much. I would never have found that without your help. I can't tell you how much I appreciate your help, knowledge and willingness to share it.

Thanks
Andy
858659
I wanted to thank you guys for this posting as I have just spent the last few days trying to figure out the disconnect between Oracle and my .NET application.

You see I have a stored procedure that has two OUT params. One was a ref cursor and the other was an plsqlassocitative array. While the cursor results were being pumped into the dataset, the array was not. I still do not know why this is the case but the fact that I found all my array results pumped back into the array parameter, I was able to manually access the parameter value and put it in my dataset manually with my other table.

As one solution sometimes present another obstruction, your post also helped me with the needed syntax for iterating through the array parameter Values.

Thanks for the time you took assisting with this post. Its a gift that keeps on giving.

Kathleen
1 - 5

Post Details

Added on Jun 26 2019
0 comments
132 views