ORACLE PLSQL - Query data in a package with the result of a table column-CodePudding

I have 1 table with 500k records records and for each record in the table I would like to query an oracle package and return the rows from this query. How can I do this with PL SQL ORACLE?

I tried to do it here:

declare
  cursor c_t is select COLUM_TABLE from SCHEMA.COMPANY;
  szSql varchar2(2048);
begin
  for rec in c_t loop 
    szSql := 'SELECT * FROM SCHEMA.PKG_COMPANY.GET_DATA_COMPANY('||rec.COLUM_TABLE||')';
    dbms_output.put_line(szSql);
    execute immediate szSql;
  end loop;
end;

I would like to know how to return the data as a common query and if there is a more performant way to do it.

Could you help me with examples?

EDIT When I call the package, I get the following return:

This data is the result of a complex query that the package makes

ID_COMPANY | REGION | LATITUDE | LONGITUDE | DENSITY | COUNTRY | ROLE 
   1.         WEST.   -0110110.  -0110110.    22.       EUA.     SUBS

CodePudding user response：

how to return the data as a common query and if there is a more performant way to do it

How about a function that returns ref cursor? You'd just pass table name to it and get the result:

SQL> create or replace function f_test (par_table_name in varchar2)
  2    return sys_refcursor
  3  is
  4    l_rc sys_refcursor;
  5  begin
  6    open l_rc for 'select * from ' || dbms_assert.sql_object_name(par_table_name);
  7    return l_rc;
  8  end;
  9  /

Function created.

Let's test it:

SQL> select f_test('dept') from dual;

F_TEST('DEPT')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Another table:

SQL> select f_test('invoice') from dual;

F_TEST('INVOICE')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DATA_RUN_ FI INVOICE_ID INVOICE_
--------- -- ---------- --------
01-JUL-22 Q4      12345 Paid
01-JAN-22 Q1      12345 Not Paid
01-JUL-22 Q4      12678 Paid
01-JAN-22 Q1      12678 Not Paid


SQL>

As of your code: it is unclear what it does. There's some package and a function, but that's a black box for us as you didn't post it. Also, you're fetching values from the company table; what does it contain? Too many unknown things to debug your code.

CodePudding user response：

If SCHEMA.PKG_COMPANY.GET_DATA_COMPANY() is a function and return a 'select' query like this:

select x,y,...,z from table where ....

then you can write the result into a target table:

cl scr
set SERVEROUTPUT ON
declare
  cursor c_t is select COLUM_TABLE from SCHEMA.COMPANY;
  szSql varchar2(3000);
begin
  for rec in c_t loop 
    szSql := 'insert into tbl_target  '||SCHEMA.PKG_COMPANY.GET_DATA_COMPANY(rec.COLUM_TABLE)||' ';
    dbms_output.put_line(szSql);
    execute immediate szSql;
    commit;
  end loop;
end;

in this manner you execute s statement like bellow and insert the result in tbl_target:

insert into tbl_target  select x,y,...,z from table where ....

I can not write exact code because SCHEMA.PKG_COMPANY.GET_DATA_COMPANY() is not defined for me.