CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
Mubarak Abdullah
Mubarak Abdullah

Reputation: 55

Procedure gives empty result when called

I have created a simple procedure to reverse a number in PL/SQL. The procedure executes fine, but the result doesn't get print. Here's the proc,

CREATE OR REPLACE PROCEDURE SAMPLE_REV (myinput IN NUMBER, finalresult OUT NUMBER)
IS 
    OperInput NUMBER;
    MYREMAINDER NUMBER;
    MYRESULT NUMBER;
BEGIN   
     OperInput:=myinput;    

     while OperInput!=0 LOOP 

        MYREMAINDER:=mod(OperInput,10);
        MYRESULT:=(MYRESULT*10)+MYREMAINDER;
        OperInput:=TRUNC(OperInput/10);  

     end LOOP;

    finalresult:=MYRESULT;

END;

Procedure, when executed works fine. But, when I call on the procedure by the following code,

DECLARE
      ENTER NUMBER;
      finalresult NUMBER;      
BEGIN
     ENTER:=&ENTER;
     SAMPLE_REV(ENTER,finalresult);
     dbms_output.put_line('Output is '|| finalresult);
END;

The result is empty as,

Output is 

PL/SQL procedure successfully completed.

I can't come to know the error here, if any. And thanks for the help.

Upvotes: 0

Views: 146

Answers (4)

Ychdziu
Ychdziu

Reputation: 445

This line is the problem:

MYRESULT:=(MYRESULT*10)+MYREMAINDER;

On the first iteration MYRESULT is null. So the MYRESULT*10 will be also null. And null + MYREMAINDER = null;

Initialize MYRESULT in the declare section to 0;

Upvotes: 0

mdparthi
mdparthi

Reputation: 133

I see your calculation is not correct. I have added additional output in the procedure to see what it is printing.

MYRESULT itslef is empty and hence you see the output is empty.

set serveroutput on;
CREATE OR REPLACE PROCEDURE SAMPLE_REV (myinput IN NUMBER, finalresult OUT NUMBER)
IS 
    OperInput NUMBER;
    MYREMAINDER NUMBER;
    MYRESULT NUMBER;
BEGIN   
     OperInput:=myinput;    

     while OperInput!=0 LOOP 

        MYREMAINDER:=mod(OperInput,10);
        dbms_output.put_line('remainder ' || MYREMAINDER);
        dbms_output.put_line('in ' || MYRESULT);

        MYRESULT:=(MYRESULT*10)+MYREMAINDER;

        dbms_output.put_line('out ' || MYRESULT);
        OperInput:=TRUNC(OperInput/10);  

     end LOOP;

    finalresult:=MYRESULT;

END;

Upvotes: 0

Amy Grange
Amy Grange

Reputation: 245

In order to view the output of a PL/SQL procedure using dbms_ouput.put_line, run the following command in your session window:

SET SERVEROUTPUT ON;

Should do the trick :)

Upvotes: 0

Kris Rice
Kris Rice

Reputation: 3410

the procedure is using MYRESULT before it is initialized hence null. So this line: 

MYRESULT:=(MYRESULT*10)+MYREMAINDER;

is essentially 

    MYRESULT:=(<<<NULL>>>*10)+MYREMAINDER;

So null overall.

Just adding a :=0 to the declaration will get it working. Also add the set serveroutput on

SQL>set serveroutput on

SQL>CREATE OR REPLACE PROCEDURE SAMPLE_REV (myinput IN NUMBER, finalresult OUT NUMBER)
  2  IS 
  3      OperInput NUMBER;
  4      MYREMAINDER NUMBER;
  5      MYRESULT NUMBER :=0;
  6  BEGIN   
  7       OperInput:=myinput;    
  8  
  9       while OperInput!=0 LOOP 
 10  
 11          MYREMAINDER:=mod(OperInput,10);
 12          MYRESULT:=(MYRESULT*10)+MYREMAINDER;
 13          OperInput:=TRUNC(OperInput/10);  
 14  
 15       end LOOP;
 16  
 17      finalresult:=MYRESULT;
 18  
 19  END;
 20* /
Procedure SAMPLE_REV compiled

SQL>DECLARE
  2        ENTER NUMBER;
  3        finalresult NUMBER;      
  4  BEGIN
  5       ENTER:=&ENTER;
  6       SAMPLE_REV(ENTER,finalresult);
  7       dbms_output.put_line('Output is '|| finalresult);
  8  END;
  9  /
Enter value for ENTER: 987


Output is 789


PL/SQL procedure successfully completed.

SQL>

Upvotes: 3

Related Questions