SQL & SQL Coding Guidelines Version 3.6

License

Philipp Salvisberg, Trivadis AG, CH-8152 Glattbrugg
valid thru	9999-12-31
valid for versions less than	3
max. files	unlimited
max. lines	unlimited
max. commands	unlimited
max. bytes	unlimited
limited to	personal 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)$
timeout	10
complexity	10
output	/usr/local/bin/tvdcc/sample/./tvdcc_report.html
template	html.xsl
excel	true
html	true
transonly	false
cleanup	true
check
skip
nosonar	true
license
validator	com.trivadis.tvdcc.validators.TrivadisGuidelines3
genmodel	false

Processing

Start of processing	2019-12-11 08:04:28
End of processing	2019-12-11 08:04:37
Processing time in seconds	8.839

Content

Number of files	93
Number of bytes	183,172
Number of lines (LOC)	8,015
Number of comment lines	814
Number of blank lines	1,294
Number of net lines	5,907
Number of commands	211
Number of statements (PL/SQL)	1,426
Max. cyclomatic complexity	5	( < 11	11..50	> 50	)
Max. Halstead volume	575	( < 1001	1001..3000	> 3000	)
Min. maintainability index (MI)	83	( > 84	64..84	< 64	)
Number of issues	288
Number of warnings	288
Number of errors	0

Issue Overview

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

Complex PL/SQL Units

File name	PL/SQL Unit	Line	# Lines	# Comment lines	# Blank lines	# Net lines	# Stmts	Cyclomatic complexity	Halstead volume	Maintainability index

File Overview

