CODEWITHSUNDEEP
oracle-databaseplsql
user2788235
user2788235

Reputation: 31

Procedure executed, but not displaying results

I have created a procedure and executing it. It is getting executed as well. but not displaying the result. I am missing some basic steps that's why this is happening. Please help.

 set serverout on;
    Create or replace procedure pro1 (a number) is 
    x varchar2(30);
    begin
    select last_name into x from employees where employee_id= a;
    end;
    /

    declare
    y varchar2(30);
    begin
    pro1 (102);
    dbms_output.put_line (y);
    end;
    /

Upvotes: 0

Views: 1908

Answers (3)

user2788235
user2788235

Reputation: 31

Even without having "OUT Parameter" procedure can return result. Find below the example:

set serverout on;
Create or replace procedure pro1 (a number) is 
x varchar2(30);
begin
select last_name into x from employees where employee_id= a;
dbms_output.put_line (x);
end;
/

declare
y varchar2(30);
begin
pro1 (102);
end;
/

Upvotes: 0

Jacob
Jacob

Reputation: 14731

Do as the following in order to see the output 

CREATE OR REPLACE PROCEDURE pro1 (i_param IN VARCHAR2, o_param OUT VARCHAR2)
IS
BEGIN
   SELECT LAST_NAME
     INTO o_param
     FROM employees
    WHERE employee_id= i_param;
END;

and execute procedure as

SET SERVEROUTPUT ON;
DECLARE       
   z   VARCHAR2 (30);
BEGIN
   pro1 ('535', z);
   DBMS_OUTPUT.put_line (z);
END;
/

If your procedure doesn't have any DML statements, it is better to use a function.

Upvotes: 1

Guneli
Guneli

Reputation: 1721

As I understand you want to display the last_name of the employee with id 102. To be able to get the lastname from pro1 you should create function instead of procedure, as procedures can not return any value. Try this:

 set serveroutput on;
    Create or replace function pro1 (a number) return varchar2
    is
    x varchar2(30);
    begin
    select last_name into x from employees where employee_id= a;
    return x;
    exception when no_data_found then
    return 'no employee with this id';
    end;
    /

    declare
    y varchar2(30);
    begin
    y:=pro1 (535);
    dbms_output.put_line (y);
    end;
    /

Upvotes: 0

Related Questions