AUTHID for viewers

Question:
Can I use AUTHID_CURRENT_USER for a view if I know we can use it for packages and procedures?
Answer:
Sonal, here are some rules to keep in mind regarding AUTHID and views:
• AUTHID CURRENT_USER is only available for use in PL/SQL program units.
• It cannot be used in the definition of a view.
• The name of a table or a view referred to by a PL/SQL subprogram is resolved at compile-time with authid definer and at run-time with authid current_user.
• A view is effectively always definer's rights. The names to which it refers are resolved at view compile time.
• An invoker's-rights PL/SQL subprogram cannot make a particular view see different things according to the current user. (However, it can see a different view according to the current user.) By extension, the owner of the view needs whatever privileges are required to access the referred objects. The privileges of the invoker on the view's referred objects is never significant.
To test this for yourself, download this zip file and run the script authid_with_view_test_setup.sql to set up schemas and objects. Then run the authid_with_view_test.sql script to see what happens with AUTHID and views.

Auditing failed INSERT statements and their authors

Question:
A particular table has been accessed by different users. I want to audit all the failed INSERT statments and the user who had tried to INSERT into the table. How do I do that?
Answer:
First of all, I encourage to investigate the built-in auditing capabilities of Oracle, such as Fine Grained Auditing (FGA). If you can utilize features already provided by Oracle, why write something yourself?
If you are going to write your own solution, then you should consider implementing BEFORE INSERT and AFTER INSERT triggers that are themselves autonomous transactions. With this combination, you can easily identify an attempt to insert (BEFORE INSERT fired) that did not complete (the corresponding AFTER INSERT trigger was not fired).
Click here to view and run a script I put together to demonstrate the use of these triggers and autonomous transactions to solve your problem

Apply best practices to cursor variables

Question:
I have a stored procedure that uses a REF CURSOR parameter to return a result set as a cursor variable. How can I call that procedure and then insert the rows identified by the cursor variable into a table, using FORALL?
Answer:
This question was answered in the March/April 2006 issue of Oracle Magazine. A cursor variable is a PL/SQL variable that points to a result set. You can fetch the rows of a cursor variable's result set just as you would fetch the rows identified by an explicitly declared cursor. You can in particular use BULK COLLECT against a cursor variable to deposit into a collection all the rows identified by the result set. You can then reference that collection in a FORALL INSERT statement.
Let's take a look at the kind of code you would write to achieve your goal. I will use the jokes and joke_archive tables as my example data structures:
CREATE TABLE jokes (
joke_id INTEGER,
title VARCHAR2(100),
text VARCHAR2(4000)
)
/
CREATE TABLE joke_archive (
archived_on DATE,
old_stuff VARCHAR2(4000)
)
/
I will place two jokes in the jokes table, as shown in Listing 1.
Code Listing 1: Inserting jokes into the jokes table

