db* CODECOP Version 4.2.0 (2021-09-10 14:21:14 +0200) for Trivadis PL/SQL & SQL Coding Guidelines Version 4.0

Copyright 2010-2021 by Trivadis AG
Saegereistrasse 29
CH-8152 Glattbrugg (Zurich)
www.trivadis.com

License

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

Parameters

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

Processing

Start of processing2021-09-10 17:49:59
End of processing2021-09-10 17:50:12
Processing time in seconds12.308

Content

Number of files116
Number of bytes217,696
Number of lines (LOC)9,029
Number of comment lines1,091
Number of blank lines1,404
Number of net lines6,534
Number of commands279
Number of statements (PL/SQL)1,545
Max. cyclomatic complexity5   ( < 11   11..50   > 50 )
Max. Halstead volume575   ( < 1001   1001..3000   > 3000 )
Min. maintainability index (MI)83   ( > 84   64..84   < 64 )
Number of issues447
Number of warnings447
Number of errors0




Issue Overview

#%SeverityCharacteristicsMessage
10.2%BlockerPortability, Reliability
G-2150: Avoid comparisons with NULL value, consider using IS [NOT] NULL.
10.2%BlockerReliability, Testability
G-5030: Never assign predefined exception names to user defined exceptions.
10.2%BlockerReliability, Testability
G-7720: Never use multiple UPDATE OF in trigger event clause.
20.4%CriticalReliability
G-5070: Avoid using Oracle predefined exceptions
10.2%CriticalReliability
G-2160: Avoid initializing variables using functions in the declaration section.
10.2%CriticalReliability
G-3310: Never commit within a cursor loop.
10.2%CriticalReliability
G-4120: Avoid using %NOTFOUND directly after the FETCH when working with BULK OPERATIONS and LIMIT clause.
10.2%CriticalEfficiency, Reliability
G-4230: Always use a COALESCE instead of a NVL command, if parameter 2 of the NVL function is a function call or a SELECT statement.
10.2%CriticalEfficiency, Reliability
G-4240: Always use a CASE instead of a NVL2 command if parameter 2 or 3 of NVL2 is either a function call or a SELECT statement.
10.2%CriticalMaintainability
G-5020: Never handle unnamed exceptions using the error number.
449.8%MajorEfficiency
G-7460: Try to define your packaged/standalone function deterministic if appropriate.
184.0%MajorMaintainability, Testability
G-2135: Avoid assigning values to local variables that are not used by a subsequent statement.
81.8%MajorEfficiency, Maintainability
G-7160: Always explicitly state parameter mode.
61.3%MajorChangeability, Maintainability
G-7110: Try to use named notation when calling program units.
51.1%MajorEfficiency
G-3210: Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for more than 4 times.
40.9%MajorMaintainability
G-3120: Always use table aliases when your SQL statement involves more than one source.
40.9%MajorMaintainability
G-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.
30.7%MajorMaintainability
G-2180: Never use quoted identifiers.
30.7%MajorChangeability, Reliability
G-3180: Always specify column names instead of positional references in ORDER BY clauses.
30.7%MajorReliability
G-5040: Avoid use of WHEN OTHERS clause in an exception section without any other specific handlers.
30.7%MajorMaintainability, Reliability, Testability
G-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.
30.7%MajorReliability
G-7230: Avoid declaring global variables public.
20.4%MajorPortability
G-2510: Avoid using the LONG and LONG RAW data types.
20.4%MajorMaintainability, Reliability
G-3140: Try to use anchored records as targets for your cursors.
20.4%MajorChangeability, Reliability
G-3190: Avoid using NATURAL JOIN.
20.4%MajorEfficiency
G-4385: Never use a cursor for loop to check whether a cursor returns data.
20.4%MajorMaintainability, Testability
G-7430: Try to use no more than one RETURN statement within a function.
20.4%MajorSecurity
G-7510: Always prefix ORACLE supplied packages with owner schema name.
20.4%MajorMaintainability, Testability
G-7710: Avoid cascading triggers.
20.4%MajorReliability
G-7730: Avoid multiple DML events per trigger if primary key is assigned in trigger.
20.4%MajorEfficiency
G-8110: Never use SELECT COUNT(*) if you are only interested in the existence of a row.
20.4%MajorMaintainability, Reliability, Security, Testability
G-9020: Try to use a format model and NLS_NUMERIC_CHARACTERS in string to number conversion functions.
10.2%MajorReliability
G-1060: Avoid storing ROWIDs or UROWIDs in database tables.
10.2%MajorMaintainability, Reliability
G-2110: Try to use anchored declarations for variables, constants and types.
10.2%MajorReliability
G-2170: Never overload variables.
10.2%MajorPortability, Reliability
G-2190: Avoid using ROWID or UROWID.
10.2%MajorReliability
G-2310: Avoid using CHAR data type.
10.2%MajorPortability
G-2320: Never use VARCHAR data type.
10.2%MajorPortability
G-2330: Never use zero-length strings to substitute NULL.
10.2%MajorMaintainability, Reliability
G-3110: Always specify the target columns when coding an insert statement.
10.2%MajorTestability
G-3185: Never use ROWNUM at the same query level as ORDER BY.
10.2%MajorReusability, Testability
G-3320: Try to move transactions within a non-cursor loop into procedures.
10.2%MajorEfficiency, Reliability
G-4130: Always close locally opened cursors.
10.2%MajorReliability
G-4140: Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute.
10.2%MajorMaintainability, Testability
G-4210: Try to use CASE rather than an IF statement with multiple ELSIF paths.
10.2%MajorReliability, Testability
G-4250: Avoid using identical conditions in different branches of the same IF or CASE statement.
10.2%MajorMaintainability, Testability
G-4310: Never use GOTO statements in your code.
10.2%MajorReliability, Testability
G-4325: Never reuse labels in inner scopes.
10.2%MajorReliability
G-4350: Always use 1 as lower and COUNT() as upper bound when looping through a dense array.
10.2%MajorEfficiency
G-4390: Avoid use of unreferenced FOR loop indexes.
10.2%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.2%MajorReliability
G-5060: Avoid unhandled exceptions
10.2%MajorMaintainability, Testability
G-6010: Always use a character variable to execute dynamic SQL.
10.2%MajorMaintainability, Reliability
G-7140: Always ensure that locally defined procedures or functions are referenced.
10.2%MajorMaintainability, Testability
G-7320: Avoid using RETURN statements in a PROCEDURE.
10.2%MajorTestability
G-7330: Always assign values to OUT parameters.
10.2%MajorMaintainability
G-7420: Always make the RETURN statement the last statement of your function.
10.2%MajorReusability
G-7440: Never use OUT parameters to return values from a function.
10.2%MajorReliability, Testability
G-7450: Never return a NULL value from a BOOLEAN function.
10.2%MajorEfficiency, Maintainability
G-7810: Never use SQL inside PL/SQL to read sequence numbers (or SYSDATE).
10.2%MajorEfficiency, Reliability
G-8120: Never check existence of a row to decide whether to create it or not.
10.2%MajorChangeability, Maintainability
G-8210: Always use synonyms when accessing objects of another application schema.
10.2%MajorMaintainability, Reliability, Security, Testability
G-9010: Always use a format model in string to date/time conversion functions.
8719.5%MinorChangeability
G-1050: Avoid using literals in your code.
8017.9%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.
163.6%MinorChangeability
G-2130: Try to use subtypes for constructs used often in your code.
92.0%MinorMaintainability
G-7125: Always use CREATE OR REPLACE instead of CREATE alone.
81.8%MinorMaintainability
G-4320: Always label your loops.
61.3%MinorMaintainability
G-1040: Avoid dead code.
61.3%MinorReliability, Testability
G-9030: Try to define a default value on conversion errors.
51.1%MinorMaintainability
G-1020: Always have a matching loop or block label.
40.9%MinorEfficiency, Maintainability
G-1030: Avoid defining variables that are not used.
30.7%MinorMaintainability
G-2185: Avoid using overly short names for explicitly or implicitly declared identifiers.
30.7%MinorMaintainability, Reliability
G-3150: Try to use identity columns for surrogate keys.
30.7%MinorMaintainability
G-4330: Always use a CURSOR FOR loop to process the complete cursor results unless you are using bulk operations.
30.7%MinorMaintainability
G-4380: Try to label your EXIT WHEN statements.
30.7%MinorChangeability, Maintainability
G-4395: Avoid hard-coded upper or lower bound values with FOR loops.
30.7%MinorMaintainability
G-7120: Always add the name of the program unit to its end keyword.
20.4%MinorMaintainability
G-1010: Try to label your sub blocks.
20.4%MinorMaintainability
G-1070: Avoid nesting comment blocks.
20.4%MinorMaintainability
G-2140: Never initialize variables with NULL.
20.4%MinorEfficiency
G-2220: Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values.
20.4%MinorEfficiency
G-2230: Try to use SIMPLE_INTEGER datatype when appropriate.
20.4%MinorReliability
G-2340: Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).
20.4%MinorMaintainability, Portability
G-4220: Try to use CASE rather than DECODE.
20.4%MinorMaintainability
G-4375: Always use EXIT WHEN instead of an IF statement to exit from a loop.
20.4%MinorTestability
G-5080: Always use FORMAT_ERROR_BACKTRACE when using FORMAT_ERROR_STACK or SQLERRM.
20.4%MinorEfficiency, Maintainability
G-7150: Try to remove unused parameters.
20.4%MinorChangeability
G-7220: Always use forward declaration for private functions and procedures.
10.2%MinorEfficiency, Testability
G-1080: Avoid using the same expression on both sides of a relational comparison operator or a logical operator.
10.2%MinorChangeability
G-2120: Try to have a single location to define your types.
10.2%MinorMaintainability
G-2145: Never self-assign a variable.
10.2%MinorEfficiency
G-2210: Avoid declaring NUMBER variables, constants or subtypes with no precision.
10.2%MinorMaintainability
G-2410: Try to use boolean data type for values with dual meaning.
10.2%MinorMaintainability, Portability, Reusability
G-2610: Never use self-defined weak ref cursor types.
10.2%MinorMaintainability
G-3115: Avoid self-assigning a column.
10.2%MinorMaintainability, Portability
G-3130: Try to use ANSI SQL-92 join syntax.
10.2%MinorMaintainability
G-3195: Always use wildcards in a LIKE clause.
10.2%MinorMaintainability
G-4110: Always use %NOTFOUND instead of NOT %FOUND to check whether a cursor returned data.
10.2%MinorTestability
G-4260: Avoid inverting boolean conditions with NOT.
10.2%MinorTestability
G-4270: Avoid comparing boolean values to boolean literals.
10.2%MinorMaintainability
G-4340: Always use a NUMERIC FOR loop to process a dense array.
10.2%MinorEfficiency
G-4360: Always use a WHILE loop to process a loose array.
10.2%MinorMaintainability
G-6020: Try to use output bind arguments in the RETURNING INTO clause of dynamic DML statements rather than the USING clause.
10.2%MinorEfficiency, Maintainability
G-7210: Try to keep your packages small. Include only few procedures and functions that are used in the same context.
10.2%MinorMaintainability
G-7250: Never use RETURN in package initialization block.
10.2%MinorMaintainability
G-7310: Avoid standalone procedures – put your procedures in packages.
10.2%MinorMaintainability
G-7410: Avoid standalone functions – put your functions in packages.
10.2%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




