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;
2 REPLIES
Four Stars

Re: tOracleSP returning a RECORD TYPE

I am looking for an example on how to read RECORD type variables in talend
Two 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.