Calling PL/SQL Functions

Contact Us or call 1-877-932-8228
Calling PL/SQL Functions

Calling PL/SQL Functions

PL/SQL functions return a value and can be called from within SQL statements (as well as from PL/SQL blocks). Functions are typically tested using an Oracle table called DUAL. The DUAL table is a special table available in all Oracle databases that contains one row and one column. It is useful for creating syntactically valid SQL statements that are used to call functions. They can also be used to return a result set with a single row for other testing purposes.

Code Sample:

select dbms_metadata.get_ddl('TABLE','EMPLOYEES')
from dual;

Code Explanation

This demonstration shows a call to a function in the context of a SQL statement. This illustrates one way that PL/SQL is tightly integrated with Oracle SQL.

The DBMS_METADATA package contains a number of subprograms related to retrieving information on the structure of database objects. This demonstration illustrates a function that is part of this package that can be called using the DUAL table. Some adjustments are required to format the display correctly in SQL*Plus.

set long 2000000000 set pages 0

More information on these and other SQL*Plus formatting options can be found in the SQL*Plus help as well as the online SQL*Plus documentation.