File name	# Warnings	# Errors	# Bytes	# Lines	# Net lines	# Cmds	# Stmts	Max. cyclomatic complexity	Max. Halstead volume	Min. MI	Elapsed seconds
guidelines/guideline_7240_68.sql	23	0	4,048	79	66	2	24	2	575	111	0.042
guidelines/guideline_8410_na.sql	12	0	2,833	94	65	4	16	2	318	95	0.028
guidelines/guideline_1040_04.sql	11	0	1,580	65	48	2	19	5	349	95	0.054
guidelines/guideline_7130_62.sql	9	0	2,688	78	56	2	14	3	169	99	0.031
guidelines/guideline_7220_66.sql	9	0	2,038	76	60	4	16	2	114	103	0.033
guidelines/guideline_7230_67.sql	9	0	2,808	73	47	4	6	1	68	123	0.022
guidelines/guideline_7430_72.sql	8	0	1,098	45	34	3	8	2	102	111	0.015
guidelines/guideline_3110_26.sql	7	0	618	24	14	2	0	0	0	221	0.020
guidelines/guideline_6020_59.sql	7	0	1,785	43	31	2	2	1	41	126	0.024
guidelines/guideline_7420_73.sql	7	0	1,051	40	32	2	9	3	114	107	0.015
guidelines/guideline_2510_25.sql	6	0	525	20	10	2	0	0	0	221	0.015
guidelines/guideline_3120_27.sql	6	0	1,508	50	33	5	0	0	0	221	0.036
guidelines/guideline_4310_39.sql	6	0	3,669	103	77	3	22	5	492	86	0.049
guidelines/guideline_7120_61.sql	6	0	1,185	49	36	2	8	3	69	107	0.020
guidelines/guideline_7150_64.sql	6	0	1,519	49	39	2	12	2	108	103	0.023
guidelines/guideline_1020_02.sql	5	0	1,526	82	62	2	26	4	306	86	0.068
guidelines/guideline_2210_18.sql	5	0	798	28	16	2	2	1	6	143	0.022
guidelines/guideline_2220_19.sql	5	0	916	32	16	2	2	1	6	143	0.015
guidelines/guideline_2330_22.sql	5	0	681	26	15	2	2	1	6	143	0.017
guidelines/guideline_3180_na.sql	5	0	712	27	14	2	0	0	0	221	0.023
guidelines/guideline_4130_33.sql	5	0	1,792	45	31	2	7	1	128	117	0.035
guidelines/guideline_4140_34.sql	5	0	2,355	67	40	2	11	2	143	123	0.039
guidelines/guideline_4320_40.sql	5	0	1,424	67	53	2	18	5	346	87	0.031
guidelines/guideline_5010_na.sql	5	0	921	32	12	2	4	1	79	158	0.020
guidelines/guideline_5060_56.sql	5	0	1,825	47	29	2	6	3	82	109	0.019
guidelines/guideline_7440_74.sql	4	0	609	24	15	2	3	1	21	133	0.013
guidelines/guideline_8310_na.sql	4	0	1,392	44	29	2	6	2	119	126	0.017
guidelines/guideline_2180_15.sql	3	0	545	30	22	2	4	2	51	121	0.018
guidelines/guideline_2185_16.sql	3	0	697	32	22	2	4	2	51	121	0.019
guidelines/guideline_2230_na.sql	3	0	1,376	35	16	2	2	1	6	143	0.017
guidelines/guideline_4110_31.sql	3	0	1,039	48	34	2	10	2	138	106	0.030
guidelines/guideline_4370_45.sql	3	0	1,887	79	62	2	22	5	411	83	0.027
guidelines/guideline_5050_55.sql	3	0	1,330	26	8	2	2	1	30	181	0.015
guidelines/guideline_7140_63.sql	3	0	1,588	42	24	2	4	1	25	136	0.016
guidelines/guideline_7410_71.sql	3	0	622	22	12	2	2	1	6	143	0.014
guidelines/guideline_7450_75.sql	3	0	534	23	14	2	2	1	6	143	0.012
guidelines/guideline_8510_na.sql	3	0	1,196	37	27	2	5	2	131	119	0.019
guidelines/guideline_0000_00.sql	2	0	532	29	18	3	3	1	41	134	1.463
guidelines/guideline_1010_01.sql	2	0	392	30	20	2	12	1	22	140	0.025
guidelines/guideline_1030_03.sql	2	0	1,263	44	34	2	7	3	139	106	0.064
guidelines/guideline_1050_05.sql	2	0	1,259	52	38	3	10	4	124	101	0.034
guidelines/guideline_1070_07.sql	2	0	451	23	10	2	4	1	0	221	0.010
guidelines/guideline_3140_29.sql	2	0	1,243	45	34	2	10	2	196	128	0.030
guidelines/guideline_4380_47.sql	2	0	2,065	61	49	2	17	3	444	92	0.029
guidelines/guideline_5030_52.sql	2	0	1,844	56	39	2	9	4	259	97	0.024
guidelines/guideline_6010_58.sql	2	0	580	23	14	2	2	1	39	134	0.014
guidelines/guideline_7110_60.sql	2	0	736	25	14	2	2	1	70	131	0.014
guidelines/guideline_7710_77.sql	2	0	1,888	59	47	3	4	1	189	95	0.024
guidelines/guideline_8120_na.sql	2	0	1,156	37	26	2	5	2	91	110	0.016
guidelines/guideline_na_54.sql	2	0	2,364	71	51	2	10	3	143	105	0.022
guidelines/guideline_1060_06.sql	1	0	982	34	22	2	2	1	57	143	0.030
guidelines/guideline_2110_08.sql	1	0	1,300	40	30	2	6	3	100	111	0.031
guidelines/guideline_2120_09.sql	1	0	872	33	21	3	2	1	39	134	0.020
guidelines/guideline_2130_10.sql	1	0	1,149	39	20	3	2	1	28	136	0.021
guidelines/guideline_2140_11.sql	1	0	307	20	12	2	2	1	41	134	0.018
guidelines/guideline_2150_12.sql	1	0	504	26	16	2	4	2	31	127	0.017
guidelines/guideline_2160_13.sql	1	0	910	32	23	2	7	2	185	105	0.026
guidelines/guideline_2170_14.sql	1	0	1,190	42	34	2	12	1	217	110	0.036
guidelines/guideline_2190_17.sql	1	0	982	31	18	2	2	1	45	125	0.022
guidelines/guideline_2310_20.sql	1	0	494	21	10	2	0	0	0	221	0.014
guidelines/guideline_2320_21.sql	1	0	594	19	8	2	0	0	0	221	0.014
guidelines/guideline_2340_23.sql	1	0	663	20	8	2	0	0	0	221	0.012
guidelines/guideline_2410_24.sql	1	0	927	44	32	3	7	2	124	114	0.022
guidelines/guideline_3130_28.sql	1	0	756	27	16	2	0	0	0	221	0.033
guidelines/guideline_3150_na.sql	1	0	1,601	42	21	4	1	1	30	135	0.034
guidelines/guideline_3160_na.sql	1	0	1,448	44	28	4	6	1	151	113	0.028
guidelines/guideline_3190_na.sql	1	0	646	26	14	2	0	0	0	221	0.018
guidelines/guideline_3210_30.sql	1	0	1,507	43	28	2	6	2	226	104	0.038
guidelines/guideline_4120_32.sql	1	0	2,684	95	69	3	21	3	304	95	0.051
guidelines/guideline_4210_35.sql	1	0	588	29	21	2	8	4	125	145	0.020
guidelines/guideline_4220_36.sql	1	0	625	28	18	2	2	1	80	146	0.025
guidelines/guideline_4230_37.sql	1	0	640	17	5	2	0	0	0	221	0.020
guidelines/guideline_4240_38.sql	1	0	766	23	10	2	0	0	0	221	0.017
guidelines/guideline_4330_41.sql	1	0	1,132	42	28	2	8	2	173	103	0.023
guidelines/guideline_4340_42.sql	1	0	1,467	49	33	2	9	2	337	101	0.029
guidelines/guideline_4350_43.sql	1	0	1,636	53	35	3	7	3	151	109	0.040
guidelines/guideline_4360_44.sql	1	0	1,929	57	39	2	11	3	367	98	0.045
guidelines/guideline_4375_46.sql	1	0	925	37	24	2	9	3	132	105	0.019
guidelines/guideline_4385_48.sql	1	0	769	34	26	2	6	2	113	117	0.014
guidelines/guideline_4390_49.sql	1	0	1,669	47	36	2	8	2	332	102	0.024
guidelines/guideline_4395_50.sql	1	0	775	30	19	2	4	2	91	116	0.015
guidelines/guideline_5020_51.sql	1	0	946	33	22	2	7	4	104	109	0.015
guidelines/guideline_5040_53.sql	1	0	1,120	29	14	2	4	2	39	155	0.013
guidelines/guideline_5070_57.sql	1	0	933	25	10	2	2	1	5	145	0.015
guidelines/guideline_7210_65.sql	1	0	69,525	4,218	3,368	2	840	1	0	221	3.980
guidelines/guideline_7310_69.sql	1	0	670	27	16	3	2	1	0	221	0.015
guidelines/guideline_7320_70.sql	1	0	1,383	43	28	2	7	3	129	108	0.015
guidelines/guideline_7460_na.sql	1	0	805	20	10	2	0	0	0	221	0.011
guidelines/guideline_7510_76.sql	1	0	578	23	12	2	2	1	47	133	0.012
guidelines/guideline_7810_na.sql	1	0	500	23	14	2	2	1	33	127	0.011
guidelines/guideline_8110_78.sql	1	0	1,480	46	35	2	7	4	244	97	0.021
guidelines/guideline_8210_79.sql	1	0	1,508	46	31	3	6	3	114	107	0.017
guidelines/guideline_3170_na.sql	0	0	796	20	8	2	0	0	0	221	0.007

