Trivadis PL/SQL Cop Version 2.0.2 (2017-02-05 17:21:40 +0100 [2373]) for Trivadis PL/SQL & SQL Coding Guidelines Version 3.2

Copyright 2010-2017 by Trivadis AG
Sägereistrasse 29
CH-8152 Glattbrugg (Zurich)
www.trivadis.com

License

Philipp Salvisberg, Trivadis AG, CH-8152 Glattbrugg
valid thru9999-12-31
valid for versions less than3
max. filesunlimited
max. linesunlimited
max. commandsunlimited
max. bytesunlimited
limited topersonal use

Parameters

path/Users/phs/Downloads/tvdcc-2.0.2/sample/.
filter(sql|prc|fnc|pks|pkb|trg|vw|tps|tbp|plb|pls|rcv|spc|typ|aqt|aqp|ctx|dbl|tab|dim|snp|con|collt|seq|syn|grt|sp|spb|sps)$
timeout10
complexity1
output/Users/phs/Downloads/tvdcc-2.0.2/sample/./tvdcc_report.html
templatehtml.xsl
exceltrue
htmltrue
transonlyfalse
cleanuptrue
check
skip
nosonartrue
license
validatorcom.trivadis.tvdcc.validators.TrivadisGuidelines3
genmodelfalse

Processing

Start of processing2017-02-05 18:52:00
End of processing2017-02-05 18:52:08
Processing time in seconds8.016

Content

Number of files93
Number of bytes150,687
Number of lines (LOC)7,382
Number of comment lines326
Number of blank lines1,193
Number of net lines5,863
Number of commands206
Number of statements (PL/SQL)1,416
Max. cyclomatic complexity5   ( < 11   11..50   > 50 )
Max. Halstead volume565   ( < 1001   1001..3000   > 3000 )
Min. maintainability index (MI)83   ( > 84   64..84   < 64 )
Number of issues281
Number of warnings281
Number of errors0




Issue Overview

#%SeverityCharacteristicsMessage
10.4%BlockerPortability, Reliability
G-2150: Avoid comparisons with NULL value, consider using IS [NOT] NULL.
10.4%BlockerReliability, Testability
G-5030: Never assign predefined exception names to user defined exceptions.
20.7%CriticalReliability
G-5070: Avoid using Oracle predefined exceptions
10.4%CriticalReliability
G-2160: Avoid initializing variables using functions in the declaration section.
10.4%CriticalReliability
G-4120: Avoid using %NOTFOUND directly after the FETCH when working with BULK OPERATIONS and LIMIT clause.
10.4%CriticalEfficiency, Reliability
G-4230: Always use COALESCE instead of NVL, if parameter 2 of the NVL function is a function call or a SELECT statement.
10.4%CriticalEfficiency, Reliability
G-4240: Always use CASE instead of NVL2 if parameter 2 or 3 of NVL2 is either a function call or a SELECT statement.
10.4%CriticalMaintainability
G-5020: Never handle unnamed exceptions using the error number.
4014.2%MajorEfficiency
G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.
82.8%MajorReliability
G-7230: Avoid declaring global variables public.
41.4%MajorMaintainability
G-3120: Always use table aliases when your SQL statement involves more than one source.
41.4%MajorMaintainability
G-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.
31.1%MajorMaintainability
G-2180: Never use quoted identifiers.
31.1%MajorChangeability, Reliability
G-3180: Always specify column names instead of positional references in ORDER BY clauses.
31.1%MajorChangeability, Maintainability
G-7110: Try to use named notation when calling program units.
31.1%MajorMaintainability, Reliability, Testability
G-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.
20.7%MajorPortability
G-2510: Avoid using the LONG and LONG RAW data types.
20.7%MajorMaintainability, Reliability
G-3140: Try to use anchored records as targets for your cursors.
20.7%MajorReliability
G-5040: Avoid use of WHEN OTHERS clause in an exception section without any other specific handlers.
20.7%MajorMaintainability, Testability
G-7430: Try to use no more than one RETURN statement within a function.
20.7%MajorMaintainability, Testability
G-7710: Avoid cascading triggers.
20.7%MajorEfficiency
G-8110: Never use SELECT COUNT(*) if you are only interested in the existence of a row.
10.4%MajorReliability
G-1060: Avoid storing ROWIDs or UROWIDs in database tables.
10.4%MajorMaintainability, Reliability
G-2110: Try to use anchored declarations for variables, constants and types.
10.4%MajorReliability
G-2170: Never overload variables.
10.4%MajorPortability, Reliability
G-2190: Avoid the use of ROWID or UROWID.
10.4%MajorReliability
G-2310: Avoid using CHAR data type.
10.4%MajorPortability
G-2320: Avoid using VARCHAR data type.
10.4%MajorPortability
G-2330: Never use zero-length strings to substitute NULL.
10.4%MajorMaintainability, Reliability
G-3110: Always specify the target columns when coding an insert statement.
10.4%MajorChangeability, Reliability
G-3190: Avoid using NATURAL JOIN.
10.4%MajorEfficiency
G-3210: Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement more than 4 times.
10.4%MajorEfficiency, Reliability
G-4130: Always close locally opened cursors.
10.4%MajorReliability
G-4140: Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute.
10.4%MajorMaintainability, Testability
G-4210: Try to use CASE rather than an IF statement with multiple ELSIF paths.
10.4%MajorMaintainability, Testability
G-4310: Never use GOTO statements in your code.
10.4%MajorReliability
G-4350: Always use 1 as lower and COUNT() as upper bound when looping through a dense array.
10.4%MajorEfficiency
G-4385: Never use a cursor for loop to check whether a cursor returns data.
10.4%MajorEfficiency
G-4390: Avoid use of unreferenced FOR loop indexes.
10.4%MajorChangeability, Maintainability
G-5050: Avoid use of the RAISE_APPLICATION_ERROR built-in procedure with a hard-coded -20nnn error number or hard-coded message.
10.4%MajorReliability
G-5060: Avoid unhandled exceptions
10.4%MajorMaintainability, Testability
G-6010: Always use a character variable to execute dynamic SQL.
10.4%MajorMaintainability, Reliability
G-7140: Always ensure that locally defined procedures or functions are referenced.
10.4%MajorEfficiency, Maintainability
G-7240: Avoid using an IN OUT parameter as IN or OUT only.
10.4%MajorMaintainability, Testability
G-7320: Avoid using RETURN statements in a PROCEDURE.
10.4%MajorMaintainability
G-7420: Always make the RETURN statement the last statement of your function.
10.4%MajorReusability
G-7440: Never use OUT parameters to return values from a function.
10.4%MajorReliability, Testability
G-7450: Never return a NULL value from a BOOLEAN function.
10.4%MajorSecurity
G-7510: Always prefix ORACLE supplied packages with owner schema name.
10.4%MajorEfficiency, Maintainability
G-7810: Do not use SQL inside PL/SQL to read sequence numbers (or SYSDATE)
10.4%MajorEfficiency, Reliability
G-8120: Never check existence of a row to decide whether to create it or not.
10.4%MajorChangeability, Maintainability
G-8210: Always use synonyms when accessing objects of another application schema.
6824.2%MinorMaintainability, Reliability, Reusability, Testability
G-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.
279.6%MinorChangeability
G-1050: Avoid using literals in your code.
93.2%MinorChangeability
G-2130: Try to use subtypes for constructs used often in your code.
51.8%MinorMaintainability
G-1020: Always have a matching loop or block label.
51.8%MinorMaintainability
G-1040: Avoid dead code.
41.4%MinorMaintainability
G-4320: Always label your loops.
31.1%MinorMaintainability
G-2185: Avoid using overly short names for explicitly or implicitly declared identifiers.
31.1%MinorMaintainability
G-4330: Always use a CURSOR FOR loop to process the complete cursor results unless you are using bulk operations.
31.1%MinorMaintainability
G-4380: Try to label your EXIT WHEN statements.
20.7%MinorMaintainability
G-1010: Try to label your sub blocks.
20.7%MinorEfficiency, Maintainability
G-1030: Avoid defining variables that are not used.
20.7%MinorMaintainability
G-1070: Avoid nesting comment blocks.
20.7%MinorMaintainability
G-2140: Never initialize variables with NULL.
20.7%MinorEfficiency
G-2220: Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values.
20.7%MinorEfficiency
G-2230: Try to use SIMPLE_INTEGER datatype when appropriate.
20.7%MinorReliability
G-2340: Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).
20.7%MinorMaintainability
G-4375: Always use EXIT WHEN instead of an IF statement to exit from a loop.
20.7%MinorMaintainability
G-7120: Always add the name of the program unit to its end keyword.
10.4%MinorChangeability
G-2120: Try to have a single location to define your types.
10.4%MinorEfficiency
G-2210: Avoid declaring NUMBER variables or subtypes with no precision.
10.4%MinorMaintainability
G-2410: Try to use boolean data type for values with dual meaning.
10.4%MinorMaintainability, Portability
G-3130: Try to use ANSI-join syntax.
10.4%MinorMaintainability, Reliability
G-3150: Try to use identity columns for surrogate keys.
10.4%MinorMaintainability
G-4110: Always use %NOTFOUND instead of NOT %FOUND to check whether a cursor returned data.
10.4%MinorMaintainability, Portability
G-4220: Try to use CASE rather than DECODE.
10.4%MinorMaintainability
G-4340: Always use a NUMERIC FOR loop to process a dense array.
10.4%MinorEfficiency
G-4360: Always use a WHILE loop to process a loose array.
10.4%MinorChangeability, Maintainability
G-4395: Avoid hard-coded upper or lower bound values with FOR loops.
10.4%MinorMaintainability
G-6020: Try to use output bind arguments in the RETURNING INTO clause of dynamic DML statements rather than the USING clause.
10.4%MinorEfficiency, Maintainability
G-7150: Try to remove unused parameters.
10.4%MinorEfficiency, Maintainability
G-7210: Try to keep your packages small. Include only few procedures and functions that are used in the same context.
10.4%MinorChangeability
G-7220: Always use forward declaration for private functions and procedures.
10.4%MinorMaintainability
G-7310: Avoid standalone procedures – put your procedures in packages.
10.4%MinorMaintainability
G-7410: Avoid standalone functions – put your functions in packages.
10.4%InfoMaintainability
G-0000: Avoid using the NOSONAR marker.




