ORACLE FECHA HORA DIFERENTES BASES DE DATOS

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

Password x Default

SELECT * FROM dba_users_with_defpwd

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 the UTL_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
• Error Stack
• Backtrace

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 the DBMS_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 the UNIT.SUBPROGRAM.LOCAL_SUBPROGRAM form 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.

The following example recreates the 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 the DBMS_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.

The following example recreates the 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 the DBMS_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.

The following example recreates the 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. The DBMS_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>