Total	288	0	183,172	8,015	5,907	211	1,426	5	575	83	7.610

File Issues

guidelines/guideline_7240_68.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	21	Major	G-7240: Avoid using an IN OUT parameter as IN or OUT only.	in_wait INTEGER
2	11	Minor	G-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
3	12	Minor	G-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
4	13	Minor	G-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
5	14	Minor	G-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
6	15	Minor	G-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
7	16	Minor	G-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
8	17	Minor	G-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
9	18	Minor	G-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
10	19	Minor	G-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
11	20	Minor	G-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
12	21	Minor	G-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
13	47	Minor	G-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
14	48	Minor	G-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
15	49	Minor	G-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
16	50	Minor	G-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
17	51	Minor	G-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
18	52	Minor	G-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
19	53	Minor	G-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
20	54	Minor	G-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
21	55	Minor	G-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
22	56	Minor	G-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
23	57	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	38	Major	G-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;
2	89	Major	G-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;
3	48	Major	G-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
4	32	Minor	G-2130: Try to use subtypes for constructs used often in your code.	co_lock_name CONSTANT VARCHAR2(30 CHAR) := 'APPLICATION_LOCK';
5	52	Minor	G-2130: Try to use subtypes for constructs used often in your code.	l_lock_handle VARCHAR2(128 CHAR);
6	82	Minor	G-2130: Try to use subtypes for constructs used often in your code.	l_handle VARCHAR2(128 CHAR);
7	83	Minor	G-2130: Try to use subtypes for constructs used often in your code.	co_lock_name CONSTANT VARCHAR2(30 CHAR) := 'APPLICATION_LOCK';
8	17	Minor	G-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
9	23	Minor	G-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
10	49	Minor	G-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
11	50	Minor	G-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
12	71	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	11	Minor	G-1040: Avoid dead code.	2=3
2	19	Minor	G-1040: Avoid dead code.	EXIT my_loop
3	26	Minor	G-1040: Avoid dead code.	'x' = 'y'
4	37	Minor	G-1040: Avoid dead code.	5=6
5	43	Minor	G-1040: Avoid dead code.	RETURN
6	11	Minor	G-1050: Avoid using literals in your code.	2
7	11	Minor	G-1050: Avoid using literals in your code.	3
8	26	Minor	G-1050: Avoid using literals in your code.	'x'
9	26	Minor	G-1050: Avoid using literals in your code.	'y'
10	37	Minor	G-1050: Avoid using literals in your code.	5
11	37	Minor	G-1050: Avoid using literals in your code.	6

