Oracle Structure in OUT parameter of a stored function

Oracle Structure in OUT parameter of a stored function

Postby Kineas » Tue 14 May 2013 14:42

Hi,
I have something kind complicated to do and I want to know if it is possible.
I want to call a stored function using "Dotconnect For Oracle" and here is the function definition :
Code: Select all
FUNCTION GET_PATTERN_FOR_JAVA(p_code IN  VARCHAR2,
                     p_loc_id IN  LOCATIONS.id%TYPE,
                     p_num_seq OUT NUMBER,
                     p_pattern_name OUT VARCHAR2,
                     p_result OUT TO_COD,
                     p_commentaire OUT VARCHAR2) RETURN VARCHAR2;

My main problem is how read the out parameter of type TO_COD since it's a table of an Oracle structure I have created :
Code: Select all
CREATE OR REPLACE TYPE CELMAN."TO_COD" AS TABLE OF O_COD;

CREATE OR REPLACE TYPE CELMAN.O_COD AS OBJECT
    (field_id      NUMBER,
     field_name    VARCHAR2(64),
     val           VARCHAR2(1000),
     LENGTH        NUMBER,
     TYPE          VARCHAR2(30),
     format        VARCHAR2(128),
     seq          VARCHAR2(128),
     flag_check_ok VARCHAR2(1),
     msg_error     VARCHAR2(1000)
     );


I have write the code in java using base oracle library like this :
Code: Select all
statement = connection.prepareCall("{CALL ? := PATTERN_CONTROL_PKG.GET_PATTERN_FOR_JAVA(?, ?, ?, ?, ?, ?) }");

statement.registerOutParameter(1, OracleTypes.VARCHAR);
statement.setString(2, value);
statement.setLong(3, id);
statement.registerOutParameter(4, OracleTypes.NUMBER);
statement.registerOutParameter(5, OracleTypes.VARCHAR);
statement.registerOutParameter(6, OracleTypes.ARRAY, "TO_COD");
statement.registerOutParameter(7, OracleTypes.VARCHAR);

statement.executeQuery();

if (statement.getString(1).equals("TRUE")) {
pattern = new PatternEntity(value, statement.getString(5), statement.getInt(4));

ARRAY recordsTab = (ARRAY)statement.getObject(6);
rs = recordsTab.getResultSet();
int ind;
RecordEntity record;
while (rs.next()) {
   record = new RecordEntity();
   STRUCT struct = (STRUCT) rs.getObject(2);
   StructDescriptor descr = struct.getDescriptor();
   ResultSetMetaData md = descr.getMetaData();
   ind = 1;
   for (Object att : struct.getAttributes()) {
      if (md.getColumnName(ind).equalsIgnoreCase("field_id")) {
         record.setId(((BigDecimal) att).longValue());
      } else if(md.getColumnName(ind).equalsIgnoreCase("field_name")) {
         record.setName((String) att);
      } else if(md.getColumnName(ind).equalsIgnoreCase("val")) {
         record.setVal((String) att);
      } else if(md.getColumnName(ind).equalsIgnoreCase("length")) {
         record.setLenght(((BigDecimal) att).longValue());
      } else if(md.getColumnName(ind).equalsIgnoreCase("type")) {
         record.setType((String) att);
      } else if(md.getColumnName(ind).equalsIgnoreCase("format")) {
         record.setFormat((String) att);
      } else if(md.getColumnName(ind).equalsIgnoreCase("seq")) {
         record.setSeq((String) att);
      } else if(md.getColumnName(ind).equalsIgnoreCase("flag_check_ok")) {
         record.setOk(((String) att).equals("Y") ? true : false);
      } else if(md.getColumnName(ind).equalsIgnoreCase("msg_error")) {
         record.setErrorMessage((String) att);
      }
      ind++;
   }
   pattern.addRecordInList(record);
}

As you can see, the "complicated" part is how read the structure (6th parameter) with the "Dotconnect For oracle" library ?

Thanks.
Kineas
 
Posts: 10
Joined: Mon 22 Apr 2013 14:46

Re: Oracle Structure in OUT parameter of a stored function

Postby Kineas » Wed 15 May 2013 10:43

So far I succeed to do this :
Code: Select all
oConnexion.Open();
               
                cmd = new OracleCommand("PATTERN_CONTROL_PKG.GET_PATTERN_FOR_JAVA", oConnexion);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new OracleParameter("ReturnValue", OracleDbType.VarChar, ParameterDirection.ReturnValue));
                cmd.Parameters.Add("p_code", scan);
                cmd.Parameters.Add("p_loc_id", zoneId);
                cmd.Parameters.Add("p_num_seq", OracleDbType.Number, ParameterDirection.Output);
                cmd.Parameters.Add("p_pattern", OracleDbType.Clob, ParameterDirection.Output);
                cmd.Parameters.Add("p_result", OracleDbType.Array, "TO_COD");
                cmd.Parameters.Add("p_commentaire", OracleDbType.Clob, ParameterDirection.Output);

                cmd.ExecuteNonQuery();

                if (cmd.Parameters["ReturnValue"].Value.Equals("TRUE")) {
                    ret = new PatternEntity(scan, (String)cmd.Parameters["p_pattern"].Value, (Decimal) cmd.Parameters["p_num_seq"].Value);
                }

