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:
OPEN v_emp_refcur FOR SELECT EMNO empno, EMNM ename FROM EMP ;
FETCH v_emp_refcur INTO V_EMNO, V_EMNM;
EXIT WHEN v_emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_EMNO || ' ' || V_EMNM);
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,
This topic has been locked.
6 replies Latest Post - 2014-04-03T15:04:36Z by hello2windows
Pinned topic PL/SQL in Oracle connector
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-11-20T12:45:22Z at 2012-11-20T12:45:22Z by RobertDickson
Re: PL/SQL in Oracle connector2012-11-18T13:03:32Z in response to RobertDicksonBecause 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.
Re: PL/SQL in Oracle connector2012-11-18T13:28:48Z in response to onevedrovHi,
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.
Re: PL/SQL in Oracle connector2012-11-19T08:15:28Z in response to RobertDicksonRobert,
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,
Re: PL/SQL in Oracle connector2012-11-20T12:45:22Z in response to onevedrovHi,
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.