Printing a statement in stored procedure-CodePudding

I have written some code to print whether the condition is true/false, but I can't get the output as true / false. Why?

Here is that code:

create or replace PROCEDURE TET_STOP_DID 
IS 
result VARCHAR2(10);
LastGeneratedcode TET_LASTGENERATEDMASKCODE.Maskedcode%TYPE;

BEGIN
  select maskedcode into LastGeneratedcode from TET_LASTGENERATEDMASKCODE;
  IF (SUBSTR(LastGeneratedcode,5,5) !='ZZZZZ') then
  result := 'true';

  else
    result :='false';

    END IF;
END TET_STOP_DID;

CodePudding user response：

You aren't printing anything; you've just set the result variable to some value, and that's it. Though, that procedure doesn't look right as it'll raise too_many_rows error if there are two (or more) rows in the table (or no_data_found if it is empty). I presume you'd want to pass some parameter which restricts number of fetched rows.

For example:

SQL> select * from tet_lastgeneratedmaskcode;

        ID MASK
---------- ----
         1 abcd
         2 fff

Procedure which accepts a parameter and handles exceptions (that's rather poor handling, just to show what to pay attention to):

SQL> create or replace procedure tet_stop_did
  2    (par_id in tet_lastgeneratedmaskcode.id%type)
  3  is
  4    result varchar2(10);
  5    lastgeneratedcode tet_lastgeneratedmaskcode.maskedcode%type;
  6  begin
  7    select maskedcode
  8      into lastgeneratedcode
  9      from tet_lastgeneratedmaskcode
 10      where id = par_id;
 11
 12    if substr(lastgeneratedcode, 5, 5) != 'ZZZZZ' then
 13       result := 'true';
 14    else
 15       result := 'false';
 16    end if;
 17
 18    dbms_output.put_line('Result = ' || result);
 19  exception
 20    when no_data_found then
 21      dbms_output.put_line('No rows for that ID');
 22    when too_many_rows then
 23      dbms_output.put_line('Two or more rows for that ID');
 24  end tet_stop_did;
 25  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec tet_stop_did (1);
Result = false                        --> here's your result

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

If you can use SERVEROUTPUT clause to run the procedure you may insert DBMS_OUTPUT in stored procedure code:

CREATE OR REPLACE PROCEDURE TET_STOP_DID 
IS 
     result VARCHAR2(10);
     LastGeneratedcode TET_LASTGENERATEDMASKCODE.Maskedcode%TYPE;

BEGIN

     select maskedcode into LastGeneratedcode from TET_LASTGENERATEDMASKCODE;
     
     IF (SUBSTR(LastGeneratedcode,5,5) !='ZZZZZ') then
        result := 'true';
     ELSE
        result := 'false';
     END IF;
     
     -- Print the output
     DBMS_OUTPUT.PUT_LINE(result);
         
END TET_STOP_DID;

And then from terminal, it runs so:

SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE TET_STOP_DID
false

Thank you