Complex PL/SQL Units

File namePL/SQL UnitLine# Lines# Comment lines# Blank lines# Net lines# StmtsCyclomatic complexityHalstead volumeMaintainability index
guidelines/guideline_1040_04.sqlAnonymousPlsqlBlock636162914349 95 
guidelines/guideline_4370_45.sqlAnonymousPlsqlBlock930032713411 83 
guidelines/guideline_4310_39.sqlmy_package.password_check1324032110474 86 
guidelines/guideline_4310_39.sqlmy_package.password_check5024032110474 86 
guidelines/guideline_4320_40.sqlAnonymousPlsqlBlock92103189289 91 
guidelines/guideline_4320_40.sqlAnonymousPlsqlBlock382603239346 87 
guidelines/guideline_4370_45.sqlAnonymousPlsqlBlock482603239346 87 
guidelines/guideline_1020_02.sqlAnonymousPlsqlBlock929042511282 86 
guidelines/guideline_1020_02.sqlAnonymousPlsqlBlock4629042511306 86 
guidelines/guideline_1050_05.sqlAnonymousPlsqlBlock61501145113 102 
guidelines/guideline_8110_78.sqlAnonymousPlsqlBlock81600165244 97 
guidelines/guideline_1050_05.sqlAnonymousPlsqlBlock311501145124 101 
guidelines/guideline_5030_52.sqlAnonymousPlsqlBlock311501145259 97 
guidelines/guideline_4210_35.sqlAnonymousPlsqlBlock41221124125 137 
guidelines/guideline_5020_51.sqlAnonymousPlsqlBlock61000104104 109 
guidelines/guideline_4210_35.sqlAnonymousPlsqlBlock197107493 145 
guidelines/guideline_4380_47.sqlAnonymousPlsqlBlock121802169444 92 
guidelines/guideline_4380_47.sqlAnonymousPlsqlBlock411601158419 94 
guidelines/guideline_4120_32.sqlAnonymousPlsqlBlock161703147290 95 
guidelines/guideline_4120_32.sqlAnonymousPlsqlBlock461602147304 96 
guidelines/guideline_4120_32.sqlAnonymousPlsqlBlock751602147301 96 
guidelines/guideline_4375_46.sqlAnonymousPlsqlBlock61202105132 105 
guidelines/guideline_7420_73.sqlmy_package.my_function81100115114 107 
guidelines/guideline_4360_44.sqlAnonymousPlsqlBlock151201115325 100 
guidelines/guideline_7120_61.sqlemployee_api.employee_by_id8130112469 107 
guidelines/guideline_7320_70.sqlmy_package.my_procedure8100194129 108 
guidelines/guideline_5030_52.sqlAnonymousPlsqlBlock91301124181 102 
guidelines/guideline_7130_62.sqlEMPLOYEE_API.calc_salary181301124121 104 
guidelines/guideline_5060_56.sqldepartment_api.name_by_id24110110482 109 
guidelines/guideline_1030_03.sqlmy_package.my_proc291101104125 106 
guidelines/guideline_7120_61.sqlemployee_api.employee_by_id29130112469 107 
guidelines/guideline_7130_62.sqlEMPLOYEE_API.calc_salary501601154169 99 
guidelines/guideline_8210_79.sqlAnonymousPlsqlBlock71100113114 107 
guidelines/guideline_2110_08.sqlmy_package.my_proc89009388 111 
guidelines/guideline_1030_03.sqlmy_package.my_proc1090093139 109 
guidelines/guideline_5020_51.sqlAnonymousPlsqlBlock198108351 144 
guidelines/guideline_na_54.sqlcalling_proc201200123114 105 
guidelines/guideline_2110_08.sqlmy_package.my_proc259009395 111 
guidelines/guideline_8210_79.sqlAnonymousPlsqlBlock26110011396 108 
guidelines/guideline_4350_43.sqlAnonymousPlsqlBlock3990093151 109 
guidelines/guideline_na_54.sqlcalling_proc45120012396 106 
guidelines/guideline_7240_68.sqlemployee_up.rcv_emp1917101612565 111 
guidelines/guideline_7240_68.sqlemployee_up.rcv_emp5517101612565 111 
guidelines/guideline_4330_41.sqlAnonymousPlsqlBlock91202106173 103 
guidelines/guideline_4390_49.sqlAnonymousPlsqlBlock131100116332 102 
guidelines/guideline_4340_42.sqlAnonymousPlsqlBlock161101106337 101 
guidelines/guideline_4360_44.sqlAnonymousPlsqlBlock381302116367 98 
guidelines/guideline_3140_29.sqlAnonymousPlsqlBlock111110105196 128 
guidelines/guideline_4110_31.sqlAnonymousPlsqlBlock12110295138 106 
guidelines/guideline_3140_29.sqlAnonymousPlsqlBlock301110105118 131 
guidelines/guideline_4110_31.sqlAnonymousPlsqlBlock34110295131 106 
guidelines/guideline_4140_34.sqlemployee_api.remove_employee411413105143 123 
guidelines/guideline_1040_04.sqlAnonymousPlsqlBlock471401135145 102 
guidelines/guideline_8310_na.sqldepartment_api.dept_by_name81312104119 126 
guidelines/guideline_7150_64.sqldepartment_api.name_by_id91401134108 103 
guidelines/guideline_8410_na.sqllock_up.request_lock111700174302 95 
guidelines/guideline_4140_34.sqlemployee_api.remove_employee14121294106 129 
guidelines/guideline_7220_66.sqldepartment_api.does_exist141401134114 103 
guidelines/guideline_2160_13.sqlAnonymousPlsqlBlock181101104185 105 
guidelines/guideline_7430_72.sqlmy_package.my_function2290184102 111 
guidelines/guideline_4375_46.sqlAnonymousPlsqlBlock23100284121 108 
guidelines/guideline_7420_73.sqlmy_package.my_function279009483 112 
guidelines/guideline_7150_64.sqldepartment_api.name_by_id311401134108 103 
guidelines/guideline_8410_na.sqlAnonymousPlsqlBlock42102084125 144 
guidelines/guideline_7220_66.sqldepartment_api.does_exist531401134114 103 
guidelines/guideline_7430_72.sqlmy_package.my_function77007365 117 
guidelines/guideline_8120_na.sqldepartment_api.ins7100010391 110 
guidelines/guideline_8420_na.sqlemployee_api.process_emps71110103131 130 
guidelines/guideline_2410_24.sqlAnonymousPlsqlBlock870073124 114 
guidelines/guideline_3210_30.sqlAnonymousPlsqlBlock81101103226 104 
guidelines/guideline_7130_62.sqlEMPLOYEE_API.calc_salary.commission1080173105 113 
guidelines/guideline_2410_24.sqlAnonymousPlsqlBlock2270073105 115 
guidelines/guideline_7320_70.sqlmy_package.my_procedure2680173123 112 
guidelines/guideline_4340_42.sqlAnonymousPlsqlBlock3590183199 107 
guidelines/guideline_7130_62.sqlEMPLOYEE_API.calc_salary.commission437016380 116 
guidelines/guideline_4395_50.sqlAnonymousPlsqlBlock47217244 162 
guidelines/guideline_5040_53.sqlAnonymousPlsqlBlock46216239 168 
guidelines/guideline_2150_12.sqlAnonymousPlsqlBlock65005231 127 
guidelines/guideline_2180_15.sqlAnonymousPlsqlBlock86006251 121 
guidelines/guideline_2185_16.sqlAnonymousPlsqlBlock86006251 121 
guidelines/guideline_4385_48.sqlAnonymousPlsqlBlock97007274 117 
guidelines/guideline_na_54.sqlcheck_hiredate970072136 113 
guidelines/guideline_7150_64.sqldepartment_api.name_by_id.find_department119009259 114 
guidelines/guideline_4350_43.sqlAnonymousPlsqlBlock1270072124 114 
guidelines/guideline_5040_53.sqlAnonymousPlsqlBlock136106239 155 
guidelines/guideline_2150_12.sqlAnonymousPlsqlBlock165005226 128 
guidelines/guideline_7220_66.sqldepartment_api.does_exist.check_row_exists169009259 114 
guidelines/guideline_4395_50.sqlAnonymousPlsqlBlock177007291 116 
guidelines/guideline_2160_13.sqlAnonymousPlsqlBlock.init206006257 120 
guidelines/guideline_2180_15.sqlAnonymousPlsqlBlock216006251 121 
guidelines/guideline_2185_16.sqlAnonymousPlsqlBlock216006251 121 
guidelines/guideline_8120_na.sqldepartment_api.ins237007216 125 
guidelines/guideline_4350_43.sqlAnonymousPlsqlBlock2670072116 114 
guidelines/guideline_4330_41.sqlAnonymousPlsqlBlock287007281 116 
guidelines/guideline_8110_78.sqlAnonymousPlsqlBlock291100112157 105 
guidelines/guideline_7220_66.sqldepartment_api.del305005221 129 
guidelines/guideline_8410_na.sqllock_up.release_lock305005269 122 
guidelines/guideline_4390_49.sqlAnonymousPlsqlBlock3380082204 109 
guidelines/guideline_7150_64.sqldepartment_api.name_by_id.find_department339009259 114 
guidelines/guideline_na_54.sqlcheck_hiredate385005272 122 
guidelines/guideline_7220_66.sqldepartment_api.del445005221 129 
guidelines/guideline_7220_66.sqldepartment_api.does_exist.check_row_exists559009259 114 
guidelines/guideline_4310_39.sqlmy_package.password_check8360062117 117 




File Overview

File name# Warnings# Errors# Bytes# Lines# Net lines# Cmds# StmtsMax. cyclomatic complexity Max. Halstead volume Min. MI Elapsed seconds
guidelines/guideline_7240_68.sql2303,6797366224565 111 0.039
guidelines/guideline_1040_04.sql1101,4276148219349 95 0.054
guidelines/guideline_7130_62.sql901,9176756214169 99 0.040
guidelines/guideline_7230_67.sql901,85560474668 123 0.019
guidelines/guideline_7220_66.sql801,8116856316114 103 0.028
guidelines/guideline_7430_72.sql80997423438102 111 0.014
guidelines/guideline_8410_na.sql801,5675243210302 95 0.019
guidelines/guideline_3110_26.sql70478201420221 0.016
guidelines/guideline_6020_59.sql701,41236312241 126 0.022
guidelines/guideline_7420_73.sql70949373229114 107 0.016
guidelines/guideline_2510_25.sql60283151020221 0.014
guidelines/guideline_3120_27.sql601,278453350221 0.029
guidelines/guideline_4310_39.sql602,9009077322474 86 0.047
guidelines/guideline_7120_61.sql601,03343362869 107 0.022
guidelines/guideline_7150_64.sql601,4114639212108 103 0.028
guidelines/guideline_1020_02.sql501,2397562226306 86 0.063
guidelines/guideline_2210_18.sql50544231622143 0.018
guidelines/guideline_2220_19.sql50565231622143 0.015
guidelines/guideline_2330_22.sql50469221522143 0.014
guidelines/guideline_3180_na.sql50390211420221 0.015
guidelines/guideline_4130_33.sql501,331383127128 117 0.027
guidelines/guideline_4140_34.sql501,5595640211143 123 0.032
guidelines/guideline_4320_40.sql501,3076453218346 87 0.031
guidelines/guideline_5060_56.sql501,00836292682 109 0.017
guidelines/guideline_7440_74.sql4044520152321 133 0.012
guidelines/guideline_8310_na.sql401,143392926119 126 0.018
guidelines/guideline_2180_15.sql3046227222451 121 0.016
guidelines/guideline_2185_16.sql3044627222451 121 0.015
guidelines/guideline_2230_na.sql30555231622143 0.013
guidelines/guideline_3160_na.sql30972362846151 113 0.025
guidelines/guideline_4110_31.sql309384534210138 106 0.023
guidelines/guideline_4370_45.sql301,6567462222411 83 0.033
guidelines/guideline_5010_na.sql303061271279 158 0.013
guidelines/guideline_5050_55.sql302971382230 181 0.012
guidelines/guideline_7140_63.sql3068129242425 136 0.013
guidelines/guideline_7410_71.sql30347171222143 0.014
guidelines/guideline_7450_75.sql30390191422143 0.011
guidelines/guideline_0000_00.sql2035525183341 134 1.286
guidelines/guideline_1010_01.sql20280272021222 140 0.023
guidelines/guideline_1030_03.sql201,165413427139 106 0.127
guidelines/guideline_1050_05.sql208834638310124 101 0.030
guidelines/guideline_1070_07.sql20282191024221 0.010
guidelines/guideline_3140_29.sql201,0514134210196 128 0.022
guidelines/guideline_4380_47.sql201,9505749217444 92 0.026
guidelines/guideline_5030_52.sql201,141463929259 97 0.018
guidelines/guideline_6010_58.sql2043419142239 134 0.015
guidelines/guideline_7710_77.sql201,747554734189 95 0.035
guidelines/guideline_8120_na.sql2081731262591 110 0.016
guidelines/guideline_8420_na.sql20639191413131 130 0.015
guidelines/guideline_na_54.sql201,5775851210136 105 0.027
guidelines/guideline_1060_06.sql1058827222257 143 0.026
guidelines/guideline_2110_08.sql101,00735302695 111 0.038
guidelines/guideline_2120_09.sql1060427213228 136 0.018
guidelines/guideline_2130_10.sql1056226203228 136 0.020
guidelines/guideline_2140_11.sql1023917122241 134 0.016
guidelines/guideline_2150_12.sql1028021162431 127 0.011
guidelines/guideline_2160_13.sql10791292327185 105 0.023
guidelines/guideline_2170_14.sql101,0883934212217 110 0.029
guidelines/guideline_2190_17.sql1050023182245 125 0.016
guidelines/guideline_2310_20.sql10265161020221 0.012
guidelines/guideline_2320_21.sql1026813820221 0.011
guidelines/guideline_2340_23.sql1032313820221 0.010
guidelines/guideline_2410_24.sql10805403237124 114 0.020
guidelines/guideline_3130_28.sql10572231620221 0.024
guidelines/guideline_3150_na.sql1071529214130 135 0.024
guidelines/guideline_3190_na.sql10418211420221 0.018
guidelines/guideline_3210_30.sql101,143362826226 104 0.026
guidelines/guideline_4120_32.sql102,5549169321304 95 0.044
guidelines/guideline_4210_35.sql10494262128125 137 0.018
guidelines/guideline_4220_36.sql1041223182280 146 0.022
guidelines/guideline_4230_37.sql1030511520221 0.014
guidelines/guideline_4240_38.sql10481181020221 0.019
guidelines/guideline_4330_41.sql10788352828173 103 0.024
guidelines/guideline_4340_42.sql101,270443329337 101 0.028
guidelines/guideline_4350_43.sql101,406483537151 109 0.026
guidelines/guideline_4360_44.sql101,5425139211367 98 0.029
guidelines/guideline_4375_46.sql10736332429132 105 0.020
guidelines/guideline_4385_48.sql10672312626113 117 0.018
guidelines/guideline_4390_49.sql101,330413628332 102 0.020
guidelines/guideline_4395_50.sql1044724192491 116 0.016
guidelines/guideline_5020_51.sql10621272227104 109 0.013
guidelines/guideline_5040_53.sql1037619142439 155 0.012
guidelines/guideline_5070_57.sql10182151022145 0.011
guidelines/guideline_7110_60.sql1042519142265 131 0.015
guidelines/guideline_7210_65.sql1069,2684,2133,3682840221 3.880
guidelines/guideline_7310_69.sql10395221632221 0.011
guidelines/guideline_7320_70.sql10801352827129 108 0.015
guidelines/guideline_7460_na.sql10504151020221 0.010
guidelines/guideline_7510_76.sql1034718122241 134 0.012
guidelines/guideline_7810_na.sql1035519142233 127 0.014
guidelines/guideline_8110_78.sql101,082403527244 97 0.019
guidelines/guideline_8210_79.sql10898373136114 107 0.017
guidelines/guideline_3170_na.sql0046014820221 0.004
Total2810150,6877,3825,8632061,4165565837.180




