Resolving PLS-00103 Error: Creating Triggers in Oracle PL/SQL

Learn how to successfully create Oracle PL/SQL triggers and fix common errors like PLS-00103. This guide covers the key steps to ensure your trigger works as intended.
---
This video is based on the question https://stackoverflow.com/q/68763100/ asked by the user 'yunyun333' ( https://stackoverflow.com/u/11649999/ ) and on the answer https://stackoverflow.com/a/68763886/ provided by the user 'Littlefoot' ( https://stackoverflow.com/u/9097906/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Error when trying to create a new trigger - not ended properly

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Resolving PLS-00103 Error: Creating Triggers in Oracle PL/SQL

Creating a trigger in Oracle PL/SQL is a task that many developers encounter while managing databases. However, sometimes you might face the frustrating PLS-00103 error, which indicates that the SQL code was not terminated properly. In this guide, we'll explore what might cause this error and how to effectively create a trigger without any issues.

Understanding the Problem

What is a Trigger?

A trigger is a special type of stored procedure in Oracle that automatically executes when specific changes are made to a database table. For instance, in the scenario mentioned, the developer wishes to create a trigger that copies the original record from table_1 to table_2 whenever a record is updated or deleted.

Here's the SQL code the developer attempted:

[[See Video to Reveal this Text or Code Snippet]]

However, upon execution, the developer encountered an error message stating that there were issues with the trigger compilation. The key part of the message was:

[[See Video to Reveal this Text or Code Snippet]]

This indicates that the SQL was not written correctly.

Analyzing the Error

Common Mistakes with SQL Syntax

Missing Semicolon: Each statement in PL/SQL should end with a semicolon. In the posted trigger code, one of the missing semicolons can cause the SQL engine to misinterpret the command.

End Slash: In Oracle SQL, you need to add a forward slash (/) after the END; statement to indicate the end of your PL/SQL block. Without it, the SQL cursor might remain blinking, expecting more input.

Fixing the Code

Based on the error analysis, the correct trigger creation should look like this:

[[See Video to Reveal this Text or Code Snippet]]

With this corrected statement, you'll find that the trigger is created successfully, and you'll no longer see the compilation error.

Conclusion

When creating triggers in Oracle PL/SQL, it’s essential to follow the correct syntax to avoid errors like PLS-00103. Double-check for missing semicolons and ensure that you include the end slash to signify the end of the SQL block. By following the fixes outlined above, you should be able to create your trigger without any issues.

If you encounter any more errors, consider using the SHOW ERRORS command in SQL to get detailed information regarding any compilation errors. Happy coding!

Видео Resolving PLS-00103 Error: Creating Triggers in Oracle PL/SQL канала vlogize