Release 2 (9.2)
Part Number A96525-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Error Messages Release 2 (9.2) Part Number A96525-01 |
|
This chapter lists messages generated by PL/SQL. For more information about PL/SQL, refer to the PL/SQL User's Guide and Reference.
Cause: The parser, which checks the syntax of PL/SQL statements, uses a data structure called a stack; the number of levels of nesting in the PL/SQL block exceeded the stack capacity.
Action: Reorganize the block structure to avoid nesting at too deep a level. For example, move the lowest-level sub-block to a higher level.
Cause: This error message is from the parser. It found a token (language element) that is inappropriate in this context.
Action: Check previous tokens as well as the one given in the error message. The line and column numbers given in the error message refer to the end of the faulty language construct.
Cause: In a subprogram call, the name of the subprogram was followed by an empty parameter list. For example, procedure P was called as P(). This is not allowed.
Action: Remove the empty parameter list. In the example, change the procedure call to P.
Cause: More than one forward declaration of a type is redundant.
Action: Remove all but one forward declaration.
Cause: While checking a declarative unit (a top-level declare block without the BEGIN...END), PL/SQL found that there was more than one item declared or that the item was not a variable declaration. A table is a common variable declaration at the unit level. To define a TABLE, compile a DECLARE compilation unit, but only one at a time is allowed.
Action: Declare variables in separate declarative units.
Cause: No declaration for the exception name referenced in an EXCEPTION_INIT pragma was found within the scope of the pragma.
Action: Make sure the pragma follows the exception declaration and is within the same scope.
Cause: A bind variable, that is, an identifier prefixed with a colon, was found in an inappropriate context.
Action: Remove the colon or replace the bind variable with the appropriate object.
Cause: A Comment had a comment initiator (/*), but before the Comment terminator (*/) was found, an end-of-file marker was encountered.
Action: Remove the Comment initiator or add a comment terminator. The line and column numbers accompanying the error message refer to the beginning of the last legal token before the Comment initiator.
Cause: A quoted identifier had a beginning quote ("), but before the ending quote (") was found, an end-of-line marker was encountered.
Action: Remove the beginning quote or add the ending quote. The line and column numbers accompanying the error message refer to the beginning of the quoted identifier.
Cause: Following the keyword END, which terminates some language constructs (such as loops, blocks, functions, and procedures), you can optionally place the name of that construct. For example, at the end of the definition of loop L you might write END L. This error occurs when the optional name does not match the name given to the language construct. It is usually caused by a misspelled identifier or by faulty block structure.
Action: Make sure the spelling of the END identifier matches the name given to the language construct and that the block structure is correct.
Cause: The name of a PL/SQL variable is longer than 30 characters. Legal identifiers (including quoted identifiers) have a maximum length of 30 characters. A string literal might have been mistakenly enclosed in double quotes instead of single quotes, in which case PL/SQL considers it a quoted identifier.
Action: Shorten the identifier.
Cause: The pragma refers to a PL/SQL object that was not declared or is not within the scope of the reference. Identifiers must be declared before they are used in a pragma; forward references are not allowed.
Action: Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure.
Cause: Two or more WHERE clauses were found in a DELETE, SELECT, or UPDATE statement. The WHERE clause specifies a condition under which rows in a table are processed. The condition can contain several logical expressions connected by AND or OR, but a statement can contain only one WHERE clause.
Action: Remove one of the WHERE clauses and, if necessary, connect logical expressions by AND or OR.
Cause: Two or more CONNECT BY clauses were found in a SELECT statement. The CONNECT BY clause defines a relationship used to return rows in a hierarchical order. The relationship can contain two expressions separated by a relational operator (such as = or !=), but a statement can contain only one CONNECT BY clause.
Action: Remove one of the CONNECT BY clauses and, if necessary, separate expressions by a relational operator.
Cause: Two or more GROUP BY clauses were found in a SELECT statement. The GROUP BY clause lists column expressions used to form a summary row for each group of selected rows. The list can contain several column expressions separated by commas, but a statement can contain only one GROUP BY clause.
Action: Remove one of the GROUP BY clauses and, if necessary, separate column expressions by commas.
Cause: Two or more HAVING clauses were found in a SELECT statement. The HAVING clause specifies a condition under which groups of rows (formed by the GROUP BY clause) are included in the result. The condition can include several logical expressions connected by AND or OR, but a statement can contain only one HAVING clause.
Action: Remove one of the HAVING clauses and, if necessary, connect logical expressions by AND or OR.
Cause: The CURSOR_NAME parameter in an OPEN statement is misspelled or does not refer to a legally declared cursor.
Action: Check the spelling of the CURSOR_NAME parameter. Make sure the cursor was declared properly.
Cause: The asterisk (*) option was used in the argument list of a SQL group function other than COUNT. For example, the code might look like:
SELECT SUM(*) INTO emp_count FROM emp; -- should be COUNT(*)
Only COUNT permits the use of the asterisk option, which returns the number of rows in a table.
Action: Remove the asterisk option from the argument list and replace it with an expression that refers to one or more database columns.
Cause: A few built-in functions use keywords (instead of commas) as argument separators. A function different from one of these built-in functions is using a keyword as an argument separator.
Action: Correct the argument list or change the function name.
Cause: PL/SQL was designed primarily for robust transaction processing. One consequence of the special-purpose design is that the PL/SQL compiler imposes a limit on block size. The limit depends on the mix of statements in the PL/SQL block. Blocks that exceed the limit cause this error.
Action: The best solution is to modularize the program by defining subprograms, which can be stored in an Oracle database. Another solution is to break the program into two sub-blocks. Have the first block INSERT any data the second block needs into a temporary database table. Then, have the second block SELECT the data from the table.
Cause: The first argument passed to the EXCEPTION_INIT pragma was something other than an exception name. The first argument must be the name of a legally declared exception.
Action: Replace the first argument with the name of a legally declared exception.
Cause: When a constant or variable was declared, its datatype was not specified. For example, the code might look like:
pi CONSTANT := 3.14159; -- should be CONSTANT REAL := 3.14159
Every constant and variable must have a datatype, which specifies a storage format, constraints, and valid range of values.
Action: Supply the type name.
Cause: The ALL shortcut for specifying system privileges or statement options was used in a SQL statement. PL/SQL does not support the ALL shortcut.
Action: Remove the ALL shortcut from the SQL statement.
Cause: The named pragma (compiler directive) is not among those supported by PL/SQL. The pragma name might be misspelled, or the pragma syntax might be faulty.
Action: Check the spelling of the pragma name, and make sure the proper syntax was used.
Cause: The number of arguments (actual parameters) passed to the named pragma (compiler directive) is incorrect. A required argument was omitted from the argument list, or the pragma syntax is faulty (for example, a comma might be missing between two parameters).
Action: Supply the missing argument, or correct the faulty syntax.
Cause: The first parameter passed to pragma INTERFACE specified a host language other than C. Currently, C is the only host language supported. The parameter might be misspelled, or the pragma syntax might be faulty (for example, a comma might be missing between two parameters).
Action: Check the spelling of the first parameter, which should be C, and make sure the proper syntax was used.
Cause: The first argument (actual parameter) passed to the named pragma (compiler directive) was not the name of a subprogram, package, or cursor, as required. The parameter might be misspelled, or the pragma syntax might be faulty (for example, a comma might be missing between two parameters).
Action: Check the spelling of the first parameter, and make sure the proper syntax was used.
Cause: The second argument (actual parameter) passed to the named pragma (compiler directive) was not the name of a procedure, as required. The parameter might be misspelled, or the pragma syntax might be faulty (for example, a comma might be missing between two parameters).
Action: Check the spelling of the second parameter, and make sure the proper syntax was used.
Cause: One of the parameters passed to pragma RESTRICT_REFERENCES was not among the following, as required: WNDS, WNPS, RNDS, RNPS. The parameter might be misspelled, or the pragma syntax might be faulty (for example, a comma might be missing between two parameters)
Action: Check the spelling of all the parameters, and make sure the proper syntax was used.
Cause: The first argument (actual parameter) passed to the named pragma (compiler directive) was not an identifier or string literal, as required. The parameter might be misspelled, or the pragma syntax might be faulty (for example, a comma might be missing between two parameters).
Action: Check the spelling of the first parameter, and make sure the proper syntax was used.
Cause: The second argument (actual parameter) passed to the named pragma (compiler directive) was not an identifier or string literal, as required. The parameter might be misspelled, or the pragma syntax might be faulty (for example, a comma might be missing between two parameters).
Action: Check the spelling of the second parameter, and make sure the proper syntax was used.
Cause: The third argument (actual parameter) passed to the named pragma (compiler directive) was not an identifier or string literal, as required. The parameter might be misspelled, or the pragma syntax might be faulty (for example, a comma might be missing between two parameters).
Action: Check the spelling of the third parameter, and make sure the proper syntax was used.
Cause: Invalid precision was specified for a signed or unsigned binary type.
Action: Specify precision as one of 8, 16, or 32.
Cause: The subprogram was found to have two external NAME specifications.
Action: Remove one of the external NAME specifications.
Cause: The subprogram was found to have two external LIBRARY specifications.
Action: Remove one of the external LIBRARY specifications.
Cause: The subprogram was found to have two external PARAMETER STYLE specifications.
Action: Remove one of the external PARAMETER STYLE specifications.
Cause: The subprogram was found to have two external PARAMETER STYLE specifications.
Action: Remove one of the external PARAMETER STYLE specifications.
Cause: The subprogram was found to have two external LANGUAGE specifications.
Action: Remove one of the external LANGUAGE specifications.
Cause: The subprogram was found to have two external CALLING STANDARD specifications.
Action: Remove one of the external CALLING STANDARD specifications.
Cause: The subprogram was found to have two external WITH CONTEXT specifications.
Action: Remove one of the external WITH CONTEXT specifications.
Cause: The subprogram was found to have two external TRUSTED/UNTRUSTED specifications.
Action: Remove one of the external TRUSTED/UNTRUSTED specifications.
Cause: A zero-length string was found for the LIBRARY file specification.
Action: Specify a non-zero length string for the LIBRARY file specification.
Cause: The subprogram was found to have two PRAGMA RESTRICT_REFERENCES.
Action: Remove one of the PRAGMA RESTRICT_REFERENCES.
Cause: This error happens in the creation of a PL/SQL external type: [SIGNED | UNSIGNED] BINARY INTEGER (precision). It may be referenced only in a create type statement. Such types are non-queryable. Something other than 'INTEGER' was supplied.
Action: Use a different type and retry the operation.
Cause: A type was used which does not belong PL/SQL. This type can only be referenced in CREATE TYPE statements, and is a non-queryable data type.
Action: Use a different type and retry the operation.
Cause: A POINTER type which does not belong to PL/SQL can only refer to an object type. This type can only be referenced in CREATE TYPE statements, and is a non-queryable data type.
Action: Use a different type and retry the operation.
Cause: A POINTER or [SIGNED/UNSIGNED] BINARY INTEGER can only be used as attributes of object types. These types can only be referenced in CREATE TYPE statements, and are non-queryable data types. One of these external PL/SQL types outside of an object type.
Action: Use a different type and retry the operation.
Cause: More than one map or order function was declared. An object type can have only one map function or one order function, but not both.
Action: Delete all but one of the MAP or ORDER functions on the type.
Cause: A procedure was declared as a MAP, ORDER, or CONSTRUCTOR method. Only functions can be MAP, ORDER, or CONSTRUCTOR methods.
Action: Change the procedure to a function.
Cause: A null constraint was specified for an attribute in an object. This is not supported.
Action: Remove the constraint.
Cause: An AUTHID clause was specified for a subprogram inside a package or type. These clauses are only supported for top-level stored procedures, packages, and types.
Action: Remove the clause.
Cause: The only two allowed options for AUTHID are CURRENT_USER and DEFINER.
Action: Fix the AUTHID clause to specify CURRENT_USER or DEFINER.
Cause: The third argument (actual parameter) passed to the named pragma (compiler directive) was not an identifier or a string literal when there is a fourth argument to the pragma. The parameter might be misspelled, or the pragma syntax might be faulty (for example, a comma might be missing between two parameters).
Action: Check the spelling of the third parameter, and make sure the proper syntax was used.
Cause: The fourth argument (actual parameter) passed to the named pragma (compiler directive) was not a numeric literal, as required. The parameter might be misspelled, or the pragma syntax might be faulty (for example, a comma might be missing between two parameters).
Action: Check the spelling of the fourth parameter, and make sure the proper syntax was used.
Cause: This feature is not yet implemented.
Action: No action required.
Cause: A CALL statement was found in PL/SQL source.
Action: Use an ordinary PL/SQL function or procedure call.
Cause: The contents of the date, time, or timestamp literal did not match the expected format 'yyyy-mm-dd hh24:mi:ssxfftzh:tzm'.
Action: Use a correctly-formatted datetime literal.
Cause: Keyword BULK is used illegally. For example, BULK is used without INTO clause.
Action: Remove the BULK keyword.
Cause: A method or object type modifier was specified more than once.
Action: Remove all but one occurrence of the duplicate modifier.
Cause: The method or object type modifier specified conflicts with an earlier modifier. For example, a FINAL modifier cannot be combined with a NOT FINAL modifier.
Action: Remove one of the conflicting modifiers.
Cause: An OPEN cursor statement or cursor FOR loop can only invoke a SELECT statement, not an UPDATE, INSERT, or DELETE.
Action: Use only SELECT statements in OPEN or cursor FOR loops.
Cause: The subprogram was found to have two dedicated AGENT specifications.
Action: Remove one of the dedicated AGENT specifications.
Cause: An attempt was made to reference either an undeclared variable, exception, procedure, or other item, or an item to which no privilege was granted or an item to which privilege was granted only through a role.
Action:
Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notified only that the item was not declared.
Cause: An attempt was made to reference an undefined type. Either the type specifier was not declared or it is not within the scope of the reference.
Action: Check the spelling and declaration of the type specifier. Also confirm that the declaration is placed correctly in the block structure.
Cause: Less than three arguments were passed to the built-in function DECODE. Though DECODE takes a variable number of (non-Boolean) arguments, at least three arguments must be passed.
Action: Call DECODE with three or more arguments.
Cause: A pseudocolumn or proscribed function was used in a procedural statement. The SQL pseudocolumns (CURRVAL, LEVEL, NEXTVAL, ROWID, ROWNUM) can be used only in SQL statements. Likewise, certain functions such as DECODE, DUMP, and VSIZE and the SQL group functions (AVG, MIN, MAX, COUNT, SUM, STDDEV, VARIANCE) can be used only in SQL statements.
Action: Remove the pseudocolumn reference or function call from the procedural statement. Or, replace the procedural statement with a SELECT INTO statement; for example, replace:
bonus := DECODE(rating, 1, 5000, 2, 2500, ...);
with the following statement:
SELECT DECODE(rating, 1, 5000, 2, 2500, ...) INTO bonus FROM dual;
Cause: An aggregate, that is, a parenthesized list of values such as (7788, 'SCOTT', 20), was found in an inappropriate context.
Action: Remove or relocate the aggregate.
Cause: The program object declared using the %TYPE datatype attribute is not of the appropriate class. It must be a variable, column, record component, subprogram formal parameter, or other object to which values can be assigned.
Action: Declare an object of the appropriate class or define the datatype in another way (for example, use %ROWTYPE).
Cause: An identifier that is not a cursor attribute was applied to the identifier SQL. For example, this error occurs if the cursor attribute is misspelled.
Action: Check the spelling of the cursor attribute name. Make sure the attribute is one of these: %NOTFOUND, %FOUND, %ROWCOUNT, %ISOPEN.
Cause: An identifier not declared as a cursor attribute was applied to an identifier declared as a cursor. For example, this error occurs if the cursor attribute is misspelled.
Action: Check the spelling of the cursor attribute name. Make sure the attribute is one of these: %NOTFOUND, %FOUND, %ROWCOUNT, %ISOPEN.
Cause: In a query, a table referenced by the select list is not named in the FROM clause.
Action: Check the spelling of the table names, make sure each column in the select list refers to a table in the FROM clause, then re-execute the query.
Cause: Unless the clauses of a CASE statement mention all values of the type of the selecting expression, an OTHERS clause must be provided as the last clause of the CASE statement. It is impossible to cover all values of type INTEGER (or NUMBER), so an OTHERS clause is always required when the expression following the keyword CASE is of type INTEGER (or NUMBER).
Action: No action required since CASE statement not supported in releases 1.x,2.x, or 3.x.
Cause: In this CASE statement, a value appears in more than one WHEN clause. A value may appear in at most one WHEN clause of a CASE statement.
Action: No action required since CASE statement not supported in releases 1.x,2.x, or 3.x.
Cause: The CASE statement is too big. The compiler did not have enough storage to process it.
Action: No action required since CASE statement not supported in releases 1.x,2.x, or 3.x.
Cause: The PL/SQL compiler could not find package STANDARD in the current Oracle database. To compile a program, PL/SQL needs package STANDARD.
Action: Make sure that package STANDARD is available in the current Oracle database, then retry the operation.
Cause: The number of levels of nesting in the PL/SQL block is too large. Blocks can be nested up to 255 levels deep, depending on the availability of system resources such as memory.
Action: Reorganize the block structure to avoid nesting at too deep a level. For example, move the lowest-level sub-block to a higher level.
Cause: When a character variable was declared, a length outside the legal range was specified. For example, the following declarations are illegal:
flag CHAR(0); -- illegal; zero length name VARCHAR2(-10); -- illegal; negative length
Action: Change the length constraint, making sure that it lies in the range 1 .. 32767.
Cause: A NUMBER variable was declared with a precision that is outside the legal range. Declarations such as N NUMBER(800) or N NUMBER(123,10) are not supported.
Action: Change the illegal NUMBER precision constraint, making sure that it lies in the range 1 .. 38.
Cause: A NUMBER variable was declared with a scale that is outside the legal range. Declarations such as N NUMBER(10,345) or N NUMBER(10,-100) are not supported.
Action: Change the illegal NUMBER scale constraint, making sure that it lies in the range -84 .. 127.
Cause: In general, variables that have no initialization clause in their declaration are automatically initialized to NULL. This is illogical for NOT NULL variables; therefore, an initialization clause is required.
Action: Add an initialization clause to the variable declaration. If the initialization is too complicated for the syntax, one can add a function call (in a later release).
Cause: A block or loop label was used to qualify a variable (as in outer_block.date) that was not declared or is not within the scope of the label. The variable name might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.
Action: Check the spelling and declaration of the variable name. Also confirm that the declaration is placed correctly in the block structure.
Cause: A qualified name such as A.B or A.B.C is not permitted here.
Action: Use a simple name such as A instead.
Cause: An identifier being referenced as a procedure was not declared or actually represents another object (for example, it might have been declared as a function).
Action: Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure.
Cause: An identifier being referenced as a function was not declared or actually represents another object (for example, it might have been declared as a procedure).
Action: Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure.
Cause: An identifier being referenced as a parameterless function actually represents a procedure.
Action: Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure. If necessary, change the declaration of the identifier or change the reference so that it does not require a return value.
Cause: An identifier being referenced as a function or an array actually represents an object (a number or date, for example) that cannot be referenced in this way.
Action: Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure.
Cause: A subprogram or cursor references a variable that was not declared or is not within the scope of the subprogram or cursor. The variable name might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.
Action: Check the spelling and declaration of the variable name. Also confirm that the declaration is placed correctly in the block structure.
Cause: A package was referenced in an expression as if it were a variable or function. Either the name of the variable or function is misspelled or the reference is not fully qualified. For example, to call the function my_function, which is stored in package my_package, dot notation must be used, as follows:
... my_package.my_function ...
Action: Correct the spelling of the variable or function name or use dot notation to reference the packaged variable or function.
Cause: When the formal parameters of a subprogram were declared, one parameter was used to initialize another, as in:
PROCEDURE my_proc (j NUMBER, k NUMBER := j) IS ...
The first parameter has no value until run time, so it cannot be used to initialize another parameter.
Action: Remove the illegal formal parameter reference.
Cause: An attempt was made to declare a variables to be of type LONG. Only columns can be of type LONG.
Action: Remove the offending variable definition.
Cause: An attribute expression, such as SQL%NOTFOUND, was used in a SQL statement, but attribute expressions are allowed only in procedural statements.
Action: To workaround this limitation, assign the value of the attribute expression to a variable, then use the variable in the SQL statement. For example, replace the statement:
INSERT INTO audits VALUES (c1%ROWCOUNT, ...);
with the following statements:
row_count := c1%ROWCOUNT; INSERT INTO audits VALUES (row_count, ...);
Cause: When the formal parameters of a procedure were declared, an OUT or IN OUT parameter was initialized to a default value, as in:
PROCEDURE calc_bonus (bonus OUT REAL := 00000, ...) IS ...
However, only IN parameters can be initialized to default values.
Action: Remove the illegal default expression.
Cause: A proscribed function was used in a SQL statement. Certain functions such as SQLCODE and SQLERRM can be used only in procedural statements.
Action: Remove the function call from the SQL statement. Or, replace the function call with a local variable. For example, the following statement is illegal:
INSERT INTO errors VALUES (SQLCODE, SQLERRM);
However, you can assign the values of SQLCODE and SQLERRM to local variables, then use the variables in the SQL statement, as follows:
err_num := SQLCODE; err_msg := SQLERRM; INSERT INTO errors VALUES (err_num, err_msg);
Cause: A package was declared inside another package, but package declarations are allowed only at the top level. In other words, packages cannot be nested.
Action: Move the package declaration outside the enclosing package.
Cause: The WHEN clause in an exception handler contains a function call instead of an exception name. A valid exception handler consists of a WHEN clause, which must specify an exception, followed by a sequence of statements to be executed when that exception is raised.
Action: Check the spelling of the identifier in the WHEN clause, then replace the function call with an exception name.
Cause: A subprogram cannot specify both PARAMETER STYLE SQL and an explicit PARAMETERS list. Use PARAMETER STYLE GENERAL to supply default PARAMETERS list types.
Action: Change the subprogram specification.
Cause: An unsuccessful attempt was made to convert a parameter to the specified external parameter type.
Action: Specify a different external type or parameter type.
Cause: The external type specified is not valid for one of the following: INDICATOR, LENGTH, MAXLEN, TDO, DURATION, CHARSETID, or CHARSETFORM.
Action: Check PL/SQL User's Guide and Reference and specify an appropriate external type.
Cause: BY VALUE was specified with an indicator or length parameter that is being passed in OUT or IN OUT mode. Only IN mode parameters may be passed by value.
Action: Remove the BY VALUE specification or change it to IN mode.
Cause: An external parameter name was specified that does not match one in the formal parameter list.
Action: Correct the external parameter name.
Cause: An inappropriate external parameter type was specified for the SQLCODE parameter.
Action: Correct the SQLCODE parameter specification.
Cause: An inappropriate external parameter type was specified for the RETURN indicator, LENGTH, TDO, or DURATION.
Action: Correct the RETURN parameter specification.
Cause: An inappropriate external parameter type was specified for the SQLSTATE parameter.
Action: Correct the SQLSTATE parameter specification.
Cause: An inappropriate external parameter type was specified for the CONTEXT parameter.
Action: Correct the CONTEXT parameter specification.
Cause: An inappropriate external parameter type was specified for the SQLNAME parameter.
Action: Correct the SQLNAME parameter specification.
Cause: There are multiple declarations of at least one the following FORMAL, INDICATOR, LENGTH, MAXLENGTH, or CONTEXT. PL/SQL allows only one declaration of the above for each formal parameter in the PARAMETERS clause.
Action: Correct the PARAMETERS clause.
Cause: There are multiple declarations of the formal parameter in the PARAMETERS clause. PL/SQL allows only one declaration of the formal parameter in the PARAMETERS clause.
Action: Correct the PARAMETERS clause.
Cause: A PARAMETER STYLE clause was placed in the external subprogram body.
Action: Remove the PARAMETER STYLE clause.
Cause: The LIBRARY name is missing from the external subprogram body.
Action: Specify the LIBRARY name in the external subprogram body.
Cause: The LIBRARY syntax was entered incorrectly.
Action: Correct the LIBRARY syntax and recompile.
Cause: CONTEXT was used in a WITH CONTEXT or a PARAMETERS clause. This is invalid.
Action: Check if WITH CONTEXT is used without specifying CONTEXT in the PARAMETERS clause or vice versa. The PARAMETERS clause must be complete. Thus if WITH CONTEXT is specified, then CONTEXT must be referenced in the PARAMETERS clause (if there is one).
Cause: The usage of Keywords such as MAXLEN, LENGTH, CHARSETID, CHARSETFORM RETURN in the parameters clause was incorrect
Action: This error occurs if keywords like MAXLEN, LENGTH, TDO, DURATION, CHARSETID, or CHARSETFORM cannot be used with PL/SQL formal parameters or the mode of the formal parameter. For example, MAXLEN can only be declared for a CHAR or VARCHAR2 PL/SQL variable and its use is allowed only for OUT or INOUT PL/SQL variables. Also, RETURN was specified in the parameters clause for a PL/SQL PROCEDURE. For example:
CREATE PROCEDURE proc IS EXTERNAL NAME ... LIBRARY ... PARAMETERS(RETURN INDICATOR SHORT, RETURN);
Cause: RETURN specification for the actual function return, used within the parameters clause must hold the very last position. Example: The following will give this error since the RETURN specification for the actual function return in the parameters clause is not the last.
FUNCTION myexternalfunc (var1 BINARY_INTEGER, var2 BINARY_INTEGER) RETURN BINARY_INTEGER IS EXTERNAL NAME "myexternalfunc" LIBRARY somelib PARAMETERS (var1 LONG, var2 SHORT, RETURN INT, RETURN INDICATOR SHORT);
The correct syntax is the following. Note that RETURN for actual function return is the last specification in the parameters clause.
FUNCTION myexternalfunc (var1 BINARY_INTEGER, var2 BINARY_INTEGER) RETURN BINARY_INTEGER IS EXTERNAL NAME "myexternalfunc" LIBRARY somelib PARAMETERS (var1 LONG, var2 SHORT, RETURN INDICATOR SHORT, RETURN INT);
Action: Correct the syntax of the RETURN specification in the parameters clause.
Cause: A reference is made to the fixed package version of STANDARD when the database is open, or to the on-disk version when the database is closed. Explicit use of a SYS.X$ package name can lead to this. One might also see this from a compilation that begins while the database is closed but has the bad luck to have another session open the database before compilation is complete. Another possible cause is having SET SERVEROUTPUT ON through SVRMGRL when doing an ALTER DATABASE CLOSE.
Action: Make use of on-disk versions of packages when the database is open and fixed (preloaded) versions when the database is closed. Do not attempt to use the wrong set. It should be quite hard to reference the wrong set, except by using SYS.X$ package names explicitly. Also, be sure to SET SERVEROUTPUT OFF before doing an ALTER DATABASE CLOSE.
Cause: The parameter clause does not have a matching parameter for a parameter specified in the formal parameter list.
Action: Provide a matching parameter in parameters clause for every formal parameter.
Cause: Actual parameter mode (OUT, or IN/OUT) is not used properly in USING clause. For USING clause in an OPEN statement, only IN mode is allowed.
Action: Change the parameter mode in the USING clause to an appropriate mode.
Cause: CALL Specification formal parameters default values are disallowed.
Action: Remove the default values in the formal parameter list (specification or body) and compile again.
Cause: The supplied external library name in the external clause is not a valid alias library.
Action: Provide the name of a valid alias library that has been previously declared.
Cause: An EXTERNAL Clause was detected in a TYPE Specification or a PACKAGE specification.
Action: Remove the EXTERNAL Clause and use the new CALL Specification syntax.
Cause: A call specification for C or Java cannot have constraints on the PL/SQL formal parameter types. PL/SQL types which have constraints are NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, INTEGER, INT, SMALLINT, DECIMAL, NUMERIC, DEC. This includes NOT NULL constraints from POSITIVEN, NATURALN.
Action: Use the unconstrained type for that PL/SQL formal declaration that is, NUMBER, BINARY_INTEGER or PLS_INTEGER
Cause: It is mandatory to specify the rights model if a PL/SQL PROCEDURE, FUNCTION, PACKAGE OR TYPE HAS CALL-Specifications.
Action: Add the AUTHID clause indicating the rights model.
Cause: A Datetime/Interval variable or attribute was declared with a constraint outside the legal range.
Action: Change the illegal constraint.
Cause: A call specification declaring that an implementation is in Java has been found for a method of an object or opaque type. This usage is not yet supported.
Action: Implement the method using a language other than Java. A PL/SQL method implementation is permitted to call to Java through a call specification provided as a schema-level procedure or in a package; such a call specification can only target a static Java method, however.
Cause: In a reference to a component (for example, in the name "A.B", "B" is a component of "A"), the component has not been declared. The component might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.
Action: Check the spelling and declaration of the component. Also confirm that the declaration is placed correctly in the block structure.
Cause: In a name such as "A.B", "A" is the qualifier, and "B" is a component of the qualifier. This error occurs when no declaration for the qualifier is found. The qualifier might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.
Action: Check the spelling and declaration of the qualifier. Also confirm that the declaration is placed correctly in the block structure.
Cause: The compiled package specification required to compile a package body could not be found. Some possible causes follow:
The package specification must be compiled before compiling the package body, and the compiler must have access to the compiled specification.
Action: Check the spelling of the package name. Compile the package specification before compiling the package body. Also, make sure the compiler has access to the compiled specification.
Cause: While looking for prior declarations of a cursor, procedure, function, or package, the compiler found another object with the same name in the same scope. Or, the headers of subprogram in a package specification and body do not match word for word.
Action: Check the spelling of the cursor, procedure, function, or package name. Also check the names of all constants, variables, parameters, and exceptions declared in the same scope. Then, remove or rename the object with the duplicate name. Or, change the headers of the packaged subprogram so that they match word for word.
Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype.
Action: Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.
Cause: The declaration of a subprogram or cursor name is ambiguous because there was no exact match between the declaration and the call and more than one declaration matched the call when implicit conversions of the parameter datatypes were used. The subprogram or cursor name might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.
Action: Check the spelling and declaration of the subprogram or cursor name. Also confirm that it's call is correct, it's parameters are of the right datatype, and, if it is not a built-in function, that it's declaration is placed correctly in the block structure.
Cause: The construct or expression does not designate a value that can be assigned to a variable. For example, the datatype name NUMBER cannot appear on the right hand side of an assignment statement as in X := NUMBER.
Action: Correct the illegal assignment statement.
Cause: The "%LAST" attribute must be applied to an identifier that has been declared as a variable of an enumerated type (or subtype of an enumerated type). This error occurs when "%LAST" follows some identifier that has not been so declared.
Action: Make sure that %LAST follows an enumerated type (or subtype of an enumerated type).
Cause: The %ROWTYPE attribute must be applied to an identifier declared as a cursor, cursor variable, or database table. This error occurs when %ROWTYPE follows some identifier that has not been so declared.
Action: Change the declaration or do not apply the %ROWTYPE attribute to the identifier.
Cause: This occurrence of the identifier cannot be compiled because its type has not been properly defined.
Action: Correct the faulty datatype declaration.
Cause: When a list of parameters is passed to a subprogram or cursor, if both positional and named associations are used, all positional associations must be placed in their declared order and before all named associations, which can be in any order.
Action: Reorder the parameter list to meet the requirements or use named association only.
Cause: There is no declaration for the given identifier within the scope of reference. The identifier might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.
Action: Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure.
Cause: In a precompiled program, the DECLARE TABLE statement was mistakenly used inside an embedded PL/SQL block. If an embedded PL/SQL block refers to a database table that does not yet exist, use the DECLARE TABLE statement to tell the precompiler what the table will look like. However, DECLARE TABLE statements are allowed only in the host program.
Action: Move the DECLARE TABLE statement outside the embedded PL/SQL block. If you want a variable that can store an entire row of data selected from a database table or fetched from a cursor or cursor variable, use the %ROWTYPE attribute.
Cause: In the INDEX BY clause of a PL/SQL table declaration, a datatype other than BINARY_INTEGER, PLS_INTEGER, or VARCHAR2 was specified. PL/SQL tables can have one column and a primary key. The column can have any scalar type, but the primary key must be either a binary integer type or VARCHAR2.
Action: Use one of the supported key types in the INDEX BY clause.
Cause: In the INDEX BY clause of a PL/SQL table declaration, a composite primary key was specified. PL/SQL tables must have a simple, unnamed primary key of a binary integer or VARCHAR2 type.
Action: Use one of the supported key types in the INDEX BY clause.
Cause: An incomplete type declaration was not completed in the declarative region where it was declared.
Action: Complete the type appropriately, then retry the operation.
Cause: A type-declaration such as:
-- non-REF recursive type type t is record (a t);
or
-- non-REF mutually dependent types type t1; type t2 is record (a t1); type t1 is record (a t2);
was entered.
Action: Use another type to remove the recursion.
Cause: An invalid expression such as X IN (SELECT A,B ...) was used. When a [NOT]IN clause is used with a subquery, it does not test for set membership. The number of expressions in the [NOT]IN clause and the subquery select list must match. So, in the example above, the subquery must specify at most one column.
Action: Change the subquery to select only one column.
Cause: In a declaration, the name of a variable or cursor is misspelled or the declaration makes a forward reference. Forward references are not allowed in PL/SQL. A variable or cursor must be declared before it is referenced it in other statements, including other declarative statements. For example, the following declaration of dept_rec raises this exception because it refers to a cursor not yet declared:
DECLARE dept_rec dept_cur%ROWTYPE; CURSOR dept_cur IS SELECT ... ...
Action: Check the spelling of all identifiers in the declaration. If necessary, move the declaration so that it makes no forward references.
Cause: The expression does not designate a variable that can have a value assigned to it. For example, the function SYSDATE cannot appear on the left hand side of an assignment statement such as:
SYSDATE := '01-JAN-1990';
Action: Correct the illegal assignment statement.
Cause: A constant declaration lacks the assignment of an initial value. For example, in the following declaration" := 3.14159" is the initialization clause:
"pi constant number := 3.14159;"
Action: Correct the constant declaration by supplying the missing initialization assignment.
Cause: A subprogram specification was placed in a package specification, but the corresponding subprogram body was not placed in the package body. The package body implements the package specification. So, the package body must contain the definition of every subprogram declared in the package specification.
Action: Check the spelling of the subprogram name. If necessary, add the missing subprogram body to the package body.
Cause: This error occurs when a cursor-attribute ("%FOUND", "%NOTFOUND", "%ROWS", "%IS_OPEN", and so on) appears following an identifier that is not declared as a cursor or cursor variable. It occurs, for example, if the variable name my_cur in my_cur%FOUND was not properly declared as a cursor or if the variable declaration was placed incorrectly in the block structure.
Action: Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure.
Cause: A non-integral numeric literal was used in a context that requires an integer (a number with no digits to the right of its decimal point).
Action: Replace the inappropriate literal with an integer literal.
Cause: The number of expressions in an IN clause did not equal the number of expressions in a corresponding subquery select list. For example, the following statement is invalid because the IN clause contains two expressions, but the subquery select list contains just one:
... WHERE (ename, sal) IN (SELECT sal FROM emp);
Action: Check the number of expressions in each set, then revise the statement to make the numbers equal.
Cause: In a SQL statement, a reference was made to an out-of-scope database object. The referenced object might be misspelled, or the reference might be mixed, as in the following example:
CURSOR c1 IS SELECT dept.dname FROM emp;
Action: Check the spelling of all database objects in the SQL statement, and make sure all references are to objects within the current scope.
Cause: A subprogram specification was declared, but the corresponding subprogram body was not defined. Write the subprogram specification and body as a unit. An alternative solution is to separate the specification from its body, which is necessary when you want to define mutually recursive subprograms or you want to group subprograms in a package.
Action: Check the spelling of the subprogram name. If necessary, supply the missing subprogram body.
Cause: An attempt was made to make a reference from a schema-level type to something other than a schema-level type.
Action: Replace the illegal reference and retry the operation.
Cause: A datatype or subtype specifier was mistakenly used in place of a constant, variable, or expression. For example, the code might look like:
IF emp_count > number THEN ... -- illegal; NUMBER is a datatype specifier
Action: Replace the datatype or subtype specifier with a valid constant, variable, or expression.
Cause: One of the following:
Action: Remove the reference.
Cause: A malformed qualified name was specified because the prefix is not valid.
Action: Remove or change the reference.
Cause: The name, appearing in the context of a REF or VALUE, did not resolve to an object-table alias.
Action:
Cause: A reference was made to a table or view name without an alias.
Action: Replace the reference with a reference to an object-table alias.
Cause: In a CREATE PACKAGE statement, a package (which is a database object that groups logically related PL/SQL types, objects, and subprograms) was given the same name as an existing database object.
Action: Give the package a unique name.
Cause: A REF or VALUE modifier was specified for a non-object-table. This is not allowed in this context.
Action: Replace the reference with a reference to an object-table alias.
Cause: An ambiguous reference was made to an object-table alias; there are at least two object-table aliases in the same scope.
Action: Remove all but one of the references or change the alias names of the remaining.
Cause: A reference was made to a name that could not be resolved as a column or row expression. This occurred in an update or insert statement involving an object-table.
Action: If this is an update statement and you intended to code this as a column reference, fix it as such. If you intended to code this as a row expression, change this to refer to an alias of an object-table.
Cause: An object-table name was specified without a REF or VALUE modifier.
Action: If you intended to code this with a REF or VALUE modifier, add the modifier; if you intended to code this as a column reference, change the reference appropriately; otherwise, remove the reference.
Cause: A cursor declaration is improper or an identifier referenced in the cursor declaration was not properly declared. A return type that does not refer to an existing database table or a previously declared cursor or cursor variable might have been specified. For example, the following cursor declaration is illegal because c1 is not yet fully defined:
CURSOR c1 RETURN c1%ROWTYPE IS SELECT ... -- illegal
In this case, a return type does not have to be specified because it is implicit.
Action: Check the spelling and declaration of the cursor name and any identifiers referenced in the cursor declaration. Also confirm that the declaration is placed correctly in the block structure. If a return type was specified, make sure that it refers to an existing database table or a previously declared cursor or cursor variable.
Cause: An attempt was made to access an Oracle database without being logged on. Probably, an invalid username or password was entered.
Action: Log on to Oracle with a correctly spelled username and password before trying to access the database.
Cause: An attempt was made to reference an object in a database other than the current local or remote Oracle database.
Action: Correct the reference and make sure the object is in the current Oracle database.
Cause: This error occurs when the username was misspelled or when the user does not exist in the database.
Action: Check the spelling of the username and make sure the user exists.
Cause: A qualified username such as scott.accts is not permitted in this context.
Action: Specify a simple username such as scott instead.
Cause: A PL/SQL table was referenced in the wrong context (for example, with a remote link).
Action: Remove the PL/SQL table reference or change the context.
Cause: The named table is not accessible to the user. This error occurs when the table name or username was misspelled, the table and/or user does not exist in the database, the user was not granted the necessary privileges, or the table name duplicates the name of a local variable or loop counter.
Action: Check the spelling of the table name and username. Also confirm that the table and user exist, the user has the necessary privileges, and the table name does not duplicate the name of a local variable or loop counter.
Cause: A reference to database table, view, or sequence was found in an inappropriate context. Such references can appear only in SQL statements or (excluding sequences) in %TYPE and %ROWTYPE declarations. Some valid examples follow:
SELECT ename, emp.deptno, dname INTO my_ename, my_deptno, my_dept FROM emp, dept WHERE emp.deptno = dept.deptno; DECLARE last_name emp.ename%TYPE; dept_rec dept%ROWTYPE;
Action: Remove or relocate the illegal reference.
Cause: The statement is ambiguous because it specifies two or more tables having the same column name. For example, the following statement is ambiguous because deptno is a column in both tables:
SELECT deptno, loc INTO my_deptno, my_loc FROM emp, dept;
Action: Precede the column name with the table name (as in emp.deptno) so that the column reference is unambiguous.
Cause: An assignment target was declared that lacks the components needed to store the assigned values. For example, this error occurs if you try to assign a row of column values to a variable instead of a record, as follows:
DECLARE dept_rec dept%ROWTYPE; my_deptno dept.deptno%TYPE; ... BEGIN SELECT deptno, dname, loc INTO my_deptno -- invalid FROM dept WHERE ...
Action: Check the spelling of the names of the assignment target and all its components. Make sure the assignment target is declared with the required components and that the declaration is placed correctly in the block structure.
Cause: A cursor declaration lacks either a body (SELECT statement) or a return type. If you want to separate a cursor specification from its body, a return type must be supplied, as in:
CURSOR c1 RETURN emp%ROWTYPE;
Action: Add a SELECT statement or return type to the cursor declaration.
Cause: A cursor parameter with mode IN cannot be modified, and therefore cannot be opened.
Action: Change the cursor parameter to be IN OUT or OUT.
Cause: In a cursor specification or REF CURSOR type definition, a non-record type such as NUMBER or TABLE was specified as the return type. This is not allowed. Only the following return types are allowed:
Action: Revise the cursor specification or REF CURSOR type definition so that it specifies one of the above return types.
Cause: A literal, constant, IN parameter, loop counter, or function call was mistakenly used as the target of an assignment. For example, the following statement is illegal because the assignment target, 30, is a literal:
SELECT deptno INTO 30 FROM dept WHERE ... -- illegal
Action: Correct the statement by using a valid assignment target.
Cause: A reference to a loop counter was found in an inappropriate context. For example, the following statement is illegal because the loop counter is used as the terminal value in its own range expression:
FOR j IN 1 .. j LOOP ... -- illegal
Action: Change the loop range expression so that it does not reference the loop counter. If you want to refer in the range expression to another variable with the same name as the loop counter, change either name or qualify the variable name with a label.
Cause: An attempt was made to assign the value of an OUT parameter to another parameter or variable. Inside a procedure, an OUT parameter acts like an uninitialized variable; therefore, its value cannot be read. For example, the following assignments are illegal:
PROCEDURE calc_bonus (bonus OUT REAL, ...) IS rating REAL; wages REAL; BEGIN ... IF rating > 90 THEN bonus := bonus * 2; -- illegal SELECT sal + bonus INTO wages FROM emp ... -- illegal ... END IF; ... END calc_bonus;
Action: Use an IN OUT parameter instead of the OUT parameter. Inside a procedure, an IN OUT parameter acts like an initialized variable; therefore, its value can be read.
Cause: After a subtype was defined as NOT NULL, it was used as the base type for another subtype defined as NULL. That is not allowed. For example, the code might look like:
DECLARE SUBTYPE Weekday IS INTEGER NOT NULL; SUBTYPE Weekend IS Weekday NULL; -- illegal
instead of:
DECLARE SUBTYPE Weekday IS INTEGER NOT NULL; SUBTYPE Weekend IS Weekday;
Action: Revise the subtype definitions to eliminate the conflict.
Cause: A RAISE statement not followed by an exception name was found outside an exception handler.
Action: Delete the RAISE statement, relocate it to an exception handler, or supply the missing exception name.
Cause: The identifier in a RAISE statement is not a valid exception name.
Action: Make sure the identifier in the RAISE statement was declared as an exception and is correctly placed in the block structure. If you are using the name of a PL/SQL predefined exception, check its spelling.
Cause: A construct of the form:
WHEN excep1 OR OTHERS =>
was encountered in the definition of an exception handler. The OTHERS handler must appear by itself as the last exception handler in a block.
Action: Remove the identifier that appears with OTHERS or write a separate exception handler for that identifier.
Cause: One or more exception handlers appear after an OTHERS handler. However, the OTHERS handler must be the last handler in a block or subprogram because it acts as the handler for all exceptions not named specifically.
Action: Move the OTHERS handler so that it follows all specific exception handlers.
Cause: A reference to an identifier is ambiguous because there are conflicting declarations for it in the declarative part of a block, procedure, or function. At most one declaration of the identifier is permitted in a declarative part.
Action: Check the spelling of the identifier. If necessary, remove all but one declaration of the identifier.
Cause: In a procedure, a RETURN statement contains an expression, which is not allowed. In functions, a RETURN statement must contain an expression because its value is assigned to the function identifier. However, in procedures, a RETURN statement lets you exit before the normal end of the procedure is reached.
Action: Remove the expression from the RETURN statement, or redefine the procedure as a function.
Cause: The label in an EXIT statement does not refer to a loop. An EXIT statement need not specify a label. However, if a label is specified (as in EXIT my_label), it must refer to a loop statement.
Action: Make sure the label name is spelled correctly and that it refers to a loop statement.
Cause: An EXIT statement need not specify a label. However, if a label is specified (as in EXIT my_label), the EXIT statement must be inside the loop designated by that label.
Action: Make sure the label name is spelled correctly; if necessary, move the EXIT statement inside the loop to which the label refers.
Cause: The line and column numbers accompanying the error message refer to a GOTO that branches from outside a construct (a loop or exception handler, for example) that contains a sequence of statements to a label inside that sequence of statements. Such a branch is not allowed.
Action: Either move the GOTO statement inside the sequence of statements or move the labeled statement outside the sequence of statements.
Cause: An EXIT statement was found outside of a loop construct. The EXIT statement is used to exit prematurely from a loop and so must always appear within a loop.
Action: Either remove the EXIT statement or place it inside a loop.
Cause: The type-name INTEGER was used in a declaration.
Action: Do not use.
Cause: A compilation unit is a file containing PL/SQL source code that is passed to the compiler. Only compilation units containing blocks, declarations, statements, and subprograms are allowed. This error occurs when some other language construct is passed to the compiler.
Action: Make sure the compilation unit contains only blocks, declarations, statements, and subprograms.
Cause: The unit being compiled contains a CASE statement. However, the current release of PL/SQL does not support CASE statements.
Action: Remove the CASE statement from the compilation unit.
Cause: The program being compiled contains a declaration of a FUNCTION or PROCEDURE.
Action: Do not use.
Cause: The datatypes of a column and a variable do not match. The variable was encountered in a subquery or INSERT statement.
Action: Change the variable datatype to match that of the column.
Cause: An expression has the wrong datatype for the context in which it was found.
Action: Change the datatype of the expression. You might want to use datatype conversion functions.
Cause: In a test for set membership such as X NOT IN (SELECT Y ... ), the expressions X and Y do not match in datatype, and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the expressions so that their datatypes match. You might want to use datatype conversion functions in the select list.
Cause: The column to the left of the equal sign in the SET clause of an UPDATE statement does not match in datatype with the column, expression, or subquery to the right of the equal sign, and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the expressions so that their datatypes match. You might want to use datatype conversion functions in the SET clause.
Cause: The expressions to the left and right of the INTO clause in a SELECT...INTO statement do not match in datatype, and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the expressions so that their datatypes match. You might want to use datatype conversion functions in the select list.
Cause: An assignment target in the INTO list of a FETCH statement does not match in datatype with the corresponding column in the select list of the cursor declaration, and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the cursor declaration or change the datatype of the assignment target. You might want to use datatype conversion functions in the select list of the query associated with the cursor.
Cause: An item in the INTO list of a FETCH or SELECT statement was found to be a database object. INTO introduces a list of user-defined variables to which output values are assigned. Therefore, database objects cannot appear in the INTO list.
Action: Check the spelling of the INTO list item. If necessary, remove the item from the INTO list or replace it with a user-defined output variable.
Cause: A subquery contains a column name that was not defined in the specified table.
Action: Change the expression to specify a column that was defined.
Cause: A table, view or alias name name corresponding to a regular (non-object) table appeared in an inappropriate context.
Action: Remove the reference, or, if you intended to code this as a column reference, fix it as such.
Cause: An INSERT statement contains an undefined column name.
Action: Check the spelling of the column name, then change the expression so that it refers only to defined columns.
Cause: An UPDATE statement refers to a column not defined for the table or view being updated.
Action: Check the spelling of the column name, then revise the statement so that it refers only to defined columns.
Cause: In a comparison such as X BETWEEN Y AND Z, the expressions X, Y, and Z do not match in datatype, and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the expressions so that their datatypes match. You might want to use datatype conversion functions.
Cause: The number of columns selected by a SELECT...INTO statement does not match the number of variables in the INTO clause.
Action: Change the number of columns in the select list or the number of variables in the INTO clause so that the numbers match.
Cause: The number of variables in the INTO clause of a FETCH statement does not match the number of columns in the cursor declaration.
Action: Change the number of variables in the INTO clause or the number of columns in the cursor declaration so that the numbers match.
Cause: The number of columns in an INSERT statement does not match the number of values in the VALUES clause. For example, the following statement is faulty because no column is specified for the value 20:
INSERT INTO emp (empno, ename) VALUES (7788, 'SCOTT', 20);
Action: Change the number of items in the column list or the number of items in the VALUES list so that the numbers match.
Cause: The number of columns in an INSERT statement does not match the number of columns in a subquery select list. For example, the following statement is faulty because no corresponding column is specified for col3:
INSERT INTO emp (ename, empno) SELECT col1, col2, col3 FROM ...
Action: Change the number of items in the column list of the INSERT statement or the number of items in the select list so that the numbers match.
Cause: In a test for set membership such as X IN (Y, Z), the expressions X, Y, and Z do not match in datatype, and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the expressions so that their datatypes match. You might want to use datatype conversion functions.
Cause: The SELECT clauses to the left and right of a UNION, INTERSECT, or MINUS expression do not select the same number of columns. For example, the following statement is faulty because the select lists do not contain the same number of items:
CURSOR my_cur IS SELECT ename FROM emp INTERSECT SELECT ename, empno FROM emp;
Action: Change the select lists so that they contain the same number of items.
Cause: The select lists to the left and right of a UNION, INTERSECT, or MINUS expression select at least one column that is mismatched in datatype. For example, the following statement is faulty because the constant 3 has datatype NUMBER, whereas SYSDATE has datatype DATE:
CURSOR my_cur IS SELECT 3 FROM emp INTERSECT SELECT SYSDATE FROM emp;
Action: Change the select lists so that they match in datatype. You might want to use datatype conversion functions in the select list of one or more queries.
Cause: In a cursor declaration, a return type (such as RETURN emp%ROWTYPE) was specified, but the number of returned column values does not match the number of select-list items.
Action: Change the cursor return type or the select list so that the number of returned column values matches the number of select-list items.
Cause: In a cursor declaration, a return type (such as RETURN emp%ROWTYPE) was specified, but a returned column value and its corresponding select-list item have different datatypes.
Action: Change the cursor return type or the select list so that each returned column value and its corresponding select-list item have the same datatype.
Cause: A cursor was declared with a SELECT statement that contains duplicate column names. Such references are ambiguous.
Action: Replace the duplicate column name in the select list with an alias.
Cause: A FETCH statement was unable to assign a value to an assignment target in its INTO list because the target is not a legally formed and declared variable. For example, the following assignment is illegal because 'Jones' is a character string, not a variable:
FETCH my_cur INTO 'Jones';
Action: Check the spelling and declaration of the assignment target. Make sure that the rules for forming variable names are followed.
Cause: The use of the CURRENT OF cursor_name clause is legal only if cursor_name was declared with a FOR UPDATE clause.
Action: Add a FOR UPDATE clause to the definition of the cursor or do not use the CURRENT OF cursor_name clause.
Cause: A subquery was used in an inappropriate context, such as:
if (SELECT deptno FROM emp WHERE ... ) = 20 then ...
Subqueries are allowed only in SQL statements.
Action: The same result can be obtained by using a temporary variable, as in:
SELECT deptno INTO temp_var FROM emp WHERE ...; IF temp_var = 20 THEN ...
Cause: A query select list is not the same length as the list of targets that will receive the returned values. For example, the following statement is faulty because the subquery returns two values for one target:
UPDATE emp SET ename = (SELECT ename, empno FROM emp WHERE ename = 'SMITH') ...
Action: Change one of the lists so that they contain the same number of items.
Cause: An asterisk (*) was used as an abbreviation for a list of column names. However, in this context the column names must be written out explicitly.
Action: Replace the asterisk with a list of column names.
Cause: An UPDATE or INSERT statement has a column list that contains duplicate column names.
Action: Check the spelling of the column names, then eliminate the duplication.
Cause: The same variable appears twice in the INTO list of a SELECT or FETCH statement.
Action: Remove one of the variables from the INTO list.
Cause: When a user-defined record was declared, the same name was given to two fields. Like column names in a database table, field names in a user-defined record must be unique.
Action: Check the spelling of the field names, then remove the duplicate.
Cause: In a statement of the form aggregate = subquery, the numbers of values in the aggregate and subquery are unequal. For example, the code might look like:
... WHERE (10,20,30) = (SELECT empno,deptno FROM emp WHERE...);
Action: Revise the aggregate or subquery so that the numbers of values match.
Cause: A parenthesized list of values separated by commas (that is, an aggregate) was used in the wrong context. For example, the following usage is invalid:
WHERE (col1, col2) > (SELECT col3, col4 FROM my_table ...)
However, an equal sign can take a list of values and a subquery as left- and right-hand-side arguments, respectively. So, the following usage is valid:
WHERE (col1, col2) = (SELECT col3, col4 FROM my_table ...)
Action: Rewrite the expression. For example, the clause:
WHERE (col1, col2) > (SELECT col3, col4 FROM my_table ...)
can be rewritten as:
WHERE col1 > (SELECT col3 FROM my_table ...) AND col2 > (SELECT col4 FROM my_table ...)
Cause: The identifier in a CURRENT OF clause names an object other than a cursor.
Action: Check the spelling of the identifier. Make sure that it names the cursor in the DELETE or UPDATE statement and that it names the cursor itself, not a FOR-loop variable.
Cause: A table name or alias was used to qualify a column reference, but the column was not found in that table. Either the column was never defined or the column name is misspelled.
Action: Confirm that the column was defined and check the spelling of the column name.
Cause: An OUT or IN OUT formal parameter was used in a function specification.
Action: Change the parameter to an IN parameter.
Cause: The third argument of DECODE is NULL.
Action: Enter an argument with a proper type and value.
Cause: A database table, view, or column was specified in a SQL statement that does not exist, or the privileges required to access the table or view were not granted.
Action: Check the spelling of the table (or view) and column names; make sure the table and columns exist. If necessary, ask the database administrator to grant the privileges required to access the table.
Cause: A host array was passed (by an Oracle Precompiler program, for example) to a PL/SQL subprogram for binding to a PL/SQL table parameter. However, the datatypes of the array elements and PL/SQL table rows are incompatible. So, the binding failed.
Action: Change the datatype of the array elements or PL/SQL table rows to make the datatypes compatible.
Cause: An attempt was made to reference a remote cursor attribute, which is not allowed. For example, the code might look like:
IF SQL%NOTFOUND@newyork THEN ...
Action: Do not try to reference a remote cursor attribute.
Cause: An attempt was made to call a built-in PL/SQL function remotely, which is not allowed. For example, the code might look like:
my_sqlerrm := SQLERRM@newyork;
or
INSERT INTO emp VALUES (my_empno, STANDARD.RTRIM@newyork(my_ename), ...);
Action: Always call built-in functions locally; never specify a database link.
Cause: Directly or indirectly, a synonym was defined in terms of itself, creating a circular definition. Or, a chain of synonyms, too long for the PL/SQL compiler to handle, was defined.
Action: Redefine the synonyms to eliminate the circular definition. If necessary, shorten the chain of synonyms.
Cause: A host variable was passed (by an Oracle Precompiler program, for example) to PL/SQL for binding. However, its datatype is not compatible with any PL/SQL datatype. So, the binding failed.
Action: Change the datatype of the host variable to make it compatible with a PL/SQL datatype.
Cause: A column alias was used in the ORDER BY clause of a SELECT statement that uses a UNION, INTERSECT, or MINUS set operator. This is not allowed. In such cases, expressions in the ORDER BY clause must be unsigned integers that designate the ordinal positions of select-list items.
Action: Change the alias in the ORDER BY clause to an unsigned integer that designates the ordinal position of the select item in question.
Cause: An attempt was made to call a remote subprogram whose defaulted parameters depend on package state, which is not allowed. When calling remote subprograms, the actual parameters must be passed explicitly if the corresponding formal parameters depend on package state.
Action: Call the remote subprogram by passing each actual parameter explicitly.
Cause: When a stored function was called from a SQL statement, parameters of the wrong type were passed. To be callable from SQL statements, a stored function must meet several requirements, one of which is that its arguments have SQL datatypes such as CHAR, DATE, or NUMBER. None of the arguments can have non-SQL types such as BOOLEAN, TABLE, or RECORD.
Action: Make sure all the arguments in the function call have SQL datatypes.
Cause: When a cursor variable was declared as the formal parameter of a subprogram that will FETCH from and/or CLOSE the cursor variable, the OUT parameter mode was specified. This is not allowed. In such cases, the IN or IN OUT mode must be specified.
Action: Change the parameter mode from OUT to IN or IN OUT.
Cause: An attempt was made to call a remote subprogram whose defaulted parameter value is calculated using a builtin operation. If the calling system uses a different version of package STANDARD than does the called system, a defaulted expression must be either a simple numeric or string literal, NULL, or a direct call to a user-written function.
Action: Call the remote subprogram by passing each actual parameter value explicitly.
Cause: The INTO clause of a SELECT INTO statement was omitted. For example, the code might look like:
SELECT deptno, dname, loc FROM dept WHERE ...
instead of:
SELECT deptno, dname, loc INTO dept_rec FROM dept WHERE ...
In PL/SQL, only a subquery is written without an INTO clause.
Action: Add the required INTO clause.
Cause: One of the following:
This is as ORA-28815.
Action: Use a separate select statement to get the values.
Cause: FORALL iteration variable can only be used as a subscript. It cannot be used directly or as a part of an expression.
Action: Use FORALL variable only as a collection subscript.
Cause: More than one index specified to access SQL bulk attribute.
Action: Use a single index of integer datatype.
Cause: SELECT statement contains both the FORALL and BULK COLLECT INTO phrases.
Action: Do not use FORALL and BULK COLLECT INTO together in SELECT statements.
Cause: PL/SQL compiler could not find the entries (such as type declarations) that should exist in the package STANDARD.
Action: Make sure that the correct version of package STANDARD is available in the current Oracle database, then retry compilation.
Cause: An attempt was made to use an unsupported type for a record field. In this release, the use of the SQL92 datetime types (time, timestamp, time with time zone, timestamp with time zone, interval year to month, interval day to second) are not supported in record fields.
Action: Use only supported types for record fields.
Cause: The DML (SELECT/INSERT/DELETE/UPDATE) statement inside the FORALL statement does not contain BULK IN-BIND variables.
Action: The DML (SELECT/INSERT/DELETE/UPDATE) statement inside the FORALL statement must contain BULK IN-BIND variables.
Cause: The table(bulk_index).field is not supported at run-time yet.
Action: Use FOR loop with plain FORALL DML statement (SELECT/INSERT/DELETE/UPDATE) instead.
Cause: RETURNING table(bulk_index) is not supported.
Action: Use RETURNING BULK COLLECT instead.
Cause: The evaluation value in the LIMIT clause of a bulk fetch was found in an inappropriate context. For example, the following statement is illegal because the LIMIT clause in a bulk fetch expects a numeric value.
Action: Change the expression of the LIMIT clause so that the evaluation result is compatible to a numeric value.
Cause: A LIMIT clause is used within a non-bulk fetch.
Action: Do not use a LIMIT clause when a non-bulk fetch is used.
Cause: A variable declaration uses a type that is declared PRIVATE in some other compilation unit.
Action: Do not use this type in a PRIVATE variable declaration.
Cause: A packaged function cannot be called from SQL statements unless its purity level is asserted by coding a RESTRICT_REFERENCES pragma in the package specification. The pragma, which is used to control side effects, tells the PL/SQL compiler to deny the packaged function read/write access to database tables, public packaged variables, or both. A SQL statement that violates the pragma will cause a compilation error.
Action: Raise the purity level of the function, or relax the pragma restrictions.
Cause: A reference to a remote object tables or user-defined type column was found.
Action: Do not refer to it.
Cause: A reference to a remote table or subquery was found in a SQL statement with a returning into clause.
Action: Do not refer to it, or do use a subsequent select statement.
Cause: A REF CURSOR was dynamically opened that has RETURN type. However, only REF CURSOR without RETURN type can be opened by an embedded dynamic OPEN statement.
Action: define a REF CURSOR without return type, and use it in the statement.
Cause: The given variable is not a cursor and therefore cannot be OPENed or FETCHed from.
Action: Check the spelling and declaration for the given variable.
Cause: An expression of the wrong type is in USING or dynamic RETURNING clause. In USING or dynamic RETURNING clause, an expression cannot be of non-SQL types such as BOOLEAN, INDEX TABLE, and record.
Action: Change the expression type to a SQL type.
Cause: The subprogram is not REPEATABLE and cannot be called from a REPEATABLE subprogram.
Action: Delete the call or delete REPEATABLE.
Cause: One of the following features were specified in the REPEATABLE subprogram: dynamic SQL statements or autonomous transaction.
Action: Delete the statements or delete REPEATABLE.
Cause: Repeatable subprogram can neither read database state (RNDS), write database state (WNDS), read package state (RNPS), nor write package state (WNPS).
Action: Make the subprogram to be RNDS, WNDS, RNPS, and WNPS or delete REPEATABLE.
Cause: REPEATABLE is declared at the subprogram's specification, but not at its body, or vice versa.
Action: Make sure both specification and body are declared as REPEATABLE.
Cause: The nested subprogram of a REPEATABLE subprogram is not declared as REPEATABLE. If a subprogram is REPEATABLE, all of its nested subprograms has to be declared as REPEATABLE.
Action: Declared the nested subprogram as REPEATABLE.
Cause: An exception appears in two different WHEN clauses (that is, two different exception handlers) in the exception-handling part of a PL/SQL block or subprogram.
Action: Remove one of the references to the exception.
Cause: The EXCEPTION_INIT pragma was used and different exceptions were initialized to the same Oracle error number. Then, they were referenced in different exception handlers within the same exception-handling part. Such references conflict.
Action: Remove one of the exceptions or initialize it to a different Oracle error number.
Cause: An identifier not declared as an exception appears in an exception handler WHEN clause. Only the name of an exception is valid in a WHEN clause.
Action: Check the spelling of the exception name and make sure the exception was declared properly.
Cause: In a SELECT statement, the select list was enclosed in parentheses, as in:
SELECT (deptno, dname, loc) FROM dept INTO ...
This breaks the rules of SQL syntax. Parentheses are not required because the keywords SELECT and FROM delimit the select list.
Action: Remove the parentheses enclosing the select list.
Cause: A variable was referenced in a way that is inconsistent with its datatype. For example, a scalar variable might have been mistakenly referenced as a record, as follows:
DECLARE CURSOR emp_cur IS SELECT empno, ename, sal FROM emp; emp_rec emp_cur%ROWTYPE; my_sal NUMBER(7,2); BEGIN ... total_sal := total_sal + my_sal.sal; -- invalid ...
Action: Check the spelling of the variable name. Make sure the variable was declared properly and that the declaration and reference are consistent regarding datatype.
Cause: The datatype specifier in a variable declaration does not designate a legal type. For example, the %TYPE attribute might not have been added to a declaration, as in:
DECLARE my_sal emp.sal%TYPE; my_ename emp.ename; -- missing %TYPE ...
When declaring a constant or variable, to provide the datatype of a column automatically, use the %TYPE attribute. Likewise, when declaring a record, to provide the datatypes of a row automatically, use the %ROWTYPE attribute.
Action: Make sure the datatype specifier designates a legal type. Remember to use the %TYPE and %ROWTYPE attributes when necessary.
Cause: In a query, a select-list item refers to a table in the FROM clause but not to a database column.
Action: Check the spelling of the column names, make sure each column in the select list refers to a table in the FROM clause, then re-execute the query.
Cause: A constant, variable, function call, or incomplete statement was used where a statement was expected. For example, instead of calling a function from an expression, it might have been called as a statement (as if it were a procedure).
Action: Check the statement, making sure that its commands, identifiers, operators, delimiters, and terminator form a complete and valid PL/SQL statement.
Cause: A constant or variable was used where a numeric literal is required. For example, the code might look like:
my_ename VARCHAR2(max_len);
instead of
my_ename VARCHAR2(15);
When specifying the maximum length of a VARCHAR2 variable, an integer literal must be used.
Action: Replace the identifier with a numeric literal.
Cause: If a package specification p declares a function f, that function may not be used in any variable declarations in that same package specification. This is because of a circular instantiation problem: in order to fully instantiate the package specification, the variable must be initialized. To initialize the variable, the function body code in the package body must be executed. That requires that the package body be instantiated. However, the package body cannot be instantiated until the package specification is fully instantiated.
Action: Remove the reference to the function from the variable initialization. A technique which often works is to move the variable initialization from the variable declaration (in the package specification) to the package body initialization block.
Cause: A reference to a server-side object (for example, a table column) or function (a group function such as SUM, AVG, MIN, MAX, ... ) was found in a context where only PL/SQL objects may be present, such as within the parameter list of a local function or as the index of a (local) PL/SQL table.
Action: Rewrite the offending statement; or, if a local function call is the problem context, make the function non-local (either packaged or top-level).
Cause: The INTO list of a SELECT or FETCH specified more than a single record- type target and the column types required coercion into a record to match the INTO list. This is type checked as correct, but not yet supported.
Action: Create a new record type to hold all of the column types or code the SELECT with an INTO target for every source column.
Cause: Type checking indicated the columns in a SELECT or FETCH were being collected into a record in the INTO list. The record was burst into its fields. There were not enough fields in the record for each of the columns.
Action: Change the number of columns or the record variable(s) in the INTO clause so that they match.
Cause: Type checking indicated the columns in a SELECT or FETCH were being collected into a record in the INTO list. The record was burst into its fields. There were not enough columns to fill all of the fields in the record.
Action: Change the number of columns or the record variable(s) in the INTO clause so that they match.
Cause: One of the following:
Action: Change the INTO list so that all variables have correct data types.
Cause: A variable or constant was declared to be of a type whose declaration appears later in the compilation unit; or, a type or subtype was declared in terms of another type whose declaration appears later in the compilation unit.
Action: Ensure that the type declaration precedes its use in the variable/ constant/type declaration.
Cause: A SELECT or FETCH may specify a column list to be coerced into a variable which is a collection of records. This is type checked as correct, but not yet supported because of the implicit layout change required.
Action: Express the SELECT with an object constructor around the columns and use a collection of objects as an INTO variable. Or, build the collection one row at a time coercing each row into a record which happens to be an element. Or, use a record of collections.
Cause: An operator binding function cannot be found in the specified scope.
Action: Provide the correct number and types of parameters for the operator binding function. Or, specify the correct names for schema, package, or type containing the operator binding function.
Cause: In a function body, a RETURN statement was used that contains no expression. In procedures, a RETURN statement contains no expression because the statement returns control to the caller. However, in functions, a RETURN statement must contain an expression because its value is assigned to the function identifier.
Action: Add an expression to the RETURN statement.
Cause: In a declaration, the datatype NUMBER_BASE (for example) was mistakenly specified. The datatypes CHAR_BASE, DATE_BASE, MLSLABEL_BASE, and NUMBER_BASE are for internal use only.
Action: Specify (for example) the datatype NUMBER instead of NUMBER_BASE.
Cause: An attempt was made to define a type other than TABLE or RECORD, but these are the only user- defined types allowed in this release of PL/SQL. For example, the following type definition is illegal:
TYPE Byte IS INTEGER(2); -- illegal
Action: Remove the type definition, or revise it to specify a TABLE or RECORD type.
Cause: An attempt was made to define a constrained subtype, but only unconstrained subtypes are allowed in this release of PL/SQL. For example, the following type definition is illegal:
SUBTYPE Acronym IS VARCHAR2(5); -- illegal
Action: Remove the illegal type constraint.
Cause: In a TABLE type definition, a nested record type was specified as the element type. This is not allowed. All fields in the record must be scalars.
Action: Remove the TABLE type definition, or replace the nested record type with a simple record type.
Cause: A datatype specifier was used instead of an expression in the RETURN statement of a user-defined function, as shown in the example below. Do not confuse the RETURN statement, which sets the function identifier to the result value, with the RETURN clause, which specifies the datatype of the result value.
FUNCTION credit-rating (acct_no NUMBER) RETURN BOOLEAN IS BEGIN ... RETURN NUMBER; -- should be an expression END;
Action: Replace the datatype specifier in the RETURN statement with an appropriate expression.
Cause: Illegal syntax was used to call a parameter-less function that returns a record or a PL/SQL table of records. When calling a function that takes parameters and returns a record, you use the following syntax to reference fields in the record:
function_name(parameters).field_name
However, you cannot use the syntax above to call a parameter-less function because PL/SQL does not allow empty parameter lists. That is, the following syntax is illegal:
function_name().field_name -- illegal; empty parameter list
The empty parameter list cannot be dropped because the following syntax is also illegal:
function_name.field_name -- illegal; no parameter list
Action: Declare a local record or PL/SQL table of records to which you can assign the function result, then reference its fields directly.
Cause: When a FLOAT variable was declared, its precision and scale were specified, as shown in the following example:
DECLARE Salary FLOAT(7,2);
However, a scale for FLOAT variables cannot be specified; only a precision can be specified, as in:
salary FLOAT(7);
Action: Remove the scale specifier from the declaration, or declare a NUMBER variable instead.
Cause: In a RECORD definition, one of the fields was declared as a PL/SQL table of records. This is not allowed. A record can be the component of another record (that is, records can be nested), but a PL/SQL table of records cannot be the component of a record.
Action: Remove the field declaration, or revise it to specify a simple record type.
Cause: An attempt was made to reference a remote packaged variable or cursor. This is not allowed. Instead, add to the remote package a function that returns the value of the variable or cursor.
Action: Remove the illegal reference.
Cause: In a SQL statement, a PL/SQL function was called that has a return type that cannot be handled by SQL. For example, type BOOLIAN, records and indexed-tables are not supported by SQL and functions returning such values cannot be called from SQL.
Action: Change the return type and retry the operation.
Cause: This INSERT statement provides REF INTO clause, which is only legal when the table specified in the INTO clause is an object table.
Action: Remove REF INTO clause and retry the operation.
Cause: In INSERT statement with REF INTO clause, the type of the data item must be REF to the type of the table used in INTO clause.
Action: Use variable or column of appropriate type.
Cause: In the INSERT statement operating on typed tables (tables of objects), the type of a non-aggregate value did not match the object type of the table.
Action: Provide a value of appropriate type.
Cause: In an INSERT statement with subquery, at lease one of the elements of the select list was not type-compatible with the corresponding column of the table in the INTO clause. This error indicates that the subquery should be rewritten to match the structure of the target table.
Action: Provide a select list element of compatible type.
Cause: A VALUES clause was entered without a list of SQL data items in parentheses. In all INSERT statements with an explicit column list, the VALUES clause must contain a list of SQL data items in parentheses. For example:
INSERT INTO my_tab (a,b,c) VALUES (1,2,my_variable);
Action: Rewrite the statement to include a list of SQL data items in parentheses.
Cause: In an INSERT statement with typed tables, an aggregate was used when an object type item was expected.
Action: Replace the aggregate with an object constructor or other object type expression.
Cause: A MAP member function was declared with a parameter. Map member functions can have only one parameter: the default SELF parameter. Map methods must be declared without any parameters. The compiler adds the SELF parameter.
Action: Remove the parameter from the map member function.
Cause: An order member function was declared without the user-specified parameter. Order member functions have two parameters, one is the default SELF parameter which is added by the compiler. the second parameter is added by the user and must declare an order method which must be the same type as the containing object type.
Action: Check and correct the way the parameter is specified.
Cause: The MAP member function was written such that it returns something other than a scalar type.
Action: Rewrite the MAP function such that it returns a scalar type.
Cause: An order member function was written such that it returns something other than an integer type.
Action: Rewrite the ORDER method such that it returns an integer type.
Cause: An order member function was declared without the user- specified parameter. Order member functions have two parameters, one is the default SELF parameter which is added by the compiler. the second parameter is added by the user and must declare an order method which must be the same type as the containing object type.
Action: Check and correct the way the parameter is specified.
Cause: A map or order function was not provided for a relational comparison. Only equality comparisons may be used when a map or order function is not supplied.
Action: Supply either a map or order function for the object. Otherwise change the program to use only equality comparisons.
Cause: Within stand alone PL/SQL, an attempt was made to compare objects without a map or order function.
Action: Provide a map or order function and retry the operation.
Cause: Either a PRAGMA RESTRICT_REFERENCES was not specified or it was specified without one of the following: WNDS, WNPS, RNPS, or RNDS.
Action: Add or correct the PRAGMA and retry the operation.
Cause: A parameter was declared to an ORDER function to have OUT or IN OUT mode.
Action: Correct the parameter to use IN mode only.
Cause: In an INSERT statement, an attempt was made to use a column name that is not an identifier. In any INSERT statement with explicit column list a column name must be a simple identifier.
Action: Rewrite the INSERT statement, using a simple identifier for the column name.
Cause: An attempt was made to use an invalid type for an object type attribute.
Action: Use only supported types for the object type attribute.
Cause: An attempt was made to use an unsupported type in a VARRAY or TABLE type.
Action: Use only supported types in a VARRAY or TABLE type.
Cause: The target of a REF can only be a complete or an incomplete object type.
Action: If a REF is to be used, change the type; otherwise, remove the REF.
Cause: An attempt was made to create a table of a type which cannot be queried. Tables of such types are not supported.
Action: Create an object type containing the non-queryable type. Then create a table of the object type.
Cause: An attempt was made to do one of the following: define a table type which contained nested collection types. or define an object table that has (perhaps nested) another table type or VARRAY type.
Action: Check the table definitions to be sure that they do not contain nested tables or VARRAYs.
Cause: An attempt was made to do one of the following: define a VARRAY type containing a nested collection type or LOB or define a VARRAY type of an object type that has a nested attribute which is one of NESTED TABLE, VARRAY or LOB type.
Action: Check the VARRAY definitions to be sure that they do not contain nested collection types, LOBs or nested attributes.
Cause: The expression of the form REFVAR.FIELD was entered. This is not supported in this version of PL/SQL.
Action: This navigation is only supported for database objects. The REF variable can be used to insert into a database column of the same type, or select from it. However, you will not be able to navigate through it. Remove the navigation.
Cause: A VARRAY type was declared with a non-positive limit, for example VARRAY(0).
Action: Declare the VARRAY with a positive limit and retry the operation.
Cause: The specified subprogram is declared in an object type's specification, but is not defined in the object type body.
Action: Define the subprogram in the object type body, or remove the declaration from the specification.
Cause: The specified subprogram is declared in an object type's body, but is not defined in the object type's specification. Notice cursor bodies can exist without a specification.
Action: Define the subprogram in the object type's specification, or remove the declaration from the body.
Cause: An object was declared inside a local scope (Function, Procedure, or anonymous block), or in a package scope which is not supported.
Action: Declare the type in a global or data base scope.
Cause: A length or size that is too large was specified for a data item.
Action: Specify a smaller value and retry the operation.
Cause: A server type is being created with an embedded CLOB or NCLOB attribute which uses a varying-width character set.
Action: Use VARCHAR2 instead of CLOB, or use a fixed-width character set.
Cause: An attempt was made to define a PL/SQL table that contained a (possibly deeply) nested table type or VARRAY. Nested collection types are not supported.
Action: Remove the nested table type or VARRAY from the table. Then retry the operation.
Cause: SELF was declared as a REF parameter to a member function or procedure. SELF is not supported as a REF.
Action: Redeclare SELF as a value parameter.
Cause: A name of an operator is used as a qualifier or appears outside from SQL context.
Action: Eliminate the use of operator name as a qualifier or place it in a SQL clause.
Cause: A character set specification was made on a type that does not require one.
Action: Remove the character set specification, or change the type.
Cause: The character set ANY_CS was specified when it is not allowed.
Action: Change or remove the character set specification.
Cause: ANY_CS or %CHARSET was used to reference an ANY_CS parameter which is not a CHAR, VARCHAR2, or CLOB type. ANY_CS or %CHARSET is not allowed for fields of a record, object, elements of a collection, and so on.
Action: Change or remove the character set specification.
Cause: An unrecognized name appears in a character set specification.
Action: Change or remove the character set specification.
Cause: A redundant or conflicting character set was specified.
Action: Remove the CHARACTER SET specification, or change the character set specified.
Cause: A default expression has been specified for a SQL operator.
Action: Eliminate the default expression from the operator.
Cause: An expression was used that has the wrong character set for this context.
Action: Adjust the expression, using TRANSLATE(... USING ...) or by rethinking the logic.
Cause: An expression was used that contains an incorrect character set. The actual argument has a character set conflict. If a default argument value is being used, it might be in conflict with some actual argument that must have the same character set.
Action: Adjust the expression, using TRANSLATE(... USING ...) or change the character set.
Cause: The current function was defined to return something other than a data type.
Action: Make sure the function is returning a data type.
Cause: CAST (in PL/SQL) was used outside of a DML scope.
Action: Do not use CAST outside DML statements.
Cause: Use of lob argument in call to remote server.
Action: Do not use LOBs in call to remote server.
Cause: Incomplete library units that are potential targets of REF dependencies must be completed so that they continue to remain potential targets of REF dependencies (since there might be library units with REF dependencies on this library unit). Potential REF targets include complete and incomplete object types. This error occurred because an attempt was made to complete a potential REF target as something other than a potential REF target.
Action: Use another name for this library unit, or drop the original incomplete library unit.
Cause: A LOB, date, boolean, ROWID, or MLSLABEL type was constrained with a length specification. For example:
X BLOB(5);
Action: Remove the constraint.
Cause: An attempt was made to pass NULL to a NOT NULL constrained parameter.
Action: Pass a NOT NULL expression instead.
Cause: An attempt was made to use TABLE expression, from a non-nested table.
Action: Pass the correct parameter.
Cause: A constant literal is either too large or too small to fit in an Oracle number.
Action: Change the value of the literal.
Cause: Number of columns in the collection returned by MULTISET and the number of columns for the CAST type do not match. For example:
create type tab_obj as object (n number); create type tab1 as table of tab_obj; create table tab2 (col1 number, col2 number); select CAST(MULTISET(select col1, col2 from tab2) as tab1) from tab2;
Action: Make sure the number of columns matches.
Cause: Remote link is used with on client side.
Action: Create a dummy function to call symbolic link on server side or wait till it is implemented.
Cause: "number" types cannot have a range. user-defined aggregate types and integer types cannot have scale or precision specified. Example: "x number(5,3)" is acceptable but "x number range 1..10" is not.
Action: Remove the constraint.
Cause: A variable declared with %TYPE or %ROWTYPE cannot have a scale, precision, or range constraint. Example: x y%TYPE(10) is not acceptable. It is legal to add "NOT NULL" or non-conflicting CHARACTER SET constraints to anchored type declarations.
Action: Remove the constraint or use a type name instead of an anchored type.
Cause: An attempt was made to declare an object type to have a supertype, but the specified supertype was not itself an object type. Object types can inherit only from other object types.
Action: Remove the supertype specification, or change it to refer to an object type. If the declaration appears correct, make sure the supertype has compiled correctly.
Cause: An attempt was made to declare an opaque type with a supertype.
Action: Remove the supertype specification.
Cause: An attempt was made to declare an attribute in an opaque type.
Action: Remove the attribute declaration.
Cause: An attempt was made to declare a fixed-length opaque type without specifying an explicit size.
Action: Specify an explicit size, or change the declaration to be varying-length.
Cause: An attempt was made to specify an explicit size for an opaque type, but the specified size was out of the valid range.
Action: Change the explicit size to be between 1 and 4000 bytes, or change the declaration to be varying-length without an explicit size.
Cause: An attempt was made to name a support library for an opaque type, but the name refers to an object that is not a library.
Action: Change the declaration to name a valid library.
Cause: An attempt was made to name a parameter SELF when declaring a static method. SELF is reserved as a parameter name in methods, to hold the object instance on which the method is applied. A parameter named SELF is not allowed in static methods because static methods do not apply to a particular object instance.
Action: Change the name of the parameter in the declaration.
Cause: An attempt was made to use an object instance value, not a type name, as the qualifier of the name of a method invocation, but only a type name can be used as the qualifier when calling a static method.
Action: Use the type name, not an object instance value, to qualify the name of a method if the method is static.
Cause: An attempt was made to use the name of an object instance attribute in the body of a static method, or in an initialization default value on another attribute. If the instance attribute is not qualified with a particular object instance that supplies a value, the instance attribute can be named only when it is inside a member method.
Action: Qualify the attribute reference with the name of an object value, or change the containing method to a member method rather than a static method.
Cause: Table of non-ADT type is defined.
Action: Table of type should be of ADT type only.
Cause: An attempt was made to create a subtype UNDER a FINAL type.
Action: Avoid deriving a subtype from this FINAL type.
Cause: One of the following features was used in a wrong context:
These listed features can only be used in server-side programs but not client-side programs.
Action: Remove it or define a server-side subprogram to do the work and call the subprogram from the client.
Cause: A typed-table of a non-object type was defined.
Action: Replace the non-object type with an object-type.
Cause: The default value initialization of a parameter in a subprogram body contained in a package body did not match that of the corresponding subprogram specification in the corresponding package specification.
Action: Change the default initialization of the parameter in body to match that of the specification.
Cause: The SELF parameter was declared as an OUT parameter.
Action: Declare the SELF parameter as either an IN or an IN OUT parameter.
Cause: The TABLE operator was used as an argument to a SELECT or CAST statement.
Action: Remove the TABLE operator from the SELECT list items or from argument of CAST operator. Make sure that the TABLE operator appears only in the FROM clause.
Cause: This exception is raised for the following errors:
Action: Use expression with correct datatype in the INTO clause.
Cause: An object is compared with NULL using a relational operator.
Action: Use IS NULL comparison operator.
Cause: The percentage number is not in the range [0.000001,100).
Action: Use a percentage number in the range [0.000001,100).
Cause: SAMPLE applying to a remote object is not supported.
Action: Do not use SAMPLE with a remote object.
Cause: User attempted to use a partition-extended object name with an object which is not a table.
Action: Avoid using partition-extended name syntax with objects which are not tables.
Cause: User attempted to use the keywords CUBE or ROLLUP outside a GROUP BY clause.
Action: Avoid using the keywords CUBE and ROLLUP outside the GROUP BY clause.
Cause: User attempted to use the GROUPING function without GROUP BY CUBE or GROUP BY ROLLUP.
Action: Avoid using the GROUPING function without GROUP BY CUBE or GROUP BY ROLLUP.
Cause: One of the functions, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, was used in a WHERE or GROUP BY clause.
Action: Remove the group function from the WHERE or GROUP BY clause. The desired result may be achieved by including the function in a subquery or HAVING clause.
Cause: A set operator was used in a subquery that was the argument to a MULTISET operator.
Action: Rewrite the SQL statement so that it does not use a set operator in the subquery that is the argument to the MULTISET operator.
Cause: A query that contains a cursor subquery is being used from client side.
Action: Use this feature from server side only.
Cause: You were trying to create a SQLJ Object Type under a non-SQLJ Object Type.
Action: Recreate the type as a SQLJ Object Type.
Cause: You were trying to create a SQLJ Object Type with a different value of the USING clause from the USING clause specified in its supertype.
Action: Change the value of the USING clause to match the USING clause in its supertype.
Cause: You were trying to a SQLJ Object Type method that override an inherited method.
Action: Change the SQLJ Object Type definition by removing the OVERRIDING method.
Cause: You were trying to use ROW more than once in UPDATE statement.
Action: Remove all the additional ROW in set clause of UPDATE statement.
Cause: With ROW on LHS, only PL/SQL records or %ROWTYPE variables are allowed on right hand side.
Action: Make appropriate changes, so that right hande side should only have variables of record or %ROWTYPE.
Cause: Number of columns do not match with number of attributes on record type on rhs.
Action: Make sure that number of columns match with the number of attributes in record on rhs.
Cause: You are trying to use more than one value in VALUES clause or RETURNING INTO clause, when one of the value is of record type.
Action: Specify only one variable of record type, or don't use variables of record type.
Cause: An attempt was made to create a FINAL NOT INSTANTIABLE type. No useful operation can be performed with this type.
Action: Avoid using the keywords FINAL and NOT INSTANTIABLE together when creating types.
Cause: In a simple CASE statement or expression, the CASE operand and WHEN operands do not match in datatype, and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the CASE operand and WHEN operands so that their datatypes match. Consider using datatype conversion functions in either the CASE operand or WHEN operands.
Cause: In a CASE expression, the result expressions do not match in datatype, and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the result expressions so that their datatypes match. Consider using datatype conversion functions in the result expressions.
Cause: All the results in the CASE expression are the literal NULL.
Action: Change at least one result in the CASE expression to be non-NULL.
Cause: In a NULLIF expression, the two operands do not match in datatype, and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the operands so that their datatypes match. Consider using datatype conversion functions in the operands.
Cause: The first operand in the NULLIF expression is the literal NULL.
Action: Change the first operand in the NULLIF expression to be non-NULL.
Cause: In a COALESCE expression, the operands do not match in datatype, and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the operands so that their datatypes match. Consider using datatype conversion functions in the operands.
Cause: All the operands in the COALESCE expression are the literal NULL.
Action: Change at least one operand in the COALESCE expression to be non-NULL.
Cause: The range constraints specified for a pls_integer or binary_integer declaration did not fall between -2147483647 and 2147483647.
Action: Use range constraints between -2147483647 and 2147483647.
Cause: The precision specified for a FLOAT, REAL, or DOUBLE PRECISION did not fall between 1 and 126.
Action: Use precision constraints between 1 and 126.
Cause: USING clause cannot be used with functions that are not declared with AGGREGATE or PIPELINED properties.
Action: Define function body in PL/SQL or some other language (using external callout call specification).
Cause: A PARTITION/CLUSTER/ORDER-BY clause was used with an OUT or IN OUT ref-cursor parameter.
Action: Change the parameter mode to IN or use some other parameter with the PARTITION/CLUSTER/ORDER-BY clause.
Cause: A partitioning or clustering/ordering specification specified on an argument that is not one of the function arguments.
Action: Specify partitioning/clustering/ordering based on the function ref-cursor formal argument.
Cause: A partitioning or clustering/ordering specification specified on an argument that is not a strongly typed ref cursor.
Action: Specify partitioning/clustering/ordering based on the function ref-cursor formal argument.
Cause: A partitioning or clustering/ordering specification must use only valid record attributes.
Action: Specify partitioning/clustering/ordering based on valid record attributes.
Cause: A PIPE statement was used in a non-pipelined function.
Action: Use PIPE statements only in pipelined functions.
Cause: A pipelined function was specified with a non-collection return type.
Action: Specify a collection type as the pipelined function return type.
Cause: PARTITION-BY and CLUSTER/ORDER-BY clauses are specified on different function arguments.
Action: Use the same argument in both PARTITION-BY and CLUSTER/ORDER-BY clauses.
Cause: An attempt was made to provide an implementation for a NOT INSTANTIABLE method.
Action: Remove the implementation for the NOT INSTANTIABLE method.
Cause: A RETURN statement in a pipelined function contains an expression, which is not allowed. Pipelined functions must send back values to the caller by using the PIPE statement.
Action: Remove the expression from the RETURN statement and use a PIPE statement to return values. Alternatively, convert the function into a non-pipelined function.
Cause: The type being created contains NOT INSTANTIABLE methods, either declared or inherited. The type must be explicitly declared NOT INSTANTIABLE.
Action: Explicitly specify the NOT INSTANTIABLE keyword or provide implementations for all the NOT INSTANTIABLE methods.
Cause: The method specification contains the OVERRIDING keyword but the method does not override a method in the ancestor types.
Action: Check the method specification.
Cause: An attempt was made to provide an overriding method specification but the OVERRIDING keyword was not specified.
Action: Modify the method specification and specify the OVERRIDING keyword.
Cause: An attempt was made to override a FINAL MEMBER method or hide a FINAL STATIC method.
Action: Avoid overriding FINAL MEMBER methods or hiding FINAL STATIC methods.
Cause: The MAP method does override the inherited one.
Action: Make the specifications match.
Cause: NCHAR/NVARCHAR can only be codepoint length sematics. BYTE qualifiers used with NCHAR are illegal:
nc NCHAR(7 BYTE);
name NVARCHAR2(10 byte);
Action: Remove BYTE qualifier to make it as codepoint length semantics.
Cause: A pipelined function was invoked from a PL/SQL expression.
Action: Use the pipelined function in the FROM clause of a SQL query.
Cause: A SELECT statement in a cursor definition, an OPEN statement, or a cursor FOR loop has an erroneous INTO clause.
Action: Remove the INTO clause.
Cause: A locally-defined (that is not schema level) collection type was used in a SQL statement. The type must be defined in a schema to be accepted in a SQL statement.
Action: Define the collection type in your schema, not inside a PL/SQL subprogram.
Cause: Items of type boolean, table of foo index by binary_integer, record and static cursors cannot be used in SQL statements.
Action: Remove expressions of these types from the SQL statement.
Cause: In a CASE expression, the result expressions do not match in character set, and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the result expressions so that their character sets match.
Cause: In a COALESCE expression, the operands do not match in character set and it is unclear which implicit conversion is required to correct the mismatch.
Action: Change the operands so that their character sets match.
Cause: An attempt was made to define a MAP or ORDER method in a subtype.
Action: Define the MAP or ORDER method in the root of the subtype hierarchy. You can override MAP methods in subtypes.
Cause: While creating a subtype, some attribute name conflicted with a method name.
Action: Use a different name for attribute or method in the type being created.
Cause: User tried to complete a type, Tsub as a subtype of another type, Tsuper where Tsuper contained an attribute of type Tsub or of type REF to Tsub. This is an implementation restriction.
Action: Change the design of code to not use any type with attribute whose type is of subtype or REF to subtype.
Cause: User tried to override the method more than once in the same subtype.
Action: Either make this an extra overload by removing the OVERRIDING keyword where the method signature is different. The argument types should be reconsidered otherwise.
Cause: An attempt was made to use an invalid type for either PARTITION BY, ORDER BY, or CLUSTER BY clauses.
Action: Use legal, allowed types.
Cause: An attempt was made to use some other database object, such as a package, to define an implementation type of an aggregate/table function.
Action: Use a valid user-defined type as an implementation type.
Cause: An attempt was made to create an aggregate function with either 0 or more than one arguments.
Action: Do not create aggregate functions with 0 or more than one arguments.
Cause: An attempt was made to use a table/aggregate function in PL/SQL scope.
Action: Do not use table/aggregate functions in PL/SQL scope.
Cause: An attempt was made to use either PARTITION BY, ORDER BY, or CLUSTER BY clauses without parallel enable clause in table function.
Action: Specify parallel enable also when using PARTITION BY, CLUSTER BY, or ORDER BY clauses.
Cause: An attempt was made to declare a procedure to be a pipelined function.
Action: Declare a function instead of a procedure to be a pipelined function.
Cause: An EXTRACT operation specified a field that was not present. For example, an attempt was made to extract the YEAR field from a TIME variable.
Action: Specify a legal field in the call to EXTRACT.
Cause: Variable or expression of associative array type was used as bulk SQL bind or define.
Action: Use collection types supported by bulk SQL.
Cause: The name of the object type constructor does not match the type name.
Action: Change the name of the constructor method to match the type name.
Cause: The return clause of the constructor method did not specify SELF AS RESULT.
Action: Change the return clause to include RETURN SELF AS RESULT.
Cause: The mode of the SELF parameter of the constructor was not IN OUT.
Action: Change the mode of the SELF parameter to be IN OUT.
Cause: An attempt was made to include an expression in the RETURN statement of a CONSTRUCTOR body.
Action: Remove the expression from the RETURN statement.
Cause: An attempt was made to create a non-external object, whose attributes are mapped externally.
Action: Create the object as an external object.
Cause: The NEW keyword is not followed by a call to a constructor method.
Action: Remove the NEW keyword, or make sure the expression following it is a constructor call.
Cause: An attempt was made to specify both ORDER BY and CLUSTER BY for the same table function.
Action: Specify either ORDER BY or CLUSTER BY for a table function, but not both.
Cause: An EXCEPTION_INIT pragma was not declared in the same block as its exception. They must be declared in the proper order in the same block, with the pragma declaration following the exception declaration.
Action: Place the EXCEPTION_INIT pragma directly after the exception declaration referenced by the pragma.
Cause: The error number passed to an EXCEPTION_INIT pragma was out of range. The error number must be in the range -9999 .. -1 (excluding -100) for Oracle errors or in the range -20000 .. -20999 for user-defined errors.
Action: Use a valid error number.
Cause: The second argument passed to an EXCEPTION_INIT pragma was something other than a numeric literal (a variable, for example). The second argument must be a numeric literal in the range -9999 .. -1 (excluding -100) for Oracle errors or in the range -20000 .. -20999 for user-defined errors.
Action: Replace the second argument with a valid error number.
Cause: Two or more actual parameters in a subprogram call refer to the same formal parameter.
Action: Remove the duplicate actual parameter.
Cause: The EXCEPTION_NAME parameter passed to an EXCEPTION_INIT pragma is misspelled or does not refer to a legally declared exception. Or, the pragma is misplaced; it must appear in the same declarative section, somewhere after the exception declaration.
Action: Check the spelling of the EXCEPTION_NAME parameter. Then, check the exception declaration, making sure the exception name and the keyword EXCEPTION are spelled correctly. Also make sure the pragma appears in the same declarative section somewhere after the exception declaration.
Cause: An exception was referred to in an expression. Exceptions have names but not values and therefore cannot contribute values to an expression. For example, the following RETURN statement is illegal:
FUNCTION credit_limit (cust_no INTEGER) RETURN NUMBER IS limit NUMBER; over_limit EXCEPTION; ... BEGIN ... RETURN over_limit; -- illegal END;
Action: Check the spelling of the identifiers in the expression, then rewrite the expression so that it does not refer to an exception.
Cause: An exception name was mistakenly used to qualify a reference to a component. For example, when dot notation was used to specify fields within a record, an exception name might have been coded instead of the record name.
Action: Rewrite the component reference using a valid prefix (for example, the name of a package, record, or schema).
Cause: At run time, this is an internal error. At compile time, it indicates one of the following problems:
Action: Either report the internal error to Oracle Support Services or, depending on the problem, take one of the following actions:
Cause: The named pragma was not declared in a package specification, as required. For example, the pragma RESTRICT_REFERENCES must be declared in a package specification.
Action: Remove or relocate the misplaced pragma.
Cause: The named pragma was declared in a package specification but not in the corresponding package body or vice-versa, as well as the package body.
Action: Add the pragma to that part of the package declaration that does not have the pragma.
Cause: The PRAGMA was declared in an inappropriate context. This PRAGMA has to be declared in the declarative section of a procedure, a function, a top level anonymous block.
Action: Remove or relocate the misplaced pragma.
Cause: The PRAGMA was declared twice in the same block.
Action: Remove the duplicate declaration of the PRAGMA.
Cause: Only top level subprograms or subprogram declarations in PACKAGE or TYPE specifications are allowed to have the options DETERMINISTIC or PARALLEL_ENABLE. For example, this error would be raised if the options are used in PACKAGE or TYPE body.
Action: Remove the option.
Cause: An attempt was made to instantiate a type that is defined as NOT INSTANTIABLE.
Action: Avoid instantiating variables of this NOT INSTANTIABLE type; derive an INSTANTIABLE subtype from it and instantiate variables of the subtype.
Cause: The supertype's AUTHID is DEFINER, but the supertype and subtype are defined in different schemas. SQL statements executed in dynamically dispatched methods can potentially pick up different values from database tables in the two schemas.
Action: Create the subtype in the same schema as the supertype.
Cause: An attempt was made to drop a nonexistant method or attribute from the type being altered.
Action: Check the spelling of the attribute or method name. Make sure it is locally defined in the type and not inherited.
Cause: User attempted more than one of ADD, DROP, or MODIFY on an attribute in a single ALTER TYPE statement. User attempted more than one of ADD or DROP on a method in a single ALTER TYPE statement.
Action: Do only one ADD, DROP, or MODIFY for an attribute in a single ALTER TYPE statement. Do only one ADD or DROP for a method in a single ALTER TYPE statement.
Cause: The method to be dropped matches the name of some existing method but does not match it in signature.
Action: Make sure that a matching method signature is provided for dropping a method.
Cause: The type of the attribute to modify is not one of VARCHAR2, NUMBER, or RAW.
Action: Only VARCHAR2, NUMBER and RAW type attributes are allowed to be modified.
Cause: Modification to the attribute did not widen its constraints or attempted to change the type of the attribute. For NUMBER type attributes, scale and precision can be changed to allow increase in number of digits after and before the decimal point. For VARCHAR2 and RAW type attributes the size can be increased. Other type attributes are not allowed to be modified. Change of type of attribute is also not allowed.
Action: Change the constraint specification so as to widen the existing constraints.To narrow the constraints or to change the type of the attribute, you must drop the attribute and add it with new constraints or new type. In that case you must also take appropriate steps to preserve your existing data based on the type (if you want to preserve it).Typically this will involve backing up the data and restoring it after the ALTER TYPE.
Cause: The type has evolved. The version of the type used by the compiled code is not the same as the latest version of the type.
Action: Recompile the code to use the latest version of the type.
Cause: This compilation was aborted because the library unit that was compiled would have formed a non-REF mutually-dependent cycle with some other library units. This happens when an attempt is made to compile types that have attributes of other types that may participate in a cycle with this type.
create type t1; create type t2 (a t1); create type t1 (a t2);
Action: Break the cycle (possibly by adding a REF or by using another type).
Cause: The subtype's AUTHID must be the same as the supertype's AUTHID.
Action: Create the subtype with the AUTHID of its supertype or do not specify an AUTHID for the subtype.
Cause: The package body is being compiled to native code whereas the corresponding package specification was compiled to interpreted bytecode.
Action: Compile the package body to the same target (native code or interpreted bytecode) as its spec.
Cause: The package body is being compiled to interpreted bytecode whereas the corresponding package specification was compiled to native code.
Action: Compile the package body to the same target (native code or interpreted bytecode) as its spec.
Cause: In TREAT(expr AS type), type must be a supertype or subtype of expr.
Cause: A top level subprogram (function or procedure) cannot be created if the system package SYS_STUB_FOR_PURITY_ANALYSIS is missing in the Oracle database.
Action: Make sure the package is in the database, and retry the creation.
Cause: This is a generic internal error that might occur during compilation or execution. The first parameter is the internal error number.
Action: Report this error as a bug to Oracle Support Services.
Cause: At run time, the body of a program unit could not be found. This can happen for one of two reasons.
i := seq.nextval;
Action: If caused by the first reason, create a body for the object that is being referenced. If caused by the second reason, move the sequence into a SQL statement. For example, i := seq.nextval; can be replaced by:
select seq.nextval into temp from dual; i := temp;
Cause: A column in a database table belongs to a datatype that is not supported by the current release of PL/SQL.
Action: Remove the offending column from the table or copy the desired columns to another table.
Cause: A host variable that is protected from update was used in a context that allows an update.
Action: Check the context and change the use of the host variable, or assign the value of the host variable to a PL/SQL local variable, then use the local variable instead.
Cause: An attempt was made to operate on a database object without the required privilege. This error occurs, for example, if an attempt was made to UPDATE a table for which only SELECT privileges were granted.
Action: Ask the database administrator to perform the operation or to grant you the required privilege.
Cause: An invalid package specification or stored subprogram was referenced. A package specification or stored subprogram is invalid if its source code or any database object it references has been DROPped, REPLACEd, or ALTERed since it was last compiled.
Action: Find out what invalidated the package specification or stored subprogram, then make sure that Oracle can recompile it without errors.
Cause: The PL/SQL compiler cannot run properly because its operating environment is corrupted. For example, its error message file might be inaccessible.
Action: Check the PL/SQL operating environment, making sure that all files required by the compiler are accessible.
Cause: PL/SQL is unable to find and load a library unit that was previously available. This typically happens when you try to load a unit which references another library unit that is non-existent or invalid.
Action: Provide the needed library unit.
Cause: PL/SQL cannot understand the stored format of a library unit. It has been compiled or shrink-wrapped with a version of PL/SQL either too new or too old for this version to understand it.
Action: Recompile the library unit for this version of PL/SQL.
Cause: An object from the operator declaration is not declared, or it is declared but there is no sufficient privileges to access that object.
Action: Declare the object, or ask the database administrator to grant the privileges required to access the object.
Cause: An attempt was made to inherit from a type without the required UNDER privilege.
Cause: A PL/SQL program is being compiled natively, but the compiler parameter plsql_native_library_dir, which denotes the directory to store the native shared objects (DLL), is not set.
Action: The database administrator should set this parameter.
Cause: A PL/SQL program is being compiled natively, but the compiler parameter plsql_native_make_utility, which denotes the full path name of the make utility to make the native shared objects (DLL), is not set.
Action: The database administrator should set this parameter.
Cause: A PL/SQL program is being compiled natively, but the compiler parameter plsql_native_make_file_name, which denotes the full path name of the make file to make the native shared objects (DLL), is not set.
Action: The database administrator should set this parameter.
Cause: The native compilation of the PL/SQL program has failed because the native shared object (DLL) could not be created successfully.
Action: The database administrator should ensure that the parameters plsql_native_c_compiler and plsql_native_linker are set correctly.
Cause: Native compilation of PL/SQL programs is not supported on this platform.
Action: Compile the PL/SQL program to bytecode by setting the PLSQL_COMPILER_FLAGS initialization parameter to INTERPRETED.
Cause: An attempt was made to create a subtype UNDER a FINAL type.
SELECT ename_tab.COUNT INTO name_count WHERE ...
Action: Remove the incorrect reference from the SQL statement.
Cause: PL/SQL cannot understand something in an imported library unit. It uses some feature either too new or too old for this version to understand it.
Action: Recompile the imported library unit with this version of PL/SQL, to discover more precisely what feature is not supported.
Cause: Schema level user-defined types, or types which recursively use such types, cannot be used in RPCs. For example:
create type foo as object (...) create package my_pack is type my_tab is table of foo; -- on a remote server: x my_pack.my_tab@rpc; -- illegal attempt to use type my_tab
Action: Use only PL/SQL-defined types for RPC calls. It may be necessary to add extra code to element-wise copy top-level types into local types in order to move such data through an RPC.
Cause: The class name defined as part of the method signature does not match the one defined in the type header in the EXTERNAL NAME clause.
create type foo as object EXTERNAL NAME 'foo' LANGUAGE JAVA (... MEMBER FUNCTION my (number) return number EXTERNAL NAME 'bar.func(oracle.sql.NUMBER) return oracle.sql.NUMBER', ...);
Action: Either omit the class name from the method signature or use the one defined in the type header.
Cause: A cursor variable was entered in a record, object, or collection. This is not supported in this PL/SQL release.
Action: Rewrite your application using stand-alone cursor variables and/or parameters, or, for usage in records, consider a downgrade to PL/SQL 2.3.
Cause: An attempt was made to build a PL/SQL index table using cursor variables.
Action: Rewrite your application, using standalone cursor variables and procedure parameters, PL/SQL index tables of records and loops.
Cause: An attempt was made to perform a FETCH from a cursor variable.
Action: Consider an upgrade to PL/SQL 2.3, where this restriction is not present, or rewrite your application using OCI or precompiler for FETCH.
Cause: An attempt was made to pass a cursor variable to or from a remote subprogram, which is not allowed. For example, a remote procedure cannot be used to open a cursor variable because remote subprograms cannot return the values of cursor variables.
Action: Change the subprogram call to reference a local database.
Cause: An attempt was made to declare a cursor variable in a package specification, which is not allowed. Although REF CURSOR types can be defined in a PL/SQL block, subprogram, or package, cursor variables can be declared only in a block or subprogram.
Action: Move the cursor variable declaration into a PL/SQL block or subprogram.
Cause: An exception was raised for which no handler was found. If it cannot find a handler for a raised exception, PL/SQL returns an unhandled exception to the host environment. The number embedded in the message is an Oracle error code listed in this manual.
Action: Fix the condition that raised the exception, write an appropriate exception handler, or use the OTHERS handler. If there is an appropriate handler in the current block, the exception was raised in a declaration or exception handler, and therefore propagated immediately to the enclosing block.
Cause: A request from PL/SQL for more memory failed.
Action: Make sure that you are not referencing the wrong row in a PL/SQL table and that the program is not recursing too deeply.
Cause: Not available at this time.
Action: Do not use.
Cause: Use of '||' token in expressions is not in ANSI's grammar. This corresponds to the "binary_add_op"'s reduction to a "CAT_" in plsql.y.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Identifiers over 18 characters long are not allowed under the ANSI grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The use of quoted identifiers is a PL/SQL extension and is not allowed under the ANSI grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: C-style comments (introduced with the '/' and '*' characters) are not part of the ANSI grammar. To conform with ANSI, comments must be preceded by the ANSI comment introducer, '--' , and must not contain a newline.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: ANSI identifiers can only consist of letters, digits, and the underscore character. PL/SQL allows "#" and "$" in identifiers, but these are not ANSI.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Under ANSI's grammar, numeric data represented in exponent notation must use an uppercase "E". Example: 3.45E-6 is ANSI, but 3.45e-6 is not.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: An identifier has been found that is considered a keyword in ANSI's grammar but not in PL/SQL's.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: One of the identifiers "CHAR", "CHARACTER", or "INTEGER" (all ANSI keywords) has been redefined by the programmer.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Use of '&' token in expressions is not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992for details.
Cause: Use of 'PRIOR_' token in expressions is not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Use of 'MOD' token in expressions is not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Use of 'REM' token in expressions is not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Use of 'EXP' token in expressions is not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Use of NULL an expression not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Use of qualified expression here is not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Use of aggregate value here is not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Use of id (value...) here is not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Use of %attribute value here is not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Subquery cannot include set operators in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Subquery must have either '*' or exactly one column in its select list according to ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: INTERSECT and MINUS set operators are not ANSI.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: FOR UPDATE clause is not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Aliases are not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Subquery on right-hand-side in set clause is not in ANSI's grammar.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: ANSI specifies an ordering to clauses in a table-expression which PL/SQL does not. The ordering according to ANSI must be:
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: ANSI does not allow connect-by clauses.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: This value_expression contains a nonconforming data type. Section 4.2 of X3H2, "Data types" states that: "A non-null value is either a character string or a number." Many built-in functions in STANDARD will be flagged with this warning by means of a pragma.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The data types of these value_expressions must be comparable. Section 4.2 of X3H2, "Data types", states that: "A character string and a number are not comparable values." Many built-in functions in STANDARD will be flagged with this warning by means of a pragma. This warning will be place on data-type coercion functions, for example, TO_CHAR(number), that are inserted in Oracle SQL to allow non-conforming data type combinations.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: This function is not part of the ANSI standard. This warning is used to flag such functions as "POWER" that are not mentioned in the ANSI specifications.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: This procedure is not part of the ANSI standard. This warning is used to flag such procedures as "STOP" that are not mentioned in the ANSI specifications.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: No operator may be used with values of data type "character string". Section 5.9, "value_expression", syntax rule 3, of X3H2 states: "If the data type of a primary is character string, then the value_expression shall not include any operators." This warning is placed on many functions in STDBASE that take CHAR arguments.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The predicates "IS NULL" and "IS NOT NULL" are defined only for column specifications, not for any other expressions. Section 5.15, "null_predicate", indicates that in the predicate "x IS NULL", x must be a column, and may not be any other kind of expression.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Name length cannot exceed 3.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The set clause in an UPDATE statement requires a column name of length 1.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Cannot access object on a remote host.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: STDDEV is not a standard set function.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: VARIANCE is not a standard set function.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Null strings are not allowed.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The value list of the IN predicate, if not a subquery, must contain only value_specifications (that is, literals and PL/SQL or embedded variables).
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The first argument of the like-predicate must be a column of type character string.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: ANSI standard does not permit records.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The escape character in the like-predicate must be a literal or a variable of type character.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The keyword DISTINCT must be present in a COUNT(DISTINCT sim_expr).
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: In a set_function_specification, if DISTINCT is present, the expression must be a column_specification.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Use <> instead of != or ~=.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Due to the combining of comparison_predicate and quantified_predicate, we may have something like sim_expr = ANY_ sim_expr, which is nonetheless accepted by kernel.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Unions are not allowed in the definition of a view.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: CREATE TABLE allows only the definition of the structure of a table. None of the .SPACE__name. .PCTFREE__numeric_literal. .cluster. is ANSI.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Constraint name and constraint status are non-ANSI.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The expression in a SET clause must not include a set function.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: CREATE TABLE allows only the definition of the structure of a table. Use of AS phase in CREATE TABLE statement is non-ANSI.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: ALTER is non-ANSI.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: CREATE INDEX is non-ANSI.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: DROP is non-ANSI.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Illegal syntax in ROLLBACK WORK statement.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Illegal syntax in COMMIT WORK statement.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: Cursor name in CLOSE statement must be of length 1.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The expressions in a sort clause may only be column specifications or unsigned integers, followed by optional ASC or DESC.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The table specified by a cursor is not updatable if the cursor specification contains a UNION or ORDER_BY.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: In a positioned DELETE or UPDATE statement, the table deleted from or updated must be identified in the specification of the cursor.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: In a searched DELETE, UPDATE or INSERT statement, the table affected must not appear in a FROM clause in any of the subqueries in the search condition.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: In an INSERT statement, the insert value list, if specified with a value list rather than a subquery, must be value specifications, that is, no compound expressions or column references.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: In a positioned DELETE or UPDATE statement, the cursor name must be of length 1.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: A SELECT statement may not contain ORDER_BY, HAVING, or GROUP_BY clause.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: ANSI does not allow bind variables as INDICATORS.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: The constraints on these types do not match.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: ANSI expects a column name not a literal value here.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: ANSI does not accept SAVEPOINTs.
Action: Refer to ANSI document ANSI X3.135-1992 for details.
Cause: There was a failure while converting a character string into a numeric value.
Action: Verify that all character strings assigned to number variables have valid numeric value interpretations.
Cause: PL/SQL was unable to transfer data into a host array because the array is too small.
Action: Increase the size of the host array.
Cause: There was a failure while converting a hexadecimal string to a raw.
Action: Verify that the hexadecimal variable to be converted contains a valid hexadecimal value.
Cause: SQLERRM() was called with a non-integer value.
Action: Make sure SQLERRM() is called with an integer value.
Cause: An attempt was made to index into a PL/SQL index table with a NULL key value.
Action: Make sure key values used to index into PL/SQL index tables are non-NULL.
Cause: An error was encountered while moving a character string from a source to a destination. This error occurs if, for example, an attempt is made to move a character string of 10 characters into a 1 character buffer. The cause of this error may not always be obvious. For example, the following will result in this error:
a varchar2(1); b number; b := 10; a := b;
An error results because an implicit conversion causes the number 10 to become the character string '10', which does not fit in the character buffer of 1 allocated for the variable a.
Action: First, look for character string assignment statements where the buffer size is mismatched. If there are none found, then consider the implicit conversion case illustrated in the example above.
Cause: The length of a raw variable being copied or assigned was too long to fit into its destination.
Action: Make sure the raw variable length is correct.
Cause: The number begin copied or assigned had too many digits to the left of the decimal and did not fit into its destination. In other words, there is a number precision mismatch. This error may also occur if, for example, an attempt is made to assign a character string to a number, as demonstrated below.
a varchar2(4); b number(1,1); a := '10.1'; b := a;
Action: First, check explicit number precision value mismatches. If none are found, then consider implicit conversions of other types to numbers, including conversions performed during binds.
Cause: PL/SQL determined that a server side DML with a RETURNING INTO clause does not use a buffer that is large enough to hold the data being returned.
Action: Increase the size of the RETURNING INTO clause buffer.
Cause: An attempt was made to assign or copy a supertype instance to a container (destination) that can only hold a subtype instance.
Action: Make sure the runtime type of the source of the assignment or copy is the same type as the destination or is a subtype of the destination type.
Cause: NLS parameters affecting comparison of keys have been altered dynamically. The associative array's index is invalid.
Action: Avoid using associative arrays with string keys in applications which require frequent changes of NLS_COMP or NLS_SORT.
Cause: The key value of an element being inserted into an associative array violates the key type constraints.
Action: Relax the constraints if the key value is meant to be acceptable. Otherwise, apply a valid key value when inserting an element.
Cause: The run-time type of the instance is not the declared type. That is, the return value of a user defined constructor is wrong.
Action: Return the correct type.
Cause: The run-time type of the instance is the not declared type or one of its subtypes. That is, TREAT(supertype AS subtype) fails.
Action: Catch the exception.
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|
