[SOLVED] Oracle PL/SQL dbms_output.put_line Throwing Errors when Using Variable

devUnix · 03-07-2012, 06:00 AM

PHP Code:



 
SQL> edit 
Wrote file afiedt.buf 
 
  1  declare 
  2     num1 number := 100; 
  3     num2 number := 300; 
  4  begin 
  5     declare 
  6             sum number(6); 
  7     begin 
  8             sum := num1 + num2; 
  9             dbms_output.put_line ('Total: '); 
 10     end; 
 11* end; 
SQL> / 
Total: 
 
PL/SQL procedure successfully completed.

The same PL/SQL block does not work when I use the variable "sum" in the output statement:

PHP Code:



 
 
SQL> edit 
Wrote file afiedt.buf 
 
  1  declare 
  2     num1 number := 100; 
  3     num2 number := 300; 
  4  begin 
  5     declare 
  6             sum number(6); 
  7     begin 
  8             sum := num1 + num2; 
  9             dbms_output.put_line ('Total: ' || sum); 
 10     end; 
 11* end; 
SQL> / 
                dbms_output.put_line ('Total: ' || sum); 
                                                      * 
ERROR at line 9: 
ORA-06550: line 9, column 41: 
PLS-00103: Encountered the symbol ")" when expecting one of the following: 
( 
 
 
SQL>

Note:

This line is causing the error shown above:

PHP Code:



dbms_output.put_line ('Total: ' || sum);

I tried using the variable name only and it still did not work:

PHP Code:



dbms_output.put_line (sum);

NevemTeve · 03-08-2012, 01:58 AM

Sum is quite likely to be a reserved word (name of a group function). Try v_sum instead.

devUnix · 03-08-2012, 02:36 AM

Quote:

Originally Posted by NevemTeve

Sum is quite likely to be a reserved word (name of a group function). Try v_sum instead.

Before going and running the same code by twisting the variable name, I can definitely say that I have by mistake used a reserved keyword as a variable name!

Let me give it a try now!

devUnix · 03-08-2012, 02:41 AM

NavemTeve:

Thanks for pointing out that variable name! sum() is a function and I just did not pay enough attention to this fact.

Here's the corrected version of the above problem:

Code:

declare
	num1 number := 100;
	num2 number := 300;
begin
	declare
		v_sum number(6);
	begin
		v_sum := num1 + num2;
		dbms_output.put_line ('Total: ' || v_sum);
	end;
end;
/


SQL> set serveroutput on
SQL> @pl.sql
Total: 400

PL/SQL procedure successfully completed.

Rated you for your contribution and time!