guidelines/guideline_7130_62.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	22	Major	G-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.	r_emp
2	24	Major	G-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.	r_emp
3	24	Major	G-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.	r_emp
4	19	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION commission RETURN NUMBER
5	50	Major	G-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
6	16	Minor	G-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
7	47	Minor	G-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
8	50	Minor	G-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
9	51	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	14	Major	G-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
2	48	Major	G-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
3	58	Major	G-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
4	14	Minor	G-7220: Always use forward declaration for private functions and procedures.	does_exist
5	14	Minor	G-7220: Always use forward declaration for private functions and procedures.	does_exist
6	14	Minor	G-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
7	32	Minor	G-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
8	51	Minor	G-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
9	58	Minor	G-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_7230_67.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	18	Major	G-7230: Avoid declaring global variables public.	co_min_increase
2	19	Major	G-7230: Avoid declaring global variables public.	co_max_increase
3	20	Major	G-7230: Avoid declaring global variables public.	g_salary_increase
4	23	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION salary_increase RETURN types_up.sal_increase_type
5	34	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION salary_increase RETURN types_up.sal_increase_type
6	44	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION salary_increase RETURN types_up.sal_increase_type
7	60	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION salary_increase RETURN types_up.sal_increase_type
8	28	Minor	G-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
9	53	Minor	G-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_7430_72.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	12	Major	G-7430: Try to use no more than one RETURN statement within a function.	RETURN TRUE
2	14	Major	G-7430: Try to use no more than one RETURN statement within a function.	RETURN FALSE
3	8	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION my_function (in_value IN PLS_INTEGER) RETURN BOOLEAN
4	22	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION my_function (in_value IN PLS_INTEGER) RETURN BOOLEAN
5	39	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION my_function (in_value IN PLS_INTEGER) RETURN BOOLEAN
6	8	Minor	G-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
7	22	Minor	G-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
8	39	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	8	Major	G-3110: Always specify the target columns when coding an insert statement.	INSERT INTO departments VALUES (departments_seq.nextval, 'Support', 100, 10)
2	10	Minor	G-1050: Avoid using literals in your code.	'Support'
3	11	Minor	G-1050: Avoid using literals in your code.	100
4	12	Minor	G-1050: Avoid using literals in your code.	10
5	20	Minor	G-1050: Avoid using literals in your code.	'Support'
6	21	Minor	G-1050: Avoid using literals in your code.	100
7	22	Minor	G-1050: Avoid using literals in your code.	10

