topic Re: tOracleSp - calling an Oracle Package in Talend Studio

tOracleSp - calling an Oracle Package

Anonymous — Sat, 16 Nov 2024 13:30:26 GMT

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

Re: tOracleSp - calling an Oracle Package

Anonymous — Mon, 15 Mar 2010 03:31:06 GMT

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

Re: tOracleSp - calling an Oracle Package

Anonymous — Mon, 15 Mar 2010 16:13:52 GMT

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

Re: tOracleSp - calling an Oracle Package

Anonymous — Tue, 16 Mar 2010 03:13:51 GMT

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

Re: tOracleSp - calling an Oracle Package

Anonymous — Mon, 29 Jul 2013 09:54:16 GMT

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

Re: tOracleSp - calling an Oracle Package

Anonymous — Tue, 30 Jul 2013 11:48:22 GMT

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

Re: tOracleSp - calling an Oracle Package

Anonymous — Tue, 30 Jul 2013 13:42:45 GMT

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

Re: tOracleSp - calling an Oracle Package

Anonymous — Wed, 31 Jul 2013 09:13:42 GMT

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

Re: tOracleSp - calling an Oracle Package

Anonymous — Wed, 31 Jul 2013 09:22:32 GMT

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

Re: tOracleSp - calling an Oracle Package

Anonymous — Thu, 07 Nov 2019 16:00:43 GMT

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

THX

Re: tOracleSp - calling an Oracle Package

Anonymous — Fri, 08 Nov 2019 08:10:34 GMT

Hello @dmutu,

It's a quite old thread. Could you please give us a more bit information about your job design and problem? Screenshots will be preferred.

Best regards

Sabrina