Oracle - Print Column values in Rows

Many times, we wonder how to print all the column values of a single record in row form which will be most widely used for analysis or studies

Here is an utility PL/SQL block which can print all column valus in a rows in tab limitied form (can be easily converted to Spreadsheets)


/**
* Author : Nambi Adhimoolam
* Date : 9/17/2008
* Revision : 1.0
*/

DECLARE
CURSOR c1 (v_table_name VARCHAR2)
IS
SELECT column_name
FROM all_tab_cols
WHERE table_name = v_table_name;

l_sql VARCHAR2 (100);
l_val VARCHAR2 (32000);
BEGIN
FOR k IN c1 ('MY_TABLE')
LOOP
l_sql :=
'SELECT ''''||'|| k.column_name
||' FROM MY_TABLE WHERE rownum < 2';

EXECUTE IMMEDIATE l_sql INTO l_val;

DBMS_OUTPUT.put_line (k.column_name||chr(9)||l_val);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM || SQLCODE);
END;



Disable Substitution variables in SQL* Plus

For example, if we need to execute a following sql, in SQL*Plus


INSERT INTO USER(ID,NAME) VALUES(12,'ABC & CO');


While executing this, SQL*Plus will promt the user to input value 'CO' as its prefixed with subsititution character &. However in this scenarios, its should not act as substitution variable but rather &hersand.

To overcome this issues, we can disable the subsititution variabels with


SET DEFINE OFF


For selective disabling and other options, refer
http://www.orafaq.com/wiki/SQL*Plus_FAQ