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 Go to next message
Duane
Messages: 557
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
Duane
Messages: 557
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
Duane
Messages: 557
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
Duane
Messages: 557
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 #687300 is a reply to message #687299] Thu, 16 February 2023 14:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you are receiving a ref cursor with unknown columns, including one known template column, then, unless somebody else can think of another way, I think you are stuck with the dbms_sql, as in my first response. This is why I usually start with just fixing someone else's code enough to make it work, because I figure there may be things that I don't know.
Re: Cast(Multiset()) [message #687301 is a reply to message #687300] Thu, 16 February 2023 14:52 Go to previous messageGo to next message
Duane
Messages: 557
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
Duane
Messages: 557
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 Go to previous messageGo to next message
Duane
Messages: 557
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #687306 is a reply to message #687305] Fri, 17 February 2023 18:00 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Ah, thank you.
Re: Cast(Multiset()) [message #687307 is a reply to message #687306] Fri, 17 February 2023 18:25 Go to previous messageGo to next message
Duane
Messages: 557
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;

[Error] Compilation (367: 15): PLS-00307: too many declarations of 'DEFINE_COLUMN' match this call

[Updated on: Fri, 17 February 2023 18:45]

Report message to a moderator

Re: Cast(Multiset()) [message #687308 is a reply to message #687307] Fri, 17 February 2023 18:46 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Never mind. I added 4000 to the statement. dbms_sql.define_column(CursorID, i, EmailAddress, 4000); That took care of the error.

[Updated on: Fri, 17 February 2023 18:46]

Report message to a moderator

Re: Cast(Multiset()) [message #687309 is a reply to message #687308] Fri, 17 February 2023 18:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I suspected something like that. I was going to suggest that you use the following to discover the correct type and length.

dbms_output.put_line(ColumnDescriptions(2).col_type);

dbms_output.put_line(ColumnDescriptions(2).col_max_len);

Re: Cast(Multiset()) [message #687310 is a reply to message #687309] Fri, 17 February 2023 20:00 Go to previous message
Duane
Messages: 557
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.
Previous Topic: Difference between two dates
Next Topic: Optional parameter in Where clause
Goto Forum:
  


Current Time: Thu Mar 28 17:11:44 CDT 2024