File Issues

guidelines/guideline_7240_68.sql overview

Issue#LineSeverityMessageCode Excerpt
115MajorG-7240: Avoid using an IN OUT parameter as IN or OUT only.in_wait INTEGER
25MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.io_first_name IN OUT employees.first_name%TYPE
36MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.io_last_name IN OUT employees.last_name%TYPE
47MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.io_email IN OUT employees.email%TYPE
58MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.io_phone_number IN OUT employees.phone_number%TYPE
69MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.io_hire_date IN OUT employees.hire_date%TYPE
710MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.io_job_id IN OUT employees.job_id%TYPE
811MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.io_salary IN OUT employees.salary%TYPE
912MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.io_commission_pct IN OUT employees.commission_pct%TYPE
1013MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.io_manager_id IN OUT employees.manager_id%TYPE
1114MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.io_department_id IN OUT employees.department_id%TYPE
1215MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_wait INTEGER
1341MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.OUT_first_name OUT employees.first_name%TYPE
1442MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.OUT_last_name OUT employees.last_name%TYPE
1543MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.OUT_email OUT employees.email%TYPE
1644MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.OUT_phone_number OUT employees.phone_number%TYPE
1745MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.OUT_hire_date OUT employees.hire_date%TYPE
1846MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.OUT_job_id OUT employees.job_id%TYPE
1947MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.OUT_salary OUT employees.salary%TYPE
2048MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.OUT_commission_pct OUT employees.commission_pct%TYPE
2149MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.OUT_manager_id OUT employees.manager_id%TYPE
2250MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.OUT_department_id OUT employees.department_id%TYPE
2351MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_wait IN INTEGER

guidelines/guideline_1040_04.sql overview

Issue#LineSeverityMessageCode Excerpt
17MinorG-1040: Avoid dead code.2=3
215MinorG-1040: Avoid dead code.EXIT my_loop
322MinorG-1040: Avoid dead code.'x' = 'y'
433MinorG-1040: Avoid dead code.5=6
539MinorG-1040: Avoid dead code.RETURN
67MinorG-1050: Avoid using literals in your code.2
77MinorG-1050: Avoid using literals in your code.3
822MinorG-1050: Avoid using literals in your code.'x'
922MinorG-1050: Avoid using literals in your code.'y'
1033MinorG-1050: Avoid using literals in your code.5
1133MinorG-1050: Avoid using literals in your code.6

guidelines/guideline_7130_62.sql overview

Issue#LineSeverityMessageCode Excerpt
111MajorG-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.r_emp
213MajorG-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.r_emp
313MajorG-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.r_emp
48MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION commission RETURN NUMBER
539MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION commission (in_salary IN employees.salary%TYPE, in_comm_pct IN employees.commission_pct%TYPE) RETURN NUMBER
65MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_employee_id IN employees.employee_id%TYPE
736MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_employee_id IN employees.employee_id%TYPE
839MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_salary IN employees.salary%TYPE
940MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_comm_pct IN employees.commission_pct%TYPE