File Overview

File name# Warnings# Errors# Bytes# Lines# Net lines# Cmds# StmtsMax. cyclomatic complexity Max. Halstead volume Min. MI Elapsed seconds
guidelines/guideline_5080_na.sql2001,7724836210136 105 0.028
guidelines/guideline_4220_36.sql1801,085392540221 0.031
guidelines/guideline_3320_na.sql1501,206442929127 109 0.039
guidelines/guideline_7170_na.sql1204,2288066224575 111 0.044
guidelines/guideline_8410_na.sql1202,8099465416318 95 0.032
guidelines/guideline_1040_04.sql1101,5686548219349 95 0.091
guidelines/guideline_9020_na.sql1101,79036182240 126 0.022
guidelines/guideline_9030_na.sql1101,08529182240 126 0.017
guidelines/guideline_9010_na.sql1001,61034182232 127 0.020
guidelines/guideline_3310_na.sql902,4578052315372 89 0.046
guidelines/guideline_7130_62.sql902,6567856214169 99 0.032
guidelines/guideline_7220_66.sql902,0207660416114 103 0.086
guidelines/guideline_7330_na.sql8078531212228 136 0.016
guidelines/guideline_7430_72.sql801,091453438102 111 0.019
guidelines/guideline_3110_26.sql70609241420221 0.027
guidelines/guideline_7230_67.sql703,32084525661 123 0.023
guidelines/guideline_7420_73.sql701,062403229114 107 0.018
guidelines/guideline_2510_25.sql60925402642221 0.021
guidelines/guideline_3120_27.sql602,068553150221 0.040
guidelines/guideline_3170_na.sql601,3664816100221 0.027
guidelines/guideline_4310_39.sql603,64510377322492 86 0.058
guidelines/guideline_7120_61.sql601,17349362869 107 0.023
guidelines/guideline_7150_64.sql601,5054939212108 103 0.025
guidelines/guideline_7250_na.sql601,88153342884 116 0.026
guidelines/guideline_1020_02.sql501,5388262226306 86 0.087
guidelines/guideline_2135_na.sql501,503473529189 102 0.043
guidelines/guideline_2145_na.sql5066225162463 127 0.024
guidelines/guideline_2180_15.sql5054530222451 121 0.024
guidelines/guideline_2185_16.sql5070032222451 121 0.021
guidelines/guideline_2210_18.sql50822281622143 0.025
guidelines/guideline_2220_19.sql50987321622143 0.021
guidelines/guideline_2330_22.sql50714261522143 0.020
guidelines/guideline_3180_na.sql50751271520221 0.020
guidelines/guideline_4130_33.sql501,798443127128 117 0.036
guidelines/guideline_4140_34.sql502,4066740211143 123 0.037
guidelines/guideline_4270_na.sql50744292026121 117 0.021
guidelines/guideline_4320_40.sql501,4216753218346 87 0.039
guidelines/guideline_5010_na.sql5090332122479 158 0.020
guidelines/guideline_5060_56.sql501,83847292682 109 0.019
guidelines/guideline_6020_59.sql501,87143312241 126 0.023
guidelines/guideline_7125_na.sql501,25649362869 107 0.021
guidelines/guideline_1080_na.sql40949281520221 0.035
guidelines/guideline_2410_24.sql40952433237124 114 0.030
guidelines/guideline_3195_na.sql40938281330221 0.021
guidelines/guideline_7160_68.sql401,437291820221 0.014
guidelines/guideline_8310_na.sql401,438443026119 126 0.022
guidelines/guideline_2120_09.sql3087233213239 134 0.034
guidelines/guideline_2130_10.sql301,15339203228 136 0.035
guidelines/guideline_2230_na.sql301,548381622143 0.020
guidelines/guideline_3185_na.sql30856362320221 0.020
guidelines/guideline_4110_31.sql301,0034834210138 106 0.028
guidelines/guideline_4325_na.sql301,1474434210104 105 0.027
guidelines/guideline_4370_45.sql301,8867862222411 83 0.038
guidelines/guideline_4385_48.sql30769342626113 117 0.018
guidelines/guideline_5050_55.sql301,3252682230 181 0.016
guidelines/guideline_7140_63.sql301,58842242425 136 0.019
guidelines/guideline_7410_71.sql30625221222143 0.015
guidelines/guideline_7440_74.sql3063524152321 133 0.014
guidelines/guideline_7450_75.sql30547231422143 0.012
guidelines/guideline_7730_na.sql301,652553769125 110 0.019
guidelines/guideline_8510_na.sql301,196372825131 106 0.022
guidelines/guideline_0000_00.sql2053028183341 134 1.078
guidelines/guideline_1010_01.sql20392302021222 140 0.042
guidelines/guideline_1030_03.sql201,262433427139 106 0.071
guidelines/guideline_1070_07.sql20451231024221 0.013
guidelines/guideline_3140_29.sql201,2434534210196 128 0.032
guidelines/guideline_3190_na.sql201,334502150221 0.024
guidelines/guideline_4260_na.sql2048525162472 122 0.021
guidelines/guideline_4380_47.sql201,9956049217444 92 0.029
guidelines/guideline_5030_52.sql202,083653929259 97 0.026
guidelines/guideline_6010_58.sql2058223142239 134 0.017
guidelines/guideline_7110_60.sql2078825142270 131 0.015
guidelines/guideline_7710_77.sql201,872594734189 95 0.025
guidelines/guideline_7720_na.sql201,49637242263 116 0.018
guidelines/guideline_7810_na.sql2051623142233 127 0.013
guidelines/guideline_8120_na.sql201,15137252591 108 0.018
guidelines/guideline_na_54.sql202,4326949210143 105 0.033
guidelines/guideline_1050_05.sql101,8215838310124 101 0.047
guidelines/guideline_1060_06.sql101,01334222257 143 0.031
guidelines/guideline_2110_08.sql101,328403026100 111 0.040
guidelines/guideline_2140_11.sql1033020122241 134 0.024
guidelines/guideline_2150_12.sql1055526162431 127 0.026
guidelines/guideline_2160_13.sql10907322327185 105 0.027
guidelines/guideline_2170_14.sql101,1904234212217 110 0.046
guidelines/guideline_2190_17.sql101,02931182245 125 0.023
guidelines/guideline_2310_20.sql10519201020221 0.018
guidelines/guideline_2320_21.sql1065719820221 0.019
guidelines/guideline_2340_23.sql1070419820221 0.016
guidelines/guideline_2610_na.sql101,009392926174 105 0.027
guidelines/guideline_3115_na.sql1050717520221 0.020
guidelines/guideline_3130_28.sql10762261620221 0.021
guidelines/guideline_3150_na.sql101,63945214130 135 0.035
guidelines/guideline_3160_na.sql101,555552946151 111 0.026
guidelines/guideline_3210_30.sql101,596443226226 102 0.031
guidelines/guideline_4120_32.sql102,7079569321304 95 0.044
guidelines/guideline_4210_35.sql10823342629175 105 0.019
guidelines/guideline_4230_37.sql1065217520221 0.017
guidelines/guideline_4240_38.sql10780221120221 0.018
guidelines/guideline_4250_na.sql101,1403928210151 105 0.021
guidelines/guideline_4330_41.sql101,150422828173 103 0.021
guidelines/guideline_4340_42.sql101,490493329345 101 0.027
guidelines/guideline_4350_43.sql101,855553537151 109 0.030
guidelines/guideline_4360_44.sql101,9715839211367 98 0.038
guidelines/guideline_4375_46.sql10927372429132 105 0.021
guidelines/guideline_4390_49.sql101,682473628332 102 0.028
guidelines/guideline_4395_50.sql1078830192491 116 0.019
guidelines/guideline_5020_51.sql10948332227104 109 0.022
guidelines/guideline_5040_53.sql101,50033142439 155 0.018
guidelines/guideline_5070_57.sql10934261022145 0.013
guidelines/guideline_7210_65.sql1069,4584,2173,3682840221 4.451
guidelines/guideline_7310_69.sql10672271632221 0.014
guidelines/guideline_7320_70.sql101,391432827129 108 0.018
guidelines/guideline_7460_na.sql10799201020221 0.011
guidelines/guideline_7510_76.sql1057722122247 133 0.012
guidelines/guideline_8110_78.sql101,520463527244 97 0.023
guidelines/guideline_8210_79.sql101,508463136114 107 0.019
Total4470217,6969,0296,5342791,5455575838.627




