CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Piyush_Chandra
Piyush_Chandra

Reputation: 111

What is location of built in SQL functions and Oracle Packages in Oracle Database

I want to know location of file / table where definition of Built In Functions / Packages / Procedures of Oracle are stored like MAX(), DBMS_OUTPUT etc.

Upvotes: 1

Views: 2376

Answers (2)

Ben
Ben

Reputation: 1927

In the PL/SQL engine, the Oracle supplied functions such as MAX() are part of the package STANDARD in the SYS schema.

Most other supplied packages reside in the SYS schema, however you can find out where any individual package is located quite easily - for example:

SELECT *
FROM all_objects
WHERE object_name = 'DBMS_OUTPUT'

Results:

|  OWNER | OBJECT_NAME | SUBOBJECT_NAME | OBJECT_ID | DATA_OBJECT_ID | OBJECT_TYPE |                       CREATED |                 LAST_DDL_TIME |           TIMESTAMP | STATUS | TEMPORARY | GENERATED | SECONDARY | NAMESPACE | EDITION_NAME |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|    SYS | DBMS_OUTPUT |         (null) |      4972 |         (null) |     PACKAGE | August, 27 2011 08:22:22+0000 | August, 27 2011 08:22:22+0000 | 2011-08-27:08:22:22 |  VALID |         N |         N |         N |         1 |       (null) |
| PUBLIC | DBMS_OUTPUT |         (null) |      4973 |         (null) |     SYNONYM | August, 27 2011 08:22:22+0000 | August, 27 2011 08:22:22+0000 | 2011-08-27:08:22:22 |  VALID |         N |         N |         N |         1 |       (null) |

The following documentation page lists off most (if not all) PL/SQL supplied packages: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/intro.htm#BABGEDBH

Upvotes: 2

wolφi
wolφi

Reputation: 8361

The scripts to create the build-in functions, packages and procedures are stored on the database server machine. You have to find the value of the environment variable $ORACLE_HOME, and then go to $ORACLE_HOME/rdbms/admin/. Just use grep to find the file you're looking for.

If the database server is a Windows machine, look at ECHO %ORACLE_HOME% at the command prompt and proceed from there.

Upvotes: 1

Related Questions