0

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).
Improve this question
1
  • 1
    You might want to check this out: stackoverflow.com/questions/2715736/…. An associative array is what languages like Python call a dictionary, that is, you can use a character string a a key to look up the value. Commented Apr 27, 2021 at 15:30

1 Answer 1

Reset to default
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 ...").

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

4 Comments

Thanks! Is there a way I could get the content column for the refcursor option into a clob variable?
I'll maybe explain the big picture of what I'm trying to do here: I'm trying to create a table with the code for the procedures so I can then, instead of having the code inside the procedure, take it from that config table (execute immediate the column of that config table). Not sure if it's a good practice though.
It's a bad practice. You'd have to run everything as dynamic SQL which doesn't scale and is difficult to debug and maintain. I wouldn't do it if I were you.
Thanks. What do you mean by doesn't scale? Also, I thought execute immediate was only using dynamic SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.