File Issues

guidelines/guideline_5080_na.sql overview

Issue#LineSeverityMessageCode Excerpt
117MajorG-7110: Try to use named notation when calling program units.in_customer_id
217MajorG-7110: Try to use named notation when calling program units.in_discount
336MajorG-7110: Try to use named notation when calling program units.in_customer_id
436MajorG-7110: Try to use named notation when calling program units.in_discount
513MajorG-7160: Always explicitly state parameter mode.in_customer_id customer.id%type
614MajorG-7160: Always explicitly state parameter mode.in_discount customer.discount_percentage%type
732MajorG-7160: Always explicitly state parameter mode.in_customer_id customer.id%type
833MajorG-7160: Always explicitly state parameter mode.in_discount customer.discount_percentage%type
943MajorG-7510: Always prefix ORACLE supplied packages with owner schema name.dbms_utility.format_error_backtrace
1018MinorG-1050: Avoid using literals in your code.10293847
1123MinorG-1050: Avoid using literals in your code.'Error: '
1237MinorG-1050: Avoid using literals in your code.10293847
1343MinorG-1050: Avoid using literals in your code.'Error: '
1443MinorG-1050: Avoid using literals in your code.' - Backtrace: '
1523MinorG-5080: Always use FORMAT_ERROR_BACKTRACE when using FORMAT_ERROR_STACK or SQLERRM.sqlerrm
1643MinorG-5080: Always use FORMAT_ERROR_BACKTRACE when using FORMAT_ERROR_STACK or SQLERRM.sqlerrm
1713MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_customer_id customer.id%type
1814MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_discount customer.discount_percentage%type
1932MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_customer_id customer.id%type
2033MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_discount customer.discount_percentage%type

guidelines/guideline_4220_36.sql overview

