Home » SQL & PL/SQL » SQL & PL/SQL » Cast(Multiset()) (Oracle, 12.2.0.1.0, Linux)
| Cast(Multiset()) [message #687288] |
Wed, 15 February 2023 16:54  |
Duane
Messages: 523
Registered: December 2002
|
Senior Member |
|
|
So I have a simple Cast(Multiset()) statement but I need to figure out how to access the DATASET within the TEMPLATE column in PL/SQL.
I want to access the rows/columns in the TEMPLATE column so that I can send that information to another procedure (not shown).
Anyone know how to do that?
CREATE OR REPLACE type Template as object(template_text varchar2(100),
template_value varchar2(100));
CREATE OR REPLACE type Templates as table of Template;
procedure Student is
Students sys_refcursor;
begin
open Students for
select pidm,
first_name,
last_name,
cast(multiset(select '<STU_ID>',
'100'
from dual
union all
select '<FIRST_NAME>',
'Some Value'
from dual
union all
select '<LAST_NAME>',
'Some Value'
from dual) as templates) template
from (select 100 pidm,
'Elvis' first_name,
'Presley' last_name
from dual
union all
select 101,
'Lisa Marie',
'Presley'
from dual
union all
select 102,
'Jeff',
'Beck'
from dual);
Parse(Students);
end Student;
procedure Parse (RefCursor in out sys_refcursor) is
CursorID number;
CursorID2 number;
ColumnCount number;
ColumnCount2 number;
ColumnDescriptions dbms_sql.desc_tab;
ColumnValue sys.odcivarchar2list := sys.odcivarchar2list();
begin
CursorID := dbms_sql.to_cursor_number(RefCursor);
dbms_sql.describe_columns(CursorID, ColumnCount, ColumnDescriptions);
ColumnValue.extend(ColumnCount);
while dbms_sql.fetch_rows(CursorID) > 0
loop
for i in 1..ColumnCount
loop
if ColumnDescriptions(i).col_name = 'TEMPLATE'
then
dbms_output.put_line(ColumnDescriptions(i).col_name);
-- Access the Rows within TEMPLATE. How do I do that?
end if;
end loop;
end loop;
end Parse;
|
|
|
|
| Re: Cast(Multiset()) [message #687290 is a reply to message #687288] |
Wed, 15 February 2023 23:08  |
 |
Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I made a few changes to your parse procedure and marked them with comment lines along the left margin.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type Template as object(template_text varchar2(100),
2 template_value varchar2(100));
3 /
Type created.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type Templates as table of Template;
2 /
Type created.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE procedure Parse (RefCursor in out sys_refcursor) is
2
3 CursorID number;
4 CursorID2 number;
5 ColumnCount number;
6 ColumnCount2 number;
7 ColumnDescriptions dbms_sql.desc_tab;
8 ColumnValue sys.odcivarchar2list := sys.odcivarchar2list();
9 -- added line below
10 temps templates;
11
12
13 begin
14 CursorID := dbms_sql.to_cursor_number(RefCursor);
15 dbms_sql.describe_columns(CursorID, ColumnCount, ColumnDescriptions);
16 -- added line below
17 dbms_sql.define_column(CursorID, 4, temps);
18 ColumnValue.extend(ColumnCount);
19
20 while dbms_sql.fetch_rows(CursorID) > 0
21 loop
22 for i in 1..ColumnCount
23 loop
24 if ColumnDescriptions(i).col_name = 'TEMPLATE'
25 then
26 dbms_output.put_line(ColumnDescriptions(i).col_name);
27
28 -- Access the Rows within TEMPLATE. How do I do that?
29 -- notice that I added temps variable and dbms_sql.define_column earlier
30 -- added section below
31 dbms_sql.column_value (CursorID, 4, temps);
32 for r in
33 (select * from table (temps))
34 loop
35 dbms_output.put_line (r.template_text);
36 dbms_output.put_line (r.template_value);
37 end loop;
38 end if;
39 end loop;
40 end loop;
41
42 end Parse;
43 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE procedure Student is
2
3 Students sys_refcursor;
4
5 begin
6
7 open Students for
8 select pidm,
9 first_name,
10 last_name,
11 cast(multiset(select '<STU_ID>',
12 '100'
13 from dual
14
15 union all
16
17 select '<FIRST_NAME>',
18 'Some Value'
19 from dual
20
21 union all
22
23 select '<LAST_NAME>',
24 'Some Value'
25 from dual) as templates) template
26 from (select 100 pidm,
27 'Elvis' first_name,
28 'Presley' last_name
29 from dual
30
31 union all
32
33 select 101,
34 'Lisa Marie',
35 'Presley'
36 from dual
37
38 union all
39
40 select 102,
41 'Jeff',
42 'Beck'
43 from dual);
44
45 Parse(Students);
46
47 end Student;
48 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> EXEC student
TEMPLATE
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
TEMPLATE
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
TEMPLATE
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
PL/SQL procedure successfully completed.
|
|
|
|
| Re: Cast(Multiset()) [message #687292 is a reply to message #687290] |
Thu, 16 February 2023 07:41 |
Duane
Messages: 523
Registered: December 2002
|
Senior Member |
|
|
|
Thank you so much, Barbara. I figured it could be done but I was a little lost on the implementation. I started to wonder if I would need to switch to using a CURSOR Expression, which, I believe would accomplish the same thing. Not sure if one method is better than the other. Maybe you can comment on that and what method you prefer.
|
|
|
|
| Re: Cast(Multiset()) [message #687295 is a reply to message #687292] |
Thu, 16 February 2023 09:49 |
|
Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I find the cursor expression easier to write and work with. The DBMS_SQL seems like kind of an old-fashioned, outdated way to do things. However, I have not done any tests to see which is more efficient. Perhaps some others will comment on that. In the modification below, I have eliminated the external types, changed cast(multiset to cursor in the student procedure, and completely rewritten the parse procedure. It might help to know what the larger picture is, in terms of where the data comes from and what you do with it to evaluate if there is a better way. Perhaps others will comment on that as well.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE procedure Parse (RefCursor in out sys_refcursor)
2 is
3 v_pidm NUMBER;
4 v_first_name VARCHAR2(15);
5 v_last_name VARCHAR2(15);
6 v_cursor SYS_REFCURSOR;
7 TYPE template IS RECORD
8 (template_text VARCHAR2(100),
9 template_value VARCHAR2(100));
10 v_template template;
11 begin
12 LOOP
13 FETCH RefCursor INTO v_pidm, v_first_name, v_last_name, v_cursor;
14 EXIT WHEN RefCursor%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE (v_pidm);
16 DBMS_OUTPUT.PUT_LINE (v_first_name);
17 DBMS_OUTPUT.PUT_LINE (v_last_name);
18 LOOP
19 FETCH v_cursor INTO v_template;
20 EXIT WHEN v_cursor%NOTFOUND;
21 DBMS_OUTPUT.PUT_LINE (v_template.template_text);
22 DBMS_OUTPUT.PUT_LINE (v_template.template_value);
23 END LOOP;
24 CLOSE v_cursor;
25 END LOOP;
26 CLOSE RefCursor;
27 end Parse;
28 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE procedure Student is
2
3 Students sys_refcursor;
4
5 begin
6
7 open Students for
8 select pidm,
9 first_name,
10 last_name,
11 cursor(select '<STU_ID>',
12 '100'
13 from dual
14
15 union all
16
17 select '<FIRST_NAME>',
18 'Some Value'
19 from dual
20
21 union all
22
23 select '<LAST_NAME>',
24 'Some Value'
25 from dual)
26 from (select 100 pidm,
27 'Elvis' first_name,
28 'Presley' last_name
29 from dual
30
31 union all
32
33 select 101,
34 'Lisa Marie',
35 'Presley'
36 from dual
37
38 union all
39
40 select 102,
41 'Jeff',
42 'Beck'
43 from dual);
44
45 Parse(Students);
46
47 end Student;
48 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> EXEC student
100
Elvis
Presley
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
101
Lisa Marie
Presley
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
102
Jeff
Beck
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
PL/SQL procedure successfully completed.
|
|
|
|
| Re: Cast(Multiset()) [message #687297 is a reply to message #687295] |
Thu, 16 February 2023 12:31 |
Duane
Messages: 523
Registered: December 2002
|
Senior Member |
|
|
The Cursor Expression appears to have less code to do the same thing.
What am I attempting to do? Students are sent an email with Template values that need to be replaced in the body of the email text. That's what the < > codes are and the values that replace those codes. So, for each student, include in each row a column that contains the <code> and value that will need to be replaced in the body of the email that is sent to the student.
Hope that helps.
|
|
|
|
| Re: Cast(Multiset()) [message #687298 is a reply to message #687297] |
Thu, 16 February 2023 13:43 |
|
Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I am guessing that selecting from dual may be a simulation for selecting from a table and that dbms_output is just a simulation for the next step in your email process. If the two select statements have no relation and you need just the individual values that you get using dbms_output, then you can replace both procedures with just the one below. If the two select statements used in the implicit cursor for loops are related, then you can add a where clause.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE student
2 AS
3 BEGIN
4 FOR s IN
5 (select 100 pidm,
6 'Elvis' first_name,
7 'Presley' last_name
8 from dual
9
10 union all
11
12 select 101,
13 'Lisa Marie',
14 'Presley'
15 from dual
16
17 union all
18
19 select 102,
20 'Jeff',
21 'Beck'
22 from dual)
23 LOOP
24 DBMS_OUTPUT.PUT_LINE (s.pidm);
25 DBMS_OUTPUT.PUT_LINE (s.first_name);
26 DBMS_OUTPUT.PUT_LINE (s.last_name);
27 FOR t IN
28 (select '<STU_ID>' template_text,
29 '100' template_value
30 from dual
31
32 union all
33
34 select '<FIRST_NAME>',
35 'Some Value'
36 from dual
37
38 union all
39
40 select '<LAST_NAME>',
41 'Some Value'
42 from dual)
43 LOOP
44 DBMS_OUTPUT.PUT_LINE (t.template_text);
45 DBMS_OUTPUT.PUT_LINE (t.template_value);
46 END LOOP;
47 END LOOP;
48 END student;
49 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> EXEC student
100
Elvis
Presley
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
101
Lisa Marie
Presley
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
102
Jeff
Beck
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
PL/SQL procedure successfully completed.
|
|
|
|
| Re: Cast(Multiset()) [message #687299 is a reply to message #687297] |
Thu, 16 February 2023 13:55 |
Duane
Messages: 523
Registered: December 2002
|
Senior Member |
|
|
Is there a way to only fetch the v_cursor column in your example? My example was only an example but in reality I won't know what columns are being returned except for the last column which will contain the <code>/value records. I just want to process whatever is in the last column. Queries that will be sending an email will always have that last column of <code</value.
open Students for
select pidm,
first_name,
last_name,
cursor(select '<STU_ID>',
'100'
from dual
.
.
.
.
From this:
FETCH RefCursor INTO v_pidm, v_first_name, v_last_name, v_cursor;
.
.
To this:
FETCH RefCursor INTO v_cursor;
.
.
[Updated on: Thu, 16 February 2023 13:56] Report message to a moderator |
|
|
|
|
|
| Re: Cast(Multiset()) [message #687301 is a reply to message #687300] |
Thu, 16 February 2023 14:52 |
Duane
Messages: 523
Registered: December 2002
|
Senior Member |
|
|
|
Thanks Barbara. I'll just stick with DBMS_SQL, then. The problem I have is providing examples with real data. That's frowned upon in an open public forum so that's why I come up with the data I do. I show what I can without getting myself in trouble.
|
|
|
|
| Re: Cast(Multiset()) [message #687302 is a reply to message #687301] |
Thu, 16 February 2023 15:40 |
|
Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I just had another thought. My initial response assumed that the template is always the fourth column. If it may be in any position, then you will need to loop through the columncount checking the column_name to get the position prior to using dbms_sql.define_column. You can then store that number and use it later in dbms_sql.column_value. I also added an exit in that loop, so that it won't continue if it is not the last column. Please see the modification below.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type Template as object(template_text varchar2(100),
2 template_value varchar2(100));
3 /
Type created.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type Templates as table of Template;
2 /
Type created.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE procedure Parse (RefCursor in out sys_refcursor) is
2
3 CursorID number;
4 ColumnCount number;
5 ColumnDescriptions dbms_sql.desc_tab;
6 -- added variable for the column position below
7 column_position number;
8 temps templates;
9
10 begin
11 CursorID := dbms_sql.to_cursor_number(RefCursor);
12 dbms_sql.describe_columns(CursorID, ColumnCount, ColumnDescriptions);
13 -- added loop to get column position for define_column and column_value below
14 for i in 1..ColumnCount
15 loop
16 if ColumnDescriptions(i).col_name = 'TEMPLATE'
17 then
18 dbms_sql.define_column (CursorID, i, temps);
19 column_position := i;
20 exit;
21 end if;
22 end loop;
23
24 while dbms_sql.fetch_rows(CursorID) > 0
25 loop
26 -- removed for i in 1 .. columncount loop, using column_position obtaind earlier
27 dbms_output.put_line(ColumnDescriptions(column_position).col_name);
28 -- changed 4 to column_position below
29 dbms_sql.column_value (CursorID, column_position, temps);
30 for r in
31 (select * from table (temps))
32 loop
33 dbms_output.put_line (r.template_text);
34 dbms_output.put_line (r.template_value);
35 end loop;
36 end loop;
37 end Parse;
38 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE procedure Student is
2
3 Students sys_refcursor;
4
5 begin
6
7 open Students for
8 select pidm,
9 first_name,
10 last_name,
11 cast(multiset(select '<STU_ID>',
12 '100'
13 from dual
14
15 union all
16
17 select '<FIRST_NAME>',
18 'Some Value'
19 from dual
20
21 union all
22
23 select '<LAST_NAME>',
24 'Some Value'
25 from dual) as templates) template
26 from (select 100 pidm,
27 'Elvis' first_name,
28 'Presley' last_name
29 from dual
30
31 union all
32
33 select 101,
34 'Lisa Marie',
35 'Presley'
36 from dual
37
38 union all
39
40 select 102,
41 'Jeff',
42 'Beck'
43 from dual);
44
45 Parse(Students);
46
47 end Student;
48 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> EXEC student
TEMPLATE
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
TEMPLATE
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
TEMPLATE
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
PL/SQL procedure successfully completed.
[Updated on: Thu, 16 February 2023 15:44] Report message to a moderator |
|
|
|
| Re: Cast(Multiset()) [message #687303 is a reply to message #687302] |
Thu, 16 February 2023 16:03 |
Duane
Messages: 523
Registered: December 2002
|
Senior Member |
|
|
|
Yes, I already made that change in my code. I knew it wouldn't be column 4 and the column number could change. That was nice of you to reach back out with an updated version. Much appreciated.
|
|
|
|
| Re: Cast(Multiset()) [message #687304 is a reply to message #687303] |
Fri, 17 February 2023 16:16 |
Duane
Messages: 523
Registered: December 2002
|
Senior Member |
|
|
Barbara,
Is there a way to define the columns if they are different? Meaning, as Templates when I need to access that data in that column and then characters (or whatever the assignment should be) for the other columns of the query.
for i in 1..ColumnCount
loop
if ColumnDescriptions(i).col_name = 'TEMPLATE'
then
dbms_sql.define_column(CursorID, ColumnCount, TemplateValue);
else
dbms_sql.define_column (CursorID, i, ColumnValue(i), 4000);
end if;
end loop;
if i = 1
then
dbms_sql.column_value(CursorID, i, ColumnValue(i));
dbms_output.put_line('ID: '||i||' '||ColumnValue(i));
end if;
ID is Blank.
I want the first value in the query but I also want Templates to work so I can access that data.
|
|
|
|
| Re: Cast(Multiset()) [message #687305 is a reply to message #687304] |
Fri, 17 February 2023 17:42 |
|
Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type Template as object(template_text varchar2(100),
2 template_value varchar2(100));
3 /
Type created.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type Templates as table of Template;
2 /
Type created.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE procedure Parse (RefCursor in out sys_refcursor) is
2
3 CursorID number;
4 ColumnCount number;
5 ColumnDescriptions dbms_sql.desc_tab;
6 column_position number;
7 temps templates;
8 -- added line below
9 v_pidm number;
10
11 begin
12 CursorID := dbms_sql.to_cursor_number(RefCursor);
13 dbms_sql.describe_columns(CursorID, ColumnCount, ColumnDescriptions);
14
15 for i in 1..ColumnCount
16 loop
17 -- added 3 lines below
18 if i = 1 then
19 dbms_sql.define_column (CursorID, 1, v_pidm);
20 elsif
21 ColumnDescriptions(i).col_name = 'TEMPLATE'
22 then
23 dbms_sql.define_column (CursorID, i, temps);
24 column_position := i;
25 exit;
26 end if;
27 end loop;
28
29 while dbms_sql.fetch_rows(CursorID) > 0
30 loop
31 -- added 3 lines below
32 dbms_output.put_line(ColumnDescriptions(1).col_name);
33 dbms_sql.column_value (CursorID, 1, v_pidm);
34 dbms_output.put_line (v_pidm);
35
36 dbms_output.put_line(ColumnDescriptions(column_position).col_name);
37 dbms_sql.column_value (CursorID, column_position, temps);
38 for r in
39 (select * from table (temps))
40 loop
41 dbms_output.put_line (r.template_text);
42 dbms_output.put_line (r.template_value);
43 end loop;
44 end loop;
45 end Parse;
46 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE procedure Student is
2
3 Students sys_refcursor;
4
5 begin
6
7 open Students for
8 select pidm,
9 first_name,
10 last_name,
11 cast(multiset(select '<STU_ID>',
12 '100'
13 from dual
14
15 union all
16
17 select '<FIRST_NAME>',
18 'Some Value'
19 from dual
20
21 union all
22
23 select '<LAST_NAME>',
24 'Some Value'
25 from dual) as templates) template
26 from (select 100 pidm,
27 'Elvis' first_name,
28 'Presley' last_name
29 from dual
30
31 union all
32
33 select 101,
34 'Lisa Marie',
35 'Presley'
36 from dual
37
38 union all
39
40 select 102,
41 'Jeff',
42 'Beck'
43 from dual);
44
45 Parse(Students);
46
47 end Student;
48 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> EXEC student
PIDM
100
TEMPLATE
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
PIDM
101
TEMPLATE
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
PIDM
102
TEMPLATE
<STU_ID>
100
<FIRST_NAME>
Some Value
<LAST_NAME>
Some Value
PL/SQL procedure successfully completed.
|
|
|
|
|
|
| Re: Cast(Multiset()) [message #687307 is a reply to message #687306] |
Fri, 17 February 2023 18:25 |
Duane
Messages: 523
Registered: December 2002
|
Senior Member |
|
|
Ok, so what's the trick here. Found out I need the first and second values from the query.
PIDM number;
EmailAddress varchar2(100);
case
when i = 1
then
dbms_sql.define_column(CursorID, i, PIDM);
when i = 2
then
dbms_sql.define_column(CursorID, i, EmailAddress);
when ColumnDescriptions(i).col_name = 'TEMPLATE'
then
dbms_sql.define_column(CursorID, i, TemplateValue);
ColumnPosition := i;
else
null;
end case;
[Updated on: Fri, 17 February 2023 18:45] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Cast(Multiset()) [message #687310 is a reply to message #687309] |
Fri, 17 February 2023 20:00 |
Duane
Messages: 523
Registered: December 2002
|
Senior Member |
|
|
I changed it to dbms_sql.define_column(CursorID, i, EmailAddress, 100); Up and running so I'm good with that.
Thanks again.
|
|
|
|
Goto Forum:
Current Time: Wed Sep 27 17:23:48 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
