tOracleSP returning a RECORD TYPE

Four Stars

tOracleSP returning a RECORD TYPE

I have a Oracle stored procedure that returns a RECORD type and I am not sure how its configured using tOracleSP.

 

create or replace package testpkg as
    --
    type test_rec is record (
        pid               number,
        pname             varchar2(30)
    );
    --
    procedure main (
        p_id                in varchar2 := null,
        p_admin_status      in varchar2 := null,
        p_return_rec        out test_rec
    );
end;
/
create or replace package body testpkg as
    --
    procedure main (
        p_id                in varchar2 := null,
        p_admin_status      in varchar2 := null,
        p_return_rec        out test_rec
    )
    is
    begin
      --
      p_return_rec.pid   := 1;
      p_return_rec.pname := 'John Doe';
      --
    end;
    --
end;
Four Stars

Re: tOracleSP returning a RECORD TYPE

I am looking for an example on how to read RECORD type variables in talend
Four Stars

Re: tOracleSP returning a RECORD TYPE

I also have similar scenario. I have a function which returns multiple records. How do we need to capture these records & process further in talend?

 

Below is the function:


create type prcs_vars_obj is object (var varchar2(10), value varchar2(10));
create type prcs_vars_tab is table of prcs_vars_obj;


create or replace FUNCTION startJob_getPrcsVars_obj (
    ,p_RunDate          NUMBER)
RETURN prcs_vars_tab
IS  
    v_prcs_vars prcs_vars_tab := prcs_vars_tab();
    i integer := 0;
BEGIN  

for r in (
    
SELECT
   VAR
  ,to_char((((TO_NUMBER(TO_CHAR(VALUE, 'YYYY')) * 100) + (TO_NUMBER(TO_CHAR(VALUE, 'MM')))) * 100) + TO_NUMBER(TO_CHAR(VALUE, 'DD'))) AS VALUE
 FROM (
  SELECT
    CONCAT('DT-',  VAL)  AS VAR,
    to_date(to_char(p_RunDate), 'YYYYMMDD')+ (VAL*-1) AS VALUE
   FROM (
    SELECT 0 VAL FROM dual UNION ALL
    SELECT 1 VAL FROM dual UNION ALL
    SELECT 2 VAL FROM dual UNION ALL
    SELECT 3 VAL FROM dual UNION ALL
    SELECT 4 VAL FROM dual UNION ALL
    SELECT 5 VAL FROM dual UNION ALL
    SELECT 6 VAL FROM dual UNION ALL
    SELECT 7 VAL FROM dual
  ) A
) A
)
loop
    v_prcs_vars.extend;
    i := i+1;
    v_prcs_vars(i) := prcs_vars_obj(r.var, r.value);
end loop;
return v_prcs_vars;
END;

 

Appreciate quick response.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog