More with Oracle Variables – 2 (COL command)

We have already seen variables being declared and values being bound to those variables. Here will see one more command COLUMN. You may know the COLUMN command as a way to format the screen display of resultset column. Once set, it will format any column of that name, until it is being unset by CLEAR COLUMNS command.

SQL> desc salgrade;
Name Null? Type
—————————————– ——– —————————-
GRADE NUMBER(3)
LOSAL NUMBER(8)
HISAL NUMBER(8)

SQL> select * from salgrade;

GRADE LOSAL HISAL
———- ———- ———-
1 500 800
2 801 1000
3 1001 1500

SQL> clear column;
columns cleared
SQL> col name a50;
SP2-0158: unknown COLUMN option “a50”
SQL> col name for a50;
SQL> select grade as name from salgrade;

NAME
———-
##########
##########
##########

what do these #s mean??? can you please reply me in comments 🙁

SQL> clear column;
columns cleared
SQL> select grade as name from salgrade;

NAME
———-
1
2
3

SQL> col uname for a30
SQL> select user as uname from dual;

——————————————————————–

Another working example,

SQL> desc emp;
Name Null? Type
—————————————– ——– —————————-
EMPNO NUMBER(4)
ENAME VARCHAR2(30)
JOB CHAR(10)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> select ename from emp;

ENAME
——————————
pandian
muruga
bala

SQL> col ename for a50;
SQL> select ename from emp;

ENAME
————————————————–
pandian
muruga
bala

SQL>

But in addition to allowing you to specify a column’s display attributes, the COLUMN command has two extremely useful parameters, NEW_VALUE and OLD_VALUE. Both take a variable. When a query using the COLUMN named in the column command is executed, the variable you specified for NEW_VALUE or OLD_VALUE is defined (if it was previously undefined) and filled with the value in that column.

In the following example, we’ll specify a format for the column label “uname” and specify that its NEW_VALUE should go to the variable myusername. Then, when we issue a query that has a column called “uname” in the resultset, the variable myusername will be populated with its value.

SQL> col uname for a30 new_value myusername
SQL> def myusername
SP2-0135: symbol myusername is UNDEFINED
SQL> select user as uname from dual;

UNAME
——————————
SCOTT

1 row selected.

SQL> def myusername
DEFINE MYUSERNAME = “SCOTT” (CHAR)

If there’s more than one row in your resultset, then the value will be taken from the last row in the resultset.

SQL> col uname for a30 new_value myuname
SQL> def myuname
SP2-0135: symbol myuname is UNDEFINED
SQL> select name as uname from customer;

UNAME
——————————
john
mary
alex
richard

SQL> def myuname
DEFINE MYUNAME = “richard” (CHAR)
SQL>