pls 00363 expression cannot be used as an assignment target

WordPress.org
Documentation
Learn WordPress

Browse through various articles and courses for Free at DeveloperPublish.com

pls 00363 expression cannot be used as an assignment target

What is My IP Address?

Oracle Error PLS-00363: expression ‘string’ cannot be used as an assignment target

Oracle error message.

PLS-00363: expression ‘string’ cannot be used as an assignment target

Reason for the Error

Oracle Error PLS-00363: expression’string’ cannot be used as an assignment target is a common error that can occur when working with PL/SQL code in Oracle. This error usually occurs when you try to use an expression as the target of an assignment statement and the expression cannot be assigned a value.

For example,

The PL/SQL block in this example attempts to assign the value of num1 + num2 to the variable str. However, num1 + num2 is a numeric expression that cannot be assigned to a VARCHAR2 variable.

Correct the statement by using a valid assignment target.

You must confirm that the expression used as the assignment statement’s target may accept a value if you want to correct this mistake. You might correct the issue in the example above by changing the str variable’s data type to a numeric type:

You could also change the expression in the assignment statement to return a value of the correct data type:

In this example, the TO CHAR function is used to convert the numeric expression num1 + num2 to a string value that can be assigned to the str variable.

Oracle Error CLSGN-32767: Internal error.

November 16, 2022

Oracle Error CLSGN-00211: OCR batch operation failed. string

Oracle error clsgn-00210: failed to get value for ocr key “string”. string, login with your site account.

Remember Me

[Updated on: Fri, 30 October 2009 06:29] by Moderator

Report message to a moderator

Syntax UTL_FILE.FSEEK ( fid IN utl_file.file_type, absolute_offset IN PL_INTEGER DEFAULT NULL, relative_offset IN PLS_INTEGER DEFAULT NULL);

[Updated on: Fri, 30 October 2009 05:46]

When WHEN OTHERS is not followed by RAISE its always almost a bug.It hides the real error.

utl_file.fseek( file IN OUT file_type, absolute_offset IN BINARY_INTEGER DEFAULT NULL, relative_offset IN BINARY_INTEGER DEFAULT NULL);

Quote: When WHEN OTHERS is not followed by RAISE its always almost a bug.It hides the real error.

Don't post anything, irrelavent.

And you should still have a raise otherwise the calling program won't know that anything went wrong.

[Updated on: Fri, 30 October 2009 06:14]

[Updated on: Fri, 30 October 2009 06:27]

Oh... Sorry Ved. I didn't notice that. But as you hadn't provided any specification as done by cookiemonster sir, I couldn't get that. By the way, thanks all. regards, Delna

[Updated on: Fri, 30 October 2009 06:32]

Home » PL/SQL Tutorial » PL/SQL Constants

PL/SQL Constants

Summary : in this tutorial, you will learn how to use the PL/SQL constants that hold values that do not change throughout the execution of the program.

Introduction to PL/SQL constants

Unlike a variable , a constant holds a value that does not change throughout the execution of the program.

Constants make your code more readable. Consider the following line of code that calculates the list price from price.

By looking at this, we don’t know what 0.1 means. It can be anything. Of course, you can use a comment to explain the meaning of 0.1 :

However, it is even better if you use a constant like this:

In this code, co_vat is a constant that stores the VAT tax of 10%.

To declare a constant, you specify the name, CONSTANT keyword, data type , and the default value. The following illustrates the syntax of declaring a constant:

In this syntax:

constant_name

is the name of the constant that you are declaring.

specify the type of value that the constant will hold.

optionally imposes a NOT NULL constraint on the constant. This prevents the constant from storing NULL or an empty string.

use an expression as the initial value for the constant. The type of the return value of the expression must be compatible with the data type of the constant.

PL/SQL constant examples

The following example declares two constants co_payment_term and co_payment_status :

If you attempt to change the co_payment_term in the execution section, PL/SQL will issue an error, for example:

Here is the error message:

The following illustrates how to declare a constant whose value is derived from an expression:

In this example, the co_area constant receives the value from an expression involving two other constants.

In this tutorial, you have learned about the PL/SQL constants that hold values that remain unchanged throughout the execution of the program.

Database PL/SQL Language Reference
PL/SQL Control Statements

4 PL/SQL Control Statements

PL/SQL has three categories of control statements: conditional selection statements, loop statements and sequential control statements.

