How to compile and run PL/SQL code snippets from a Case Study

How to compile and run PL/SQL code snippets from a Case Study

PL/SQL is a process-oriented structured programming statement developed by Oracle for database business logic requirements. In the Oracle kernel, there are two important components: PL/SLQ engine and SQL engine, which are used to process structured PL/SQL statements and SQL statements respectively.

Like all advanced languages, PL/SQL statements also have two key steps for compiling and running. In the Compile stage, the syntax permission check, object method check, and syntax structure check are implemented. In the Runtime stage, the same process will still be performed. Some statement errors are detected during running.

This article mainly analyzes the characteristics and differences of the two in detail through an error case.

1. Problem Description

A friend asked the author why the error is not caught by exception when the prepared PL/SQL anonymous block is in the execution status. Description: Based on the Data Protection reason, the code snippet is a simulated version.

The lab environment is 11gR2, and the specific version is 11.2.0.4.

SQL> select * from v $ version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

The simulated code snippets are as follows:

SQL> declare

2 I varchar2 (10 );

3 begin

4 select err

5 into I

6 from t

7 where rownum <2;

8 exception

9 when others then

10 dbms_output.put_line ('errors Catch! ');

11 end;

12/

Select err

*

ERROR at line 4:

ORA-06550: line 4, column 10:

PL/SQL: ORA-00904: "ERR": invalid identifier

ORA-06550: line 4, column 3:

PL/SQL: SQL Statement ignored

My friend's question is, the data table T does not have the err column, but why is the exception section not captured during execution? Instead, an error is reported directly?

Table T is described as follows:

SQL> desc t;

Name Type Nullable Default Comments

------------------------------------------------

USER_ID VARCHAR2 (100 BYTE) Y

2. Analysis and Testing

This problem is more intuitive and confusing, but there are still conceptual barriers after careful analysis. This is the compile time and run time of the program.

When we compile a PL/SQL program, both procedure and package have a compile action. After the compile operation is successful, we can execute the program exec. In compile, the work is similar to that in any language compiler.

Most of the common examples are verification and verification. For example, are variables defined? Does the user have permission to use objects and variables? Variable visibility range? Or is the syntax used correctly? After compile, the compiler considers that the program has the precondition for execution and converts it to the target object.

However, is the execution process (that is, Runtime) of compiled programs foolproof? Certainly not. Memory, CPU, and disk resource constraints, internal logic operation errors, and digital operation errors may cause a series of runtime faults. The exception in PL/SQL is for the runtime error.

Assume that an anonymous block is used incorrectly. In the execution process, it is actually a situation where two steps are taken: compiling and running. The program has not entered the runtime stage, and has been compiled into compile and found errors.

We use a series of simple experiments to prove the conclusion. First, create a separate stored procedure, not just compile it. What is the result?

SQL> create or replace procedure TEST

2

3 I varchar2 (10 );

4 begin

5 select err

6 into I

7 from t

8 where rownum <2;

9 exception

10 when others then

11 dbms_output.put_line ('errors Catch! ');

12 end;

13/

Warning: Procedure created with compilation errors.

Compilation error. show error:

SQL> show error

Errors for procedure test:

LINE/COL ERROR

-------------------------------------------------------------------------

5/3 PL/SQL: SQL Statement ignored

5/10 PL/SQL: ORA-00904: "ERR": invalid identifier

The error message is exactly the same as that of the anonymous block. At this time, it is assumed that the error of the anonymous block occurs during compilation.

So how can we avoid checking during compilation and send this error to the runtime so that exceptions can be caught? We can use strings.

SQL> create or replace procedure TEST

2

3 I varchar2 (10 );

4 begin

5 execute immediate 'select err from t where rownum <2'

6 into I;

7

8 exception

9 when others then

10 dbms_output.put_line ('errors Catch! ');

11 end;

12/

Procedure created.

Execute immediate can directly execute string-type SQL statements. If the stored procedure is successfully created, the PL/SQL engine does not check the strings.

Run:

SQL> set serveroutput on;

SQL> exec test;

Errors Catch!

PL/SQL procedure successfully completed.

It can be captured by exception, which has the same effect as expected. According to this idea, the code for modifying a friend is as follows:

SQL> declare

2 I varchar2 (10 );

3 begin

4 execute immediate 'select err from t where rownum <2'

5 into I;

6

7 exception

8 when others then

9 dbms_output.put_line ('errors Catch! ');

10 end;

11/

Errors Catch!

PL/SQL procedure successfully completed.

Compile the Compile check and report an error at Runtime.

3. Conclusion

PL/SQL statements are an important tool for structured and procedural database processing. We should be aware that PL/SQL is also a programming language and requires the Compile and Runtime stages. Each stage has a different mission.

Basic Design of PL/SQL programs for Oracle databases

PL/SQL Developer Practical Skills

Use PL/Scope to analyze PL/SQL code

Use the date type in PL/SQL