Topic
6 replies Latest Post - ‏2014-04-03T15:04:36Z by hello2windows
onevedrov
onevedrov
7 Posts
ACCEPTED ANSWER

Pinned topic PL/SQL in Oracle connector

‏2012-11-14T10:42:57Z |
Hello,

I'm trying to figure out how to pull data from oracle database using PL/SQL. We are not allowed to create any objects in the database. I'm trying to find the way to consolidate data using PL/SQL. In Oracle Connector stage I set read mode to PL/SQL and defined following code in PL/SQL block:
DECLARE
V_EMNO INT;
V_EMNM VARCHAR(10);
v_emp_refcur SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
OPEN v_emp_refcur FOR SELECT EMNO empno, EMNM ename FROM EMP ;
LOOP
FETCH v_emp_refcur INTO V_EMNO, V_EMNM;
EXIT WHEN v_emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_EMNO || ' ' || V_EMNM);
END LOOP;
CLOSE v_emp_refcur;
END;

I set my output to the seq file and I'm getting only one record with values 0,0. I know i have more records, because in SQL developer it get all my rows (100). If some one can direct me how this stage works with PL/SQL.

Thanks in advance,
Oleg
Updated on 2012-11-20T12:45:22Z at 2012-11-20T12:45:22Z by RobertDickson
  • RobertDickson
    RobertDickson
    33 Posts
    ACCEPTED ANSWER

    Re: PL/SQL in Oracle connector

    ‏2012-11-16T17:07:49Z  in response to onevedrov
    Hi,

    Why don't you just use DataStage to extract the data (as it was designed) using the Oracle Stage? You won't have to write PL/SQL...

    Regards,
    Robert
    • onevedrov
      onevedrov
      7 Posts
      ACCEPTED ANSWER

      Re: PL/SQL in Oracle connector

      ‏2012-11-18T13:03:32Z  in response to RobertDickson
      Because too many tables involved and copiyng all the tables is not efficient. Alternatively we have to copy all the parameters into parameter sets and then build final SQL. I'm trying to find the way to have it in PL/SQL, since this option is available.
      • RobertDickson
        RobertDickson
        33 Posts
        ACCEPTED ANSWER

        Re: PL/SQL in Oracle connector

        ‏2012-11-18T13:28:48Z  in response to onevedrov
        Hi,

        Sorry - I only saw one table in your example. How many tables do you have, and what is your target? Your PL/SQL is dead simple, and would be much easier and scaleable using parameter sets.

        Regards,
        Robert
        • onevedrov
          onevedrov
          7 Posts
          ACCEPTED ANSWER

          Re: PL/SQL in Oracle connector

          ‏2012-11-19T08:15:28Z  in response to RobertDickson
          Robert,

          the number of tables will depend on the job. I'm working with multi-tenant database, where each tenant has its own set of tables. For Sales Orders the table will be <TENANTID>_SLORD. At the same time some of the master data tables are common. For example Products will be COMMOM_PROD. There is a repository in the transactional database, which tells me which tables are specific to tenant and which are common. There are different parameters, which I have to consider later, like language, time zone. As I mentioned before I can do all this via Parameter Sets, but I would like to explore PL/SQL, just cannot figure out how it works.

          Thanks in advance,
          Oleg
  • RobertDickson
    RobertDickson
    33 Posts
    ACCEPTED ANSWER

    Re: PL/SQL in Oracle connector

    ‏2012-11-20T12:45:22Z  in response to onevedrov
    Hi,

    It looks like it should work, but one thought was to only output data - no header or '----'. Also, you may want to output two columns instead of one concatenated column. This may prevent you from trying to split the data up later.

    Regards,
    Robert
  • hello2windows
    hello2windows
    1 Post
    ACCEPTED ANSWER

    Re: PL/SQL in Oracle connector

    ‏2014-04-03T15:04:36Z  in response to onevedrov

    Hi,

    By any chance someone have solution for this issue ? I am also facing the same issue.