root@USBLDMD1012:~# date
Thursday, January 26, 2017 03:54:10 PM COT
root@USBLDMD1012:~# date -u
Thursday, January 26, 2017 08:54:14 PM GMT
srvctl setenv database -d ORCL -t "TZ=America/Bogota"
srvctl setenv database -d ACME -t "TZ=America/New_york"
srvctl setenv listener -l listener -t "TZ=America/Bogota"
export TZ=America/Bogota
select * from nls_database_parameters
lunes, 23 de octubre de 2017
jueves, 12 de octubre de 2017
martes, 3 de octubre de 2017
UTL_CALL_STACK
UTL_CALL_STACK : Get Detailed Information About the Currently Running Subprogram in Oracle Database 12c Release 1 (12.1)
Oracle database 12c introduced theUTL_CALL_STACK package to allow programmatic access to the call stack and error stack, giving much greater flexibility for debugging and error handling of PL/SQL code. This is only a replacement for the existing functionality if you need the extra level of control. The existing functionality in the DBMS_UTILITY package is still available and has not been deprecated.Call Stack
The call stack allows you to identify exactly where you are in the currently running code, which includes information about nesting of subprogram calls. In previous releases this information was displayed using theDBMS_UTILITY.FORMAT_CALL_STACK function, as shown below.-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_call_stack AS
BEGIN
DBMS_OUTPUT.put_line('***** Call Stack Start *****');
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
DBMS_OUTPUT.put_line('***** Call Stack End *****');
END;
/
-- Test package to show a nested call.
CREATE OR REPLACE PACKAGE test_pkg AS
PROCEDURE proc_1;
PROCEDURE proc_2;
PROCEDURE proc_3;
END;
/
CREATE OR REPLACE PACKAGE BODY test_pkg AS
PROCEDURE proc_1 AS
BEGIN
proc_2;
END;
PROCEDURE proc_2 AS
BEGIN
proc_3;
END;
PROCEDURE proc_3 AS
BEGIN
display_call_stack;
END;
END;
/
-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Call Stack Start *****
----- PL/SQL Call Stack -----
object line object
handle number
name
0xb6d4ac18 4 procedure TEST.DISPLAY_CALL_STACK
0xb6d14298
15 package body TEST.TEST_PKG
0xb6d14298 10 package body
TEST.TEST_PKG
0xb6d14298 5 package body TEST.TEST_PKG
0xb99fe7c8
1 anonymous block
***** Call Stack End *****
PL/SQL procedure successfully completed.
SQL>
As you can see, the output from the DBMS_UTILITY.FORMAT_CALL_STACK function is rather ugly and we have no control over it, other than to manually parse it.The
UTL_CALL_STACK package contains APIs to display the contents of the call stack in a more readable form.DYNAMIC_DEPTH: The number of subprograms on the call stack, starting at the current position in the call stack, to the initial call.LEXICAL_DEPTH: Lexical depth of the subprogram within the current call.UNIT_LINE: Line number in the subprogram of the current call.SUBPROGRAM: Subprogram name associated with the current call.CONCATENATE_SUBPROGRAM: Returns theUNIT.SUBPROGRAM.LOCAL_SUBPROGRAMform of the subprogram name.OWNER: The owner of the subprogram associated with the current call.CURRENT_EDITION: The edition of the subprogram associated with the current call.
DISPLAY_CALL_STACK procedure to use the UTL_CALL_STACK package, then re-runs the test.-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_call_stack AS
l_depth PLS_INTEGER;
BEGIN
l_depth := UTL_CALL_STACK.dynamic_depth;
DBMS_OUTPUT.put_line('***** Call Stack Start *****');
DBMS_OUTPUT.put_line('Depth Lexical Line Owner Edition Name');
DBMS_OUTPUT.put_line('. Depth Number');
DBMS_OUTPUT.put_line('--------- --------- --------- --------- --------- --------------------');
FOR i IN 1 .. l_depth LOOP
DBMS_OUTPUT.put_line(
RPAD(i, 10) ||
RPAD(UTL_CALL_STACK.lexical_depth(i), 10) ||
RPAD(TO_CHAR(UTL_CALL_STACK.unit_line(i),'99'), 10) ||
RPAD(NVL(UTL_CALL_STACK.owner(i),' '), 10) ||
RPAD(NVL(UTL_CALL_STACK.current_edition(i),' '), 10) ||
UTL_CALL_STACK.concatenate_subprogram(UTL_CALL_STACK.subprogram(i))
);
END LOOP;
DBMS_OUTPUT.put_line('***** Call Stack End *****');
END;
/
-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Call Stack Start *****
Depth Lexical Line Owner Edition Name
. Depth Number
--------- --------- --------- --------- --------- --------------------
1 0 13 TEST DISPLAY_CALL_STACK
2 1 15 TEST TEST_PKG.PROC_3
3 1 10 TEST TEST_PKG.PROC_2
4 1 5 TEST TEST_PKG.PROC_1
5 0 1 __anonymous_block
***** Call Stack End *****
PL/SQL procedure successfully completed.
SQL>
Starting with the call to DISPLAY_CALL_STACK, we can work back through all the nested calls to the original anonymous block. The output includes the procedure names in the package as well as the associated line numbers of the calls. If we wanted to, we could have displayed the output in reverse order, starting at the top-level call.-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_call_stack AS
l_depth PLS_INTEGER;
BEGIN
l_depth := UTL_CALL_STACK.dynamic_depth;
DBMS_OUTPUT.put_line('***** Call Stack Start *****');
DBMS_OUTPUT.put_line('Depth Lexical Line Owner Edition Name');
DBMS_OUTPUT.put_line('. Depth Number');
DBMS_OUTPUT.put_line('--------- --------- --------- --------- --------- --------------------');
FOR i IN REVERSE 1 .. l_depth LOOP
DBMS_OUTPUT.put_line(
RPAD(i, 10) ||
RPAD(UTL_CALL_STACK.lexical_depth(i), 10) ||
RPAD(TO_CHAR(UTL_CALL_STACK.unit_line(i),'99'), 10) ||
RPAD(NVL(UTL_CALL_STACK.owner(i),' '), 10) ||
RPAD(NVL(UTL_CALL_STACK.current_edition(i),' '), 10) ||
UTL_CALL_STACK.concatenate_subprogram(UTL_CALL_STACK.subprogram(i))
);
END LOOP;
DBMS_OUTPUT.put_line('***** Call Stack End *****');
END;
/
-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Call Stack Start *****
Depth Lexical Line Owner Edition Name
. Depth Number
--------- --------- --------- --------- --------- --------------------
5 0 1 __anonymous_block
4 1 5 TEST TEST_PKG.PROC_1
3 1 10 TEST TEST_PKG.PROC_2
2 1 15 TEST TEST_PKG.PROC_3
1 0 13 TEST DISPLAY_CALL_STACK
***** Call Stack End *****
PL/SQL procedure successfully completed.
SQL>
You now have programmatic control to interrogate and display the call stack if you need to.Error Stack
Exceptions are often handled by exception handlers and re-raised. In some cases, exceptions in nested calls result in different errors being produced by the error handler of the calling routine. The error stack allows you to display chains of errors, making it easier to determine the real cause of the issue. In previous releases this information was displayed using theDBMS_UTILITY.FORMAT_ERROR_STACK function, as shown below.-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_error_stack AS
BEGIN
DBMS_OUTPUT.put_line('***** Error Stack Start *****');
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line('***** Error Stack End *****');
END;
/
-- Test package to show a nested call.
CREATE OR REPLACE PACKAGE test_pkg AS
PROCEDURE proc_1;
PROCEDURE proc_2;
PROCEDURE proc_3;
END;
/
CREATE OR REPLACE PACKAGE BODY test_pkg AS
PROCEDURE proc_1 AS
BEGIN
proc_2;
EXCEPTION
WHEN OTHERS THEN
display_error_stack;
END;
PROCEDURE proc_2 AS
BEGIN
proc_3;
EXCEPTION
WHEN OTHERS THEN
RAISE DUP_VAL_ON_INDEX;
END;
PROCEDURE proc_3 AS
BEGIN
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN OTHERS THEN
RAISE TOO_MANY_ROWS;
END;
END;
/
-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Error Stack Start *****
ORA-00001: unique constraint (.) violated
ORA-06512: at "TEST.TEST_PKG", line
16
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512:
at "TEST.TEST_PKG", line 24
ORA-01403: no data found
***** Error Stack End *****
PL/SQL procedure successfully completed.
SQL>
The output from DBMS_UTILITY.FORMAT_ERROR_STACK function is fine, but there are occasional formatting errors and we can't order the output to suit our taste.The
UTL_CALL_STACK package contains APIs to display the contents of the error stack.ERROR_DEPTH: The number of errors on the error stack.ERROR_MSG: The error message associated with the current line in the error stack.ERROR_NUMBER: The error number associated with the current line in the error stack.
DISPLAY_ERROR_STACK procedure to use the UTL_CALL_STACK package, then re-runs the test.-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_error_stack AS
l_depth PLS_INTEGER;
BEGIN
l_depth := UTL_CALL_STACK.error_depth;
DBMS_OUTPUT.put_line('***** Error Stack Start *****');
DBMS_OUTPUT.put_line('Depth Error Error');
DBMS_OUTPUT.put_line('. Code Message');
DBMS_OUTPUT.put_line('--------- --------- --------------------');
FOR i IN 1 .. l_depth LOOP
DBMS_OUTPUT.put_line(
RPAD(i, 10) ||
RPAD('ORA-' || LPAD(UTL_CALL_STACK.error_number(i), 5, '0'), 10) ||
UTL_CALL_STACK.error_msg(i)
);
END LOOP;
DBMS_OUTPUT.put_line('***** Error Stack End *****');
END;
/
-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Error Stack Start *****
Depth Error Error
. Code Message
--------- --------- --------------------
1 ORA-00001 unique constraint (.) violated
2 ORA-06512 at "TEST.TEST_PKG", line 16
3 ORA-01422 exact fetch returns more than requested number of rows
4 ORA-06512 at "TEST.TEST_PKG", line 24
5 ORA-01403 no data found
***** Error Stack End *****
PL/SQL procedure successfully completed.
SQL>
In the previous example, the display order matches the DBMS_UTILITY.FORMAT_ERROR_STACK output, which reports last to first in the chain. We could easily reverse it to display first to last.-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_error_stack AS
l_depth PLS_INTEGER;
BEGIN
l_depth := UTL_CALL_STACK.error_depth;
DBMS_OUTPUT.put_line('***** Error Stack Start *****');
DBMS_OUTPUT.put_line('Depth Error Error');
DBMS_OUTPUT.put_line('. Code Message');
DBMS_OUTPUT.put_line('--------- --------- --------------------');
FOR i IN REVERSE 1 .. l_depth LOOP
DBMS_OUTPUT.put_line(
RPAD(i, 10) ||
RPAD('ORA-' || LPAD(UTL_CALL_STACK.error_number(i), 5, '0'), 10) ||
UTL_CALL_STACK.error_msg(i)
);
END LOOP;
DBMS_OUTPUT.put_line('***** Error Stack End *****');
END;
/
-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Error Stack Start *****
Depth Error Error
. Code Message
--------- --------- --------------------
5 ORA-01403 no data found
4 ORA-06512 at "TEST.TEST_PKG", line 24
3 ORA-01422 exact fetch returns more than requested number of rows
2 ORA-06512 at "TEST.TEST_PKG", line 16
1 ORA-00001 unique constraint (.) violated
***** Error Stack End *****
PL/SQL procedure successfully completed.
SQL>
Backtrace
Backtrace shows a walk through the call stack from the line where the exception was raised, to the last call before the exception was trapped. This shows the propagation of the exception, which allows you to identify the actual line that caused the problem. In previous releases this information was displayed using theDBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, as shown below.-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_backtrace AS
BEGIN
DBMS_OUTPUT.put_line('***** Backtrace Start *****');
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace);
DBMS_OUTPUT.put_line('***** Backtrace End *****');
END;
/
-- Test package to show a nested call.
CREATE OR REPLACE PACKAGE test_pkg AS
PROCEDURE proc_1;
PROCEDURE proc_2;
PROCEDURE proc_3;
END;
/
CREATE OR REPLACE PACKAGE BODY test_pkg AS
PROCEDURE proc_1 AS
BEGIN
proc_2;
EXCEPTION
WHEN OTHERS THEN
display_backtrace;
END;
PROCEDURE proc_2 AS
BEGIN
proc_3;
END;
PROCEDURE proc_3 AS
BEGIN
RAISE NO_DATA_FOUND;
END;
END;
/
-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Backtrace Start *****
ORA-06512: at "TEST.TEST_PKG", line 18
ORA-06512: at "TEST.TEST_PKG", line
13
ORA-06512: at "TEST.TEST_PKG", line 5
***** Backtrace End *****
PL/SQL procedure successfully completed.
SQL>
With the exception of some minor formatting issues, this output is fine and will probably be OK for most situations.The
UTL_CALL_STACK package contains APIs to display the backtrace.BACKTRACE_DEPTH: The number of backtrace messages on the error stack.BACKTRACE_LINE: Line number in the subprogram of the current call.BACKTRACE_UNIT: Subprogram name associated with the current call.
DISPLAY_BACKTRACE procedure to use the UTL_CALL_STACK package, then re-runs the test.-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_backtrace AS
l_depth PLS_INTEGER;
BEGIN
l_depth := UTL_CALL_STACK.backtrace_depth;
DBMS_OUTPUT.put_line('***** Backtrace Start *****');
DBMS_OUTPUT.put_line('Depth BTrace BTrace');
DBMS_OUTPUT.put_line('. Line Unit');
DBMS_OUTPUT.put_line('--------- --------- --------------------');
FOR i IN 1 .. l_depth LOOP
DBMS_OUTPUT.put_line(
RPAD(i, 10) ||
RPAD(TO_CHAR(UTL_CALL_STACK.backtrace_line(i),'99'), 10) ||
UTL_CALL_STACK.backtrace_unit(i)
);
END LOOP;
DBMS_OUTPUT.put_line('***** Backtrace End *****');
END;
/
-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Backtrace Start *****
Depth BTrace BTrace
. Line Unit
--------- --------- --------------------
1 5 TEST.TEST_PKG
2 13 TEST.TEST_PKG
3 18 TEST.TEST_PKG
***** Backtrace End *****
PL/SQL procedure successfully completed.
SQL>
There is very little you can do with the backtrace, other than reordering it. The "ORA-06512" error is not included, but this is implied because it is a backtrace message. The following example shows the backtrace in reverse order.-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_backtrace AS
l_depth PLS_INTEGER;
BEGIN
l_depth := UTL_CALL_STACK.backtrace_depth;
DBMS_OUTPUT.put_line('***** Backtrace Start *****');
DBMS_OUTPUT.put_line('Depth BTrace BTrace');
DBMS_OUTPUT.put_line('. Line Unit');
DBMS_OUTPUT.put_line('--------- --------- --------------------');
FOR i IN REVERSE 1 .. l_depth LOOP
DBMS_OUTPUT.put_line(
RPAD(i, 10) ||
RPAD(TO_CHAR(UTL_CALL_STACK.backtrace_line(i),'99'), 10) ||
UTL_CALL_STACK.backtrace_unit(i)
);
END LOOP;
DBMS_OUTPUT.put_line('***** Backtrace End *****');
END;
/
-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Backtrace Start *****
Depth BTrace BTrace
. Line Unit
--------- --------- --------------------
3 18 TEST.TEST_PKG
2 13 TEST.TEST_PKG
1 5 TEST.TEST_PKG
***** Backtrace End *****
PL/SQL procedure successfully completed.
SQL>
DBMS_UTILITY.EXPAND_SQL_TEXT
DBMS_UTILITY.EXPAND_SQL_TEXT : Expand SQL References to Views in Oracle Database 12c Release 1 (12.1)
Views can be a useful way to hide complexity from developers, but that can in turn cause problems. It's easy to write apparently simple statements, that result in extremely complex SQL being sent to the server. TheDBMS_UTILITY.EXPAND_SQL_TEXT procedure expands references to views, turning them into subqueries in the original statement. A simple example of this is shown below.Create a view containing a join.
CONN scott/tiger@pdb1
CREATE OR REPLACE VIEW emp_v AS
SELECT e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno,
d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno;
The view has hidden the complexity of the join, allowing us to use an extremely simple query.SELECT * FROM emp_v;We can see the real SQL statement processed by the server by expanding the statement.
SET SERVEROUTPUT ON
DECLARE
l_clob CLOB;
BEGIN
DBMS_UTILITY.expand_sql_text (
input_sql_text => 'SELECT * FROM emp_v',
output_sql_text => l_clob
);
DBMS_OUTPUT.put_line(l_clob);
END;
/
SELECT "A1"."EMPNO" "EMPNO",
"A1"."ENAME" "ENAME",
"A1"."JOB" "JOB","A1"."MGR" "MGR",
"A1"."HIREDATE" "HIREDATE",
"A1"."SAL" "SAL",
"A1"."COMM" "COMM",
"A1"."DEPTNO" "DEPTNO",
"A1"."DNAME" "DNAME"
FROM (SELECT "A2"."EMPNO_0" "EMPNO",
"A2"."ENAME_1" "ENAME",
"A2"."JOB_2" "JOB",
"A2"."MGR_3" "MGR",
"A2"."HIREDATE_4" "HIREDATE",
"A2"."SAL_5" "SAL",
"A2"."COMM_6" COMM",
"A2"."QCSJ_C000000000400000_7" "DEPTNO",
"A2"."DNAME_9" "DNAME"
FROM (SELECT "A4"."EMPNO" "EMPNO_0",
"A4"."ENAME" "ENAME_1",
"A4"."JOB" "JOB_2",
"A4"."MGR" "MGR_3",
"A4"."HIREDATE" "HIREDATE_4",
"A4"."SAL" "SAL_5",
"A4"."COMM" "COMM_6",
"A4"."DEPTNO" "QCSJ_C000000000400000_7",
"A3"."DEPTNO" "QCSJ_C000000000400001",
"A3"."DNAME" "DNAME_9"
FROM SCOTT."EMP" "A4",
SCOTT."DEPT" "A3"
WHERE "A4"."DEPTNO"="A3"."DEPTNO") "A2"
) "A1"
PL/SQL procedure successfully completed.
SQL>
sábado, 30 de septiembre de 2017
AUTHID CURRENT (USER - DEFINER)
Specify
CURRENT_USER to indicate that the package executes with the privileges of CURRENT_USER. This clause creates an invoker's rights package.This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of
CURRENT_USER. External names in all other statements resolve in the schema in which the package resides.AUTHID DEFINER
Specify
DEFINER to indicate that the package executes with the privileges of the owner of the schema in which the package resides and that external names resolve in the schema where the package resides. This is the default and creates a definer's rights package.WARNING: Writing PL/SQL code with the default authid definer, can facilitate SQL injection attacks, because an intruder would get privileges that they would not get if they used authid current_user.
PL/SQL Function Result Cache
On the PL/SQL Function Result Cache
By Steven Feuerstein
Best practices—and preparation—for PL/SQL in Oracle Database 11g
I have been reading that Oracle is launching the 11th release of its database. Very exciting! But here's the problem: I don't think I'll be able to use it for another two years. So why should I even care about the new PL/SQL features of this future (for me) release?
Ah, yes, the real world. Oracle comes out with a new database release, and by the time it does so, its developers are already focused on the next new release. People like me start writing about, demonstrating, and even training on the newer release. And then there's almost everybody else: still on older releases, hoping and praying that someday maybe their management will see fit to catch up.
I feel your pain.
Having said that, I do think it makes an awful lot of sense to learn now about what Oracle Database 11g will have to offer you and your company in the future. The reason is very simple: once you see what is going to be available in Oracle Database 11g, you will probably change the way you write your code now !
I would say that the single most important new PL/SQL feature in Oracle Database 11g is thePL/SQL function result cache . Quite a mouthful, but then it is quite a feature.
I offer in this answer a quick overview of this feature, and I conclude by discussing how knowing about this feature should affect the way you write PL/SQL programs for earlier Oracle Database releases.
Suppose I am on a team that is building a human resources application. The employees table is one of the key structures, holding all the data for all the employees. Hundreds of users execute numerous programs in the application that read from this table—and read from it very often. Yet the table changes relatively infrequently, perhaps once or twice an hour. As a result, the application code repeatedly retrieves from the block buffer cache what is mostly static data, enduring the overhead of checking to see if the particular query has already been parsed, finding the data in the buffer, and returning it.
The team needs to improve the performance of querying data from the employees table. Currently, we use the following function to return a row from the employees table:
FUNCTION one_employee (employee_id_in
IN employees.employee_id%TYPE)
RETURN employees%ROWTYPE
IS
l_employee employees%ROWTYPE;
BEGIN
SELECT *
INTO l_employee
FROM employees
WHERE employee_id = employee_id_in;
RETURN l_employee;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
/* Return an empty record. */
RETURN l_employee;
END one_employee;
In Oracle Database 11g, however, we can add a line to the header of this function as follows:
FUNCTION one_employee (employee_id_in
IN employees.employee_id%TYPE)
RETURN employees%ROWTYPE
RESULT_CACHE RELIES_ON (employees)
IS
l_employee employees%ROWTYPE;
BEGIN
.
.
.
This RESULT_CACHE clause tells Oracle Database that it should remember (store in a special in-memory result cache) each record retrieved for a specific employee ID number. And when a session executes this function and passes in an employee ID that was previously stored, the PL/SQL runtime engine will not execute the function body, which includes that query.
Instead, it will simply retrieve the record from the cache and return that data immediately. The result is much faster retrieval.
In addition, by specifying RELIES_ON (employees), we inform Oracle Database that if any session commits changes to that table, any data in the result cache drawn from the table must be invalidated. The next call to the one_employee function would then have to execute the query and retrieve the data fresh from the table.
Because the cache is a part of the System Global Area (SGA), its contents are available to all sessions connected to the instance. Furthermore, Oracle Database will apply its "least recently used algorithm" to the cache, to ensure that the most recently accessed data will be preserved in the cache.
Prior to Oracle Database 11g, a similar kind of caching was possible with package-level collections, but this cache is session-specific and located in the Process Global Area (PGA). This means that if I have 1,000 different sessions running the application, I could use up an enormous amount of memory in addition to that consumed by the SGA.
The PL/SQL function result cache minimizes the amount of memory needed to cache and share this data across all sessions. This low memory profile, plus the automatic purge of cached results whenever changes are committed, makes this feature of Oracle Database 11g very practical for optimizing performance in PL/SQL applications.
Analyze Performance and PGA Memory Impact
To test the improvement in performance and the impact on PGA memory over repeated queries of the data, I put together a set of scripts, available at oracle.com/technetwork/oramag/oracle/07-sep/o57plsql.zip, that compares three different ways to retrieve a row of employee data:
1. Execute the query repeatedly
2. Cache all the rows of the employees table in a packaged collection and then retrieve the data from that cache
3. Use the PL/SQL function result cache to avoid repetitive querying
2. Cache all the rows of the employees table in a packaged collection and then retrieve the data from that cache
3. Use the PL/SQL function result cache to avoid repetitive querying
To try this out yourself, unzip the o57plsql.zip file and run the 11g_emplu.tst script. It should take about five or six seconds to complete, and then you should see results like this:
PGA before tests are run:
session PGA: 910860 bytes
Execute query each time
Elapsed: 4.5 seconds.
session PGA: 910860 bytes
Cache table in PGA memory
Elapsed: .11 seconds.
session PGA: 1041932 bytes
Oracle Database 11
g result cache
Elapsed: .27 seconds.
session PGA: 1041932 bytes
Here are my conclusions from this admittedly incomplete analysis:
- The Oracle Database 11g PL/SQL function result cache is, indeed, much faster than repetitive querying. In this test, it was over an order of magnitude faster.
- A packaged collection cache is even faster, most likely because the PL/SQL runtime engine can access the data from PGA memory rather than SGA memory. Unfortunately, this also means that the consumption of memory occurs on a per-session basis, which is not very scalable.
- The packaged collection approach consumed additional PGA memory, but the Oracle Database 11g function result cache did not.
And then, of course, there are the other key advantages of the function result cache: automatic invalidation of cache contents when a dependent table is changed, the fact that the cache is shared across sessions, and the application of the least recently used algorithm to the memory in the cache.
So Why Should You Care Now?
"All right," you may be saying to yourself, "It's cool. Super cool. But I still can't use it for two years or more, so what good does that do me now?"
You may not be able to use the PL/SQL function result cache yet, but you can write your code now so that when you eventually upgrade to Oracle Database 11g, you will be able to quickly and easily use this cache in your application code.
In other words, you can and should prepare now for this future feature.
How do you do that? By placing all your queries (at least those against tables that change infrequently but are queried often) inside functions, so that you can easily add the RESULT_CACHE clause.
Think about it: today you probably don't do that. Instead, whenever you need data from the database, you write the required query, right there in the application logic you are writing (whether that logic resides in the back end—other PL/SQL programs—or the front end—languages such as Java).
|
And that same query (or some minor variation on it) will likely appear in multiple places in your application code. Why not? It is so easy to write those SQL statements; that's one of the beauties of PL/SQL. But that ease of use in executing SQL inside PL/SQL makes us all take SQL for granted, and when you upgrade to Oracle Database 11g, you will pay the price.
If after upgrading, you want to take advantage of RESULT_CACHE, you will have to find every affected SQL statement and either put the RESULT_CACHE hint inside that query (that's right, this feature is available natively within SQL as well as for functions) or construct the function, put the query inside it, find each of the applicable queries, and replace the query with the function call.
Certainly both of these approaches are eminently doable, but they are also very unlikely to happen. IT managers are loathe to go into existing, working production code and upset the applecart by making lots of changes.
If, conversely, you start right now , in Oracle9i Database or Oracle Database 10g, to place your queries inside functions, you will almost instantly be able to upgrade your code to use this fantastic new feature when you upgrade to Oracle Database 11g.
And, best of all, the application code that calls the function will not have to be changed at all! Your manager will be very impressed.
And that is why you should learn about the new features of Oracle Database 11g. Today.
sábado, 16 de septiembre de 2017
PL/SQL White Lists
PL/SQL White Lists Using the ACCESSIBLE BY Clause in Oracle Database 12c Release 1 (12.1)
TheACCESSIBLE BY clause can be added to packages, procedures, functions and types to specify which objects are able to reference the PL/SQL object directly. This ability to create so called white lists is a new feature introduced in Oracle Database 12c Release 1 (12.1) to allow you to add an extra layer of security to your PL/SQL objects.- Setup
- Basic Usage
- Cross-Schema Usage
- Unit Type
- Package White Lists
- Package White Lists (12.2 Update)
Setup
The following examples will use the two database users defined below.CONN sys/password@pdb1 AS SYSDBA CREATE USER test1 IDENTIFIED BY test1; GRANT CREATE SESSION, CREATE PROCEDURE, CREATE TABLE TO test1; CREATE USER test2 IDENTIFIED BY test2; GRANT CREATE SESSION, CREATE PROCEDURE TO test2;
Basic Usage
The procedure below includes anACCESSIBLE BY clause, indicating it can only be called by an object called calling_proc.CONN test1/test1@pdb1
CREATE OR REPLACE PROCEDURE protected_proc
ACCESSIBLE BY (calling_proc)
AS
BEGIN
DBMS_OUTPUT.put_line('TEST1 : protected_proc');
END;
/
Procedure created.
SQL>
Notice we have not created the calling_proc procedure yet, but no error is produced. This is because the objects referenced by the ACCESSIBLE BY clause are not checked at compile time. Only the syntax of the clause is checked.We can create the
calling_proc procedure and use it to call the protected_proc procedure.CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
DBMS_OUTPUT.put_line('TEST1 : calling_proc');
protected_proc;
END;
/
Procedure created.
SQL> SET SERVEROUTPUT ON
SQL> EXEC calling_proc;
TEST1 : calling_proc
TEST1 : protected_proc
PL/SQL procedure successfully completed.
SQL>
If we attempt to call the protected_proc procedure directly, we get an error.SQL> EXEC protected_proc;
BEGIN protected_proc; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object PROTECTED_PROC
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL>
If we try to create a new object that references the protected_proc procedure and the new object is not in the white list, we get a compilation error.CREATE OR REPLACE PROCEDURE another_calling_proc AS
BEGIN
DBMS_OUTPUT.put_line('TEST1 : another_calling_proc');
protected_proc;
END;
/
Warning: Procedure created with compilation errors.
SQL> SHOW ERRORS
Errors for PROCEDURE ANOTHER_CALLING_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: Statement ignored
4/3 PLS-00904: insufficient privilege to access object PROTECTED_PROC
SQL>
Cross-Schema Usage
If no schema is explicitly mentioned in the white list, it is assumed the object listed is in the same schema as the object with theACCESSIBLE BY clause. For example, if we switch to another user and create a procedure called calling_proc that accesses test1.protected_proc, it will not work.CONN test1/test1@pdb1
GRANT EXECUTE ON protected_proc TO test2;
Grant succeeded.
SQL>
CONN test2/test2@pdb1
CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
DBMS_OUTPUT.put_line('TEST2 : calling_proc');
test1.protected_proc;
END;
/
Warning: Procedure created with compilation errors.
SQL> SHOW ERRORS
Errors for PROCEDURE CALLING_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: Statement ignored
4/3 PLS-00904: insufficient privilege to access object PROTECTED_PROC
SQL>
For this to be successful, we must add the reference into the white list using the fully qualified object name. The example below includes an object reference without a schema prefix, signifying current schema (TEST1) and one with an explicit schema reference.CONN test1/test1@pdb1
CREATE OR REPLACE PROCEDURE protected_proc
ACCESSIBLE BY (calling_proc, test2.calling_proc)
AS
BEGIN
DBMS_OUTPUT.put_line('TEST1 : protected_proc');
END;
/
Procedure created.
SQL>
Now, we can reference and execute the protected_proc procedure from the other user.CONN test2/test2@pdb1
CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
DBMS_OUTPUT.put_line('TEST2 : calling_proc');
test1.protected_proc;
END;
/
Procedure created.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> EXEC calling_proc;
TEST2 : calling_proc
TEST1 : protected_proc
PL/SQL procedure successfully completed.
SQL>
Unit Type
The examples so far have not specified the unit type (object type) when defining the white list references, which means any compatible object type with the correct name in the correct schema will pass the white list test. If we want to make the test more stringent, we can specify not just the name, but the unit type also. Some examples are shown below.ACCESSIBLE BY (PACKAGE calling_pkg) ACCESSIBLE BY (PROCEDURE calling_proc) ACCESSIBLE BY (FUNCTION calling_func) ACCESSIBLE BY (TYPE calling_type) ACCESSIBLE BY (TRIGGER calling_trg)There seem to be some discrepancies about which unit types can access PL/SQL objects that use the
ACCESSIBLE BY clause. The New Features Guide suggests tables, indexes and views can also be referenced in the white list, but the unit types TABLE, INDEX and VIEW are not allowed. The following example shows that function-based indexes and views do not work against a function using the ACCESSIBLE BY clause.CONN test1/test1@pdb1
CREATE OR REPLACE FUNCTION protected_func (id IN NUMBER)
RETURN NUMBER
ACCESSIBLE BY (t1_fbi, t1_vw)
AS
BEGIN
RETURN id;
END;
/
CREATE TABLE t1 (
id NUMBER
);
SQL> CREATE INDEX t1_fbi ON t1(protected_func(id));
CREATE INDEX t1_fbi ON t1(protected_func(id))
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC
SQL>
-- Views are created normally, but fail when run.
CREATE OR REPLACE VIEW t1_vw AS
SELECT protected_func(id) AS id_vw
FROM t1;
View created.
SQL> SELECT * FROM t1_vw;
SELECT * FROM t1_vw
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC
SQL>
Package White Lists
In Oracle 12.1 theACCESSIBLE BY clause is only valid at the top-level of the package specification. It can not be applied to individual packaged procedures, functions or types within the package. The following example shows a white list applied to a package specification.CREATE OR REPLACE PACKAGE protected_pkg ACCESSIBLE BY (PROCEDURE calling_proc) AS PROCEDURE protected_proc; END; / Package created. SQL>Trying to apply the white list to the packaged procedure, rather than the top-level package, results in an error.
CREATE OR REPLACE PACKAGE protected_pkg
AS
PROCEDURE protected_proc
ACCESSIBLE BY (PROCEDURE calling_proc);
END;
/
Warning: Package created with compilation errors.
SQL> SHOW ERRORS
Errors for PACKAGE PROTECTED_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00157: Only schema-level programs allow ACCESSIBLE BY
SQL>
The ACCESSIBLE CLAUSE is not valid in the package body. It can only be defined in the package specification.CREATE OR REPLACE PACKAGE protected_pkg
ACCESSIBLE BY (PROCEDURE calling_proc)
AS
PROCEDURE protected_proc;
END;
/
Package created.
SQL>
CREATE OR REPLACE PACKAGE BODY protected_pkg
ACCESSIBLE BY (PROCEDURE calling_proc)
AS
PROCEDURE protected_proc AS
BEGIN
NULL;
END;
END;
/
Warning: Package Body created with compilation errors.
SQL> SHOW ERRORS
Errors for PACKAGE BODY PROTECTED_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3 PLS-00103: Encountered the symbol "ACCESSIBLE" when expecting one
of the following:
is as compress compiled wrapped
SQL>
Package White Lists (12.2 Update)
In Oracle database 12.1 it was only possible to use a white list for a whole package. In Oracle 12.2 this limitation is no longer present, making the concept of white lists for packages much more granular. It is now possible to white list individual subprograms or a package.CREATE OR REPLACE PACKAGE protected_pkg
AS
PROCEDURE protected_proc
ACCESSIBLE BY (PROCEDURE calling_proc);
FUNCTION protected_func RETURN NUMBER
ACCESSIBLE BY (PROCEDURE calling_func);
END;
/
CREATE OR REPLACE PACKAGE BODY protected_pkg
AS
PROCEDURE protected_proc
ACCESSIBLE BY (PROCEDURE calling_proc)
AS
BEGIN
NULL;
END;
FUNCTION protected_func RETURN NUMBER
ACCESSIBLE BY (PROCEDURE calling_func)
AS
BEGIN
RETURN NULL;
END;
END;
/
sys_refcursor
You can do this:
PROCEDURE abc( p_cursor IN SYS_REFCURSOR) IS
v_sol_id blt.sol_id%TYPE;
v_bill_id blt.bill_id%TYPE;
v_bank_id blt.bank_id%TYPE;
BEGIN
LOOP
FETCH p_cursor INTO v_sol_id, v_bill_id, v_bank_id;
EXIT WHEN p_cursor%NOTFOUND;
...
END LOOP;
END;
Then use it:
DECLARE
v_cursor SYS_REFCURSOR;
BEGIN
OPEN v_cursor FOR
SELECT BLT.sol_id,
BLT.bill_id,
BLT.bank_id
FROM BLT;
abc (v_cursor);
CLOSE v_cursor;
END;
CREATE OR REPLACE FUNCTION func_refcur(
Suscribirse a:
Entradas (Atom)