PL/SQL categories of control statements are:

Conditional selection statements , which run different statements for different data values.

The conditional selection statements are IF and CASE .

Loop statements , which run the same statements with a series of different data values.

The loop statements are the basic LOOP , FOR LOOP , and WHILE LOOP .

The EXIT statement transfers control to the end of a loop. The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration. Both EXIT and CONTINUE have an optional WHEN clause, where you can specify a condition.

Sequential control statements , which are not crucial to PL/SQL programming.

The sequential control statements are GOTO , which goes to a specified statement, and NULL , which does nothing.

Conditional Selection Statements

LOOP Statements

Sequential Control Statements

4.1 Conditional Selection Statements

The conditional selection statements , IF and CASE , run different statements for different data values.

The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement has these forms:

IF THEN ELSE

IF THEN ELSIF

The CASE statement chooses from a sequence of conditions, and runs the corresponding statement. The CASE statement has these forms:

Simple, which evaluates a single expression and compares it to several potential values.

Searched, which evaluates multiple conditions and chooses the first one that is true.

The CASE statement is appropriate when a different action is to be taken for each alternative.

IF THEN Statement

IF THEN ELSE Statement

IF THEN ELSIF Statement

Simple CASE Statement

Searched CASE Statement

4.1.1 IF THEN Statement

The IF THEN statement either runs or skips a sequence of one or more statements, depending on a condition.

The IF THEN statement has this structure:

If the condition is true, the statements run; otherwise, the IF statement does nothing.

For complete syntax, see " IF Statement " .

Avoid clumsy IF statements such as:

Instead, assign the value of the BOOLEAN expression directly to a BOOLEAN variable:

A BOOLEAN variable is either TRUE , FALSE , or NULL . Do not write:

Instead, write:

Example 4-1 IF THEN Statement

In this example, the statements between THEN and END IF run if and only if the value of sales is greater than quota +200.

4.1.2 IF THEN ELSE Statement

The IF THEN ELSE statement has this structure:

If the value of condition is true, the statements run; otherwise, the else_statements run.

IF statements can be nested, as in Example 4-3 .

Example 4-2 IF THEN ELSE Statement

In this example, the statement between THEN and ELSE runs if and only if the value of sales is greater than quota +200; otherwise, the statement between ELSE and END IF runs.

Example 4-3 Nested IF THEN ELSE Statements

4.1.3 IF THEN ELSIF Statement

The IF THEN ELSIF statement has this structure:

The IF THEN ELSIF statement runs the first statements for which condition is true. Remaining conditions are not evaluated. If no condition is true, the else_statements run, if they exist; otherwise, the IF THEN ELSIF statement does nothing.

A single IF THEN ELSIF statement is easier to understand than a logically equivalent nested IF THEN ELSE statement:

Example 4-4 IF THEN ELSIF Statement

In this example, when the value of sales is larger than 50000, both the first and second conditions are true. However, because the first condition is true, bonus is assigned the value 1500, and the second condition is never tested. After bonus is assigned the value 1500, control passes to the DBMS_OUTPUT . PUT_LINE invocation.

Example 4-5 IF THEN ELSIF Statement Simulates Simple CASE Statement

This example uses an IF THEN ELSIF statement with many ELSIF clauses to compare a single value to many possible values. For this purpose, a simple CASE statement is clearer—see Example 4-6 .

4.1.4 Simple CASE Statement

The simple CASE statement has this structure:

The selector is an expression (typically a single variable). Each selector_value can be either a literal or an expression. (For complete syntax, see " CASE Statement " .)

The simple CASE statement runs the first statements for which selector_value equals selector . Remaining conditions are not evaluated. If no selector_value equals selector , the CASE statement runs else_statements if they exist and raises the predefined exception CASE_NOT_FOUND otherwise.

Example 4-6 uses a simple CASE statement to compare a single value to many possible values. The CASE statement in Example 4-6 is logically equivalent to the IF THEN ELSIF statement in Example 4-5 .

As in a simple CASE expression, if the selector in a simple CASE statement has the value NULL , it cannot be matched by WHEN NULL (see Example 2-51 ). Instead, use a searched CASE statement with WHEN condition IS NULL (see Example 2-53 ).

Example 4-6 Simple CASE Statement

4.1.5 Searched CASE Statement

The searched CASE statement has this structure:

The searched CASE statement runs the first statements for which condition is true. Remaining conditions are not evaluated. If no condition is true, the CASE statement runs else_statements if they exist and raises the predefined exception CASE_NOT_FOUND otherwise. (For complete syntax, see " CASE Statement " .)

The searched CASE statement in Example 4-7 is logically equivalent to the simple CASE statement in Example 4-6 .

In both Example 4-7 and Example 4-6 , the ELSE clause can be replaced by an EXCEPTION part. Example 4-8 is logically equivalent to Example 4-7 .

Example 4-7 Searched CASE Statement

Example 4-8 EXCEPTION Instead of ELSE Clause in CASE Statement

4.2 LOOP Statements

Loop statements run the same statements with a series of different values. The loop statements are:

Cursor FOR LOOP

The statements that exit a loop are:

The statements that exit the current iteration of a loop are:

CONTINUE WHEN

EXIT , EXIT WHEN , CONTINUE , and CONTINUE WHEN and can appear anywhere inside a loop, but not outside a loop. Oracle recommends using these statements instead of the " GOTO Statement " , which can exit a loop or the current iteration of a loop by transferring control to a statement outside the loop. (A raised exception also exits a loop. For information about exceptions, see " Overview of Exception Handling " .)

LOOP statements can be labeled, and LOOP statements can be nested. Labels are recommended for nested loops to improve readability. You must ensure that the label in the END LOOP statement matches the label at the beginning of the same loop statement (the compiler does not check).

Basic LOOP Statement

EXIT Statement

EXIT WHEN Statement

CONTINUE Statement

CONTINUE WHEN Statement

FOR LOOP Statement

WHILE LOOP Statement

For information about the cursor FOR LOOP , see " Processing Query Result Sets With Cursor FOR LOOP Statements " .

4.2.1 Basic LOOP Statement

The basic LOOP statement has this structure:

With each iteration of the loop, the statements run and control returns to the top of the loop. To prevent an infinite loop, a statement or raised exception must exit the loop.

" Basic LOOP Statement "

4.2.2 EXIT Statement

The EXIT statement exits the current iteration of a loop unconditionally and transfers control to the end of either the current loop or an enclosing labeled loop.

In Example 4-9 , the EXIT statement inside the basic LOOP statement transfers control unconditionally to the end of the current loop.

" EXIT Statement "

Example 4-9 Basic LOOP Statement with EXIT Statement

4.2.3 EXIT WHEN Statement

The EXIT WHEN statement exits the current iteration of a loop when the condition in its WHEN clause is true, and transfers control to the end of either the current loop or an enclosing labeled loop.

Each time control reaches the EXIT WHEN statement, the condition in its WHEN clause is evaluated. If the condition is not true, the EXIT WHEN statement does nothing. To prevent an infinite loop, a statement inside the loop must make the condition true, as in Example 4-10 .

In Example 4-10 , the EXIT WHEN statement inside the basic LOOP statement transfers control to the end of the current loop when x is greater than 3. Example 4-10 is logically equivalent to Example 4-9 .

In Example 4-11 , one basic LOOP statement is nested inside the other, and both have labels. The inner loop has two EXIT WHEN statements; one that exits the inner loop and one that exits the outer loop.

An EXIT WHEN statement in an inner loop can transfer control to an outer loop only if the outer loop is labeled.

In Example 4-12 , the outer loop is not labeled; therefore, the inner loop cannot transfer control to it.

Example 4-10 Basic LOOP Statement with EXIT WHEN Statement

Example 4-11 Nested, Labeled Basic LOOP Statements with EXIT WHEN Statements

Example 4-12 Nested, Unabeled Basic LOOP Statements with EXIT WHEN Statements

4.2.4 CONTINUE Statement

The CONTINUE statement exits the current iteration of a loop unconditionally and transfers control to the next iteration of either the current loop or an enclosing labeled loop.

In Example 4-13 , the CONTINUE statement inside the basic LOOP statement transfers control unconditionally to the next iteration of the current loop.

" CONTINUE Statement "

Example 4-13 CONTINUE Statement in Basic LOOP Statement

4.2.5 CONTINUE WHEN Statement

The CONTINUE WHEN statement exits the current iteration of a loop when the condition in its WHEN clause is true, and transfers control to the next iteration of either the current loop or an enclosing labeled loop.

Each time control reaches the CONTINUE WHEN statement, the condition in its WHEN clause is evaluated. If the condition is not true, the CONTINUE WHEN statement does nothing.

