topic Re: setup a job that reads RECORD TYPE variable from a Oracle Stored Procedure in Talend Studio

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

Anonymous — Tue, 25 Apr 2017 23:03:46 GMT

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;

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

Anonymous — Wed, 26 Apr 2017 09:32:00 GMT

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

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

Anonymous — Wed, 26 Apr 2017 18:03:53 GMT

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 ?

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

Anonymous — Thu, 27 Apr 2017 06:11:50 GMT

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

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

Anonymous — Thu, 27 Apr 2017 16:08:29 GMT

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

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

Anonymous — Fri, 28 Apr 2017 04:44:43 GMT

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

Regards
Shong

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

Anonymous — Fri, 28 Apr 2017 17:01:00 GMT

Shong,

this is what i have for basic setting for tParseRecordSet

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

Anonymous — Wed, 12 Jul 2017 10:11:27 GMT

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