guidelines/guideline_7230_67.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-7230: Avoid declaring global variables public.co_min_increase
26MajorG-7230: Avoid declaring global variables public.co_max_increase
37MajorG-7230: Avoid declaring global variables public.g_salary_increase
410MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION salary_increase RETURN types_up.sal_increase_type
521MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION salary_increase RETURN types_up.sal_increase_type
631MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION salary_increase RETURN types_up.sal_increase_type
747MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION salary_increase RETURN types_up.sal_increase_type
815MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_increase IN types_up.sal_increase_type
940MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_increase IN types_up.sal_increase_type

guidelines/guideline_7220_66.sql overview

Issue#LineSeverityMessageCode Excerpt
111MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION does_exist (in_department_id IN departments.department_id%TYPE) RETURN BOOLEAN
240MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION does_exist (in_department_id IN departments.department_id%TYPE) RETURN BOOLEAN
350MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION does_exist (in_department_id IN departments.department_id%TYPE) RETURN BOOLEAN
411MinorG-7220: Always use forward declaration for private functions and procedures.does_exist
511MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_department_id IN departments.department_id%TYPE
629MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_department_id IN departments.department_id%TYPE
743MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_department_id IN departments.department_id%TYPE
850MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_department_id IN departments.department_id%TYPE

guidelines/guideline_7430_72.sql overview

Issue#LineSeverityMessageCode Excerpt
19MajorG-7430: Try to use no more than one RETURN statement within a function. RETURN TRUE
211MajorG-7430: Try to use no more than one RETURN statement within a function. RETURN FALSE
35MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_function (in_value IN PLS_INTEGER) RETURN BOOLEAN
419MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_function (in_value IN PLS_INTEGER) RETURN BOOLEAN
536MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_function (in_value IN PLS_INTEGER) RETURN BOOLEAN
65MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_value IN PLS_INTEGER
719MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_value IN PLS_INTEGER
836MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_value IN PLS_INTEGER

guidelines/guideline_8410_na.sql overview

Issue#LineSeverityMessageCode Excerpt
147MajorG-5040: Avoid use of WHEN OTHERS clause in an exception section without any other specific handlers.WHEN OTHERS THEN -- log error lock_up.release_lock(in_lock_handle => l_handle); RAISE;
26MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION request_lock( in_lock_name IN VARCHAR2, in_release_on_commit IN BOOLEAN := FALSE) RETURN VARCHAR2
310MinorG-2130: Try to use subtypes for constructs used often in your code.l_lock_handle VARCHAR2(128 CHAR);
440MinorG-2130: Try to use subtypes for constructs used often in your code.l_handle VARCHAR2(128 CHAR);
541MinorG-2130: Try to use subtypes for constructs used often in your code.co_lock_name CONSTANT VARCHAR2(30 CHAR) := 'APPLICATION_LOCK';
67MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_lock_name IN VARCHAR2
78MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_release_on_commit IN BOOLEAN := FALSE
829MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_lock_handle IN VARCHAR2

guidelines/guideline_3110_26.sql overview

Issue#LineSeverityMessageCode Excerpt
14MajorG-3110: Always specify the target columns when coding an insert statement.INSERT INTO departments VALUES (departments_seq.nextval, 'Support', 100, 10)
26MinorG-1050: Avoid using literals in your code.'Support'
37MinorG-1050: Avoid using literals in your code.100
48MinorG-1050: Avoid using literals in your code.10
516MinorG-1050: Avoid using literals in your code.'Support'
617MinorG-1050: Avoid using literals in your code.100
718MinorG-1050: Avoid using literals in your code.10

guidelines/guideline_6020_59.sql overview

Issue#LineSeverityMessageCode Excerpt
115MinorG-6020: Try to use output bind arguments in the RETURNING INTO clause of dynamic DML statements rather than the USING clause. EXECUTE IMMEDIATE co_sql_stmt USING in_increase_pct, in_employee_id, OUT out_new_salary
25MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_employee_id IN employees.employee_id%TYPE
36MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_increase_pct IN types_up.percentage
47MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.out_new_salary OUT employees.salary%TYPE
522MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_employee_id IN employees.employee_id%TYPE
623MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_increase_pct IN types_up.percentage
724MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.out_new_salary OUT employees.salary%TYPE

guidelines/guideline_7420_73.sql overview

Issue#LineSeverityMessageCode Excerpt
115MajorG-7420: Always make the RETURN statement the last statement of your function.RETURN l_ret
25MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_function (in_from IN PLS_INTEGER, in_to IN PLS_INTEGER) RETURN PLS_INTEGER
324MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_function (in_from IN PLS_INTEGER, in_to IN PLS_INTEGER) RETURN PLS_INTEGER
45MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_from IN PLS_INTEGER
56MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_to IN PLS_INTEGER
624MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_from IN PLS_INTEGER
725MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_to IN PLS_INTEGER

guidelines/guideline_2510_25.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-2510: Avoid using the LONG and LONG RAW data types.l_long LONG;
26MajorG-2510: Avoid using the LONG and LONG RAW data types.l_raw LONG RAW;
35MajorG-7230: Avoid declaring global variables public.l_long
46MajorG-7230: Avoid declaring global variables public.l_raw
512MajorG-7230: Avoid declaring global variables public.l_long
613MajorG-7230: Avoid declaring global variables public.l_raw

guidelines/guideline_3120_27.sql overview

Issue#LineSeverityMessageCode Excerpt
14MajorG-3120: Always use table aliases when your SQL statement involves more than one source.last_name
25MajorG-3120: Always use table aliases when your SQL statement involves more than one source.first_name
36MajorG-3120: Always use table aliases when your SQL statement involves more than one source.department_name
433MajorG-3120: Always use table aliases when your SQL statement involves more than one source.employee_id
535MinorG-1050: Avoid using literals in your code.'%Manager%'
643MinorG-1050: Avoid using literals in your code.'%Manager%'

guidelines/guideline_4310_39.sql overview

Issue#LineSeverityMessageCode Excerpt
125MajorG-4310: Never use GOTO statements in your code.GOTO check_other_things
262MajorG-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.EXIT check_digit
362MinorG-4375: Always use EXIT WHEN instead of an IF statement to exit from a loop.EXIT check_digit
45MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_password IN VARCHAR2
542MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_password IN VARCHAR2
679MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_password IN VARCHAR2

guidelines/guideline_7120_61.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION employee_by_id (in_employee_id IN employees.employee_id%TYPE) RETURN employees%rowtype
226MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION employee_by_id (in_employee_id IN employees.employee_id%TYPE) RETURN employees%rowtype
34MinorG-7120: Always add the name of the program unit to its end keyword.employee_api
45MinorG-7120: Always add the name of the program unit to its end keyword.employee_by_id
55MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_employee_id IN employees.employee_id%TYPE
626MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_employee_id IN employees.employee_id%TYPE

guidelines/guideline_7150_64.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION name_by_id (in_department_id IN departments.department_id%TYPE, in_manager_id IN departments.manager_id%TYPE) RETURN departments.department_name%TYPE
228MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION name_by_id (in_department_id IN departments.department_id%TYPE) RETURN departments.department_name%TYPE
36MinorG-7150: Try to remove unused parameters.in_manager_id IN departments.manager_id%TYPE
45MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_department_id IN departments.department_id%TYPE
56MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_manager_id IN departments.manager_id%TYPE
628MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_department_id IN departments.department_id%TYPE

