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

Question

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

If I understand your question correctly, you want to print and execute a query like that: ``` SELECT * FROM SCHEMA.PKG_COMPANY.GET_DATA_COMPANY(COLUM_TABLE); ``` PL/SQL block can not show result of 'select' query. you can insert result of select query into a table. — Reza Davoudian
– Reza Davoudian, Commented Oct 15, 2022 at 8:41
good question, but the answer depends on SCHEMA.PKG_COMPANY.GET_DATA_COMPANY and the process it does. I will write the answer in a general case. please be patient. Lucas Travagin — Reza Davoudian
– Reza Davoudian, Commented Oct 15, 2022 at 11:15
Thanks! Returns a structured table, basically this package takes the ID_COMPANY and returns multiple selects and returns as a new table structure. — Lucas Travagin
– Lucas Travagin, Commented Oct 15, 2022 at 11:21

Littlefoot · Accepted Answer · 2022-10-15 09:02:59Z

1

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.

answered Oct 15, 2022 at 9:02

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lucas Travagin Over a year ago

select ID_COMPANY from SCHEMA.COMPANY - I want to get all id_company from this table, just one column. For each id returned, I want to return the data from this package, which has a search rule with multiple validations passing the ID_COMPANY. SELECT * FROM SCHEMA.PKG_COMPANY.GET_DATA_COMPANY(ID_COMPANY)

Lucas Travagin Over a year ago

SELECT * FROM SCHEMA.PKG_COMPANY.GET_DATA_COMPANY(SELECT ID_COMPANY FROM SCHEMA.COMPANY);

Littlefoot Over a year ago

Unfortunately, I still don't understand what you have and what you'd want to get as a result. Please, edit the question and add sample information and desired result (based on that sample data).

Lucas Travagin Over a year ago

Added callback details from package. Thanks

Reza Davoudian · Accepted Answer · 2022-10-15 11:28:58Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related