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.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.