Oracle SQL

To get the list of columns of a table in Oracle, we use to query the view “USER_TAB_COLUMNS” to get the columns of the tables or views owned by the current user. Or use the other view “USER_TAB_COLS“.

SELECT table_name
,column_name
,data_type
,data_length
,nullable
,data_default
FROM  user_tab_columns
WHERE table_name = '&YOUTABLENAME'
ORDER BY column_id;

in the above query, we selected 7 columns to retrieve the most important information that you might be looking for in table columns which are

• table names: the table name.
• column name: column name.
• data type: column data type like varchar2, number, etc.
• data length: the value length.
• nullable: whether the table accepts the null value or not.
• data_default: whether the column has a default value or not.

Note: you have to specify the table name parameter in uppercase.

Note 2: If you use the above view, you will get all unhidden columns, but if you query the view “USER_TAB_COLS” you will get all columns, including hidden columns.

Another view is “ALL_TAB_COLUMNS” this one is the same as “USER_TAB_COLUMNS” except that the “ALL_TAB_COLUMNS” view has a column called “OWNER” in which you can specify the owner of the table that you want to get its columns list.

BULK COLLECT in Oracle PL/SQL:

BULK COLLECT in Oracle PL/SQL reduces the round trip between SQL engine and PL/SQL engine and allows SQL engine to fitch a bulk collection of data at once instead of the traditional LOOP statement.

We are using the BULK COLLECT clause in the SELECT statement to fitch the cursor result set in bulk or to populate the records in bulk.

Since the BULK COLLECT fetches a result set or more than one record, the INTO clause must contain a collection variable like Oracle TYPE.

Example:

DECLARE
CURSOR oraask_dept_details
IS
SELECT * FROM departments;

TYPE l_dept_details_tbl IS TABLE OF departments%ROWTYPE;

l_dept_details l_dept_details_tbl;
BEGIN
OPEN oraask_dept_details;

LOOP
FETCH oraask_dept_details BULK COLLECT INTO l_dept_details LIMIT 10;

EXIT WHEN l_dept_details.count = 0;

FOR dept_details_rec IN l_dept_details.first .. l_dept_details.last
LOOP
dbms_output.put_line ('DEPARTMENT ID:' || l_dept_details (dept_details_rec).department_id || ' - DEPARTMENT NAME:' || l_dept_details (dept_details_rec).department_name);
END LOOP;
END LOOP;

CLOSE oraask_dept_details;
END;
/

Output :

DEPARTMENT ID:10 - DEPARTMENT NAME:Administration
DEPARTMENT ID:20 - DEPARTMENT NAME:Marketing
DEPARTMENT ID:30 - DEPARTMENT NAME:Purchasing
DEPARTMENT ID:40 - DEPARTMENT NAME:Human Resources
DEPARTMENT ID:50 - DEPARTMENT NAME:Shipping
DEPARTMENT ID:60 - DEPARTMENT NAME:IT
DEPARTMENT ID:70 - DEPARTMENT NAME:Public Relations
DEPARTMENT ID:80 - DEPARTMENT NAME:Sales
DEPARTMENT ID:90 - DEPARTMENT NAME:Executive
DEPARTMENT ID:100 - DEPARTMENT NAME:Finance
DEPARTMENT ID:110 - DEPARTMENT NAME:Accounting
DEPARTMENT ID:120 - DEPARTMENT NAME:Treasury
DEPARTMENT ID:130 - DEPARTMENT NAME:Corporate Tax
DEPARTMENT ID:140 - DEPARTMENT NAME:Control And Credit
DEPARTMENT ID:150 - DEPARTMENT NAME:Shareholder Services
DEPARTMENT ID:160 - DEPARTMENT NAME:Benefits
DEPARTMENT ID:170 - DEPARTMENT NAME:Manufacturing
DEPARTMENT ID:180 - DEPARTMENT NAME:Construction
DEPARTMENT ID:190 - DEPARTMENT NAME:Contracting
DEPARTMENT ID:200 - DEPARTMENT NAME:Operations
DEPARTMENT ID:210 - DEPARTMENT NAME:IT Support
DEPARTMENT ID:220 - DEPARTMENT NAME:NOC
DEPARTMENT ID:230 - DEPARTMENT NAME:IT Helpdesk
DEPARTMENT ID:240 - DEPARTMENT NAME:Government Sales
DEPARTMENT ID:250 - DEPARTMENT NAME:Retail Sales
DEPARTMENT ID:260 - DEPARTMENT NAME:Recruiting
DEPARTMENT ID:270 - DEPARTMENT NAME:Payroll

In the above script, we used a LIMIT clause to limit fetching the bulk of records to 10 records at a time. This clause is very handful when dealing with a large number of records to avoid running out of memory because the collection type we use is expanding as more records are inserted into it.

Do you find it helpful? Share to spread the knowledge