Issue#LineSeverityMessageCode Excerpt
110MinorG-1050: Avoid using literals in your code.'X'
211MinorG-1050: Avoid using literals in your code.'Y'
311MinorG-1050: Avoid using literals in your code.2
412MinorG-1050: Avoid using literals in your code.'Z'
512MinorG-1050: Avoid using literals in your code.3
616MinorG-1050: Avoid using literals in your code.'X'
717MinorG-1050: Avoid using literals in your code.'Y'
817MinorG-1050: Avoid using literals in your code.2
924MinorG-1050: Avoid using literals in your code.'X'
1025MinorG-1050: Avoid using literals in your code.'Y'
1125MinorG-1050: Avoid using literals in your code.2
1226MinorG-1050: Avoid using literals in your code.'Z'
1326MinorG-1050: Avoid using literals in your code.3
1432MinorG-1050: Avoid using literals in your code.'X'
1533MinorG-1050: Avoid using literals in your code.'Y'
1633MinorG-1050: Avoid using literals in your code.2
1710MinorG-4220: Try to use CASE rather than DECODE.decode(dummy, 'X', 1 , 'Y', 2 , 'Z', 3 , 0)
1816MinorG-4220: Try to use CASE rather than DECODE.decode(dummy, 'X', 1 , 'Y', 2 , null, -1 , 0)

guidelines/guideline_3320_na.sql overview

Issue#LineSeverityMessageCode Excerpt
112MajorG-3210: Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for more than 4 times.for l_counter in 1..5 loop insert into headers (id, text) values (l_counter, 'Number '||l_counter); insert into lines (header_id, line_no, text) select l_counter, rownum, 'Line '||rownum from dual connect by level <= 3; commit; end loop
214MajorG-3210: Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for more than 4 times.for l_counter in 1..5 loop insert into headers (id, text) values (l_counter, 'Number '||l_counter); insert into lines (header_id, line_no, text) select l_counter, rownum, 'Line '||rownum from dual connect by level <= 3; commit; end loop
319MajorG-3320: Try to move transactions within a non-cursor loop into procedures.commit
412MinorG-1050: Avoid using literals in your code.'Number '
515MinorG-1050: Avoid using literals in your code.'Line '
617MinorG-1050: Avoid using literals in your code.3
730MinorG-1050: Avoid using literals in your code.'Number '
833MinorG-1050: Avoid using literals in your code.'Line '
935MinorG-1050: Avoid using literals in your code.3
1011MinorG-4320: Always label your loops.for l_counter in 1..5 loop insert into headers (id, text) values (l_counter, 'Number '||l_counter); insert into lines (header_id, line_no, text) select l_counter, rownum, 'Line '||rownum from dual connect by level <= 3; commit; end loop
1140MinorG-4320: Always label your loops.for l_counter in 1..5 loop create_rows(l_counter); end loop
1211MinorG-4395: Avoid hard-coded upper or lower bound values with FOR loops.1..5
1340MinorG-4395: Avoid hard-coded upper or lower bound values with FOR loops.1..5
1426MinorG-7120: Always add the name of the program unit to its end keyword.create_rows
1527MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.p_header_id in headers.id%type

guidelines/guideline_7170_na.sql overview

Issue#LineSeverityMessageCode Excerpt
112MinorG-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
213MinorG-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
314MinorG-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
415MinorG-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
516MinorG-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
617MinorG-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
718MinorG-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
819MinorG-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
920MinorG-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
1021MinorG-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
1122MinorG-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
1258MinorG-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_8410_na.sql overview

Issue#LineSeverityMessageCode Excerpt
138MajorG-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_name => co_lock_name); raise;
289MajorG-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;
348MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function request_lock( in_lock_name in varchar2, in_release_on_commit in boolean := false) return varchar2
432MinorG-2130: Try to use subtypes for constructs used often in your code.co_lock_name constant varchar2(30 char) := 'APPLICATION_LOCK';
552MinorG-2130: Try to use subtypes for constructs used often in your code.l_lock_handle varchar2(128 char);
682MinorG-2130: Try to use subtypes for constructs used often in your code.l_handle varchar2(128 char);
783MinorG-2130: Try to use subtypes for constructs used often in your code.co_lock_name constant varchar2(30 char) := 'APPLICATION_LOCK';
817MinorG-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
923MinorG-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
1049MinorG-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
1150MinorG-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
1271MinorG-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_1040_04.sql overview

Issue#LineSeverityMessageCode Excerpt
111MinorG-1040: Avoid dead code.2=3
219MinorG-1040: Avoid dead code.exit my_loop
326MinorG-1040: Avoid dead code.'x' = 'y'
437MinorG-1040: Avoid dead code.5=6
543MinorG-1040: Avoid dead code.return
611MinorG-1050: Avoid using literals in your code.2
711MinorG-1050: Avoid using literals in your code.3
826MinorG-1050: Avoid using literals in your code.'x'
926MinorG-1050: Avoid using literals in your code.'y'
1037MinorG-1050: Avoid using literals in your code.5
1137MinorG-1050: Avoid using literals in your code.6

guidelines/guideline_9020_na.sql overview

Issue#LineSeverityMessageCode Excerpt
121MajorG-9020: Try to use a format model and NLS_NUMERIC_CHARACTERS in string to number conversion functions.to_number(in_salary)
232MinorG-1050: Avoid using literals in your code.'99999999999999999999.99999'
332MinorG-1050: Avoid using literals in your code.q'[nls_numeric_characters='.,']'
417MinorG-7125: Always use CREATE OR REPLACE instead of CREATE alone.employee_api
528MinorG-7125: Always use CREATE OR REPLACE instead of CREATE alone.employee_api
618MinorG-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
718MinorG-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 varchar2
829MinorG-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
929MinorG-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 varchar2
1021MinorG-9030: Try to define a default value on conversion errors.to_number(in_salary)
1132MinorG-9030: Try to define a default value on conversion errors.to_number(in_salary, '99999999999999999999.99999', q'[nls_numeric_characters='.,']')

guidelines/guideline_9030_na.sql overview

Issue#LineSeverityMessageCode Excerpt
114MajorG-9020: Try to use a format model and NLS_NUMERIC_CHARACTERS in string to number conversion functions.to_number(in_salary)
225MinorG-1050: Avoid using literals in your code.'99999999999999999999.99999'
325MinorG-1050: Avoid using literals in your code.q'[nls_numeric_characters='.,']'
410MinorG-7125: Always use CREATE OR REPLACE instead of CREATE alone.employee_api
521MinorG-7125: Always use CREATE OR REPLACE instead of CREATE alone.employee_api
611MinorG-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
711MinorG-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 varchar2
822MinorG-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
922MinorG-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 varchar2
1014MinorG-9030: Try to define a default value on conversion errors.to_number(in_salary)
1125MinorG-9030: Try to define a default value on conversion errors.to_number(in_salary, '99999999999999999999.99999', q'[nls_numeric_characters='.,']')

guidelines/guideline_9010_na.sql overview

Issue#LineSeverityMessageCode Excerpt
119MajorG-9010: Always use a format model in string to date/time conversion functions.to_date(in_dob_str)
230MinorG-1050: Avoid using literals in your code.'YYYY-MM-DD'
315MinorG-7125: Always use CREATE OR REPLACE instead of CREATE alone.employee_api
426MinorG-7125: Always use CREATE OR REPLACE instead of CREATE alone.employee_api
516MinorG-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
616MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_dob_str in varchar2
727MinorG-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
827MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_dob_str in varchar2
919MinorG-9030: Try to define a default value on conversion errors.to_date(in_dob_str)
1030MinorG-9030: Try to define a default value on conversion errors.to_date(in_dob_str, 'YYYY-MM-DD')