In Example 4-14 , the CONTINUE WHEN statement inside the basic LOOP statement transfers control to the next iteration of the current loop when x is less than 3. Example 4-14 is logically equivalent to Example 4-13 .

Example 4-14 CONTINUE WHEN Statement in Basic LOOP Statement

4.2.6 FOR LOOP Statement

The FOR LOOP statement runs one or more statements while the loop index is in a specified range. The statement has this structure:

Without REVERSE , the value of index starts at lower_bound and increases by one with each iteration of the loop until it reaches upper_bound . If lower_bound is greater than upper_bound , then the statements never run.

With REVERSE , the value of index starts at upper_bound and decreases by one with each iteration of the loop until it reaches lower_bound . If upper_bound is less than lower_bound , then the statements never run.

An EXIT , EXIT WHEN , CONTINUE , or CONTINUE WHEN in the statements can cause the loop or the current iteration of the loop to end early.

To process the rows of a query result set, use a cursor FOR LOOP , which has a query instead of a range of integers. For details, see " Processing Query Result Sets With Cursor FOR LOOP Statements " .

" FOR LOOP Statement "

In Example 4-15 , index is i , lower_bound is 1, and upper_bound is 3. The loop prints the numbers from 1 to 3.

The FOR LOOP statement in Example 4-16 is the reverse of the one in Example 4-15 : It prints the numbers from 3 to 1.

In some languages, the FOR LOOP has a STEP clause that lets you specify a loop index increment other than 1. To simulate the STEP clause in PL/SQL, multiply each reference to the loop index by the desired increment.

In Example 4-17 , the FOR LOOP effectively increments the index by five.

FOR LOOP Index

Lower Bound and Upper Bound

EXIT WHEN or CONTINUE WHEN Statement in FOR LOOP Statement

Example 4-15 FOR LOOP Statements

Example 4-16 Reverse FOR LOOP Statements

Example 4-17 Simulating STEP Clause in FOR LOOP Statement

4.2.6.1 FOR LOOP Index

The index of a FOR LOOP statement is implicitly declared as a variable of type PLS_INTEGER that is local to the loop. The statements in the loop can read the value of the index, but cannot change it. Statements outside the loop cannot reference the index. After the FOR LOOP statement runs, the index is undefined. (A loop index is sometimes called a loop counter.)

In Example 4-18 , the FOR LOOP statement tries to change the value of its index, causing an error.

In Example 4-19 , a statement outside the FOR LOOP statement references the loop index, causing an error.

If the index of a FOR LOOP statement has the same name as a variable declared in an enclosing block, the local implicit declaration hides the other declaration, as Example 4-20 shows.

Example 4-21 shows how to change Example 4-20 to allow the statement inside the loop to reference the variable declared in the enclosing block.

In Example 4-22 , the indexes of the nested FOR LOOP statements have the same name. The inner loop references the index of the outer loop by qualifying the reference with the label of the outer loop. For clarity only, the inner loop also qualifies the reference to its own index with its own label.

Example 4-18 FOR LOOP Statement Tries to Change Index Value

Example 4-19 Outside Statement References FOR LOOP Statement Index

Example 4-20 FOR LOOP Statement Index with Same Name as Variable

Example 4-21 FOR LOOP Statement References Variable with Same Name as Index

Example 4-22 Nested FOR LOOP Statements with Same Index Name

4.2.6.2 Lower Bound and Upper Bound

The lower and upper bounds of a FOR LOOP statement can be either numeric literals, numeric variables, or numeric expressions. If a bound does not have a numeric value, then PL/SQL raises the predefined exception VALUE_ERROR .

In Example 4-24 , the upper bound of the FOR LOOP statement is a variable whose value is determined at run time.

Example 4-23 FOR LOOP Statement Bounds

Example 4-24 Specifying FOR LOOP Statement Bounds at Run Time

4.2.6.3 EXIT WHEN or CONTINUE WHEN Statement in FOR LOOP Statement

Suppose that you must exit a FOR LOOP statement immediately if a certain condition arises. You can put the condition in an EXIT WHEN statement inside the FOR LOOP statement.

In Example 4-25 , the FOR LOOP statement executes 10 times unless the FETCH statement inside it fails to return a row, in which case it ends immediately.

