Originally posted by: SystemAdmin

Hello!

I built a stored procedure in an Oracle database. Among others, this procedure has a parameter whose type is user-defined:

CREATE PROCEDURE "Track" (
p_id INTEGER,
p_list user_list_type,
...

user_list_type is a user-defined collection type of another user-defined object type. Something like

CREATE TYPE "user_item_type" AS OBJECT (.....);
CREATE TYPE "user_list_type" AS TABLE OF user_item_type;

The types and the procedure are owned by user Anne. User John has to call this procedure. Permissions are fine. Using SQL*Plus, everything works.

Now to the problem:
Generating a type tree in Database Interface Designer only works when logging on as Anne. Logging on with John results in a crippled type tree, missing the part for parameter p_list. No hint to the error, no message in the trace file.

When John tries to call the procedure in the output card of a mapping, he fails with the follwing message in the trace file:

COLUMN 2 (P_LIST) type is ADT
Error returned by OCIDescribeAny, errcode=4043
ORA-04043: object user_list_type does not exist.
...
Returned status: (-1097) Failed to create an object.

Again, logging on as Anne, everything works fine.

My guess: WTX doesn't get right the schema of the type. The procedure is always referred to as "Anne.Track", but when retrieving the meta data on the procedure, WTX seems to lose the schema "Anne".

What do you think? Did I miss some configuration?

Thank you in advance! I appreciate any help, as this problem has bothered me for hours.

Message was edited by: karatan

Message was edited by: karatan
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

Try to add connect string name in the adapter command list and try..
I am notsure it works but once i resolved a database issue with that.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Hello smith1,

thanks for your reply.

I already have the command "CONNECT" with the database name in my adapter command list.
My command list looks like this:

-DT %db_type%
-C %db_connect%
-US %db_user%
-PW %db_pw%
-PR ANNE.TRACK
-TE+ ./trace/db_track.log

With db_connect containing the database name "MY_DB".
karatan.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: Kepa_Screen

Try to use schema name of the owner always.

"select * from ANNE.table_name"

if you are using a user-created type, try to use the owner name of the object

ANNE.p_list_user
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: janhess

I believe this is a permissions issue with the Schema. You should not use qualidfied names as the user ANNE probably won't exist in the production environment. You need to get your DBA to give you the correct permission.
However, if you use a package in Oracle, this only works with a qualified name and so should be avoided.

Message was edited by: janhess
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

as suggested already this is a d/b schema permissions issue.
get ur sysdba to grant the same user permissions to John as that of Anne.

Hopefully,this should then solve the issue.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Thank you for your contributions!

But I don't think this is a permission issue.

When I log on as JOHN with SQL*Plus and call the procedure owned by ANNE (of course, with fully qualified names) everything works as it is supposed to work.

But calling the procedure fails with the Oracle Database Adapter.

Thanks again,
karatan
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Karatan,

Were you ever able to get this to work? I'm trying to do the same thing, having a stored procedure with a user defined type as one of the parameters. I'm just using one schema owner for the whole process to avoid the permissions issue, but WTX still doesn't seem to be able to properly generate the type tree for either TYPE or PACKAGE definitions.

According to what I see in Oracle's documentation, the way to get a result set out of a stored procedure is to use a ref cursor that is defined in a PACKAGE. But I can't get the Database Interface Designer to understand a REF CURSOR type in Oracle.

Shouldn't this be part of the Oracle adapter functionality?
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: Kepa_Screen

You can use Database Interface Designer, create a "query" with that procedure.


The query would be something like


call itfcoladm.PK_ITF_BATCH.P_OBT_FICH_ALL_DEST(#COD_INT#, #COD_APL#, #TIPO_MENSAJE#, ?, ?)


and named P_OBT_FICH_ALL_DEST at mdq file definition ( query )


? are output parameters


Then, at the input card, use adapter database, and use comand:


-MDQ %mdq_file% -DBNAME INTERF -QUERY P_OBT_FICH_ALL_DEST -V COD_INT = 1019 -V COD_APL = 154 -V TIPO_MENSAJE = 'S'




where


%mdq_file% would be your mdq file ( with path )

INTERF would be your db name

P_OBT_FICH_ALL_DEST would be your procedure name at MDQ file ( query )

-V xxxx would be each input parameter. ( variables )


Output parameters from procedure must match type definition for the input card.

Message was edited by: Kepa
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender