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.
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.
6 Comments
Thank you for sharing the new tip. It really helpful for me! 🙂
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).
This is true 🙂 Both tools have the same scripting engine.
I just found SQLcl. This is a pretty epic utility. I really like it a lot!
I like it. This is so useful and more detailed.
Thank you very much Jeff.
you’re very welcome!