guidelines/guideline_3310_na.sql overview

Issue#LineSeverityMessageCode Excerpt
136CriticalG-3310: Never commit within a cursor loop.commit
230MajorG-3210: Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for more than 4 times.for r_order in ( select o.order_id, o.customer_id from orders o where o.order_status = 'New' ) loop l_discount := sales_api.calculate_discount(p_customer_id => r_order.customer_id); update order_lines ol set ol.discount = l_discount where ol.order_id = r_order.order_id; l_counter := l_counter + 1; if l_counter = 100 then commit; l_counter := 0; end if; end loop
357MajorG-3210: Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for more than 4 times.for r_order in ( select o.order_id, o.customer_id from orders o where o.order_status = 'New' ) loop l_discount := sales_api.calculate_discount(p_customer_id => r_order.customer_id); update order_lines ol set ol.discount = l_discount where ol.order_id = r_order.order_id; end loop
426MinorG-1050: Avoid using literals in your code.'New'
535MinorG-1050: Avoid using literals in your code.100
653MinorG-1050: Avoid using literals in your code.'New'
774MinorG-1050: Avoid using literals in your code.'New'
823MinorG-4320: Always label your loops.for r_order in ( select o.order_id, o.customer_id from orders o where o.order_status = 'New' ) loop l_discount := sales_api.calculate_discount(p_customer_id => r_order.customer_id); update order_lines ol set ol.discount = l_discount where ol.order_id = r_order.order_id; l_counter := l_counter + 1; if l_counter = 100 then commit; l_counter := 0; end if; end loop
950MinorG-4320: Always label your loops.for r_order in ( select o.order_id, o.customer_id from orders o where o.order_status = 'New' ) loop l_discount := sales_api.calculate_discount(p_customer_id => r_order.customer_id); update order_lines ol set ol.discount = l_discount where ol.order_id = r_order.order_id; end loop

guidelines/guideline_7130_62.sql overview

Issue#LineSeverityMessageCode Excerpt
122MajorG-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.r_emp
224MajorG-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.r_emp
324MajorG-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.r_emp
419MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function commission return number
550MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function commission (in_salary in employees.salary%type ,in_comm_pct in employees.commission_pct%type) return number
616MinorG-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
747MinorG-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
850MinorG-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
951MinorG-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_7220_66.sql overview

Issue#LineSeverityMessageCode Excerpt
114MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function does_exist (in_department_id in departments.department_id%type) return boolean
248MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function does_exist (in_department_id in departments.department_id%type) return boolean
358MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function does_exist (in_department_id in departments.department_id%type) return boolean
414MinorG-7220: Always use forward declaration for private functions and procedures.does_exist
514MinorG-7220: Always use forward declaration for private functions and procedures.does_exist
614MinorG-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
732MinorG-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
851MinorG-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
958MinorG-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_7330_na.sql overview

Issue#LineSeverityMessageCode Excerpt
116MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_message := 'Hello, ' || in_name
212MajorG-7330: Always assign values to OUT parameters.out_greeting
316MinorG-1050: Avoid using literals in your code.'Hello, '
428MinorG-1050: Avoid using literals in your code.'Hello, '
514MinorG-2130: Try to use subtypes for constructs used often in your code.l_message varchar2(100 char);
612MinorG-7150: Try to remove unused parameters.out_greeting out varchar2
711MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_name in varchar2
824MinorG-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.in_name in varchar2

guidelines/guideline_7430_72.sql overview

Issue#LineSeverityMessageCode Excerpt
112MajorG-7430: Try to use no more than one RETURN statement within a function.return true
214MajorG-7430: Try to use no more than one RETURN statement within a function.return false
38MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_function (in_value in pls_integer) return boolean
422MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_function (in_value in pls_integer) return boolean
539MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_function (in_value in pls_integer) return boolean
68MinorG-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
722MinorG-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
839MinorG-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_3110_26.sql overview

Issue#LineSeverityMessageCode Excerpt
18MajorG-3110: Always specify the target columns when coding an insert statement.insert into departments values (departments_seq.nextval ,'Support' ,100 ,10)
210MinorG-1050: Avoid using literals in your code.'Support'
311MinorG-1050: Avoid using literals in your code.100
412MinorG-1050: Avoid using literals in your code.10
520MinorG-1050: Avoid using literals in your code.'Support'
621MinorG-1050: Avoid using literals in your code.100
722MinorG-1050: Avoid using literals in your code.10

guidelines/guideline_7230_67.sql overview

Issue#LineSeverityMessageCode Excerpt
125MajorG-7230: Avoid declaring global variables public.g_salary_increase
228MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function salary_increase return types_up.sal_increase_type
339MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function salary_increase return types_up.sal_increase_type
455MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function salary_increase return types_up.sal_increase_type
571MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function salary_increase return types_up.sal_increase_type
633MinorG-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
764MinorG-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_7420_73.sql overview

Issue#LineSeverityMessageCode Excerpt
118MajorG-7420: Always make the RETURN statement the last statement of your function.return l_ret
28MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_function (in_from in pls_integer , in_to in pls_integer) return pls_integer
327MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_function (in_from in pls_integer , in_to in pls_integer) return pls_integer
48MinorG-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
59MinorG-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
627MinorG-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
728MinorG-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
110MajorG-2510: Avoid using the LONG and LONG RAW data types.g_long long;
211MajorG-2510: Avoid using the LONG and LONG RAW data types.g_raw long raw;
310MajorG-7230: Avoid declaring global variables public.g_long
411MajorG-7230: Avoid declaring global variables public.g_raw
532MinorG-1030: Avoid defining variables that are not used.g_long clob;
633MinorG-1030: Avoid defining variables that are not used.g_raw blob;

guidelines/guideline_3120_27.sql overview

Issue#LineSeverityMessageCode Excerpt
19MajorG-3120: Always use table aliases when your SQL statement involves more than one source.last_name
210MajorG-3120: Always use table aliases when your SQL statement involves more than one source.first_name
311MajorG-3120: Always use table aliases when your SQL statement involves more than one source.department_name
440MajorG-3120: Always use table aliases when your SQL statement involves more than one source.employee_id
542MinorG-1050: Avoid using literals in your code.'%Manager%'
653MinorG-1050: Avoid using literals in your code.'%Manager%'

guidelines/guideline_3170_na.sql overview

Issue#LineSeverityMessageCode Excerpt
119MinorG-1050: Avoid using literals in your code.'Value'
220MinorG-1050: Avoid using literals in your code.2
321MinorG-1050: Avoid using literals in your code.3
438MinorG-1050: Avoid using literals in your code.'Value'
539MinorG-1050: Avoid using literals in your code.2
640MinorG-1050: Avoid using literals in your code.3

guidelines/guideline_4310_39.sql overview

