 |
|
Oracle Tips by Burleson |
Retrieving the Source from the Database
The developers have created a set of procedures
and functions and loaded them into the database. Sometimes you need to
be able to pull the source code back out of the database. When a
named block is sent to the database its source is loaded into the
database and then compiled. The source is maintained in a view called
user_source
. To pull the source code out of
the database, first list the objects in the database belonging to the
user.
SQL> select
2 object_name,
3 object_type,
4 status
5 from user_objects;
OBJECT_NAME
OBJECT_TYPE STATUS
------------------------------ ------------------ ------
AUTHOR TABLE VALID
BOOK TABLE VALID
BOOK_AUTHOR TABLE VALID
CALC_AREA FUNCTION VALID
EMP TABLE VALID
F2C FUNCTION VALID
FULL_NAME FUNCTION VALID
GET_AREA PROCEDURE VALID
GET_AREA2 PROCEDURE VALID
JOB TABLE VALID
MK_MONEY FUNCTION VALID
NUM2WORD FUNCTION VALID
NUM_CHECK PROCEDURE VALID
PUBLISHER TABLE VALID
SALES TABLE VALID
STORE TABLE VALID
VALID_NUMB FUNCTION VALID
17 rows selected.
To find the code for the function num2word
we look at user_source.
SQL> desc
user_source;
Name Type
---------------------------- --------------
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
The user_source
view contains a row for each line
of source code. To retrieve the code for the num2word
function, select the text for that function.
SQL> select
text
2 from user_source
3 where name = 'NUM2WORD'
4 order by line;
TEXT
-----------------------------------------
function num2word
(n_number IN number)
return varchar2
as
begin
if n_number = 1
then return 'one';
elsif n_number = 2 then return 'two';
elsif n_number = 3 then return 'three';
end if;
end;
11 rows
selected.
The code is listed in the format that it was
submitted to the database. To find out which objects have source
listed in user_source, select the object
name.
SQL> select
distinct name from user_source;
NAME
------------------------------
CALC_AREA
F2C
FULL_NAME
GET_AREA
GET_AREA2
MK_MONEY
NUM2WORD
NUM_CHECK
VALID_NUMB
9 rows
selected.
The query must use the DISTINCT clause
as there is a row for each line of source code defining each object.
Since each line of source code is in a separate row in the view, you
need to order the row by line or the lines could be returned
out of order.
At this point, we have covered three of the four PL/SQL blocks;
anonymous blocks
, procedures and functions. The
forth block, the trigger
will be covered in Chapter 5
Bulk Operations, Packages and
Triggers. Before continuing on to using PL/SQL to interact
with the database, we need to cover PL/SQL’s error handling
capabilities. So what happens when PL/SQL runs into an error? If the
error is caught during compilation, the PL/SQL compiler returns the
error to the submitting application, such as SQL*Plus. If the
code is already compiled and is executing in the database, the PL/SQL
engine will raise an exception.
The above book excerpt is from:
Easy Oracle PL/SQL Programming
Get Started
Fast with Working PL/SQL Code Examples
ISBN 0-9759135-7-3
John Garmany
http://www.rampant-books.com/book_2005_1_easy_plsql.htm
|
