Printing the values of variables in oracle procedure

[Gerri]

Sponsored Links

Hello !
I am writing a dynamic query using stored procedure and getting difficulties to print the values of particular variable in procedure. I am running with oracle 9i using Windows XP. I need some guidance regarding this topic. So please help me to achieve my task..

Thanks & regard.

Gerri

[Reegan]

Procedure in oracle :

The oracle database is featured by a object named 'Procedure'.The procedure is a group of some SQL statement which is called by a name.After creation of the procedure,we can call it anywhere,whenever we want to use it in the PL/SQL program.

Stored procedures has some availability in the areas of development, integrity, security, performance, and memory allocation in database.

[opaper]

Creating procedure :

As you now already about the procedure. I will discuss the creation of procedure here and the statement would be used to create the procedure :

This is the syntax here to create procedure :

CREATE PROCEDURE Emp_sal (employee_id NUMBER) AS
   tot_emps NUMBER;

   BEGIN
      Select salary FROM employees
      WHERE dept_id='Account_dept';
   END;
/

This is the simple syntax here to define to create a procedure which will show the salary of 'ACCOUNT' department.

[MindSpace]

Printing the value of a variable :

Here, I am going to built a program which will show you the value of an existing variable and it takes the following form :

SQL> set serveroutput on format wrap

SQL> declare
  2      Msg varchar2(30) := 'Have a good a day..';
  3  begin
  4      dbms_output.enable(1000000);
  5      dbms_output.put_line ('The value of variable "Meg" is: ['||Msg||']');
  6  end;
SQL> /

The value of variable "Msg" is: [Have a good a day..]

PL/SQL procedure successfully completed.

[Modifier]

SET SERVEROUTPUT clause :

The SQL*PLUS is unable to read the output written by the PL/SQL program which is default behavior of SQL*PLUS. For this purpose,we use the SERVEROUTPUT clause with SET command .When we alter it into ON,the behavior changed automatically.
It has some options :

SQL> set serveroutput on
SQL> set serveroutput off
SQL> set serveroutput on size unlimited

The ON option shows behavior has been changed and OFF perform vice versa where as SIZE UNLIMITED is introduced in 10g.