guidelines/guideline_6020_59.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	22	Minor	G-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
2	12	Minor	G-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
3	13	Minor	G-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
4	14	Minor	G-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
5	29	Minor	G-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
6	30	Minor	G-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
7	31	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	18	Major	G-7420: Always make the RETURN statement the last statement of your function.	RETURN l_ret
2	8	Major	G-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
3	27	Major	G-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
4	8	Minor	G-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
5	9	Minor	G-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
6	27	Minor	G-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
7	28	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	10	Major	G-2510: Avoid using the LONG and LONG RAW data types.	l_long LONG;
2	11	Major	G-2510: Avoid using the LONG and LONG RAW data types.	l_raw LONG RAW;
3	10	Major	G-7230: Avoid declaring global variables public.	l_long
4	11	Major	G-7230: Avoid declaring global variables public.	l_raw
5	17	Major	G-7230: Avoid declaring global variables public.	l_long
6	18	Major	G-7230: Avoid declaring global variables public.	l_raw

guidelines/guideline_3120_27.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	9	Major	G-3120: Always use table aliases when your SQL statement involves more than one source.	last_name
2	10	Major	G-3120: Always use table aliases when your SQL statement involves more than one source.	first_name
3	11	Major	G-3120: Always use table aliases when your SQL statement involves more than one source.	department_name
4	38	Major	G-3120: Always use table aliases when your SQL statement involves more than one source.	employee_id
5	40	Minor	G-1050: Avoid using literals in your code.	'%Manager%'
6	48	Minor	G-1050: Avoid using literals in your code.	'%Manager%'

guidelines/guideline_4310_39.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	38	Major	G-4310: Never use GOTO statements in your code.	GOTO check_other_things
2	75	Major	G-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.	EXIT check_digit
3	75	Minor	G-4375: Always use EXIT WHEN instead of an IF statement to exit from a loop.	EXIT check_digit
4	18	Minor	G-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
5	55	Minor	G-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
6	92	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	11	Major	G-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
2	32	Major	G-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
3	10	Minor	G-7120: Always add the name of the program unit to its end keyword.	employee_api
4	11	Minor	G-7120: Always add the name of the program unit to its end keyword.	employee_by_id
5	11	Minor	G-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
6	32	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	8	Major	G-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
2	31	Major	G-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
3	9	Minor	G-7150: Try to remove unused parameters.	in_manager_id IN departments.manager_id%TYPE
4	8	Minor	G-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
5	9	Minor	G-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
6	31	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	17	Minor	G-1020: Always have a matching loop or block label.	<<prepare_data>>
2	22	Minor	G-1020: Always have a matching loop or block label.	<<process_data>>
3	28	Minor	G-1020: Always have a matching loop or block label.	<<while_loop>>
4	34	Minor	G-1020: Always have a matching loop or block label.	<<basic_loop>>
5	39	Minor	G-1020: Always have a matching loop or block label.	<<for_loop>>

guidelines/guideline_2210_18.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	12	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION small_increase RETURN NUMBER
2	23	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION small_increase RETURN NUMBER
3	10	Minor	G-2130: Try to use subtypes for constructs used often in your code.	co_small_increase CONSTANT NUMBER := 0.1;
4	21	Minor	G-2130: Try to use subtypes for constructs used often in your code.	co_small_increase CONSTANT NUMBER(5,1) := 0.1;
5	10	Minor	G-2210: Avoid declaring NUMBER variables or subtypes with no precision.	NUMBER

guidelines/guideline_2220_19.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	16	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION big_increase RETURN NUMBER
2	27	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION big_increase RETURN PLS_INTEGER
3	14	Minor	G-2130: Try to use subtypes for constructs used often in your code.	co_big_increase CONSTANT NUMBER(5,0) := 1;
4	14	Minor	G-2220: Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values.	NUMBER(5,0)
5	14	Minor	G-2230: Try to use SIMPLE_INTEGER datatype when appropriate.	NUMBER(5,0)

guidelines/guideline_2330_22.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	9	Major	G-2330: Never use zero-length strings to substitute NULL.	:= ''
2	11	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION null_string RETURN VARCHAR2
3	21	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION empty_string RETURN VARCHAR2
4	9	Minor	G-2130: Try to use subtypes for constructs used often in your code.	co_null_string CONSTANT VARCHAR2(1) := '';
5	9	Minor	G-2340: Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).	VARCHAR2(1)

