Finding Your Source Code
If you’re writing “stored code” from SQL*Plus and switching back and forth between the
editor and the SQL*Plus prompt, you run the risk of losing your source code after you compile
it to the database. For example: you create a function to return which of two site codes has
the fewer instructors. You return to SQL*Plus from the editor and try to compile the code, and
get the encouraging result:
SQL> /
Function created.
SQL>
Then, before testing, you decide to look at the table with a quick query:
SQL> select * from instructor where home_site_code = 'SEA';
…
7 rows selected.
You decide to further modify your code, so you pop back to the editor with
SQL>edit
And up pops: NOTHING!!!
Ahhhhh!!! What happened to my source code?
Two things:
1. Always save your source code in an operating system file. Always.
2. If you forget (1), there’s a backup plan. You can retrieve all the source code that has been
compiled to the database through the user_source table. Here’s how:
SQL> desc user_source
Name Null? Type
----------------------------------------- -------- -----------------
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
SQL> set serveroutput on
SQL> set heading off
SQL> set feedback off
SQL> select text
from user_source
order by name, line ;
If there were only one set of source code in there, you would see something like this. In this
example, there is an object named 'hurtin' in the current schema:
…
function hurtin(
site_1 instructor.home_site_code%type,
site_2 instructor.home_site_code%type)
return instructor.home_site_code%type IS
first number:=0;
second number:=0;
site instructor.home_site_code%type; --return variable
BEGIN
select count(*)
into first
from instructor
where home_site_code = site_1;
select count(*)
into second
from instructor
where home_site_code = site_2;
if first <= second then
site := site_1;
dbms_output.put_line(site_1 ||' has fewer instructors');
return site;
end if;
site := site_2;
dbms_output.put_line(site_2 || ' has fewer instructors');
return site;
END;
269 rows selected.
And now you have your source code back, plus all the source code for every other procedure/function/trigger/etc.
ever compiled to the database.
You can select a specific function, procedure, trigger, or package by getting creative with the “name”and “type” columns
in your query.
You might want to spool this to a file, instead of having it dump to the screen, because in a
production environment this could take awhile…
Cursors --- foiled again!
(Hat tip to Snidely Whiplash). An Oracle cursor is a very powerful feature, but those who move
from SQL*Plus and similar 'batch' oriented tools to program units need to make adjustments. Here,
I take a cursor, run it in a block, then migrate it to a program. The differences are few, but
significant.
A cursor within an Anonymous Block
Anonymous Blocks are blocks (‘batches') of instructions which are sent as one unit to the server.
They are identified by DECLARE statements, (only needed if you are setting up variables or cursors)
and the mandatory BEGIN / END commands, to ‘contain' the instructions which are sent to the server.
Multi-line operations such as the one illustrated below cannot run outside of a block. (Blocks
can also be given a name, in which case they are stored as procedures, functions, or triggers,
on the server).
SQL> create table frequent_students
2 ( student_sid number(3) PRIMARY KEY,
3 last_name varchar2(20) NOT NULL,
4 first_name varchar2(20) NOT NULL,
5 change_date date DEFAULT SYSDATE );
Table created.
DECLARE
v_student_sid NUMBER(3);
v_fname VARCHAR2(20);
v_lname VARCHAR2(20);
v_count NUMBER;
CURSOR student_cursor IS
SELECT student_sid, first_name, last_name
FROM student;
BEGIN
OPEN student_cursor ;
-- This puts the 'pointer' at top of 'file' but above first 'record'
LOOP
FETCH student_cursor INTO v_student_sid, v_fname, v_lname;
EXIT WHEN student_cursor %NOTFOUND;
-- Until we run out of rows in the cursor:
SELECT COUNT(*)
INTO v_count
FROM student_class_schedule
WHERE student_sid = v_student_sid;
IF v_count > 3 THEN
INSERT INTO frequent_students ( student_sid, last_name, first_name )
VALUES ( v_student_sid, v_lname, v_fname );
END IF;
END LOOP;
CLOSE student_cursor ;
COMMIT;
END;
PL/SQL procedure successfully completed.
SQL> select count(*) from frequent_students;
COUNT(*)
---------- 50
A Cursor inside a Stored Procedure
Anonymous blocks are OK, but work which is done more than once should be put into a stored procedure
or function, for a number of reasons. This is only slightly different from the anonymous block,
but will typically execute much faster. This execution speed is a consideration because of the
fact that DML is being run from this procedure, and from the block, and locks are held on the
table until a COMMIT/ROLLBACK.
1 CREATE OR REPLACE PROCEDURE freq_students (These two lines are new)
2 IS
3 DECLARE v_student_sid NUMBER(3); -The DECLARE will cause an error
4 v_fname VARCHAR2(20);
5 v_lname VARCHAR2(20);
6 v_count NUMBER;
7 CURSOR student_cursor IS
8 SELECT student_sid, first_name, last_name
9 FROM student;
10 BEGIN
11 OPEN student_cursor;
12 -- This puts the 'pointer' at top of 'file' but above first 'record'
13 LOOP
14 FETCH student_cursor INTO v_student_sid, v_fname, v_lname;
15 EXIT WHEN student_cursor%NOTFOUND;
16 -- Until we run out of rows in the cursor:
17 SELECT COUNT(*)
18 INTO v_count
19 FROM student_class_schedule
20 WHERE student_sid = v_student_sid;
21 IF v_count > 3 THEN
22 INSERT INTO frequent_students ( student_sid, last_name, first_name )
23 VALUES ( v_student_sid, v_lname, v_fname );
24 END IF;
25 END LOOP;
26 CLOSE student_cursor;
27 COMMIT;
28* END
SQL> /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE FREQ_STUDENTS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one of
the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cursor
form current external language
The symbol "begin" was substituted for "DECLARE" to continue.
1 CREATE OR REPLACE PROCEDURE freq_students
2 IS
3 v_student_sid NUMBER(3);
4 v_fname VARCHAR2(20);
5 v_lname VARCHAR2(20);
6 v_count NUMBER;
7 CURSOR student_cursor IS
8 SELECT student_sid, first_name, last_name
9 FROM student;
10 BEGIN
11 OPEN student_cursor ;
12 -- This puts the 'pointer' at top of 'file' but above first 'record'
13 LOOP
14 FETCH student_cursor INTO v_student_sid, v_fname, v_lname;
15 EXIT WHEN student_cursor %NOTFOUND;
16 -- Until we run out of rows in the cursor:
17 SELECT COUNT(*)
18 INTO v_count
19 FROM student_class_schedule
20 WHERE student_sid = v_student_sid;
21 IF v_count > 3 THEN
22 INSERT INTO frequent_students ( student_sid, last_name, first_name )
23 VALUES ( v_student_sid, v_lname, v_fname );
24 END IF;
25 END LOOP;
26 CLOSE student_cursor ;
27 COMMIT;
28* END;
SQL> /
Procedure created.
SQL> truncate table frequent_students;
Table truncated.
SQL> exec freq_students;
PL/SQL procedure successfully completed.
SQL> select count(*) from frequent_students;
COUNT(*)
----------
50
SQL> spool off
Permissions Tour
A question came up about 'How can I find out which permissions were GRANTed to me?' Although
I did not have the answer at the tips of my fingers, I knew how to approach answering the question,
so let me share this approach with you.
Step 1:
Interrogate the 'Mother of all Views', in the Data Dictionary, for likely names of relevant views:
SQL> SELECT VIEW_NAME
FROM ALL_VIEWS
WHERE VIEW_NAME LIKE '%PRIV%'
-- This is a 'contained within' substring search; ( I figured that PRIV, for ‘privileges’ would
be a good bet )
VIEW_NAME
------------------------------
ALL_COL_PRIVS
ALL_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD
ALL_REPGROUP_PRIVILEGES
ALL_TAB_PRIVS
ALL_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD
COLUMN_PRIVILEGES
QUEUE_PRIVILEGES
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
SESSION_PRIVS
TABLE_PRIVILEGES
USER_COL_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
USER_REPGROUP_PRIVILEGES
USER_ROLE_PRIVS
USER_RSRC_CONSUMER_GROUP_PRIVS
USER_RSRC_MANAGER_SYSTEM_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD
PRODUCT_PRIVS
26 rows selected.
Step 2:
I then ran a series of DESCribe operations to get a sense of the contents of those views, and
also ran COUNT operations on them. The results are shown next, for those views which proved to
have contents; (a lot did not have contents, so I ignored them).
Step 3:
I then ran queries on those views, and got results which were useful.