DBA Tips Archive for Oracle

  


Inline Views

by Jeff Hunter, Sr. Database Administrator

Oracle Corporation officially introduced the use of inline views in their 7.1 release and became a supported feature in version 7.2. The use of inline views in PL/SQL didn't officially began until version 2.3 (v7.3 of the database). Before version 2.3 of PL/SQL, developers would have to use DBMS_SQL (dynamic sql) in order to use inline views in stored procedures.

Inline views can be extremely useful in multiple many to many relationships. For example, the code in Table 1 displays the use of inline views in a report that displays all employee name, salaries and their salary percentage.

emp_percentage.sql Example Run
COLUMN percent  HEADING '%|Total Salary'
COLUMN name     HEADING 'Name'
COLUMN salary   HEADING 'Salary'

SELECT
    name
  , salary
  , ROUND(salary / tot_salary *100, 0) percent
FROM
    emp,
    (select sum(salary) tot_salary from emp)
/
                             %
Name            Salary Total Salary
----------- ---------- ------------
John             47000            8
Mary             87109           15
Jill             40912            7
Cliff            70881           12
Bob              81293           14
Stan            102345           17
Eric            163911           28

7 rows selected.
Table 1 : Inline view example - Calculating Percentages

Below (Table 2) is another example of using inline views. This example displays the number of distinct values for a certain table. Indeed there are many ways to produce the same results, but this method is very straightforward.

The EMP Table Example Run
SQL> select name from emp;

Name
------------------------------
John
Mary
Jill
Cliff
Bob
Stan
Eric
Mary
Stan
Mel

10 rows selected.
SQL> select count(*)
  2  from (select distinct name from emp);

  COUNT(*)
----------
         8
Table 2 : Inline view example - Distinct Values

And finally, another very useful script that shows the use of inline views can be found in Table 3.

tablespace_size.sql
COLUMN dummy NOPRINT
COLUMN pct_used FORMAT 999.9        HEADING "%|Used" 
COLUMN name     FORMAT a16          HEADING "Tablespace Name"
COLUMN Kbytes   FORMAT 999,999,999  HEADING "KBytes"
COLUMN used     FORMAT 999,999,999  HEADING "Used"
COLUMN free     FORMAT 999,999,999  HEADING "Free"
COLUMN largest  FORMAT 999,999,999  HEADING "Largest" 
BREAK ON report
COMPUTE sum OF kbytes ON REPORT
COMPUTE sum OF free   ON REPORT
COMPUTE sum OF used   ON REPORT
SELECT
    NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
  , kbytes_alloc                                           kbytes
  , kbytes_alloc-NVL(kbytes_free,0)                        used
  , NVL(kbytes_free,0)                                     free
  , ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100   pct_used
  , NVL(largest,0)                                         largest 
FROM   ( SELECT   SUM(bytes)/1024 Kbytes_free
                , MAX(bytes)/1024 largest 
                , tablespace_name 
         FROM sys.dba_free_space
         GROUP BY tablespace_name 
       ) a
     , ( SELECT   SUM(bytes)/1024 Kbytes_alloc 
                , tablespace_name 
         FROM sys.dba_data_files
         GROUP BY tablespace_name
       ) b 
WHERE a.tablespace_name (+) = b.tablespace_name 
/
Example Run
Tablespace Name        KBytes         Used         Free   Used      Largest
---------------- ------------ ------------ ------------ ------ ------------
DRSYS                 102,400        4,720       97,680    4.6       97,360
RBS1                  153,600       12,488      141,112    8.1      127,592
RBS2                  153,600       12,488      141,112    8.1      137,992
SYSTEM                512,000      272,144      239,856   53.2      239,088
TEMP                  256,000            8      255,992     .0      253,912
                 ------------ ------------ ------------
sum                 1,177,600      301,848      875,752
Table 3 : Inline view example - Tablespace Report


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 04-Oct-2001 00:00:00 EDT
Page Count: 22502