Selecting Oracle object with collection of objects without bulk collect

Question

Is there a way to select/fill an Oracle Collection ("IS TABLE OF") in a normal select from jdbc i.e. no procedure with declared variable for bulk collect or anonymous block in Oracle12c?

The problems is selecting objects with collection of objects since I can't find a way to fill the collection and therefore it puts every object in a separate collection instead of collecting them in one (i.e., "a single-row query returned more than one row").

Long Story: Most of out services/apis have dynamic search parameters and then they are translated to SQL statements.

A java code does parse the limited "search/where", sanitize and parameterize them then select an objects from oracle, so in most of the time it execute much faster than oracle's hard parse, however i can't find a solution for filling objects with list of objects.

The current work around is calling procedures and leaving them to do bulk collect in declared objects then returning them, however because of the dynamic nature of the "search" clause most of them do EXECUTE IMMEDIATE which is quite heavy for CPU of the database.

Other solutions such as multiple queries per every row to fetch it's list or left join and fetch everything in single ResultSet just increase the time exponentially, since the fetches are for usually for huge number of columns, rows and nested lists :<

CREATE OR REPLACE TYPE SUB_TYPE_OBJ Force AS OBJECT
(
  SVAR1 VARCHAR2(100 CHAR);
  SVAR2 DATE,
  SVAR3 VARCHAR2(100 CHAR)
);

CREATE OR REPLACE TYPE SUB_TYPE_LST IS TABLE OF SUB_TYPE_OBJ;

CREATE OR REPLACE TYPE MAIN_TYPE_OBJ Force AS OBJECT
(
  MVAR1 VARCHAR2(100 CHAR),
  MVAR2 VARCHAR2(100 CHAR),
  MVAR3 SUB_TYPE_OBJ,
  MVAR4 SUB_TYPE_LST
);

SELECT MAIN_TYPE_OBJ (
    tab1.val1, 
    tab1.val2,
    (SELECT SUB_TYPE_OBJ( table2.val1, table2.val2, table2.val3) obj FROM table2 WHERE tab1.val1 = tab2.val1),
    (SUB_TYPE_LST ( (SELECT SUB_TYPE_OBJ( table3.val1, table3.val2, table3.val3) obj FROM table3 DYNAMIC_WHERE_WITH_LIMITED_FUNCTIONALITY)))
    /* if it return more than one row everything breaks :( */
), CNT
FROM (
SELECT table1.*, COUNT(*) OVER(table1.val1) AS CNT FROM table1
WHERE DYNAMIC_WHERE_WITH_LIMITED_FUNCTIONALITY
ORDER BY val1 ASC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY) tab1;

if table3 returns more than one row we get the "a single-row query returned more than one row" because I'm just creating a bunch of lists instead of one containing all objects, but I cant figure out how to do it;

Any ideas are appreciated, but please exclude DBMS_SQL or NoSQL for now :).

Answer 1

You have several options here. You can open your query as an explicit cursor and then FETCH...BULK COLLECT INTO an appropriate collection; you can use EXECUTE IMMEDIATE...BULK COLLECT INTO; or, as you say you don't want to hear, you can use DBMS_SQL.

To use EXECUTE IMMEDIATE...BULK COLLECT you'd use something like

CREATE TABLE DATA_TABLE(FIELD1         NUMBER,
                        FIELD2         VARCHAR2(100));

INSERT INTO DATA_TABLE (FIELD1, FIELD2)
  SELECT 1, 'ONE' FROM DUAL UNION ALL
  SELECT 1, 'TWO' FROM DUAL UNION ALL
  SELECT 2, 'THREE' FROM DUAL UNION ALL
  SELECT 2, 'FOUR' FROM DUAL UNION ALL
  SELECT 3, 'LAST' FROM DUAL;

DECLARE
  TYPE typeCol IS TABLE OF DATA_TABLE%ROWTYPE;
  colVals        typeCol;
  strField_name  VARCHAR2(30) := 'FIELD1';
  nField_val     NUMBER := 2;

  strQuery       VARCHAR2(4000);
BEGIN
  strQuery := 'SELECT * FROM DATA_TABLE WHERE ' || strField_name || '=' || nField_val;

  DBMS_OUTPUT.PUT_LINE(strQuery);

  EXECUTE IMMEDIATE strQuery BULK COLLECT INTO colVals;

  FOR i IN colVals.FIRST..colVals.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(i || ': FIELD1=' || colVals(i).FIELD1 || '  FIELD2=' || colVals(i).FIELD2);
  END LOOP;
END;
/

dbfiddle here

docs here (from 10.1 - better write-up than later versions IMO)

Answer 2

Actually EXECUTE IMMEDIATE have a using_clause which allows to pass bind variables

To adapt the example of @Bob simple adjust

strQuery := 'SELECT * FROM DATA_TABLE WHERE ' || strField_name || '= :x' ;

EXECUTE IMMEDIATE strQuery BULK COLLECT INTO colVals USING nField_val;

This produces a SQL statement with bind variable that is only soft parsed

 SELECT * FROM DATA_TABLE WHERE FIELD1= :x

Of course this approach has a limitation, the number of bind variables is static defined as a list in the EXECUT EIMMEDIATE statement, e.g. USING a,b,c,d,e

Two problems come in my mind. The first one are optional parameters, i.e. some queries search on name and birth_date some only on name

You can find lot of solutions with OR or NVL e.g. here or here, but the superior solution popularized by Tom Kyte used the 1=1 OR shortcut trick:

If the bind variable is used you generate it in the WHEREclause

 birth_date = :birth_date 

if is is not used you get rid of it generating following predicate

(1=1 or :birth_date  is NULL)

The effect is as follows 1) the number of bind variables remain fixed (even if :birth_date is not used) and 2) evaluating 1=1 as true will skip the rest of the predicate, i.e. the bind variable has no effect.

The second problem deals with the IN lists that can have a dynamic number of members; from 1 to 1000 on Oracle.

A benchmark here is the Hibernate solution, that counts the length of the IN list and generates the corresponding dynamic SQL, e.g. col1 in (?,?,?,?) for four mebmer IN list. You end with up to 1000 hard parsed SQLs (one for each lengt), but this is anyway a logarithmic improvement to the worst case of pasting the list in the dymanic SQL.

From the limitation described above you already knows, that this is not possible using EXECUTE IMMEDIATE (and you'll need to learn something new;), e.g. DBMS_SQL)

You may try a trick and limit the IN list with say 10 members and fill it with NULLs.

I.e. to pass 4 members you will bind following variables:

 col1 IN (1,2,3,4,null,null,null,null,null,null)

but note that I have no experiance if there will be a performance hit on long IN list (for short IN List this will work fine). Additionally this approach definitively fail for col1 NOT IN which return nothing if there are NULLs in the IN list.

Answer 3

Easy, use EXTEND in order to add new row into collection!

First of all create a local varabile (ex: one_row) that will point tot the structure of your collection after that:

SUB_TYPE_LST.extend;
                                one_row.SVAR1 := 'VALUE1';
                                one_row.SVAR2 := sysdate;
                                one_row.SVAR3 := 'value2';
                                SUB_TYPE_LST(1) := one_row; -- 1 - number of row in the table - you can put a variable which will be incremented inside a loop