Devart Forums

Hello,

I have a question concerning using plsql-tables in stored procedures as output-paremeters in my oracle packages

When executing with dotconnect 5.35.79.0 (non-direct mode) I got the message:

OCI-22303: type "user_test"."PCK_XXX.P_XXX_TABLE" not found

When executing with dotconnect 5.35.79.0 (direct mode) I got the message about unsupported network datatype (which I believe is indeed a remark on the direct-mode). So this is normal ?


When executing my stored procedure in Oracle SQL Developer, it's is working. So my procedure/package is OK.


Does anyone know what I do wrong, or why it is not working. Why do I get the type-not-found error (I'm connecting with the owner-account). Is there some example-code available which works with plsql-tables with multicolumns and as output-parameter.

PS: I got the same errors when trying to execute the procedure directly from with vs.net 2008 with OraDeveloperTools 2.55.148


Maybe some extra info

My oracle server is 10.2.0.4 64bit
My local client is version 8 --> this because we need acces also to older 7.3.4. Could the problem be solved with a oracle 10 client (which cannnot sdaly be installed next to oracle8 client)



Fred







This is my package code (simplified, and only relevant code, so not 100% complete)


PACKAGE user_test.PCK_XXX AS

TYPE P_XXX_TABLE IS TABLE OF user_test.XXX%ROWTYPE;

PROCEDURE SELECT_DETAILS (
p_Thickness OUT NUMBER,
p_ResultSet OUT user_test.PCK_XXX.P_XXX_TABLE
);

END PCK_XXX;


PACKAGE BODY user_test.PCK_XXX AS

PROCEDURE SELECT_DETAILS (
p_Thickness OUT NUMBER,
p_ResultSet OUT user_test.PCK_XXX.P_XXX_TABLE
)
AS
BEGIN

-- Normal PL-SQL code to create cursor: tmp_p_ResultSet
-- tmp_P_rec is record of rowtype of table to fetch

LOOP
FETCH tmp_p_ResultSet INTO tmp_P_rec;
EXIT WHEN tmp_p_ResultSet%NOTFOUND OR tmp_p_ResultSet%NOTFOUND IS NULL;

-- Add to table
tmp_Total := tmp_Total + 1;
p_ResultSet.EXTEND;

p_ResultSet(tmp_Total).FIELD1 := tmp_P_rec.FIELD1;
p_ResultSet(tmp_Total).FIELD2 := tmp_P_rec.FIELD2;

-- MORE different FIELDs

END LOOP;
CLOSE tmp_p_ResultSet;


END SELECT_DETAILS;

END PCK_XXX;

This is the expected behaviour.

In the Direct mode, you get an exception as Oracle objects are not supported.

In the OCI mode, the problem is that the type defined inside the package cannot be used outside. In particular, this means that you can use the table returned by SELECT_DETAILS inside PCK_XXX only.

To resolve the situation, you may define the custom type globally.

StanislavK wrote:This is the expected behaviour.

In the Direct mode, you get an exception as Oracle objects are not supported.

In the OCI mode, the problem is that the type defined inside the package cannot be used outside. In particular, this means that you can use the table returned by SELECT_DETAILS inside PCK_XXX only.

To resolve the situation, you may define the custom type globally.

OK,

thx for the info.

I modified the type-declarations to public/global types. And indeed everything is working. (tables as input and output parameters)

Just one other question.
When working with those plsql-tables in dotconnect (by way of oracleTable / OracleObject) --> there is no type-checking at development for attributes/fields...

Is there a way to "generate" some c# class which is strong-typed based an can be used for the DB-type.

Just like some proxy class which is generated specially by a dotconnect wizard when choosing the Db-type. This could make development and using the pl-sqltables a lot easier + bullet-proof if we can use some type-based objects...

Maybe a possibility for future releases if not yet available.

thx
Fred

Please try Typed Oracle Objects Wizard: Tools -> Oracle -> Oracle Objects Wizard. It allows generating C# classes representing user-defined types. Please specify whether this is the functionality you need.

StanislavK wrote:Please try Typed Oracle Objects Wizard: Tools -> Oracle -> Oracle Objects Wizard. It allows generating C# classes representing user-defined types. Please specify whether this is the functionality you need.

AHA,

very interesting features.

Didn't noticed yet that they exists. Seems there is also a full package code-generator...


I tried the wizard, but the wizard finds only my custom object-type, and NOT my custom table-type. Or is this the normal behaviour ?


Nevetheless,
great thank for the wizard-tips !

This is the expected behaviour: Object Wizard supports object types only.