Issue#LineSeverityMessageCode Excerpt
138MajorG-4310: Never use GOTO statements in your code.goto check_other_things
275MajorG-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.exit check_digit
375MinorG-4375: Always use EXIT WHEN instead of an IF statement to exit from a loop.exit check_digit
418MinorG-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
555MinorG-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
692MinorG-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
111MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function employee_by_id (in_employee_id in employees.employee_id%type) return employees%rowtype
232MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function employee_by_id (in_employee_id in employees.employee_id%type) return employees%rowtype
310MinorG-7120: Always add the name of the program unit to its end keyword.employee_api
411MinorG-7120: Always add the name of the program unit to its end keyword.employee_by_id
511MinorG-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
632MinorG-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
18MajorG-7460: Try to define your packaged/standalone function 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
231MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function name_by_id (in_department_id in departments.department_id%type) return departments.department_name%type
39MinorG-7150: Try to remove unused parameters.in_manager_id in departments.manager_id%type
48MinorG-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
59MinorG-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
631MinorG-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_7250_na.sql overview

Issue#LineSeverityMessageCode Excerpt
120MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function salary_increase return types_up.sal_increase_type
245MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function salary_increase return types_up.sal_increase_type
328MinorG-1040: Avoid dead code.return
428MinorG-7250: Never use RETURN in package initialization block.return
513MinorG-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
638MinorG-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_1020_02.sql overview

Issue#LineSeverityMessageCode Excerpt
117MinorG-1020: Always have a matching loop or block label.<<prepare_data>>
222MinorG-1020: Always have a matching loop or block label.<<process_data>>
328MinorG-1020: Always have a matching loop or block label.<<while_loop>>
434MinorG-1020: Always have a matching loop or block label.<<basic_loop>>
539MinorG-1020: Always have a matching loop or block label.<<for_loop>>

guidelines/guideline_2135_na.sql overview

Issue#LineSeverityMessageCode Excerpt
119MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_message := 'Hello, ' || l_last_name
219MinorG-1050: Avoid using literals in your code.'Hello, '
339MinorG-1050: Avoid using literals in your code.'Hello, '
412MinorG-2130: Try to use subtypes for constructs used often in your code.l_message varchar2(100 char);
532MinorG-2130: Try to use subtypes for constructs used often in your code.l_message varchar2(100 char);

guidelines/guideline_2145_na.sql overview

Issue#LineSeverityMessageCode Excerpt
112MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_function_result := maintenance.get_config('parallel_degree')
223MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_parallel_degree := l_function_result
312MinorG-1050: Avoid using literals in your code.'parallel_degree'
422MinorG-1050: Avoid using literals in your code.'parallel_degree'
513MinorG-2145: Never self-assign a variable.l_parallel_degree := l_parallel_degree

guidelines/guideline_2180_15.sql overview

Issue#LineSeverityMessageCode Excerpt
112MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement."sal+comm" := "my constant"
225MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_sal_comm := co_my_constant
38MajorG-2180: Never use quoted identifiers."sal+comm"
49MajorG-2180: Never use quoted identifiers."my constant"
510MajorG-2180: Never use quoted identifiers."my exception"

guidelines/guideline_2185_16.sql overview

Issue#LineSeverityMessageCode Excerpt
114MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.i := c
227MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_sal_comm := co_my_constant
310MinorG-2185: Avoid using overly short names for explicitly or implicitly declared identifiers.i integer;
411MinorG-2185: Avoid using overly short names for explicitly or implicitly declared identifiers.c constant integer := 1;
512MinorG-2185: Avoid using overly short names for explicitly or implicitly declared identifiers.e exception;

guidelines/guideline_2210_18.sql overview

Issue#LineSeverityMessageCode Excerpt
112MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function small_increase return number
223MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function small_increase return number
310MinorG-2130: Try to use subtypes for constructs used often in your code.co_small_increase constant number := 0.1;
421MinorG-2130: Try to use subtypes for constructs used often in your code.co_small_increase constant number(5,1) := 0.1;
510MinorG-2210: Avoid declaring NUMBER variables, constants or subtypes with no precision.number

guidelines/guideline_2220_19.sql overview

Issue#LineSeverityMessageCode Excerpt
116MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function big_increase return number
227MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function big_increase return pls_integer
314MinorG-2130: Try to use subtypes for constructs used often in your code.co_big_increase constant number(5,0) := 1;
414MinorG-2220: Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values.number(5,0)
514MinorG-2230: Try to use SIMPLE_INTEGER datatype when appropriate.number(5,0)

guidelines/guideline_2330_22.sql overview

Issue#LineSeverityMessageCode Excerpt
19MajorG-2330: Never use zero-length strings to substitute NULL.:= ''
211MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function null_string return varchar2
321MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function empty_string return varchar2
49MinorG-2130: Try to use subtypes for constructs used often in your code.co_null_string constant varchar2(1) := '';
59MinorG-2340: Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).varchar2(1)

guidelines/guideline_3180_na.sql overview

Issue#LineSeverityMessageCode Excerpt
115MajorG-3180: Always specify column names instead of positional references in ORDER BY clauses.4
215MajorG-3180: Always specify column names instead of positional references in ORDER BY clauses.1
315MajorG-3180: Always specify column names instead of positional references in ORDER BY clauses.3
415MinorG-1050: Avoid using literals in your code.4
515MinorG-1050: Avoid using literals in your code.3

guidelines/guideline_4130_33.sql overview

Issue#LineSeverityMessageCode Excerpt
120MajorG-4130: Always close locally opened cursors.open c_department_salary(p_dept_id => in_dept_id)
212MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function department_salary (in_dept_id in departments.department_id%type) return number
330MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function department_salary (in_dept_id in departments.department_id%type) return number
412MinorG-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
530MinorG-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
132MajorG-4140: Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute.sql%rowcount
218MinorG-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
323MinorG-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
444MinorG-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
549MinorG-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_4270_na.sql overview

Issue#LineSeverityMessageCode Excerpt
19MinorG-1050: Avoid using literals in your code.'42'
221MinorG-1050: Avoid using literals in your code.'42'
39MinorG-2130: Try to use subtypes for constructs used often in your code.l_string varchar2(10 char) := '42';
421MinorG-2130: Try to use subtypes for constructs used often in your code.l_string varchar2(10 char) := '42';
513MinorG-4270: Avoid comparing boolean values to boolean literals.l_is_valid = true

guidelines/guideline_4320_40.sql overview

Issue#LineSeverityMessageCode Excerpt
114MinorG-4320: Always label your loops.while (i <= co_max_value) loop i := i + co_increment; end loop
219MinorG-4320: Always label your loops.loop exit; end loop
323MinorG-4320: Always label your loops.for i in co_min_value..co_max_value loop sys.dbms_output.put_line(i); end loop
428MinorG-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
520MinorG-4380: Try to label your EXIT WHEN statements.exit

guidelines/guideline_5010_na.sql overview

Issue#LineSeverityMessageCode Excerpt
117MinorG-1050: Avoid using literals in your code.'START'
219MinorG-1050: Avoid using literals in your code.'END'
326MinorG-1050: Avoid using literals in your code.'DEMO'
428MinorG-1050: Avoid using literals in your code.'START'
530MinorG-1050: Avoid using literals in your code.'END'

guidelines/guideline_5060_56.sql overview

Issue#LineSeverityMessageCode Excerpt
120MajorG-5060: Avoid unhandled exceptionsselect department_name into l_department_name from departments where department_id = in_id
216MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function name_by_id (in_id in departments.department_id%type) return departments.department_name%type
332MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function name_by_id (in_id in departments.department_id%type) return departments.department_name%type
416MinorG-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
532MinorG-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_6020_59.sql overview

