INFORMATION is a new command available in SQLcl, a command-line interface to Oracle Database.

HR@orcl?? >help information
INFORMATION
--------
 
This command IS LIKE DESCRIBE but WITH more details about the objects requested.
 
INFO[RMATION] {[schema.]object[@connect_identifier]}
INFO+ will SHOW COLUMN statistics
 
HR@orcl?? >

It’s available for tables and views, of course.

Here’s a table. Note that we show the primary key marked with a ‘*’ in the column list.

HR@orcl?? >info hr.departments
TABLE: DEPARTMENTS 
	 LAST ANALYZED:2015-09-09 22:01:34.0 
	 ROWS         :27 
	 SAMPLE SIZE  :27 
	 INMEMORY     :DISABLED 
	 COMMENTS     :Departments TABLE that shows details OF departments WHERE employees
                       WORK. Contains 27 ROWS; REFERENCES WITH locations, employees, AND job_history TABLES. 
 
COLUMNS 
NAME              DATA TYPE             NULL  DEFAULT    COMMENTS
*DEPARTMENT_ID    NUMBER(4,0)           No                          PRIMARY KEY COLUMN OF departments TABLE.
 DEPARTMENT_NAME  VARCHAR2(30 BYTE)     No                          A NOT NULL COLUMN that shows name OF a department. Administration,
                                                                              Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
                                                                              Relations, Sales, Finance, AND Accounting. 
 MANAGER_ID       NUMBER(6,0)           Yes                         Manager_id OF a department. FOREIGN KEY TO employee_id COLUMN OF employees TABLE. The manager_id COLUMN OF the employee TABLE REFERENCES this COLUMN.
 LOCATION_ID      NUMBER(4,0)           Yes                         Location id WHERE a department IS located. FOREIGN KEY TO location_id COLUMN OF locations TABLE.
 REVIEW           VARCHAR2(4000 BYTE)   Yes   '{"comments": []}'    
 
Indexes
INDEX_NAME           UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS        COLUMN_EXPRESSION  
HR.DEPT_ID_PK        UNIQUE      VALID                   DEPARTMENT_ID                     
HR.DEPT_LOCATION_IX  NONUNIQUE   VALID                   LOCATION_ID                       
 
 
REFERENCES
TABLE_NAME   CONSTRAINT_NAME  DELETE_RULE  STATUS   DEFERRABLE      VALIDATED  GENERATED  
EMPLOYEES    EMP_DEPT_FK      NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
JOB_HISTORY  JHIST_DEPT_FK    NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME

And here’s a VIEW.

HR@orcl?? >info emp_details_view
COLUMNS 
NAME              DATA TYPE           NULL  DEFAULT    COMMENTS
 EMPLOYEE_ID      NUMBER(6,0)         No                   this IS a COLUMN comment ON a VIEW.
 JOB_ID           VARCHAR2(10 BYTE)   No                   
 MANAGER_ID       NUMBER(6,0)         Yes                  
 DEPARTMENT_ID    NUMBER(4,0)         Yes                  
 LOCATION_ID      NUMBER(4,0)         Yes                  
 COUNTRY_ID       CHAR(2 BYTE)        Yes                  
 FIRST_NAME       VARCHAR2(20 BYTE)   Yes                  
 LAST_NAME        VARCHAR2(25 BYTE)   No                   
 SALARY           NUMBER(8,2)         Yes                  
 COMMISSION_PCT   NUMBER(2,2)         Yes                  
 DEPARTMENT_NAME  VARCHAR2(30 BYTE)   No                   
 JOB_TITLE        VARCHAR2(35 BYTE)   No                   
 CITY             VARCHAR2(30 BYTE)   No                   
 STATE_PROVINCE   VARCHAR2(25 BYTE)   Yes                  
 COUNTRY_NAME     VARCHAR2(40 BYTE)   Yes                  
 REGION_NAME      VARCHAR2(25 BYTE)   Yes

You can get the statistics for a table instead of the column comments if you want. Use INFO+ for that.

Info, plus some stats.
Info, plus some stats.

Let’s look at other things you can ‘INFO,’ like PL/SQL.

A Procedure

HR@orcl?? >info add_job_history
/*  PROCEDURE  HR.ADD_JOB_HISTORY  */
    HR.ADD_JOB_HISTORY(   P_EMP_ID          =>  p_IN_param0  /*   NUMBER(6)   */,
                          P_START_DATE      =>  p_IN_param1  /*   DATE   */,
                          P_END_DATE        =>  p_IN_param2  /*   DATE   */,
                          P_JOB_ID          =>  p_IN_param3  /*   VARCHAR2(10 BYTE)   */,
                          P_DEPARTMENT_ID   =>  p_IN_param4  /*   NUMBER(4)   */);
HR@orcl?? >

DESC for a PL/SQL procedure is nearly identical. It will print a table of the arguments. But with the INFO you can quickly copy and paste that into a script so you can execute it.

Same for a function.

HR@orcl?? >info lpb_get_rev_doctemp_ep01
/*  FUNCTION  HR.LPB_GET_REV_DOCTEMP_EP01  */
    /*   RETURN VARCHAR2   */
     v_ret := HR.LPB_GET_REV_DOCTEMP_EP01(   P_APPR_ID   =>  p_IN_param0  /*   NUMBER   */);
HR@orcl?? >

And for packages…you can INFO a package.procedure or package.function.

Everyone knows DBMS_OUTPUT.PUT_LINE, yes?

What about UTL_FILE.FOPEN*?

HR@orcl?? >info dbms_output.put_line
Package
 
/* Package SYS.DBMS_OUTPUT */
/*  PROCEDURE  SYS.DBMS_OUTPUT.PUT_LINE  */
    SYS.DBMS_OUTPUT.PUT_LINE(   A   =>  p_IN_param0  /*   VARCHAR2   */);
 
 
HR@orcl?? >info utl_file.fopen
Package
 
/* Package SYS.UTL_FILE */
/*  FUNCTION  SYS.UTL_FILE.FOPEN  */
    /*   RETURN PL/SQL RECORD   */
     v_ret := SYS.UTL_FILE.FOPEN(   LOCATION       =>  p_IN_param0  /*   VARCHAR2   */,
                                    FILENAME       =>  p_IN_param1  /*   VARCHAR2   */,
                                    OPEN_MODE      =>  p_IN_param2  /*   VARCHAR2   */,
                                    MAX_LINESIZE   =>  p_IN_param3  /*   BINARY_INTEGER   */);
 
/*  FUNCTION  SYS.UTL_FILE.FOPEN_NCHAR  */
    /*   RETURN PL/SQL RECORD   */
     v_ret := SYS.UTL_FILE.FOPEN_NCHAR(   LOCATION       =>  p_IN_param0  /*   VARCHAR2   */,
                                          FILENAME       =>  p_IN_param1  /*   VARCHAR2   */,
                                          OPEN_MODE      =>  p_IN_param2  /*   VARCHAR2   */,
                                          MAX_LINESIZE   =>  p_IN_param3  /*   BINARY_INTEGER   */);
 
 
HR@orcl?? >

Support for more schema or database object types may be forthcoming, so stay tuned.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

6 Comments

  1. Thank you for sharing the new tip. It really helpful for me! 🙂

  2. I think it’s worth mentioning that both “INFO” and INFO+” work in plain old SQLDeveloper as well (at least in version 4.1.3.20).

  3. Jeremy Sheehan Reply

    I just found SQLcl. This is a pretty epic utility. I really like it a lot!

Reply To thatjeffsmith Cancel Reply