Printing output form a cursor in SQLPlus
754172May 17 2010 — edited May 17 2010OK, 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.