making a message body variable from an oracle select statement

Lawrence Avery
Lawrence Avery used Ask the Experts™
on
I am looking to create a message body variable in a stored procedure by assigning results of a select statement to it.

Can I do that?
In other words, something like:

Declare message_body  varchar2

message_body = select * from table1;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Not the way you are wanting.

How do you want the results formatted?

You can say generate all the rows/columns as XML and make it a CLOB into a single variable.

Something like:
message_body := dbms_xmlgen.getxml('select * from table1');
Lawrence AverySystem Developer

Author

Commented:
I am sending the results of the select statement to another procedure but I want the results to have column headings followed by the data.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>but I want the results to have column headings followed by the data.

That is a sqlplus thing.  In PL/SQL if/when you are dealing with a cursor, you don't get column headings.

>>I am sending the results of the select statement to another procedure

When I see message_body, I think email.  If you are just passing data around, I wouldn't do it as a CLOB.  I would use a cursor, XML or some other PL/SQL collection object.
Lawrence AverySystem Developer

Author

Commented:
Good solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial