CODEWITHSUNDEEP
sqloracle-databaseplsql
Chicken2
Chicken2

Reputation: 5

PLSQL Check if varchar2 contains True

I wrote a PLSQL Script which should check if one of the selected rows contains TRUE if yes then he should print me a text.. but he fetches more than one row.

DECLARE

tmp varchar2(20);

BEGIN

Select Status into tmp 
From Cdb_Dv_Status;

    IF tmp = 'TRUE' THEN
    DBMS_Output.put_line('the output is false');
    ELSE
    DBMS_Output.put_line('the output is true');
    END IF;
end;
/

This is what i get when i select it with SQL

NAME                     STATUS         
-------------------      ----------- 
DV_ENABLE_STATUS           FALSE            
DV_APP_PROTECTION          NOT CONFIGURED       
DV_CONFIGURE_STATUS        FALSE            
DV_APP_PROTECTION          NOT CONFIGURED      
DV_ENABLE_STATUS           FALSE            
DV_CONFIGURE_STATUS        FALSE           
DV_APP_PROTECTION          NOT CONFIGURED      
DV_CONFIGURE_STATUS        FALSE            
DV_ENABLE_STATUS           FALSE           
DV_CONFIGURE_STATUS        FALSE            
DV_APP_PROTECTION          NOT CONFIGURED      
DV_ENABLE_STATUS           FALSE

this doesnt work either.. he tells me i have to declare tmp

    BEGIN
  for rec in (
    select status tmp
      from Cdb_Dv_Status
  )loop
    IF rec.tmp='TRUE' then
    dbms_output.put_line('Database Vault wird genutzt');
    end if;

   end loop;

    IF rec.tmp='TRUE' then 
        dbms_output.put_line('wird genutzt');

    ELSE 
    dbms_output.put_line('Database Vault wird nicht genutzt');

    END IF;

end;
/

Upvotes: 0

Views: 893

Answers (2)

user5683823
user5683823

Reputation:

If all you need to do is to check if the value 'TRUE' appears at least once in the column STATUS of table (or view) CDB_DV_STATUS, you can do something like this:

declare
  str varchar2(5);
begin
  select case when 'TRUE' in (select status from cdb_dv_status)
              then 'true' else 'false' end
    into str
    from dual;
  dbms_output.put_line('The output is ' || str);
end;
/

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

You may get some too_many_rows or no_data_found exceptions depending on the data for the current case, rather prefer using a COUNT aggregation as in the following code block to check the existence

DECLARE
  tmp INT;
BEGIN
  SELECT COUNT(*) 
    INTO tmp 
    FROM Cdb_Dv_Status
   WHERE Status = 'TRUE';

  IF tmp > 0 THEN
    DBMS_Output.put_line('the output is true');
  ELSE
    DBMS_Output.put_line('the output is false');
  END IF;
END;
/

Demo

Upvotes: 3

Related Questions