Issue#LineSeverityMessageCode Excerpt
122MinorG-6020: Try to use output bind arguments in the RETURNING INTO clause of dynamic DML statements rather than the USING clause.out out_new_salary
212MinorG-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
313MinorG-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
429MinorG-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
530MinorG-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

guidelines/guideline_7125_na.sql overview

Issue#LineSeverityMessageCode Excerpt
111MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function employee_by_id (in_employee_id in employees.employee_id%type) return employees%rowtype
232MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function employee_by_id (in_employee_id in employees.employee_id%type) return employees%rowtype
310MinorG-7125: Always use CREATE OR REPLACE instead of CREATE alone.employee_api
411MinorG-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
532MinorG-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_1080_na.sql overview

Issue#LineSeverityMessageCode Excerpt
116MinorG-1050: Avoid using literals in your code.3000
216MinorG-1050: Avoid using literals in your code.3000
325MinorG-1050: Avoid using literals in your code.3000
416MinorG-1080: Avoid using the same expression on both sides of a relational comparison operator or a logical operator.emp.salary > 3000 or emp.salary > 3000

guidelines/guideline_2410_24.sql overview

Issue#LineSeverityMessageCode Excerpt
114MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_bigger := constants_up.co_numeric_true
228MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_bigger := true
341MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_bigger := nvl(co_newfile < co_oldfile, false)
411MinorG-2410: Try to use boolean data type for values with dual meaning. (co_numeric_false, co_numeric_true).l_bigger pls_integer;

guidelines/guideline_3195_na.sql overview

Issue#LineSeverityMessageCode Excerpt
115MinorG-1050: Avoid using literals in your code.'Smith'
221MinorG-1050: Avoid using literals in your code.'Smith%'
326MinorG-1050: Avoid using literals in your code.'Smith'
415MinorG-3195: Always use wildcards in a LIKE clause.e.last_name like 'Smith'

guidelines/guideline_7160_68.sql overview

Issue#LineSeverityMessageCode Excerpt
112MajorG-7160: Always explicitly state parameter mode.in_first_name employees.first_name%type
213MajorG-7160: Always explicitly state parameter mode.in_last_name employees.last_name%type
314MajorG-7160: Always explicitly state parameter mode.in_email employees.email%type
415MajorG-7160: Always explicitly state parameter mode.in_department_id employees.department_id%type

guidelines/guideline_8310_na.sql overview

Issue#LineSeverityMessageCode Excerpt
110MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function dept_by_name (in_dept_name in departments.department_name%type) return departments%rowtype
231MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function dept_by_name (in_dept_name in departments.department_name%type) return departments%rowtype
315MinorG-1050: Avoid using literals in your code.20
410MinorG-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_2120_09.sql overview

Issue#LineSeverityMessageCode Excerpt
115MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_note := some_function()
230MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_note := some_function()
312MinorG-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
121MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_note := some_function()
236MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_note := some_function()
319MinorG-2130: Try to use subtypes for constructs used often in your code.l_note varchar2(1000 char);

guidelines/guideline_2230_na.sql overview

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

guidelines/guideline_3185_na.sql overview

Issue#LineSeverityMessageCode Excerpt
117MajorG-3185: Never use ROWNUM at the same query level as ORDER BY.rownum
217MinorG-1050: Avoid using literals in your code.5
334MinorG-1050: Avoid using literals in your code.5

guidelines/guideline_4110_31.sql overview

Issue#LineSeverityMessageCode Excerpt
121MinorG-4110: Always use %NOTFOUND instead of NOT %FOUND to check whether a cursor returned data.not c_employees%found
220MinorG-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
342MinorG-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_4325_na.sql overview

Issue#LineSeverityMessageCode Excerpt
117MajorG-4325: Never reuse labels in inner scopes.<<my_label>>
29MinorG-7125: Always use CREATE OR REPLACE instead of CREATE alone.pkg
328MinorG-7125: Always use CREATE OR REPLACE instead of CREATE alone.pkg

guidelines/guideline_4370_45.sql overview

Issue#LineSeverityMessageCode Excerpt
120MajorG-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.exit while_loop when i > co_max_value
232MajorG-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.exit for_loop when i = co_max_value
341MajorG-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.exit process_employees

guidelines/guideline_4385_48.sql overview

Issue#LineSeverityMessageCode Excerpt
116MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_employee_found := true
231MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_employee_found := c_employees%found
314MajorG-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_5050_55.sql overview

Issue#LineSeverityMessageCode Excerpt
118MajorG-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')
218MinorG-1050: Avoid using literals in your code.20501
318MinorG-1050: Avoid using literals in your code.'Invalid employee_id'

guidelines/guideline_7140_63.sql overview

Issue#LineSeverityMessageCode Excerpt
119MajorG-7140: Always ensure that locally defined procedures or functions are referenced.function my_func return number
219MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_func return number
333MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_func return number

guidelines/guideline_7410_71.sql overview

Issue#LineSeverityMessageCode Excerpt
19MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.create or replace function my_function return varchar2 is begin return null; end my_function; /
217MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_function return varchar2
39MinorG-7410: Avoid standalone functions – put your functions in packages.my_function

guidelines/guideline_7440_74.sql overview

Issue#LineSeverityMessageCode Excerpt
19MajorG-7440: Never use OUT parameters to return values from a function.out_date out date
29MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_function (out_date out date) return boolean
319MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_function return date

guidelines/guideline_7450_75.sql overview

Issue#LineSeverityMessageCode Excerpt
111MajorG-7450: Never return a NULL value from a BOOLEAN function.return null
29MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_function return boolean
318MajorG-7460: Try to define your packaged/standalone function deterministic if appropriate.function my_function return boolean

guidelines/guideline_7730_na.sql overview

Issue#LineSeverityMessageCode Excerpt
114MajorG-7730: Avoid multiple DML events per trigger if primary key is assigned in trigger.insert or update on departments
218MinorG-3150: Try to use identity columns for surrogate keys.nextval
332MinorG-3150: Try to use identity columns for surrogate keys.nextval

guidelines/guideline_8510_na.sql overview

Issue#LineSeverityMessageCode Excerpt
112MajorG-4385: Never use a cursor for loop to check whether a cursor returns data.for emp_rec in (select employee_id from employees order by employee_id) loop null; -- some processing end loop employees
227MinorG-1050: Avoid using literals in your code.'Init'
333MinorG-1050: Avoid using literals in your code.'Processing '

guidelines/guideline_0000_00.sql overview

Issue#LineSeverityMessageCode Excerpt
116MinorG-2140: Never initialize variables with NULL.l_value pls_integer := null;
28InfoG-0000: Avoid using the NOSONAR marker.:= null; -- NOSONAR

guidelines/guideline_1010_01.sql overview

Issue#LineSeverityMessageCode Excerpt
18MinorG-1010: Try to label your sub blocks.begin null; end
212MinorG-1010: Try to label your sub blocks.begin null; end

guidelines/guideline_1030_03.sql overview

Issue#LineSeverityMessageCode Excerpt
110MinorG-1030: Avoid defining variables that are not used.l_first_name employees.first_name%type;
212MinorG-1030: Avoid defining variables that are not used.e_good exception;

