Oracle Structure in OUT parameter of a stored function

[Kineas]

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]

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]

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? ... ble#p74224
Except it's for an IN parameter and not an OUT parameter !

[Pinturiccio]

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.

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;

[Kineas]

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()

[Pinturiccio]

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.

[Kineas]

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]

We just buy the license and now we have the 7.7.242 version.
But it didn't change anything.

[Pinturiccio]

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

[Kineas]

Hi,
Have you any news about this issue ?

[Pinturiccio]

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.

[arronlee]

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

[jackmay]

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?