CODEWITHSUNDEEP
plsqloracle10g
Layla
Layla

Reputation: 5486

printing variables in pl/sql

I have the following code:

DECLARE
   v_hire_date DATE:='30-Oct-2000';
   v_six_years BOOLEAN;  
BEGIN
IF MONTHS_BETWEEN(SYSDATE,v_fecha_contrato)/12 > 6 THEN
      v_six_years:=TRUE;
ELSE
      v_six_years:=FALSE;
END IF;
DBMS_OUTPUT.PUT_LINE('flag '||v_six_years);
END;

I want to print the value of the variable v_six_years, but I am getting the error:

ORA-06550: line 10, column 24:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 10, column 3

How to print the value of the variable v_six_years?

Upvotes: 21

Views: 119015

Answers (6)

builder-7000
builder-7000

Reputation: 7657

You can print the contents of a variable using the \echo meta-command. From man psql:

To retrieve the content of the variable, precede the name with a colon, for example:

testdb=> \echo :foo
bar

Upvotes: 0

Aniket Thakur
Aniket Thakur

Reputation: 69035

dbms_output.put_line is not overloaded to accept a boolean argument.Simple one line answer would be

dbms_output.put_line(case when v_six_years = true then 'true' else 'false' end );

Upvotes: 3

Ankur
Ankur

Reputation: 85

declare
     v_line varchar2(40); --declare
begin
     v_line := 'Happy new year'; --assigns
     dbms_output.put_line (v_line); --display 
end;

Happy new year

Upvotes: -4

mzzzzb
mzzzzb

Reputation: 1452

It seems you cannot concat varchar and boolean.

Define this function:

FUNCTION BOOLEAN_TO_CHAR(FLAG IN BOOLEAN)
RETURN VARCHAR2 IS
BEGIN
  RETURN
   CASE FLAG
     WHEN TRUE THEN 'TRUE'
     WHEN FALSE THEN 'FALSE'
     ELSE 'NULL'
   END;
END;

and use it like this: 

DBMS_OUTPUT.PUT_LINE('flag '|| BOOLEAN_TO_CHAR(v_six_years));

Upvotes: 12

Rocker
Rocker

Reputation: 91

You can use below to print Boolean Value in PLSQL

dbms_output.put_line('v_six_years '||  sys.diutil.bool_to_int(v_six_years));

Upvotes: 9

WoMo
WoMo

Reputation: 7266

PL/SQL does not have a literal to represent boolean values. You will have to either convert the v_six_years boolean value to a string, or not use a boolean if you wish to print the value. PL/SQL booleans are great for logic but useless if you wish to display the value.

DECLARE
   v_hire_date DATE:='30-Oct-2000';
   v_six_years VARCHAR2(1);  
BEGIN
IF MONTHS_BETWEEN(SYSDATE,v_fecha_contrato)/12 > 6 THEN
      v_six_years:='1';
ELSE
      v_six_years:='0';
END IF;
DBMS_OUTPUT.PUT_LINE('flag '||v_six_years);
END;

Or

DECLARE
   v_hire_date DATE:='30-Oct-2000';
   v_six_years BOOLEAN;  
   v_six_years_display VARCHAR2(5);
BEGIN
IF MONTHS_BETWEEN(SYSDATE,v_fecha_contrato)/12 > 6 THEN
      v_six_years:=TRUE;
      v_six_years_display := 'true';
ELSE
      v_six_years:=FALSE;
      v_six_years_display := 'false';
END IF;
DBMS_OUTPUT.PUT_LINE('flag '||v_six_years_display);
END;

Upvotes: 1

Related Questions