So first, why I have to use an OracleDbType.Clob for my VARCHAR2 parameters ? If I try with OracleDbType.VarChar ori with OracleDbType.NVarChar I have the following error :
ORA-06502: PL/SQL : numeric or value error : character string buffer too small

It's not a big deal since it works with OracleDbType.Clob but I just wanted to know why.

And secondly, how can I read my parameter p_result of type TO_COD which is a a table of an Oracle structure ?
Kineas
 
Posts: 10
Joined: Mon 22 Apr 2013 14:46

Re: Oracle Structure in OUT parameter of a stored function

Postby Kineas » Wed 15 May 2013 13:05

It's me again !

I find my reponse alone for why I can't use OracleDbType.VarChar and it was simply because I had not specify the size of the parameter !
I change like this and it works :
Code: Select all
cmd.Parameters.Add("p_pattern", OracleDbType.VarChar, 30, "", ParameterDirection.Output);
...
cmd.Parameters.Add("p_commentaire", OracleDbType.VarChar, 100, "", ParameterDirection.Output);


Now, for the difficult part, I have made a lot of tests but I don't succeed in getting my table.
Here is my last test :
Code: Select all
OracleParameter param = new OracleParameter("p_result", OracleDbType.Table, ParameterDirection.Output);
param.ObjectTypeName = "TO_COD";
cmd.Parameters.Add(param);

So I try to use OracleDbType.Table. I really thought it will work but I got an expection :
System.PlatformNotSupportedException at System.Text.Encoding.GetDataItem()
...

I have not mentionned it yet but my application is deploy on a Windows Mobile environment.
Is it why it doesn't work ?

By the way, I find an almost similar post to my issue here :
http://forums.devart.com/viewtopic.php?f=1&t=22243&p=74224&hilit=OracleDbType.Table#p74224
Except it's for an IN parameter and not an OUT parameter !
Kineas
 
Posts: 10
Joined: Mon 22 Apr 2013 14:46

Re: Oracle Structure in OUT parameter of a stored function

Postby Pinturiccio » Mon 20 May 2013 08:22

We could not reproduce the issue. In our environment it works fine.

In your replies, you use two ways of declaring the "p_result" parameter:
1.
Code: Select all
cmd.Parameters.Add("p_result", OracleDbType.Array, "TO_COD");

2.
Code: Select all
OracleParameter param = new OracleParameter("p_result", OracleDbType.Table, ParameterDirection.Output);
param.ObjectTypeName = "TO_COD";
cmd.Parameters.Add(param);

The second way is the right one.

Kineas wrote:System.PlatformNotSupportedException at System.Text.Encoding.GetDataItem()

Could you please provide the following information?
1) the exact version of dotConnect for Oracle Mobile edition;
2) the stack trace of the exception;
Pinturiccio
Devart Team
 
Posts: 1866
Joined: Wed 02 Nov 2011 09:44

Re: Oracle Structure in OUT parameter of a stored function

Postby Kineas » Tue 21 May 2013 07:45

Hi,

First thanks for your answer.

1) I use the version 7.7.224 of dotConnect for Oracle Mobile edition

2) Here is the complete trace :
Code: Select all
System.PlatformNotSupportedException: PlatformNotSupportedException
   at System.Text.Encoding.GetDataItem()
   at System.Text.CodePageEncoding..ctor(Int32 codepage)
   at System.Text.Encoding.GetEncoding(Int32 codepage)
   at Devart.Data.Oracle.bg.m()
   at Devart.Data.Oracle.o.f(Byte[] A_0, Int32 A_1, Boolean A_2, bz A_3)
   at Devart.Data.Oracle.bn.q(OracleAttribute A_0)
   at Devart.Data.Oracle.bn.g()
   at Devart.Data.Oracle.bn.b(bi A_0)
   at Devart.Data.Oracle.bn.q(OracleAttribute A_0)
   at Devart.Data.Oracle.ak.x(OracleArray A_0)
   at Devart.Data.Oracle.ak.g()
   at Devart.Data.Oracle.bn.b(bi A_0)
   at Devart.Data.Oracle.bn.y(Byte[] A_0)
   at Devart.Data.Oracle.s.f(Byte[] A_0, Int32 A_1, Boolean A_2)
   at Devart.Data.Oracle.s.bv(Byte[] A_0, Int32 A_1, Boolean A_2, ay A_3)
   at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Type& A_2, Object& A_3, Byte[] A_4, Hashtable A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, Boolean A_11, OracleCommand A_12, ParameterDirection A_13, r A_14, OracleType A_15)
   at Devart.Data.Oracle.OracleParameter.g(a9& A_0, Boolean A_1, Boolean A_2, OracleCommand A_3, Byte[] A_4, Hashtable A_5, r A_6)
   at Devart.Data.Oracle.OracleCommand.v(OracleParameterCollection A_0, a9[] A_1, y A_2, r A_3)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
   at IronGun.dao.CommonDao.recupPattern(OracleConnection oConnexion, String scan, Decimal zoneId)
   at IronGun.forms.LoginControl.OnKeyDown(Object sender, KeyEventArgs e)
   at System.Windows.Forms.Control.OnKeyDown(KeyEventArgs e)
   at System.Windows.Forms.Control.WnProc(WM wm, Int32 wParam, Int32 lParam)
   at System.Windows.Forms.Control._InternalWnProc(WM wm, Int32 wParam, Int32 lParam)
   at Microsoft.AGL.Forms.EVL.EnterMainLoop(IntPtr hwnMain)
   at System.Windows.Forms.Application.Run(Form fm)
   at IronGun.Program.Main()