guidelines/guideline_1070_07.sql overview

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

guidelines/guideline_3140_29.sql overview

Issue#LineSeverityMessageCode Excerpt
117MajorG-3140: Try to use anchored records as targets for your cursors.into l_employee_id, l_first_name, l_last_name
222MajorG-3140: Try to use anchored records as targets for your cursors.into l_employee_id, l_first_name, l_last_name

guidelines/guideline_3190_na.sql overview

Issue#LineSeverityMessageCode Excerpt
112MajorG-3190: Avoid using NATURAL JOIN.natural join departments d
230MajorG-3190: Avoid using NATURAL JOIN.natural join departments d

guidelines/guideline_4260_na.sql overview

Issue#LineSeverityMessageCode Excerpt
19MinorG-2130: Try to use subtypes for constructs used often in your code.l_color varchar2(7 char);
211MinorG-4260: Avoid inverting boolean conditions with NOT.not l_color

guidelines/guideline_4380_47.sql overview

Issue#LineSeverityMessageCode Excerpt
127MinorG-4380: Try to label your EXIT WHEN statements.exit when l_innerlp = co_exit_value
230MinorG-4380: Try to label your EXIT WHEN statements.exit when l_innerlp = co_exit_value

guidelines/guideline_5030_52.sql overview

Issue#LineSeverityMessageCode Excerpt
116BlockerG-5030: Never assign predefined exception names to user defined exceptions.no_data_found exception;
226CriticalG-5070: Avoid using Oracle predefined exceptionsraise no_data_found

guidelines/guideline_6010_58.sql overview

Issue#LineSeverityMessageCode Excerpt
111MajorG-6010: Always use a character variable to execute dynamic SQL.execute immediate 'select employees_seq.nextval from dual' into l_next_val
211MinorG-1050: Avoid using literals in your code.'select employees_seq.nextval from dual'

guidelines/guideline_7110_60.sql overview

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

guidelines/guideline_7710_77.sql overview

Issue#LineSeverityMessageCode Excerpt
111MajorG-7710: Avoid cascading triggers.departments_hist
240MajorG-7710: Avoid cascading triggers.departments_hist

guidelines/guideline_7720_na.sql overview

Issue#LineSeverityMessageCode Excerpt
112BlockerG-7720: Never use multiple UPDATE OF in trigger event clause.update of department_id
212MajorG-7730: Avoid multiple DML events per trigger if primary key is assigned in trigger.update of department_id or update of department_name on departments

guidelines/guideline_7810_na.sql overview

Issue#LineSeverityMessageCode Excerpt
121MajorG-2135: Avoid assigning values to local variables that are not used by a subsequent statement.l_sequence_number := employees_seq.nextval
211MajorG-7810: Never use SQL inside PL/SQL to read sequence numbers (or SYSDATE).employees_seq.nextval

guidelines/guideline_8120_na.sql overview

Issue#LineSeverityMessageCode Excerpt
114MajorG-8110: Never use SELECT COUNT(*) if you are only interested in the existence of a row.count(*)
219MajorG-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_na_54.sql overview

Issue#LineSeverityMessageCode Excerpt
118MinorG-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
248MinorG-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_1050_05.sql overview

Issue#LineSeverityMessageCode Excerpt
124MinorG-1050: Avoid using literals in your code.'AD_PRES'

guidelines/guideline_1060_06.sql overview

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

guidelines/guideline_2110_08.sql overview

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

guidelines/guideline_2140_11.sql overview

Issue#LineSeverityMessageCode Excerpt
18MinorG-2140: Never initialize variables with NULL.l_note big_string_type := null;

guidelines/guideline_2150_12.sql overview

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

guidelines/guideline_2160_13.sql overview

Issue#LineSeverityMessageCode Excerpt
19CriticalG-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
117MajorG-2170: Never overload variables.l_variable

guidelines/guideline_2190_17.sql overview

Issue#LineSeverityMessageCode Excerpt
118MajorG-2190: Avoid using ROWID or UROWID.rowid

guidelines/guideline_2310_20.sql overview

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

guidelines/guideline_2320_21.sql overview

Issue#LineSeverityMessageCode Excerpt
111MajorG-2320: Never use VARCHAR data type.varchar(200)

guidelines/guideline_2340_23.sql overview

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

guidelines/guideline_2610_na.sql overview

Issue#LineSeverityMessageCode Excerpt
110MinorG-2610: Never use self-defined weak ref cursor types.type local_weak_cursor_type is ref cursor;

guidelines/guideline_3115_na.sql overview

Issue#LineSeverityMessageCode Excerpt
111MinorG-3115: Avoid self-assigning a column.first_name = first_name

guidelines/guideline_3130_28.sql overview

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

guidelines/guideline_3150_na.sql overview

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

guidelines/guideline_3160_na.sql overview

Issue#LineSeverityMessageCode Excerpt
121MinorG-1050: Avoid using literals in your code.107

guidelines/guideline_3210_30.sql overview

Issue#LineSeverityMessageCode Excerpt
122MajorG-3210: Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for 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
126CriticalG-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
110MajorG-4210: Try to use CASE rather than an IF statement with multiple ELSIF paths.if l_color = constants_up.co_red then my_package.do_red(); elsif l_color = constants_up.co_blue then my_package.do_blue(); elsif l_color = constants_up.co_black then my_package.do_black(); end if

guidelines/guideline_4230_37.sql overview

Issue#LineSeverityMessageCode Excerpt
110CriticalG-4230: Always use a COALESCE instead of a NVL command, 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
19CriticalG-4240: Always use a CASE instead of a NVL2 command 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_4250_na.sql overview

Issue#LineSeverityMessageCode Excerpt
118MajorG-4250: Avoid using identical conditions in different branches of the same IF or CASE statement.constants_up.co_red

guidelines/guideline_4330_41.sql overview

Issue#LineSeverityMessageCode Excerpt
121MinorG-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
127MinorG-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
118MajorG-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
128MinorG-4360: Always use a WHILE loop to process a loose array.1..t_employees.count()

guidelines/guideline_4375_46.sql overview

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

guidelines/guideline_4390_49.sql overview

Issue#LineSeverityMessageCode Excerpt
123MajorG-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
112MinorG-4395: Avoid hard-coded upper or lower bound values with FOR loops.1..5

guidelines/guideline_5020_51.sql overview

Issue#LineSeverityMessageCode Excerpt
118CriticalG-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
121MajorG-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
116CriticalG-5070: Avoid using Oracle predefined exceptionsraise no_data_found

guidelines/guideline_7210_65.sql overview

Issue#LineSeverityMessageCode Excerpt
19MinorG-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
19MinorG-7310: Avoid standalone procedures – put your procedures in packages.my_procedure

guidelines/guideline_7320_70.sql overview

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

guidelines/guideline_7460_na.sql overview

Issue#LineSeverityMessageCode Excerpt
110MajorG-7460: Try to define your packaged/standalone function 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
112MajorG-7510: Always prefix ORACLE supplied packages with owner schema name.dbms_output.put_line(co_hello_world)

guidelines/guideline_8110_78.sql overview

Issue#LineSeverityMessageCode Excerpt
115MajorG-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
119MajorG-8210: Always use synonyms when accessing objects of another application schema.oe.products