BEGIN
INSERT INTO jokes
VALUES (100, 'Why does an elephant take a shower?'
,'Why does an elephant take a shower? ' ||
'Because it can't fit into the bathtub!');

INSERT INTO jokes
VALUES (101
,'How can you prevent diseases caused by biting insects?'
,'How can you prevent diseases caused by biting insects?' || 'Don't bite any!');

COMMIT;
END;
I now need to write a procedure that will identify joke text or titles that need to be moved to the joke_archive table. Here is the header of my procedure:
CREATE OR REPLACE
PROCEDURE get_title_or_text (
title_like_in IN VARCHAR2
,return_title_in IN BOOLEAN
,joke_count_out OUT PLS_INTEGER
,jokes_out OUT SYS_REFCURSOR
)
I pass in a string (title_like_in) that acts as a filter to identify the rows in the jokes table to be moved to the archive. I specify whether I want to retrieve titles (return_title_in => TRUE) or text (return_title_in => FALSE). I then return the total number of rows identified by the result set (joke_count_out), as well as the result set itself (joke_out). I use the system-defined weak REF CURSOR type, SYS_REFCURSOR (available in Oracle9i Database Release 2 and later).
Listing 2 contains the implementation of the get_title_or_text procedure.
Code Listing 2: get_title_or_text procedure
1 CREATE OR REPLACE PROCEDURE get_title_or_text (
2 title_like_in IN VARCHAR2
3 ,return_title_in IN BOOLEAN
4 ,joke_count_out OUT PLS_INTEGER
5 ,jokes_out OUT SYS_REFCURSOR
6 )
7 IS
8 c_from_where VARCHAR2 (100) := ' FROM jokes WHERE title LIKE :your_title';
9 l_colname all_tab_columns.column_name%TYPE := 'TEXT';
10 l_query VARCHAR2 (32767);
11 BEGIN
12 IF return_title_in
13 THEN
14 l_colname := 'TITLE';
15 END IF;
16
17 l_query := 'SELECT ' || l_colname || c_from_where;
18
19 OPEN jokes_out FOR l_query USING title_like_in;
20
21 EXECUTE IMMEDIATE 'SELECT COUNT(*)' || c_from_where
22 INTO joke_count_out
23 USING title_like_in;
24 END get_title_or_text;
Here is an explanation of the interesting parts of the get_title_or_text procedure in Listing 2.
Line 8. Because the get_title_or_text procedure is executing two dynamic queries, differing only in the SELECT list, store the rest of those queries (the FROM and WHERE clauses) in a reusable string.
Lines 12-17. Construct the dynamic query to retrieve all titles or text with a matching title.
Line 19. Associate the result set with the cursor variable, for the specified title filter.
Lines 21-23. Compute the number of rows identified by the query.
I can now call this procedure, return a result set, move that data into a collection, and then use the collection in a FORALL statement, as shown in Listing 3.
Code Listing 3: From results to collection to FORALL
1 DECLARE
2 l_count PLS_INTEGER;
3 l_jokes sys_refcursor;
4
5 TYPE jokes_tt IS TABLE OF jokes.text%TYPE;
6
7 l_joke_array jokes_tt := jokes_tt ();
8 BEGIN
9 get_title_or_text (title_like_in => '%insect%'
10 ,return_title_in => FALSE
11 ,joke_count_out => l_count
12 ,jokes_out => l_jokes
13 );
14 DBMS_OUTPUT.put_line ('Number of jokes found = ' || l_count);
15
16 FETCH l_jokes
17 BULK COLLECT INTO l_joke_array;
18
19 CLOSE l_jokes;
20
21 FORALL indx IN l_joke_array.FIRST .. l_joke_array.LAST
22 INSERT INTO joke_archive
23 VALUES (SYSDATE, l_joke_array (indx));
24 END;
Here is an explanation of the interesting parts of the anonymous block in Listing 3.
Lines 2 and 3. Declare variables to retrieve the values returned by the get_title_or_text procedure.
Lines 5-7. Declare a nested table to hold the data identified by the cursor variable.
Lines 8-13. Call the stored procedure to return the cursor variable and the count of rows in the result set.
Lines 16-19. Use BULK COLLECT to fetch all the rows in the result set into the nested table. Then close the cursor variable. Note that I use BULK COLLECT with an implicit SELECT INTO to retrieve all the rows identified by the cursor. If you are querying a large volume of data, this approach can consume an unacceptable amount of memory. Under such circumstances, you may want to switch to using BULK COLLECT with a LIMIT clause.
Lines 21-23. Use a FORALL statement to push the data into the joke archive.
Listing 4 contains a modified version of the previous anonymous block in Listing 3, showing how to use the LIMIT clause to restrict the number of rows fetched with a BULK COLLECT query, thus reducing the amount of memory needed to populate the collection.
Code Listing 4: From results to collection to FORALL plus LIMIT
DECLARE
l_count PLS_INTEGER;
l_jokes sys_refcursor;

TYPE jokes_tt IS TABLE OF jokes.text%TYPE;

l_joke_array jokes_tt := jokes_tt ();
BEGIN
get_title_or_text (title_like_in => '%insect%'
,return_title_in => FALSE
,joke_count_out => l_count
,jokes_out => l_jokes
);
DBMS_OUTPUT.put_line ('Number of jokes found = ' || l_count);

LOOP
-- Fetch the next 100 rows.
FETCH l_jokes
BULK COLLECT INTO l_joke_array LIMIT 100;

EXIT WHEN l_joke_array.COUNT = 0;

-- Push them into the archive.
FORALL indx IN l_joke_array.FIRST .. l_joke_array.LAST
INSERT INTO joke_archive
VALUES (SYSDATE, l_joke_array (indx));
END LOOP;

CLOSE l_jokes;
END;
This answer demonstrates how you can take advantage of the flexibility of cursor variables to construct your queries at runtime without losing the performance benefits of the BULK COLLECT and FORALL features.

An Error FORALL

Question:
We have been just amazed at how much better our programs perform when we use FORALL to do our inserts and updates. We are now building a new application on Oracle Database 10g Release 2, and we have run into a problem. In all previous usages of FORALL, we would generally take a collection that was populated with a BULK COLLECT and push it into one or more tables. Now we have a more complicated scenario, in which we must go through our collection of "candidate" data for inserts and remove some (perhaps all) of the rows before doing the insert. When we try to use FORALL, we get this error message: ORA-22160: element at index [2750] does not exist How can we avoid this error and get all our data inserted?
Answer:
I agree that FORALL is wonderful—one of the most important enhancements to PL/SQL since Oracle8i was released. And back in Oracle8i and Oracle9i Database, it is true that the only format with which you could use FORALL was this:
FORALL index_variable
IN low_value .. high_value
<DML_Statement>;
And as in a "regular" numeric FOR loop, FORALL will iterate through each integer between low_value and high_value, using that integer to identify an element in all collections that are bound into the DML statement with the index_variable. If no element exists at a particular index value, Oracle Database raises an exception, as you can see in the example in Listing 3.
Code Listing 3: Raising ORA-22160
SQL> DECLARE
2 TYPE list_of_names_t IS TABLE OF VARCHAR2 (32767)
3 INDEX BY PLS_INTEGER;
4
5 happyfamily list_of_names_t;
6 BEGIN
7 happyfamily (1) := 'Eli';
8 happyfamily (2) := 'Chris';
9 happyfamily (3) := 'Veva';
10 happyfamily (5) := 'Steven';
11 FORALL indx IN happyfamily.FIRST .. happyfamily.LAST
12 INSERT INTO first_names
13 VALUES (happyfamily (indx));
14 END;
15 /
DECLARE
*
ERROR at line 1:
ORA-22160: element at index [4] does not exist
FORALL, in other words, requires a sequentially or densely filled collection. Now if you were still running Oracle8i or Oracle9i Database and wanted to fix this problem, you would have to copy the data from your sparsely filled collection over to one without any gaps. From a performance standpoint, this is nothing to worry about; manipulating collections is very fast. But it does involve writing and maintaining even more code.
In Oracle Database 10g, Oracle added two new clauses to the FORALL statement: INDICES OF and VALUES OF. They allow you to avoid the restriction on using densely filled collections. Instead of using an IN range of values, you can point to a collection (usually, but not necessarily, the same collection that is referenced inside the FORALL's DML statement) and say, in effect, "Use only those index values defined in that other collection" (INDICES OF) or "Use only index values that are found in the elements of that other collection" (VALUES OF).
Here is a rewrite of the code in Listing 3 that avoids the ORA-22160 error (notice the boldfaced lines):
DECLARE
TYPE list_of_names_t
IS TABLE OF VARCHAR2 (32767)
INDEX BY PLS_INTEGER;

happyfamily list_of_names_t;
BEGIN
happyfamily (1) := 'Eli';
happyfamily (2) := 'Chris';
happyfamily (3) := 'Veva';
happyfamily (5) := 'Steven';

FORALL indx
IN INDICES OF happyfamily
INSERT INTO first_names
VALUES (happyfamily (indx));
END;
/
That is an example of the simplest way to apply INDICES OF: "self- reference" the same collection used within the DML statement, to easily avoid errors due to sparseness in that collection.
Now let's take a look at VALUES OF. This clause comes in very handy when you want to use only a subset of the collection to be used within the DML statement.
Suppose, for example, that I have a procedure that accepts a collection of employee records and should insert only records for employees with a salary of $10,000 or more. Listing 4 contains the package specification and the body for this employees_dml program.
Code Listing 4: Package and body for employees_dml
CREATE OR REPLACE PACKAGE employees_dml
IS
TYPE employees_aat IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;

PROCEDURE insert_some (employees_in IN employees_aat);
END employees_dml;
/

SQL>CREATE OR REPLACE PACKAGE BODY employees_dml
2 IS
3 PROCEDURE insert_some (employees_in IN employees_aat)
4 IS
5 TYPE index_values_aat IS TABLE OF PLS_INTEGER
6 INDEX BY PLS_INTEGER;
7
8 l_values_of index_values_aat;
9 l_index PLS_INTEGER;
10 BEGIN
11 -- Only insert those employees with a salary >= 10000.
12 l_index := employees_in.FIRST;
13
14 WHILE (l_index IS NOT NULL)
15 LOOP
16 IF employees_in (l_index).salary >= 10000
17 THEN
18 l_values_of (l_values_of.COUNT + 1) := l_index;
19 END IF;
20
21 l_index := employees_in.NEXT (l_index);
22 END LOOP;
23
24 FORALL indx IN VALUES OF l_values_of
25 INSERT INTO employees
26 VALUES employees_in (indx);
27 END insert_some;
28 END employees_dml;
Lines 5 through 9 in Listing 4 declare the VALUES OF collection, a collection of PLS_INTEGER values. Then in my WHILE loop (lines 14 through 22), I populate a row in l_values_of with the index value from employees_in, only if the salary in that record is at least $10,000.
Thus, when I get to the FORALL statement (lines 24 through 26), the VALUES OF clause ensures that all other employee records are ignored.
If you have the standard Oracle employees table installed with default data, you can run the script in Listing 5 to verify the behavior of the employees_dml package.
Code Listing 5: Verifying behavior of employees_dml package
SELECT COUNT(*)
FROM employees
WHERE salary < 10000
/

DECLARE
l_employees employees_dml.employees_aat;
BEGIN
SELECT *
BULK COLLECT INTO l_employees
FROM employees;

DELETE FROM employees;

employees_dml.insert_some (l_employees);
END;
/

SELECT COUNT(*)
FROM employees
WHERE salary < 10000
/

ROLLBACK
/

COUNT(*)
-------------------------
88

1 row selected.
PL/SQL procedure successfully completed.

COUNT(*)
-------------------------
0

1 row selected.
Rollback complete.
Finally, you can also use INDICES OF with an entirely different collection that serves as a kind of filter for the collections used in the DML statement.
Listing 6 shows an example of this approach.
Code Listing 6: Using INDICES OF as a filter
SQL> DECLARE
2 TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
3 INDEX BY PLS_INTEGER;
4
5 l_employees employee_aat;
6
7 TYPE boolean_aat IS TABLE OF Boolean
8 INDEX BY PLS_INTEGER;
9
10 l_employee_indices boolean_aat;
11 BEGIN
12 l_employees (1) := 137;
13 l_employees (100) := 126;
14 l_employees (500) := 147;
15 --
16 l_employee_indices (1) := false;
17 l_employee_indices (500) := TRUE;
18 l_employee_indices (799) := null;
19 --
20 FORALL l_index IN INDICES OF l_employee_indices
21 BETWEEN 1 AND 500
22 UPDATE employees
23 SET salary = 10000
24 WHERE employee_id = l_employees (l_index);
25 END;
In this code, I use the index values of defined elements in the l_employee_indices collection to specify which elements of the l_employees collection to use in my update statement. Note that on line 21 of Listing 6, I include a BETWEEN clause to constrain which of the index values of l_employee_indices will be used. So INDICES OF should fix your problem in this case.