guidelines/guideline_3180_na.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	15	Major	G-3180: Always specify column names instead of positional references in ORDER BY clauses.	4
2	15	Major	G-3180: Always specify column names instead of positional references in ORDER BY clauses.	1
3	15	Major	G-3180: Always specify column names instead of positional references in ORDER BY clauses.	3
4	15	Minor	G-1050: Avoid using literals in your code.	4
5	15	Minor	G-1050: Avoid using literals in your code.	3

guidelines/guideline_4130_33.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	20	Major	G-4130: Always close locally opened cursors.	OPEN c_department_salary(p_dept_id => in_dept_id)
2	12	Major	G-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
3	31	Major	G-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
4	12	Minor	G-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
5	31	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	32	Major	G-4140: Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute.	SQL%ROWCOUNT
2	18	Minor	G-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
3	23	Minor	G-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
4	44	Minor	G-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
5	49	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	14	Minor	G-4320: Always label your loops.	WHILE (i <= co_max_value) LOOP i := i + co_increment; END LOOP
2	19	Minor	G-4320: Always label your loops.	LOOP EXIT; END LOOP
3	23	Minor	G-4320: Always label your loops.	FOR i IN co_min_value..co_max_value LOOP sys.dbms_output.put_line(i); END LOOP
4	28	Minor	G-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
5	20	Minor	G-4380: Try to label your EXIT WHEN statements.	EXIT

guidelines/guideline_5010_na.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	17	Minor	G-1050: Avoid using literals in your code.	'START'
2	19	Minor	G-1050: Avoid using literals in your code.	'END'
3	26	Minor	G-1050: Avoid using literals in your code.	'DEMO'
4	28	Minor	G-1050: Avoid using literals in your code.	'START'
5	30	Minor	G-1050: Avoid using literals in your code.	'END'

guidelines/guideline_5060_56.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	20	Major	G-5060: Avoid unhandled exceptions	SELECT department_name INTO l_department_name FROM departments WHERE department_id = in_id
2	16	Major	G-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
3	32	Major	G-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
4	16	Minor	G-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
5	32	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	9	Major	G-7440: Never use OUT parameters to return values from a function.	out_date OUT DATE
2	9	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION my_function (out_date OUT DATE) RETURN BOOLEAN
3	19	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION my_function RETURN DATE
4	9	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	10	Major	G-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
2	31	Major	G-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
3	14	Minor	G-1050: Avoid using literals in your code.	20
4	10	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	8	Major	G-2180: Never use quoted identifiers.	"sal+comm"
2	9	Major	G-2180: Never use quoted identifiers.	"my constant"
3	10	Major	G-2180: Never use quoted identifiers.	"my exception"

guidelines/guideline_2185_16.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	10	Minor	G-2185: Avoid using overly short names for explicitly or implicitly declared identifiers.	i INTEGER;
2	11	Minor	G-2185: Avoid using overly short names for explicitly or implicitly declared identifiers.	c CONSTANT INTEGER := 1;
3	12	Minor	G-2185: Avoid using overly short names for explicitly or implicitly declared identifiers.	e exception;

guidelines/guideline_2230_na.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	17	Minor	G-2130: Try to use subtypes for constructs used often in your code.	co_big_increase CONSTANT NUMBER(5,0) := 1;
2	17	Minor	G-2220: Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values.	NUMBER(5,0)
3	17	Minor	G-2230: Try to use SIMPLE_INTEGER datatype when appropriate.	NUMBER(5,0)

guidelines/guideline_4110_31.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	21	Minor	G-4110: Always use %NOTFOUND instead of NOT %FOUND to check whether a cursor returned data.	NOT c_employees%FOUND
2	20	Minor	G-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
3	42	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	20	Major	G-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.	EXIT while_loop WHEN i > co_max_value
2	32	Major	G-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.	EXIT for_loop WHEN i = co_max_value
3	41	Major	G-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.	EXIT process_employees

guidelines/guideline_5050_55.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	18	Major	G-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')
2	18	Minor	G-1050: Avoid using literals in your code.	20501
3	18	Minor	G-1050: Avoid using literals in your code.	'Invalid employee_id'