guidelines/guideline_1020_02.sql overview

Issue#LineSeverityMessageCode Excerpt
110MinorG-1020: Always have a matching loop or block label.<<prepare_data>>
215MinorG-1020: Always have a matching loop or block label.<<process_data>>
321MinorG-1020: Always have a matching loop or block label.<<while_loop>>
427MinorG-1020: Always have a matching loop or block label.<<basic_loop>>
532MinorG-1020: Always have a matching loop or block label.<<for_loop>>

guidelines/guideline_2210_18.sql overview

Issue#LineSeverityMessageCode Excerpt
17MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION small_increase RETURN NUMBER
218MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION small_increase RETURN NUMBER
35MinorG-2130: Try to use subtypes for constructs used often in your code.co_small_increase CONSTANT NUMBER := 0.1;
416MinorG-2130: Try to use subtypes for constructs used often in your code.co_small_increase CONSTANT NUMBER(5,1) := 0.1;
55MinorG-2210: Avoid declaring NUMBER variables or subtypes with no precision. NUMBER

guidelines/guideline_2220_19.sql overview

Issue#LineSeverityMessageCode Excerpt
17MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION big_increase RETURN NUMBER
218MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION big_increase RETURN PLS_INTEGER
35MinorG-2130: Try to use subtypes for constructs used often in your code.co_big_increase CONSTANT NUMBER(5,0) := 1;
45MinorG-2220: Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values. NUMBER(5,0)
55MinorG-2230: Try to use SIMPLE_INTEGER datatype when appropriate. NUMBER(5,0)

guidelines/guideline_2330_22.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-2330: Never use zero-length strings to substitute NULL.:= ''
27MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION null_string RETURN VARCHAR2
317MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION empty_string RETURN VARCHAR2
45MinorG-2130: Try to use subtypes for constructs used often in your code.co_null_string CONSTANT VARCHAR2(1) := '';
55MinorG-2340: Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).VARCHAR2(1)

guidelines/guideline_3180_na.sql overview

Issue#LineSeverityMessageCode Excerpt
19MajorG-3180: Always specify column names instead of positional references in ORDER BY clauses.4
29MajorG-3180: Always specify column names instead of positional references in ORDER BY clauses.1
39MajorG-3180: Always specify column names instead of positional references in ORDER BY clauses.3
49MinorG-1050: Avoid using literals in your code.4
59MinorG-1050: Avoid using literals in your code.3

guidelines/guideline_4130_33.sql overview

Issue#LineSeverityMessageCode Excerpt
113MajorG-4130: Always close locally opened cursors.OPEN c_department_salary(p_dept_id => in_dept_id)
25MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION department_salary (in_dept_id IN departments.department_id%TYPE) RETURN NUMBER
324MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION department_salary (in_dept_id IN departments.department_id%TYPE) RETURN NUMBER
45MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_dept_id IN departments.department_id%TYPE
524MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_dept_id IN departments.department_id%TYPE

guidelines/guideline_4140_34.sql overview

Issue#LineSeverityMessageCode Excerpt
121MajorG-4140: Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute.SQL%ROWCOUNT
27MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_dept_id IN departments.department_id%TYPE
312MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_employee_id IN employees.employee_id%TYPE
433MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_dept_id IN departments.department_id%TYPE
538MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_employee_id IN employees.employee_id%TYPE

guidelines/guideline_4320_40.sql overview

Issue#LineSeverityMessageCode Excerpt
111MinorG-4320: Always label your loops.WHILE (i <= co_max_value) LOOP i := i + co_increment; END LOOP
216MinorG-4320: Always label your loops.LOOP EXIT; END LOOP
320MinorG-4320: Always label your loops.FOR i IN co_min_value..co_max_value LOOP sys.dbms_output.put_line(i); END LOOP
425MinorG-4320: Always label your loops.FOR r_employee IN (SELECT last_name FROM employees) LOOP sys.dbms_output.put_line(r_employee.last_name); END LOOP
517MinorG-4380: Try to label your EXIT WHEN statements.EXIT

guidelines/guideline_5060_56.sql overview

Issue#LineSeverityMessageCode Excerpt
19MajorG-5060: Avoid unhandled exceptionsSELECT department_name INTO l_department_name FROM departments WHERE department_id = in_id
25MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION name_by_id (in_id IN departments.department_id%TYPE) RETURN departments.department_name%TYPE
321MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION name_by_id (in_id IN departments.department_id%TYPE) RETURN departments.department_name%TYPE
45MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_id IN departments.department_id%TYPE
521MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_id IN departments.department_id%TYPE

guidelines/guideline_7440_74.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-7440: Never use OUT parameters to return values from a function.out_date OUT DATE
25MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_function (out_date OUT DATE) RETURN BOOLEAN
315MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_function RETURN DATE
45MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.out_date OUT DATE

guidelines/guideline_8310_na.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION dept_by_name (in_dept_name IN departments.department_name%TYPE) RETURN departments%ROWTYPE
226MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION dept_by_name (in_dept_name IN departments.department_name%TYPE) RETURN departments%ROWTYPE
39MinorG-1050: Avoid using literals in your code.20
45MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_dept_name IN departments.department_name%TYPE

guidelines/guideline_2180_15.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-2180: Never use quoted identifiers. "sal+comm"
26MajorG-2180: Never use quoted identifiers. "my constant"
37MajorG-2180: Never use quoted identifiers. "my exception"

guidelines/guideline_2185_16.sql overview

Issue#LineSeverityMessageCode Excerpt
15MinorG-2185: Avoid using overly short names for explicitly or implicitly declared identifiers. i INTEGER;
26MinorG-2185: Avoid using overly short names for explicitly or implicitly declared identifiers. c CONSTANT INTEGER := 1;
37MinorG-2185: Avoid using overly short names for explicitly or implicitly declared identifiers. e exception;

guidelines/guideline_2230_na.sql overview

Issue#LineSeverityMessageCode Excerpt
15MinorG-2130: Try to use subtypes for constructs used often in your code.co_big_increase CONSTANT NUMBER(5,0) := 1;
25MinorG-2220: Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values. NUMBER(5,0)
35MinorG-2230: Try to use SIMPLE_INTEGER datatype when appropriate. NUMBER(5,0)

guidelines/guideline_3160_na.sql overview

Issue#LineSeverityMessageCode Excerpt
113MajorG-7110: Try to use named notation when calling program units.l_id
230MajorG-7110: Try to use named notation when calling program units.co_id
311MinorG-1050: Avoid using literals in your code.107

guidelines/guideline_4110_31.sql overview

Issue#LineSeverityMessageCode Excerpt
118MinorG-4110: Always use %NOTFOUND instead of NOT %FOUND to check whether a cursor returned data.NOT c_employees%FOUND
217MinorG-4330: Always use a CURSOR FOR loop to process the complete cursor results unless you are using bulk operations.FETCH c_employees INTO r_employee
339MinorG-4330: Always use a CURSOR FOR loop to process the complete cursor results unless you are using bulk operations.FETCH c_employees INTO r_employee

guidelines/guideline_4370_45.sql overview

Issue#LineSeverityMessageCode Excerpt
115MajorG-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.EXIT while_loop WHEN i > co_max_value
227MajorG-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.EXIT for_loop WHEN i = co_max_value
336MajorG-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.EXIT process_employees