Now suppose that the FOR LOOP statement that you must exit early is nested inside another FOR LOOP statement. If, when you exit the inner loop early, you also want to exit the outer loop, then label the outer loop and specify its name in the EXIT WHEN statement, as in Example 4-26 .

If you want to exit the inner loop early but complete the current iteration of the outer loop, then label the outer loop and specify its name in the CONTINUE WHEN statement, as in Example 4-27 .

" Overview of Exception Handling " for information about exceptions, which can also cause a loop to end immediately if a certain condition arises

Example 4-25 EXIT WHEN Statement in FOR LOOP Statement

Example 4-26 EXIT WHEN Statement in Inner FOR LOOP Statement

Example 4-27 CONTINUE WHEN Statement in Inner FOR LOOP Statement

4.2.7 WHILE LOOP Statement

The WHILE LOOP statement runs one or more statements while a condition is true. It has this structure:

If the condition is true, the statements run and control returns to the top of the loop, where condition is evaluated again. If the condition is not true, control transfers to the statement after the WHILE LOOP statement. To prevent an infinite loop, a statement inside the loop must make the condition false or null. For complete syntax, see " WHILE LOOP Statement " .

Some languages have a LOOP UNTIL or REPEAT UNTIL structure, which tests a condition at the bottom of the loop instead of at the top, so that the statements run at least once. To simulate this structure in PL/SQL, use a basic LOOP statement with an EXIT WHEN statement:

In Example 4-28 , the statements in the first WHILE LOOP statement never run, and the statements in the second WHILE LOOP statement run once.

Example 4-28 WHILE LOOP Statements

4.3 Sequential Control Statements

Unlike the IF and LOOP statements, the sequential control statements GOTO and NULL are not crucial to PL/SQL programming.

The GOTO statement, which goes to a specified statement, is seldom needed. Occasionally, it simplifies logic enough to warrant its use.

The NULL statement, which does nothing, can improve readability by making the meaning and action of conditional statements clear.

GOTO Statement

NULL Statement

4.3.1 GOTO Statement

The GOTO statement transfers control to a label unconditionally. The label must be unique in its scope and must precede an executable statement or a PL/SQL block. When run, the GOTO statement transfers control to the labeled statement or block. For GOTO statement restrictions, see " GOTO Statement " .

Use GOTO statements sparingly—overusing them results in code that is hard to understand and maintain. Do not use a GOTO statement to transfer control from a deeply nested structure to an exception handler. Instead, raise an exception. For information about the PL/SQL exception-handling mechanism, see PL/SQL Error Handling .

A label can appear only before a block (as in Example 4-21 ) or before a statement (as in Example 4-29 ), not in a statement, as in Example 4-30 .

To correct Example 4-30 , add a NULL statement, as in Example 4-31 .

A GOTO statement can transfer control to an enclosing block from the current block, as in Example 4-32 .

The GOTO statement transfers control to the first enclosing block in which the referenced label appears.

The GOTO statement in Example 4-33 transfers control into an IF statement, causing an error.

Example 4-29 GOTO Statement

Example 4-30 Incorrect Label Placement

Example 4-31 GOTO Statement Goes to Labeled NULL Statement

Example 4-32 GOTO Statement Transfers Control to Enclosing Block

Example 4-33 GOTO Statement Cannot Transfer Control into IF Statement

4.3.2 NULL Statement

The NULL statement only passes control to the next statement. Some languages refer to such an instruction as a no-op (no operation).

Some uses for the NULL statement are:

To provide a target for a GOTO statement, as in Example 4-31 .

To improve readability by making the meaning and action of conditional statements clear, as in Example 4-34

To create placeholders and stub subprograms, as in Example 4-35

To show that you are aware of a possibility, but that no action is necessary, as in Example 4-36

In Example 4-34 , the NULL statement emphasizes that only salespersons receive commissions.

In Example 4-35 , the NULL statement lets you compile this subprogram and fill in the real body later.

Using the NULL statement might raise an unreachable code warning if warnings are enabled. For information about warnings, see " Compile-Time Warnings " .

In Example 4-36 , the NULL statement shows that you have chosen to take no action for grades other than A, B, C, D, and F.

Example 4-34 NULL Statement Showing No Action

Example 4-35 NULL Statement as Placeholder During Subprogram Creation

Example 4-36 NULL Statement in ELSE Clause of Simple CASE Statement

Install App

SQL & PL/SQL

For appeals, questions and feedback, please email [email protected]