Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Printing output form a cursor in SQLPlus

754172May 17 2010 — edited May 17 2010
OK, I need to 'print out' records in my a table using SQLPlus. However, instead of printing out the data of a record in a single row, I need one row for each column, with the column name first, and then the value.

For a very, very simplified example, if the table is as follows. Again, this is oversimplified, as I have actually have about 50 different columns and a few thousand entries. Anyways....

CREATE TABLE my_names (
f_name VARCHAR2(25),
l_name VARCHAR2(25),
job VARCHAR2(25)
)

And the data is
INSERT INTO my_names SELECT 'chris', 'smith', 'programmer' FROM dual;
INSERT INTO my_names SELECT 'tom', 'jones', 'analyst' FROM dual;
INSERT INTO my_names SELECT 'mary', 'clark', 'developer' FROM dual;

I need it to print out like such...

First Name: Chris
Last Name: Smith
Position: Programmer
-----------------------------
First Name: Tom
Last Name: Jones
Position: analyst
-----------------------------
First Name: Mary
Last Name: Clark
Position: Programmer

I figure I need a cursor to do this, however, I usually don't use cursors, and the only way I know how to do something like this is wth dbms_output, but I get a buffer overflow error when I try to use that in my cursor. I did some reasearch and think that perhaps a 'ref' cursor' might be what I need, but I'm not sure how to use it, and would like to see an example used in this fashion.

One other thing that may or may not be important is that I have to pass in a parameter that will be used in the where clause of the cursor.any assistance you can provide would be greatly appreciated.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 14 2010
Added on May 17 2010
5 comments
3,604 views