Reputation:
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).
Upvotes: 0
Views: 91
Reputation: 143163
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 ...").
Upvotes: 1