guidelines/guideline_7140_63.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	19	Major	G-7140: Always ensure that locally defined procedures or functions are referenced.	FUNCTION my_func RETURN NUMBER
2	19	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION my_func RETURN NUMBER
3	33	Major	G-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#	Line	Severity	Message	Code Excerpt
1	9	Major	G-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; /
2	17	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION my_function RETURN VARCHAR2
3	9	Minor	G-7410: Avoid standalone functions – put your functions in packages.	my_function

guidelines/guideline_7450_75.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	11	Major	G-7450: Never return a NULL value from a BOOLEAN function.	RETURN NULL
2	9	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION my_function RETURN BOOLEAN
3	18	Major	G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.	FUNCTION my_function RETURN BOOLEAN

guidelines/guideline_8510_na.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	12	Major	G-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
2	26	Minor	G-1050: Avoid using literals in your code.	'Init'
3	32	Minor	G-1050: Avoid using literals in your code.	'Processing '

guidelines/guideline_0000_00.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	17	Minor	G-2140: Never initialize variables with NULL.	l_value PLS_INTEGER := NULL;
2	9	Info	G-0000: Avoid using the NOSONAR marker.	:= NULL; -- NOSONAR

guidelines/guideline_1010_01.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	8	Minor	G-1010: Try to label your sub blocks.	BEGIN NULL; END
2	12	Minor	G-1010: Try to label your sub blocks.	BEGIN NULL; END

guidelines/guideline_1030_03.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	10	Minor	G-1030: Avoid defining variables that are not used.	l_first_name employees.first_name%TYPE;
2	12	Minor	G-1030: Avoid defining variables that are not used.	e_good EXCEPTION;

guidelines/guideline_1050_05.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	31	Major	G-7230: Avoid declaring global variables public.	co_president
2	18	Minor	G-1050: Avoid using literals in your code.	'AD_PRES'

guidelines/guideline_1070_07.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	9	Minor	G-1070: Avoid nesting comment blocks.	/* comment one -- nested comment two */
2	11	Minor	G-1070: Avoid nesting comment blocks.	-- comment three /* nested comment four */

guidelines/guideline_3140_29.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	17	Major	G-3140: Try to use anchored records as targets for your cursors.	INTO l_employee_id, l_first_name, l_last_name
2	22	Major	G-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#	Line	Severity	Message	Code Excerpt
1	28	Minor	G-4380: Try to label your EXIT WHEN statements.	EXIT WHEN l_innerlp = co_exit_value
2	31	Minor	G-4380: Try to label your EXIT WHEN statements.	EXIT WHEN l_innerlp = co_exit_value

guidelines/guideline_5030_52.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	16	Blocker	G-5030: Never assign predefined exception names to user defined exceptions.	no_data_found EXCEPTION;
2	26	Critical	G-5070: Avoid using Oracle predefined exceptions	RAISE no_data_found

guidelines/guideline_6010_58.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	11	Major	G-6010: Always use a character variable to execute dynamic SQL.	EXECUTE IMMEDIATE 'select employees_seq.nextval from dual' INTO l_next_val
2	11	Minor	G-1050: Avoid using literals in your code.	'select employees_seq.nextval from dual'

guidelines/guideline_7110_60.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	14	Major	G-7110: Try to use named notation when calling program units.	r_employee
2	14	Major	G-7110: Try to use named notation when calling program units.	co_id

guidelines/guideline_7710_77.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	11	Major	G-7710: Avoid cascading triggers.	departments_hist
2	40	Major	G-7710: Avoid cascading triggers.	departments_hist

guidelines/guideline_8120_na.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	14	Major	G-8110: Never use SELECT COUNT(*) if you are only interested in the existence of a row.	count(*)
2	18	Major	G-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#	Line	Severity	Message	Code Excerpt
1	18	Minor	G-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
2	50	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	19	Major	G-1060: Avoid storing ROWIDs or UROWIDs in database tables.	ROWID

guidelines/guideline_2110_08.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	11	Major	G-2110: Try to use anchored declarations for variables, constants and types.	l_last_name VARCHAR2(20 CHAR);

guidelines/guideline_2120_09.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	12	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	19	Minor	G-2130: Try to use subtypes for constructs used often in your code.	l_note VARCHAR2(1000 CHAR);

