Why am I getting SP2-0253
I am getting in my testing. But I think the line of lines is already big enough. I know that increasing the line will solve the problem. Please let me know why I am getting SP2-0253 here.
SQL> COLUMN sal HEADING 'Salary' FORMAT $99,999.99
SQL> set lines 10
SQL> show user
USER is "SCOTT"
SQL> desc sal from emp
Usage: DESCRIBE [schema.]object[@db_link]
SQL> select sal from emp where rownum = 1;
SP2-0253: data item 1 ("SAL") will not fit on line
SQL> set lines 20
SQL> /
Salary
-----------
$800.00
SQL>
source to share
SP2-0253: Item 1 ("SAL") Will Not Fit On Line
The reason is simple. You want the output format like:
FORMAT $99,999.99
What does it require lineize 11
with correct position and alignment in SQL * Plus output .
We'll see:
SQL> set linesize 11
SQL> select sal from emp where rownum = 1;
Salary
-----------
$800.00
The easiest way to see how many lines are being output is to check the LENGTH underscore :
SQL> select length('-----------') length from dual;
LENGTH
----------
11
SQL>
So, it answers the question " why the output of 11 lines cannot fit into 10 lines ".
Refresh . The actual reason for the behavior is that the column name is formatted as the new column name and there is no valid alias specified in select .
COLUMN commands only apply to column names in the SELECT list when they exactly match. If they differ from each other, you must use the exact alias in the column list in the select statement.
Solution Add the same aliases as the column name provided in its format.
SQL> COLUMN sal HEADING 'Salary' FORMAT $99,999.99
SQL> set lines 10
SQL> show user
USER is "SCOTT"
SQL> select sal from emp where rownum = 1;
SP2-0253: data item 1 ("SAL") will not fit on line
SQL> select sal Salary from emp where rownum = 1;
SALARY
----------
800
SQL>
As an alternative,
SQL> set linesize 10
SQL> select sal Salary from emp where rownum = 1;
SALARY
----------
800
SQL> select sal as "Salary" from emp where rownum = 1;
Salary
----------
800
SQL> select sal from emp where rownum = 1;
SP2-0253: data item 1 ("SAL") will not fit on line
SQL> set linesize 11
SQL> select sal from emp where rownum = 1;
Salary
-----------
$800.00
SQL>
source to share