How to Properly Return Variable Values in Oracle PL/SQL: A Guide for SQL Server Migrants

Learn how to retrieve variable values in Oracle PL/SQL after migrating from SQL Server. This guide highlights common errors and provides clear solutions.
---

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: Oracle return the value of a variable

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Properly Return Variable Values in Oracle PL/SQL: A Guide for SQL Server Migrants

Migrating from Microsoft SQL Server to Oracle 19c can be a challenging task, especially when dealing with PL/SQL syntax differences. One common issue many developers face is how to return the value of a variable, particularly after executing stored procedures that set its value. In this guide, we'll explore the question of how to properly retrieve and display values from a variable in Oracle PL/SQL, addressing a specific error message you might encounter along the way.

The Problem: Understanding the Error

You might find yourself in a situation where you've written PL/SQL code to call a stored procedure, but when attempting to retrieve the variable's value, you encounter an error like this:

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

This error typically occurs when you try to write a SELECT statement to fetch a variable without the proper syntax. The code block below demonstrates such a scenario:

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

The key issue lies in the misuse of the SELECT clause to retrieve the variable's value. Fortunately, there is a straightforward way to resolve this issue.

The Solution: Correctly Returning Variable Values

Using DBMS_OUTPUT to Display Values

One of the simplest ways to return and view the value of a variable in Oracle PL/SQL is to use the DBMS_OUTPUT.PUT_LINE method. This function allows you to print the value of the variable directly to the console. Here’s how to do it:

Define the Procedure: First, make sure your stored procedure is properly defined. The example below showcases a procedure setting the value of an OUT parameter.

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

Declare and Use the Variable: Next, declare the variable and call your procedure to set its value. Finally, print the value using DBMS_OUTPUT:

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

This will produce the output: Cardnum = ABC.

Using SELECT...INTO for Variable Retrieval

If you prefer to use a SELECT statement within your PL/SQL block, make sure to include the INTO clause to fetch the value properly. Here's how:

Declare the Variables: Set up your variables as shown below.

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

In this method, l_var successfully receives the value from cardnum, and the procedure will complete without error.

Choosing the Right Method

The approach you choose depends on your specific requirements. If you simply need to display the value for debugging or logging purposes, using DBMS_OUTPUT is effective and straightforward. On the other hand, if you need to manipulate the value further within your PL/SQL block, using a SELECT...INTO would be the more appropriate choice.

Conclusion

Migrating from SQL Server to Oracle requires getting used to a few syntax differences and adapting your coding practices. When it comes to returning variable values from procedures, ensure that you're using the appropriate methods like DBMS_OUTPUT.PUT_LINE or SELECT...INTO to avoid common pitfalls such as the ORA-00923 error.

By understanding these concepts and practicing them in your migrations, you'll become more proficient in Oracle PL/SQL and better equipped to handle similar challenges in the future.