6 Replies Latest reply on Dec 1, 2008 3:53 AM by JustinCave

    sqlplus command describe wrapping

    672949
      Hi,

      I would like to know why the output of describe command depends of linesize setting and how can I "normalize" it. Examples
      SQL> set linesize 80
      SQL> describe employees
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       EMPLOYEE_ID                               NOT NULL NUMBER(6)
       FIRST_NAME                                         VARCHAR2(20)
       LAST_NAME                                 NOT NULL VARCHAR2(25)
       EMAIL                                     NOT NULL VARCHAR2(25)
       PHONE_NUMBER                                       VARCHAR2(20)
       HIRE_DATE                                 NOT NULL DATE
       JOB_ID                                    NOT NULL VARCHAR2(10)
       SALARY                                             NUMBER(8,2)
       COMMISSION_PCT                                     NUMBER(2,2)
       MANAGER_ID                                         NUMBER(6)
       DEPARTMENT_ID                                      NUMBER(4)
      SQL> set linesize 1000
      SQL> describe employees
       Name                                                                                                 Null?    Type
       ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       EMPLOYEE_ID                                                                                          NOT NULL NUMBER(6)
       FIRST_NAME                                                                                            VARCHAR2(20)
       LAST_NAME                                                                                            NOT NULL VARCHAR2(25)
       EMAIL                                                                                                NOT NULL VARCHAR2(25)
       PHONE_NUMBER                                                                                          VARCHAR2(20)
       HIRE_DATE                                                                                            NOT NULL DATE
       JOB_ID                                                                                               NOT NULL VARCHAR2(10)
       SALARY                                                                                                NUMBER(8,2)
       COMMISSION_PCT                                                                                        NUMBER(2,2)
       MANAGER_ID                                                                                            NUMBER(6)
       DEPARTMENT_ID                                                                                         NUMBER(4)
      Thanks.
        • 1. Re: sqlplus command describe wrapping
          Walter Fernández
          Hi,

          For 9.2 for example [SQL*Plus Command Reference|http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1012664].

          Text taken from above link...

          The DESCRIBE command allows you to describe objects recursively to the depth level set in the SET DESCRIBE command. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. For more information, see the SET command later in this chapter.

          To control the width of the data displayed, use the SET LINESIZE command.

          Columns output for the DESCRIBE command are typically allocated a proportion of the linesize currently specified. Decreasing or increasing the linesize with the SET LINESIZE command usually makes each column proportionally smaller or larger. This may give unexpected text wrapping in your display. For more information, see the SET command later in this chapter.

          Regards,
          1 person found this helpful
          • 2. Re: sqlplus command describe wrapping
            JustinCave
            I'm not sure what sort of explanation you're looking for-- linesize should indicate the maximum line length you can view and the DESCRIBE command right-aligns the last column.

            I'm also not sure what you mean by "normalize" here. You can, of course, set the linesize back to 80 (or whatever else makes sense in your environment) before running the DESCRIIBE command, but I suspect that's not what you're looking for...

            Justin
            • 3. Re: sqlplus command describe wrapping
              672949
              I'm using console with horizontal scrolling ability, so I can set linesize to say 2000 or 3000 and see any output without text wrapping. If linesize is set to 3000, and I execute describe command, the output is spread 3000 characters horizontally, that's little weird. That's what I was asking: how to set sqlplus environmet to display the output of describe command to be always visible inside "linesize of 80 characters" (physical screen) regardless of actual linesize setting.
              • 4. Re: sqlplus command describe wrapping
                JustinCave
                I don't believe that it is possible to simultaneously have a linesize of 3000 for everything else and a linesize of 80 just for the DESCRIBE command.

                You could potentially write a small SQL*Plus script that queries DBA_/ ALL_/ USER_TAB_COLS and prints out some fixed-width result using appropriate DBMS_OUTPUT calls. You could call that script DESCRIBE.SQL and then do
                SQL> @describe table_name
                Of course, you could spend a bit of time making your script have the flexibility of the DESCRIBE command in SQL*Plus that can handle packages/ procedures/ functions/ objects/ etc.

                Justin
                1 person found this helpful
                • 5. Re: sqlplus command describe wrapping
                  672949
                  Well, if I got you right, it's not possible. But you must admit my question was pretty reasonable. If decreasing or increasing the linesize doesn't make any column proportionally smaller or larger in the output of SELECT statement, why does it proportionally change the columns of describe command's output? After all, the output of describe command consists of only three columns (Name, Null?, Type), and I don't see any reason for the columns to be proportionally changed according to the linesize setting.

                  Ok, I consider my question being answered.

                  Thanks a lot.
                  • 6. Re: sqlplus command describe wrapping
                    JustinCave
                    It's certainly not an unreasonable question.

                    Unfortunately, I think the set of users that are using SQL*Plus and have horizontal scrolling enabled and linesize set at 3000 is relatively small. The population that is using PL/SQL IDE's (TOAD, SQL Programmer, SQL Developer, etc) is probably growing steadily.

                    Justin