Oracle compiler warning: oracle Compiler

Oracle compiler warning: oracle Compiler

Compiler Warnings Compiler warningOracle 10g allows you to enable compile-time warnings that are useful to identify potential run-time problems in your programs. these warnings are not serious enough to raise an exception at compile time, but may cause run-time errors or poor performance. to enable these warnings globally for your database, the administrator needs to set the database initialization parameter plsql_warnings either in The parameter file or dynamically with an alter system set PLSQL_WARNINGS statement. to enable warnings in only your session, use an alter session set PLSQL_WARNINGS statement. the setting string is a comma delimited list of settings. at the beginning of Oracle 10 Gb, you can enable the compile-time warning to detect exceptions during program running. These warnings are insufficient to throw exceptions during compilation, but may cause errors or low performance during runtime. You can specify the plsql_warnings parameter in the parameter file or use the alter system set PLSQL_WARNINGS statement to dynamically SET the warning when DBA enables global compilation. You can use the alter session set PLSQL_WARNINGS statement if you only enable it in the SESSION. This setting is separated by commas. The syntax for each setting is: syntax: '[ENABLE | DISABLE | ERROR]: [ALL | SEVERE | INFORMATIONAL | PERFORMANCE | warning_number]' To enable all warning messning ages execute: Example: alter system set plsql_warnings = 'Enable: all'; To enable all severe and performance messages execute: -- enable all severe and performance warnings alter system set plsql_warnings = 'Enable: severe ', 'Enable: performance '; To enable all warning messages t message 06002, execute: -- enable all warning information except 06002: alter system set plsql_warnings = 'Enable: all', 'Disable: 100'; Alternatively, you can use the built-in package dbms_warnings to set or view your warning setting. for example, to see what the current setting is, execute: optional, you can use the built-in package dbms_warnings to set or view the warning settings. For example, view The current warning settings BEGINDBMS_OUTPUT.PUT_LINE (DBMS_WARNING.GET_WARNING_SETTING_STRING (); END;/DISABLE: ALL -- The warning error codes all begin with a 'plw-'by default -'. the SEVERE errors are in the range 05000 to 05999. the INFORMATIONAL errors are in the range 06000 to 06999. the PERFORMANCE errors are in the range 07000 to 07249. on UNIX systems, a text file with the all of error codes, together with their cause Nd action can be found in $ ORACLE_HOME/plsql/mesg/plwus. msg or a similar filename (plw ??. Msg) if the locale is not US. the warning error code starts with 'plw-'. The range of critical error codes is 05000 to 05999. The range of reported error codes is 06000 to 06999. The range of performance error codes is 07000 to 07249; in UNIX systems, all error codes, causes, and handling methods are contained in the following files. $ ORACLE_HOME/plsql/mesg/plwus. msgAn example of compiler warnings appears below: Let's look at two examples of compilation warnings: -- 1 ineffective code (dead code): SQL> CREATE OR REPLACE PROCEDURE dead_code IS 2 x NUMBER: = 10; 3 BEGIN 4 IF x = 10 THEN -- always TRUE 5 x: = 20; 6 ELSE 7 x: = 100; -- dead code 8 end if; 9 END dead_code; 10/SP2-0804: Procedure created with compilation warningsSQL> show errorsErrors for PROCEDURE DEAD_CODE: LINE/COL ERROR -------- ------------------------------------------- 7/7 PLW-06002: Unreachable code -- 2 how to reinforce our function return value Logic Description: Structured Programming should always be: One way in, one way out. do not try to dig holes everywhere. The last pitfall is yourself. Consider the following code: create or replace function status_desc (cd_in IN VARCHAR2) RETURN VARCHAR2ISBEGIN IF cd_in = 'C' then return 'closed '; ELSIF cd_in = 'O' then return 'open '; ELSIF cd_in = 'A' then return 'active'; ELSIF cd_in = 'I 'then RETURN 'inactive'; END IF; END; no warning is triggered during compilation, on the surface, there are no major problems, right? How can I execute the following statements? BEGIN DBMS_OUTPUT.PUT_LINE (status_desc ('x'); END;/ORA-06503: PL/SQL: Function returned without value problem is not small !! Next, enable the compile-time warning to detect problems in advance! Alter session set plsql_warnings = 'Enable: 8080'/alter function status_desc COMPILE/PLW-05005: subprogram STATUS_DESC returns without value at line 15 warning is available, but the FUNCTION can still be executed! How can this be done? This is a problematic program! Next we will enable stricter warnings to prevent program compilation from being successful! Alter session set plsql_warnings = 'error: 808080'/alter function status_desc COMPILE/PLS-05005: subprogram STATUS_DESC returns without value at line 15 now OK, the program compilation returns a severe warning code PLS-05005, compilation failed! The next step is to fix the code. That's it!