PLsql bilinmesi gerekenler -3

5-DBMS_WARNING paketi:
Detaylı Debug ve Compiling işlemleri
– plsql_warnings init.ora parameter
• 3 şeçenekli (enable, disable, error)
• 3 uyarı seviyeli
– Severe: PLW aralık 5000 to 5999
– Informational: PLW aralık 6000 to 6249
– Performance: PLW aralık 7000 to 7249
• Default DISABLE:ALL

select name, value, isses_modifiable, issys_modifiable
from v$parameter
where name like 'plsql_warning%'
NAME VALUE ISSES ISSYS_MOD
-------------- -------------------------------------- ----- ---------
plsql_warnings DISABLE:ALL TRUE IMMEDIATE
alter system set plsql_warnings='enable:severe';
System altered.
select name, value, isses_modifiable, issys_modifiable
from v$parameter
where name like 'plsql_warning%'
NAME VALUE ISSES ISSYS_MOD
-------------- ------------------------------------------- ------ --------
------------------------------------------- ------ --------
plsql_warnings DISABLE:INFORMATIONAL, DISABLE:PERFORMANCE
ENABLE:SEVERE


alter system set plsql_warnings='enable:all';
alter session set plsql_warnings = 'enable:(7000, 7203)'
alter system set plsql_warnings='enable:performance';

EXECUTE dbms_warning.set_warning_setting_string('ENABLE:ALL','SYSTEM');
PL/SQL procedure successfully completed.

select dbms_warning.get_warning_setting_string
from dual;

GET_WARNING_SETTING_STRING
-------------------------------------------------------------
ENABLE:ALL


Compile sırasında uyarı mesajını alabiliriz. Bunun show error yada user_errors viewdan görebiliriz.

SQL> show errors
Errors for PROCEDURE MY_PROC:
LINE/COL ERROR
-------- ----------------------------------------------------
2/2 PLW-07203: parameter 'P_DATE_INFO' may benefit from
use of the NOCOPY compiler hint

6-DBMS_APPLICATION_INFO

Gerçek zamanlı asynchronous uygulama bilgisi

SET_MODULE Procedure
– Updates V$SESSION ve V$SQLAREA
– MODULE ve ACTION kolonları
– commite gerek yok
– Syntax
– V$SESSION Updated anında
– V$SQLAREA Updated yeni calıştırmalar için

DBMS_APPLICATION_INFO.SET_MODULE
(module_name VARCHAR2, action_name VARCHAR2);

SELECT sid, serial#, username, module, action
FROM v$session
WHERE USERNAME = 'PLSQL_USER';

SID SERIAL# USERNAME MODULE ACTION
---- ---------- ----------- ----------------- ------
7 7 PLSQL_USER SQL*Plus

DBMS_APPLICATION_INFO.SET_MODULE
('Package: TEST', 'Procedure: MAIN');

SELECT sid, serial#, username, module, action
FROM v$session
WHERE USERNAME = 'PLSQL_USER';

SID SERIAL# USERNAME MODULE ACTION
---- ------- ----------- -------------- ---------------
7 7 PLSQL_USER Package: TEST Procedure: MAIN

SELECT sql_text, module, action
FROM v$sqlarea
WHERE INSTR(UPPER(sql_text), 'INVENTORY') > 0;

SQL_TEXT MODULE ACTION
-------------------------- --------------- ---------------
SELECT * FROM s_inventory SQL*Plus
SELECT sql_text, module, a Package: TEST Procedure: MAIN
ction FROM v$sqlarea
WHERE INSTR(UPPER(s
ql_text), 'INVENTORY') > 0



DECLARE
CURSOR cur_employee IS
SELECT employee_id, salary, ROWID
FROM s_employee;
lv_count_num PLS_INTEGER := 0;
lv_start_time_num PLS_INTEGER;
BEGIN
lv_start_time_num := DBMS_UTILITY.GET_TIME;
FOR cur_employee_rec IN cur_employee LOOP
lv_count_num := lv_count_num + 1;
-- Employee processing logic...
IF MOD(lv_count_num, 1000) = 0 THEN
DBMS_APPLICATION_INFO.SET_MODULE
('Records Processed: ' || lv_count_num,
'Elapsed: ' || (DBMS_UTILITY.GET_TIME -
lv_start_time_num)/100 || ' sec');
END IF;
END LOOP;
END;
/

SELECT username, module, action
FROM v$session
WHERE username = 'PLSQL_USER';

USERNAME MODULE ACTION
---------- ------------------------- -----------------
PLSQL_USER SQL*Plus
PLSQL_USER Records Processed: 1000 Elapsed: 4.37 sec

USERNAME MODULE ACTION
---------- ------------------------- -------------------
PLSQL_USER SQL*Plus
PLSQL_USER Records Processed: 25000 Elapsed: 126.66 sec