Oracle SQL bulk variable collect

Question

I have the below config table:

ID_PROCEDURE	VSTR_NAME	VSTR_CONTENT
P1	A	C1
P1	B	C2
P1	C	C3
P1	D	C4
P1	E	C5
P1	F	C6
P1	G	C7

And I have the below method to get the VSTR_CONTENT values based on the id_procedure.

SET SERVEROUTPUT ON;

DECLARE
    config procedures_text_config%rowtype;
BEGIN
    SELECT
        *
    INTO config
    FROM
        procedures_text_config
    WHERE
        id_procedure = 'P1';

    dbms_output.put_line(config.vstr_content);
END;

However, I'd like a way of being able to extract vstr_content specifying id_procedure and vstr_name in the below format (or similar):

config.a.vstr_content (expected output:C1).

Answer 1

I'm not sure you can do what you want (at least, not in Oracle).

Usual way of getting something out of a table is to run a select statement or, if you want, a (stored) function. You pass parameters to it and get a result. That's how it works. You don't pass "config.a.vstr_content" (whatever it represents).

Here are several examples, see if any of these helps.

(Oh, yes - your code wouldn't even run, it would fail with the TOO_MANY_ROWS error).

Sample table:

SQL> select * From config;

ID_PROCEDURE    VSTR_NAME  VSTR_CONTENT
--------------- ---------- ------------
P1              A          C1
P1              B          C2
P1              C          C3

SQL>

---

A simple function, returning scalar variable; just like in your case, it might fail if more than a single row fulfills the WHERE condition(s).

SQL> create or replace function f_config
  2    (par_id_procedure in varchar2, par_name in varchar2)
  3  return config.vstr_content%type is
  4    retval config.vstr_content%type;
  5  begin
  6    select vstr_content
  7      into retval
  8      from config
  9      where (id_procedure = par_id_procedure or par_id_procedure is null)
 10        and (vstr_name = par_name or par_name is null);
 11    return retval;
 12  end;
 13  /

Function created.

Testing:

SQL> select f_config('P1', 'A') from dual;

F_CONFIG('P1','A')
--------------------------------------------------------------------------------
C1

SQL> select f_config('P1', null) from dual;
select f_config('P1', null) from dual
       *
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCOTT.F_CONFIG", line 6


SQL>

---

So, how about returning a refcursor instead?

SQL> create or replace function f_config
  2    (par_id_procedure in varchar2, par_name in varchar2)
  3  return sys_refcursor is
  4    retval sys_refcursor;
  5  begin
  6    open retval for
  7    select *
  8      from config
  9      where (id_procedure = par_id_procedure or par_id_procedure is null)
 10        and (vstr_name = par_name or par_name is null);
 11    return retval;
 12  end;
 13  /

Function created.

Testing:

SQL> select f_config('P1', 'A') from dual;

F_CONFIG('P1','A')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ID V VS
-- - --
P1 A C1
   
SQL> select f_config('P1', null) from dual;

F_CONFIG('P1',NULL)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ID V VS
-- - --
P1 A C1
P1 B C2
P1 C C3


SQL>

---

Or, return a collection:

SQL> create or replace type t_row as object
  2    (id_procedure varchar2(10),
  3     name varchar2(10),
  4     content varchar2(10));
  5  /

Type created.

SQL> create or replace type t_tab as table of t_row;
  2  /

Type created.

SQL> create or replace function f_config
  2    (par_id_procedure in varchar2, par_name in varchar2)
  3  return t_tab is
  4    retval t_tab;
  5  begin
  6    select t_row(id_procedure, vstr_name, vstr_content)
  7      bulk collect into retval
  8      from config
  9      where (id_procedure = par_id_procedure or par_id_procedure is null)
 10        and (vstr_name = par_name or par_name is null);
 11    return retval;
 12  end;
 13  /

Function created.

Testing:

SQL> select * From table(f_config('P1', 'A'));

ID_PROCEDU NAME       CONTENT
---------- ---------- ----------
P1         A          C1

SQL> select content From table(f_config('P1', 'A'));

CONTENT
----------
C1

SQL> select * from table(f_config('P1', null));

ID_PROCEDU NAME       CONTENT
---------- ---------- ----------
P1         A          C1
P1         B          C2
P1         C          C3

SQL>

---

If nothing of this helps, please, explain what exactly you meant by the last sentence you wrote ("however, I'd like a way ...").