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>

      

+3


source to share


3 answers


1) show linesize

to get the current line. My guess is "80".

2) set linesize 32767



3) Run the SQL command. Check the actual line size.

+1


source


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>

      

+1


source


the problem is not in the format, but in the trailing sign for negative numbers. To avoid this, there is no number format. Use to_char

to select your data in the required format in combination with substr

to trim the result to 10 characters

0


source







All Articles