Four Stars

setup a job that reads RECORD TYPE variable from a Oracle Stored Procedure

I am looking for an example to setup a job that reads RECORD TYPE variable from a Oracle Stored Procedure. my stored procedure looks something like this. I will really appreciate any help.

 

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;
  • Data Integration
7 REPLIES
Community Manager

Re: setup a job that reads RECORD TYPE variable from a Oracle Stored Procedure

Hi mhyder
Please take a look at this KB article about how to call a stored procedure or function in a Job. In your case, you need to define one column with Object type on the schema of tOracleSP, and select Type as 'Record SET' in the Parameter list, use a tParseRecordSet component to parse the record set after tOracleSP.
https://help.talend.com/pages/viewpage.action?pageId=190513258
let me know if it works.

Regards
Shong

----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: setup a job that reads RECORD TYPE variable from a Oracle Stored Procedure

Shong, 
Thank you for your reply. I pretty much followed the steps you detail. but i am still missing something.

How do i upload screen shots to this discussion ?

Community Manager

Re: setup a job that reads RECORD TYPE variable from a Oracle Stored Procedure

Hi
You can insert images from your computer (this uploads the image to your gallery), from your image gallery (if the image has been approved), or from another location on the web.

To insert an image in a post:
1. Start a new post.
2. Click Insert Image in the editor tool bar.
3. Choose one of the image source options and follow the on-screen instructions.

Regards
Shong

----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: setup a job that reads RECORD TYPE variable from a Oracle Stored Procedure

this is the complete work flow and as you can see tParseRecordSet is erroring. 

 

job.jpgjob-params.jpgtoraclesp.jpgtparserecordset.jpg

Community Manager

Re: setup a job that reads RECORD TYPE variable from a Oracle Stored Procedure

Hi
It seems you don't configure tParseRecordset correctly. Can you upload a screenshot of basic settings of tParseRecordset?

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: setup a job that reads RECORD TYPE variable from a Oracle Stored Procedure

Shong,

 

this is what i have for basic setting for tParseRecordSet

 

tparserecordset1.jpg

Two Stars

Re: setup a job that reads RECORD TYPE variable from a Oracle Stored Procedure

Can you please provide the correct link?
The given link is not working.