guidelines/guideline_2140_11.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	8	Minor	G-2140: Never initialize variables with NULL.	l_note big_string := NULL;

guidelines/guideline_2150_12.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	12	Blocker	G-2150: Avoid comparisons with NULL value, consider using IS [NOT] NULL.	l_value = NULL

guidelines/guideline_2160_13.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	9	Critical	G-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#	Line	Severity	Message	Code Excerpt
1	17	Major	G-2170: Never overload variables.	l_variable

guidelines/guideline_2190_17.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	18	Major	G-2190: Avoid the use of ROWID or UROWID.	ROWID

guidelines/guideline_2310_20.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	11	Major	G-2310: Avoid using CHAR data type.	CHAR(200)

guidelines/guideline_2320_21.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	11	Major	G-2320: Avoid using VARCHAR data type.	VARCHAR(200)

guidelines/guideline_2340_23.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	12	Minor	G-2340: Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).	VARCHAR2(200)

guidelines/guideline_2410_24.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	11	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	12	Minor	G-3130: Try to use ANSI SQL-92 join syntax.	employees e, departments d

guidelines/guideline_3150_na.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	23	Minor	G-3150: Try to use identity columns for surrogate keys.	nextval

guidelines/guideline_3160_na.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	19	Minor	G-1050: Avoid using literals in your code.	107

guidelines/guideline_3190_na.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	12	Major	G-3190: Avoid using NATURAL JOIN.	NATURAL JOIN departments d

guidelines/guideline_3210_30.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	21	Major	G-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#	Line	Severity	Message	Code Excerpt
1	26	Critical	G-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#	Line	Severity	Message	Code Excerpt
1	8	Major	G-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#	Line	Severity	Message	Code Excerpt
1	10	Minor	G-4220: Try to use CASE rather than DECODE.	DECODE(dummy, a, x , b, y , c, z , 0)

guidelines/guideline_4230_37.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	10	Critical	G-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#	Line	Severity	Message	Code Excerpt
1	9	Critical	G-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#	Line	Severity	Message	Code Excerpt
1	21	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	27	Minor	G-4340: Always use a NUMERIC FOR loop to process a dense array.	i > t_employees.COUNT()

guidelines/guideline_4350_43.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	19	Major	G-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#	Line	Severity	Message	Code Excerpt
1	27	Minor	G-4360: Always use a WHILE loop to process a loose array.	1 .. t_employees.COUNT()

guidelines/guideline_4375_46.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	16	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	14	Major	G-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#	Line	Severity	Message	Code Excerpt
1	23	Major	G-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#	Line	Severity	Message	Code Excerpt
1	12	Minor	G-4395: Avoid hard-coded upper or lower bound values with FOR loops.	1..5

guidelines/guideline_5020_51.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	18	Critical	G-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#	Line	Severity	Message	Code Excerpt
1	17	Major	G-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#	Line	Severity	Message	Code Excerpt
1	15	Critical	G-5070: Avoid using Oracle predefined exceptions	RAISE NO_DATA_FOUND

guidelines/guideline_7210_65.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	10	Minor	G-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#	Line	Severity	Message	Code Excerpt
1	9	Minor	G-7310: Avoid standalone procedures – put your procedures in packages.	my_procedure

guidelines/guideline_7320_70.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	20	Major	G-7320: Avoid using RETURN statements in a PROCEDURE.	RETURN

guidelines/guideline_7460_na.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	10	Major	G-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#	Line	Severity	Message	Code Excerpt
1	12	Major	G-7510: Always prefix ORACLE supplied packages with owner schema name.	dbms_output.put_line(co_hello_world)

guidelines/guideline_7810_na.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	11	Major	G-7810: Do not use SQL inside PL/SQL to read sequence numbers (or SYSDATE)	employees_seq.NEXTVAL

guidelines/guideline_8110_78.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	15	Major	G-8110: Never use SELECT COUNT(*) if you are only interested in the existence of a row.	count(*)

guidelines/guideline_8210_79.sql overview

Issue#	Line	Severity	Message	Code Excerpt
1	19	Major	G-8210: Always use synonyms when accessing objects of another application schema.	oe.products