guidelines/guideline_5010_na.sql overview

Issue#LineSeverityMessageCode Excerpt
16MinorG-1050: Avoid using literals in your code.'DEMO'
28MinorG-1050: Avoid using literals in your code.'START'
310MinorG-1050: Avoid using literals in your code.'END'

guidelines/guideline_5050_55.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-5050: Avoid use of the RAISE_APPLICATION_ERROR built-in procedure with a hard-coded -20nnn error number or hard-coded message. raise_application_error(-20501,'Invalid employee_id')
25MinorG-1050: Avoid using literals in your code.20501
35MinorG-1050: Avoid using literals in your code.'Invalid employee_id'

guidelines/guideline_7140_63.sql overview

Issue#LineSeverityMessageCode Excerpt
16MajorG-7140: Always ensure that locally defined procedures or functions are referenced.FUNCTION my_func RETURN NUMBER
26MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_func RETURN NUMBER
320MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_func RETURN NUMBER

guidelines/guideline_7410_71.sql overview

Issue#LineSeverityMessageCode Excerpt
14MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.CREATE OR REPLACE FUNCTION my_function RETURN VARCHAR2 IS BEGIN RETURN NULL; END my_function; /
212MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_function RETURN VARCHAR2
34MinorG-7410: Avoid standalone functions – put your functions in packages.my_function

guidelines/guideline_7450_75.sql overview

Issue#LineSeverityMessageCode Excerpt
17MajorG-7450: Never return a NULL value from a BOOLEAN function.RETURN NULL
25MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_function RETURN BOOLEAN
314MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION my_function RETURN BOOLEAN

guidelines/guideline_0000_00.sql overview

Issue#LineSeverityMessageCode Excerpt
113MinorG-2140: Never initialize variables with NULL.l_value PLS_INTEGER := NULL;
25InfoG-0000: Avoid using the NOSONAR marker.:= NULL; -- NOSONAR

guidelines/guideline_1010_01.sql overview

Issue#LineSeverityMessageCode Excerpt
15MinorG-1010: Try to label your sub blocks.BEGIN NULL; END
29MinorG-1010: Try to label your sub blocks.BEGIN NULL; END

guidelines/guideline_1030_03.sql overview

Issue#LineSeverityMessageCode Excerpt
17MinorG-1030: Avoid defining variables that are not used.l_first_name employees.first_name%TYPE;
29MinorG-1030: Avoid defining variables that are not used.e_good EXCEPTION;

guidelines/guideline_1050_05.sql overview

Issue#LineSeverityMessageCode Excerpt
125MajorG-7230: Avoid declaring global variables public.co_president
212MinorG-1050: Avoid using literals in your code.'AD_PRES'

guidelines/guideline_1070_07.sql overview

Issue#LineSeverityMessageCode Excerpt
15MinorG-1070: Avoid nesting comment blocks./* comment one -- nested comment two */
27MinorG-1070: Avoid nesting comment blocks.-- comment three /* nested comment four */

guidelines/guideline_3140_29.sql overview

Issue#LineSeverityMessageCode Excerpt
113MajorG-3140: Try to use anchored records as targets for your cursors.INTO l_employee_id, l_first_name, l_last_name
218MajorG-3140: Try to use anchored records as targets for your cursors.INTO l_employee_id, l_first_name, l_last_name

guidelines/guideline_4380_47.sql overview

Issue#LineSeverityMessageCode Excerpt
124MinorG-4380: Try to label your EXIT WHEN statements.EXIT WHEN l_innerlp = co_exit_value
227MinorG-4380: Try to label your EXIT WHEN statements.EXIT WHEN l_innerlp = co_exit_value

guidelines/guideline_5030_52.sql overview

Issue#LineSeverityMessageCode Excerpt
16BlockerG-5030: Never assign predefined exception names to user defined exceptions.no_data_found EXCEPTION;
216CriticalG-5070: Avoid using Oracle predefined exceptionsRAISE no_data_found

guidelines/guideline_6010_58.sql overview

Issue#LineSeverityMessageCode Excerpt
17MajorG-6010: Always use a character variable to execute dynamic SQL.EXECUTE IMMEDIATE 'select employees_seq.nextval from dual' INTO l_next_val
27MinorG-1050: Avoid using literals in your code.'select employees_seq.nextval from dual'

guidelines/guideline_7710_77.sql overview

Issue#LineSeverityMessageCode Excerpt
17MajorG-7710: Avoid cascading triggers.departments_hist
236MajorG-7710: Avoid cascading triggers.departments_hist

guidelines/guideline_8120_na.sql overview

Issue#LineSeverityMessageCode Excerpt
18MajorG-8110: Never use SELECT COUNT(*) if you are only interested in the existence of a row.count(*)
212MajorG-8120: Never check existence of a row to decide whether to create it or not.l_count = 0 THEN INSERT INTO departments VALUES in_r_department;

guidelines/guideline_8420_na.sql overview

Issue#LineSeverityMessageCode Excerpt
18MinorG-1050: Avoid using literals in your code.'Init'
214MinorG-1050: Avoid using literals in your code.'Processing '

guidelines/guideline_na_54.sql overview

Issue#LineSeverityMessageCode Excerpt
15MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.date_in IN DATE
237MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.date_in IN DATE

guidelines/guideline_1060_06.sql overview

Issue#LineSeverityMessageCode Excerpt
112MajorG-1060: Avoid storing ROWIDs or UROWIDs in database tables.ROWID

guidelines/guideline_2110_08.sql overview

Issue#LineSeverityMessageCode Excerpt
16MajorG-2110: Try to use anchored declarations for variables, constants and types.l_last_name VARCHAR2(20 CHAR);

guidelines/guideline_2120_09.sql overview

Issue#LineSeverityMessageCode Excerpt
16MinorG-2120: Try to have a single location to define your types.SUBTYPE big_string_type IS VARCHAR2(1000 CHAR);

guidelines/guideline_2130_10.sql overview

Issue#LineSeverityMessageCode Excerpt
16MinorG-2130: Try to use subtypes for constructs used often in your code.l_note VARCHAR2(1000 CHAR);

guidelines/guideline_2140_11.sql overview

Issue#LineSeverityMessageCode Excerpt
15MinorG-2140: Never initialize variables with NULL.l_note big_string := NULL;

guidelines/guideline_2150_12.sql overview

Issue#LineSeverityMessageCode Excerpt
17BlockerG-2150: Avoid comparisons with NULL value, consider using IS [NOT] NULL. l_value = NULL

guidelines/guideline_2160_13.sql overview

Issue#LineSeverityMessageCode Excerpt
16CriticalG-2160: Avoid initializing variables using functions in the declaration section.l_department_name departments.department_name%TYPE := department_api.name_by_id(in_id => co_department_id);

guidelines/guideline_2170_14.sql overview

Issue#LineSeverityMessageCode Excerpt
114MajorG-2170: Never overload variables.l_variable

guidelines/guideline_2190_17.sql overview

Issue#LineSeverityMessageCode Excerpt
110MajorG-2190: Avoid the use of ROWID or UROWID.ROWID

guidelines/guideline_2310_20.sql overview

Issue#LineSeverityMessageCode Excerpt
16MajorG-2310: Avoid using CHAR data type.CHAR(200)