Kineas
 
Posts: 10
Joined: Mon 22 Apr 2013 14:46

Re: Oracle Structure in OUT parameter of a stored function

Postby Pinturiccio » Thu 23 May 2013 14:51

According to the stack trace you have provided, the issue is connected with encodings.

Try adding the 'Unicode=true;' parameter to your connection string. If using 'Unicode=true;' does not help, please provide the following information:
1. the NLS_LANGUAGE, NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters of your Oracle server;
2. Oracle database version;
3. The device you work with;
4. The device encoding. You can get this information using the Encoding.Default.CodePage property.
Pinturiccio
Devart Team
 
Posts: 1866
Joined: Wed 02 Nov 2011 09:44

Re: Oracle Structure in OUT parameter of a stored function

Postby Kineas » Tue 28 May 2013 08:36

Thanks for your answer.

I try to add the 'Unicode=true;' parameter in my connection string but it didn't change anything.

So here are the informations you ask.

Code: Select all
NLS_LANGUAGE            AMERICAN
NLS_CHARACTERSET        WE8ISO8859P1
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_RDBMS_VERSION       10.2.0.4.0


The device I use is a LXE TECTON MX7 (specifications here and here)
Part Number : MX7T1D1B1A0ET4D (802.11a/b/g / BT / 32-key numeric / Lorax / 256MB RAM x 256MB Flash / CE 6.0 / ET)

And the device encoding is 1252 !

Hope this will help to solve the problem !
Kineas
 
Posts: 10
Joined: Mon 22 Apr 2013 14:46

Re: Oracle Structure in OUT parameter of a stored function

Postby Kineas » Wed 29 May 2013 07:32

We just buy the license and now we have the 7.7.242 version.
But it didn't change anything.
Kineas
 
Posts: 10
Joined: Mon 22 Apr 2013 14:46

Re: Oracle Structure in OUT parameter of a stored function

Postby Pinturiccio » Thu 30 May 2013 10:24

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1866
Joined: Wed 02 Nov 2011 09:44

Re: Oracle Structure in OUT parameter of a stored function

Postby Kineas » Fri 14 Jun 2013 09:32

Hi,
Have you any news about this issue ?
Kineas
 
Posts: 10
Joined: Mon 22 Apr 2013 14:46

Re: Oracle Structure in OUT parameter of a stored function

Postby Pinturiccio » Wed 19 Jun 2013 14:41

The issue is caused by Oracle’s behavior. Oracle returns object text fields only in the server encoding. If Oracle allows setting encoding for text fields of the table, view or text parameters, then it does not allow changing encoding for object text fields. As a result, text fields are re-encoded on your device from server encoding to device encoding. But there is no server encoding on your device and thus an error occurs.

You can use one of the following workarounds:
1. do not use objects, replace them with something;
2. replace all fields having the VARCHAR2 type with fields having the NVARCHAR2 type in the object;
3. do not use servers with encoding that is not supported by the device. E.g. The device works fine with a server that has the "NLS_CHARACTERSET=CL8MSWIN1251" encoding.
Pinturiccio
Devart Team
 
Posts: 1866
Joined: Wed 02 Nov 2011 09:44

Re: Oracle Structure in OUT parameter of a stored function

Postby arronlee » Tue 17 Jun 2014 09:42

Kineas wrote:Hi,
Have you any news about this issue ?

Hi, Kineas.
As for me, I am testing the related
java code 128 generation projects these days. Do you have any ideas about it? Or any good suggestion? I am totally a green hand on barcode generating field. Any suggestion will be appreciated. Thanks in advance.


Best regards,
Arron
arronlee
 
Posts: 1
Joined: Tue 17 Jun 2014 09:39

Re: Oracle Structure in OUT parameter of a stored function

Postby jackmay » Fri 21 Nov 2014 03:14

arronlee wrote:
Kineas wrote:Hi,
Have you any news about this issue ?

Hi, Kineas.
As for me, I am testing the related
java code 128 generation projects these days. Do you have any ideas about it? Or any good suggestion? I am totally a green hand on barcode generating field. Any suggestion will be appreciated. Thanks in advance.


Best regards,
Arron

Hi,are you an expert in barcode and Oracle?Recently i want to print barcode from Oracle,but i dont know where to start,can you give some advice?
jackmay
 
Posts: 1
Joined: Fri 21 Nov 2014 03:02


Return to dotConnect for Oracle