tOracleSp - calling an Oracle Package

[Anonymous]

Hi,
I need to run the following package from Talend:
BEGIN
IO_ERRMSG := NULL;
IO_ERRNO := NULL;
I_CATEGORY_ID := 129;
I_CHARGE_ACC_SEG5 := '6500';
I_DEBUG_FLAG := 'Y';
APPS.VRTX_VICI_ORDER_INTF_PKG.MAIN_PROCESS ( IO_ERRMSG, IO_ERRNO, I_CATEGORY_ID, I_CHARGE_ACC_SEG5, I_DEBUG_FLAG );
COMMIT;
END;
What is the best way to do this?
I tried the following, but am getting a lot of different errors, and it does not look like I am using a correct approach:
tOracleInput --> tMap --> tOracleSP
tOracleInput has select 1 from dual
in tMap I define the 5paramaters that are needed, and pass to tOracleSP.
The latest errors I am getting is "Parameter schema is different from query" on tOracleInput, and PLS-00221: 'VRTX_VICI_ORDER_INTF_PKG' is not a procedure or is undefined
ORA-06550: line 1, column 7:
What should I have in tOracleSP, in SP Name, just the oracle package name????
Anyway, please let me know how to get this to work.
Thanks,
Boris

[Anonymous]

Hello

What should I have in tOracleSP, in SP Name, just the oracle package name????

It should be packageName.procedureName. Here is an example:
create or replace package person_package is
procedure add_person(id number, name varchar2, age number);
end person_package;
create or replace
package body person_package is
procedure add_person(id number, name varchar2, age number)
is
begin
insert into person values(id,name,age);
end;
end person_package;
Best regards
Shong

[Anonymous]

Shong,
Thanks, that worked.
The only thing is: My proc returns a 'Yes' or 'No' value based on Input parameters, and no matter what I put in for default value in tOracleInput and then sync columns, I keep getting the same value for my out parameter.
When I run the proc manually it works fine.
Is there something else I need to change in order to have it return different values based on what I pass as input?
Thanks,
Boris

[Anonymous]

Hello

and no matter what I put in for default value in tOracleInput and then sync columns, I keep getting the same value for my out parameter.

Add a new column on tOracleSP component and set it as out parameter.
Best regards
Shong

[Anonymous]

Hello,
I am using tOracleSP to execute the stored procedure with the required parameters below:

create or replace PACKAGE BODY INSERTREF_PKG
AS
PROCEDURE INSERTREF
(
	var1		IN VARCHAR2,
 	var2   	IN TIMESTAMP,
 	var3 		IN TABLEAU
)
........

where TABLEAU is an array of String.
Can you please tell me how to pass array parameter to a strore procedure?
Regards.
Jruf

[Anonymous]

Hi Jruf
Define the column type as object to map the complex type of SP parameters, I upload some screenshots to show how to pass a array type as input parameter.
Let me know if it works.
Shong

[Anonymous]

Hi Shong,
It's not working yet. I have got an error. these are the screeshots.
Regards
Jruf

[Anonymous]

Hi
I do not speak French, can you translate the error message to English? TABLEAU is custom type defined in your database?
Shong

[Anonymous]

HI,
Tableau is ARRAY in english version. it is the built-in custom type.
java.Sql.Exception :Invalid column type
Jruf

[Anonymous]

I have the same problem, there is a solution for this problem?

THX