guidelines/guideline_2320_21.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-2320: Avoid using VARCHAR data type.VARCHAR(200)

guidelines/guideline_2340_23.sql overview

Issue#LineSeverityMessageCode Excerpt
15MinorG-2340: Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).VARCHAR2(200)

guidelines/guideline_2410_24.sql overview

Issue#LineSeverityMessageCode Excerpt
17MinorG-2410: Try to use boolean data type for values with dual meaning. (co_numeric_false, co_numeric_true).l_bigger PLS_INTEGER;

guidelines/guideline_3130_28.sql overview

Issue#LineSeverityMessageCode Excerpt
18MinorG-3130: Try to use ANSI-join syntax.employees e, departments d

guidelines/guideline_3150_na.sql overview

Issue#LineSeverityMessageCode Excerpt
117MinorG-3150: Try to use identity columns for surrogate keys.nextval

guidelines/guideline_3190_na.sql overview

Issue#LineSeverityMessageCode Excerpt
17MajorG-3190: Avoid using NATURAL JOIN.NATURAL JOIN departments d

guidelines/guideline_3210_30.sql overview

Issue#LineSeverityMessageCode Excerpt
114MajorG-3210: Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement more than 4 times.FOR i IN 1..t_employee_ids.COUNT() LOOP UPDATE employees SET salary = salary + (salary * co_increase) WHERE employee_id = t_employee_ids(i); END LOOP process_employees

guidelines/guideline_4120_32.sql overview

Issue#LineSeverityMessageCode Excerpt
122CriticalG-4120: Avoid using %NOTFOUND directly after the FETCH when working with BULK OPERATIONS and LIMIT clause. c_employees%NOTFOUND

guidelines/guideline_4210_35.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-4210: Try to use CASE rather than an IF statement with multiple ELSIF paths.IF l_color = constant_pkg.co_red THEN do_red; ELSIF l_color = constant_pkg.co_blue THEN do_blue; ELSIF l_color = constant_pkg.co_black THEN do_black; END IF

guidelines/guideline_4220_36.sql overview

Issue#LineSeverityMessageCode Excerpt
15MinorG-4220: Try to use CASE rather than DECODE.DECODE(dummy, a, x , b, y , c, z , 0)

guidelines/guideline_4230_37.sql overview

Issue#LineSeverityMessageCode Excerpt
14CriticalG-4230: Always use COALESCE instead of NVL, if parameter 2 of the NVL function is a function call or a SELECT statement.NVL(dummy, my_package.expensive_null(value_in => dummy))

guidelines/guideline_4240_38.sql overview

Issue#LineSeverityMessageCode Excerpt
14CriticalG-4240: Always use CASE instead of NVL2 if parameter 2 or 3 of NVL2 is either a function call or a SELECT statement.NVL2(dummy, my_package.expensive_nn(value_in => dummy), my_package.expensive_null(value_in => dummy))

guidelines/guideline_4330_41.sql overview

Issue#LineSeverityMessageCode Excerpt
114MinorG-4330: Always use a CURSOR FOR loop to process the complete cursor results unless you are using bulk operations.FETCH c_employees INTO r_employee

guidelines/guideline_4340_42.sql overview

Issue#LineSeverityMessageCode Excerpt
122MinorG-4340: Always use a NUMERIC FOR loop to process a dense array.i > t_employees.COUNT()

guidelines/guideline_4350_43.sql overview

Issue#LineSeverityMessageCode Excerpt
114MajorG-4350: Always use 1 as lower and COUNT() as upper bound when looping through a dense array. t_employees.FIRST()..t_employees.LAST()

guidelines/guideline_4360_44.sql overview

Issue#LineSeverityMessageCode Excerpt
121MinorG-4360: Always use a WHILE loop to process a loose array.1 .. t_employees.COUNT()

guidelines/guideline_4375_46.sql overview

Issue#LineSeverityMessageCode Excerpt
112MinorG-4375: Always use EXIT WHEN instead of an IF statement to exit from a loop.EXIT process_employees

guidelines/guideline_4385_48.sql overview

Issue#LineSeverityMessageCode Excerpt
111MajorG-4385: Never use a cursor for loop to check whether a cursor returns data.FOR r_employee IN c_employees LOOP l_employee_found := TRUE; END LOOP check_employees

guidelines/guideline_4390_49.sql overview

Issue#LineSeverityMessageCode Excerpt
117MajorG-4390: Avoid use of unreferenced FOR loop indexes.FOR i IN co_lower_bound .. co_upper_bound LOOP sys.dbms_output.put_line(l_row || co_delimiter || l_value); l_row := l_row + co_row_incr; l_value := l_value + co_value_incr; END LOOP for_loop

guidelines/guideline_4395_50.sql overview

Issue#LineSeverityMessageCode Excerpt
16MinorG-4395: Avoid hard-coded upper or lower bound values with FOR loops. 1..5

guidelines/guideline_5020_51.sql overview

Issue#LineSeverityMessageCode Excerpt
112CriticalG-5020: Never handle unnamed exceptions using the error number.WHEN OTHERS THEN IF SQLCODE = co_no_data_found THEN NULL; END IF;

guidelines/guideline_5040_53.sql overview

Issue#LineSeverityMessageCode Excerpt
17MajorG-5040: Avoid use of WHEN OTHERS clause in an exception section without any other specific handlers.WHEN OTHERS THEN my_package.some_further_processing();

guidelines/guideline_5070_57.sql overview

Issue#LineSeverityMessageCode Excerpt
15CriticalG-5070: Avoid using Oracle predefined exceptionsRAISE NO_DATA_FOUND

guidelines/guideline_7110_60.sql overview

Issue#LineSeverityMessageCode Excerpt
18MajorG-7110: Try to use named notation when calling program units.co_id

guidelines/guideline_7210_65.sql overview

Issue#LineSeverityMessageCode Excerpt
15MinorG-7210: Try to keep your packages small. Include only few procedures and functions that are used in the same context.example_pkg

guidelines/guideline_7310_69.sql overview

Issue#LineSeverityMessageCode Excerpt
14MinorG-7310: Avoid standalone procedures – put your procedures in packages.my_procedure

guidelines/guideline_7320_70.sql overview

Issue#LineSeverityMessageCode Excerpt
112MajorG-7320: Avoid using RETURN statements in a PROCEDURE.RETURN

guidelines/guideline_7460_na.sql overview

Issue#LineSeverityMessageCode Excerpt
15MajorG-7460: Try to define your packaged/standalone function to be deterministic if appropriate.FUNCTION name_by_id (in_department_id IN departments.department_id%TYPE) RETURN departments.department_name%TYPE

guidelines/guideline_7510_76.sql overview

Issue#LineSeverityMessageCode Excerpt
17MajorG-7510: Always prefix ORACLE supplied packages with owner schema name.dbms_output.put_line(co_hello_world)

guidelines/guideline_7810_na.sql overview

Issue#LineSeverityMessageCode Excerpt
17MajorG-7810: Do not use SQL inside PL/SQL to read sequence numbers (or SYSDATE)employees_seq.NEXTVAL

guidelines/guideline_8110_78.sql overview

Issue#LineSeverityMessageCode Excerpt
19MajorG-8110: Never use SELECT COUNT(*) if you are only interested in the existence of a row.count(*)

guidelines/guideline_8210_79.sql overview

Issue#LineSeverityMessageCode Excerpt
110MajorG-8210: Always use synonyms when accessing objects of another application schema.oe.products