This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Dec 27, 2012 8:56 AM by EdStevens RSS

Plsql

858631 Newbie
Currently Being Moderated
Hi

For Just learning purpose This is an example found in text book but while i try to execute it fails
I am trying to set boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
DELARE
v_Hire_date date :='12-Dec-2005';
v_five_years BOOLEAN;

BEGIN
IF 

Months_between(to_date(sysdate,'DD-MON-YYYY'),:v_Hire_date)/12 > 5 then 
 v_five_years := True;
ELSE
 v_five_years := False;
END IF;

Dbms_output.put_line (v_five_years);

END;
Thanks
Suresh
  • 1. Re: Plsql
    SomeoneElse Guru
    Currently Being Moderated
    but while i try to execute it fails
    No error message or number posted.
  • 2. Re: Plsql
    kendenny Expert
    Currently Being Moderated
    Boolean is not a valid parameter type for dbms_output.put_line. You must convert it to a string
    dbms_output.put_line(CASE WHEN v_five_years THEN 'True' ELSE 'False' END);
  • 3. Re: Plsql
    858631 Newbie
    Currently Being Moderated
    I get
    Error 06550 - V_FIVE_YEARS must be declared
    But i have already declared it even i have checked semi colon and all i have doubt
    here
    Months_between(to_date(sysdate,'DD-MON-YYYY'),:v_Hire_date)/12 > 5

    Thanks
  • 4. Re: Plsql
    971895 Journeyer
    Currently Being Moderated
    It prints number or date ONLY because there is an implicit conversion to VARCHAR2 which does not exist with boolean datatype.
    Boolean data type is actually used for logical comaprison.
  • 5. Re: Plsql
    971895 Journeyer
    Currently Being Moderated
    Try like...
    DECLARE
    v_Hire_date date :='12-Dec-2005';
    v_five_years BOOLEAN;
     
    BEGIN
    IF Months_between(to_date(sysdate,'DD-MON-YYYY'),v_Hire_date)/12 > 5 then 
     v_five_years := True;
    ELSE
     v_five_years := False;
    END IF;
     
    dbms_output.put_line( sys.dbms_sqltcb_internal.i_convert_from_boolean(v_five_years));
    
    END;
  • 6. Re: Plsql
    Arild Explorer
    Currently Being Moderated
    It is normally spelled DE C LARE ...

    Suresh*** wrote:
    Hi

    For Just learning purpose This is an example found in text book but while i try to execute it fails
    I am trying to set boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
    DELARE
    v_Hire_date date :='12-Dec-2005';
    v_five_years BOOLEAN;
    .....
    Edited by: Arild on Dec 27, 2012 5:47 AM
  • 7. Re: Plsql
    858631 Newbie
    Currently Being Moderated
    Hi
    Now i get this error :
    PLS-00201: identifier 'SYS.DBMS_SQLTCB_INTERNAL' must be declared

    I using Toad Tool :9.7.2.5
    DECLARE
    v_Hire_date date :='12-Dec-2005';
    v_five_years BOOLEAN;
     
    BEGIN
    IF Months_between(to_date(sysdate,'DD-MON-YYYY'),v_Hire_date)/12 > 5 then 
     v_five_years := True;
    ELSE
     v_five_years := False;
    END IF;
    --dbms_output.put_line(v_five_year); 
    dbms_output.put_line( sys.dbms_sqltcb_internal.i_convert_from_boolean(v_five_years));
     
    END;
  • 8. Re: Plsql
    858631 Newbie
    Currently Being Moderated
    Hi Arild

    While I execute I have given correct spelling only DECLARE
    but even then it show the same error
  • 9. Re: Plsql
    EdStevens Guru
    Currently Being Moderated
    Suresh*** wrote:
    Hi

    For Just learning purpose This is an example found in text book but while i try to execute it fails
    I am trying to set boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
    DELARE
    v_Hire_date date :='12-Dec-2005';
    v_five_years BOOLEAN;
    
    BEGIN
    IF 
    
    Months_between(to_date(sysdate,'DD-MON-YYYY'),:v_Hire_date)/12 > 5 then 
    v_five_years := True;
    ELSE
    v_five_years := False;
    END IF;

    Dbms_output.put_line (v_five_years);

    END;
    Thanks 
    Suresh
    v_Hire_date date :='12-Dec-2005';
    When you initialize v_Hire_date you are assigning a string to a date, forcing oracle to make an assumption (and we all know what 'assume' spells) about the format of the string. That line should read
    v_Hire_date date :=to_date('12-Dec-2005',dd-Mon-yyyy);
    v_five_years BOOLEAN;
    there is no BOOLEAN data type in oracle
    Months_between(to_date(sysdate,'DD-MON-YYYY'),:v_Hire_date)/12 > 5 then
    sysdate is already a DATE datatype. Why are you applying to_date to it? You are just forcing oracle to convert it to a string so that it can convert it back to a date.
  • 10. Re: Plsql
    Arild Explorer
    Currently Being Moderated
    EdStevens wrote:
    there is no BOOLEAN data type in oracle
    There is certainly a BOOLEAN datatype in PL/SQL, only not in Oracle SQL.

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/datatypes.htm

    Edited by: Arild on Dec 27, 2012 6:15 AM
  • 11. Re: Plsql
    Arild Explorer
    Currently Being Moderated
    Hi, try
    DECLARE
       v_hire_date    DATE := TO_DATE ('12-Dec-2005', 'dd-mon-yyyy');
       v_five_years   BOOLEAN;
    BEGIN
       IF (MONTHS_BETWEEN (SYSDATE, v_hire_date) / 12 > 5) = TRUE
       THEN
          v_five_years := TRUE;
       ELSE
          v_five_years := FALSE;
       END IF;
       DBMS_OUTPUT.put_line (CASE WHEN v_five_years = TRUE THEN 'TRUE...' ELSE 'FALSE!!' END);
    END;
    or just
    DECLARE
       v_hire_date    DATE := TO_DATE ('12-Dec-2005', 'dd-mon-yyyy');   
    BEGIN
       DBMS_OUTPUT.put_line (CASE WHEN (MONTHS_BETWEEN (SYSDATE, v_hire_date) / 12 > 5) = TRUE THEN 'TRUE...' ELSE 'FALSE!!' END);
    END;
  • 12. Re: Plsql
    981337 Newbie
    Currently Being Moderated
    Hi,

    As Boolean cannot be printed using dbms_output.put();

    Please create a small procedure to handle/print boolean results.

    CREATE PROCEDURE print_boolean (b BOOLEAN)
    AS
    BEGIN
    CASE
    WHEN b IS NULL THEN DBMS_OUTPUT.PUT_LINE('Unknown');
    WHEN b THEN DBMS_OUTPUT.PUT_LINE('Yes');
    WHEN NOT b THEN DBMS_OUTPUT.PUT_LINE('No');
    END CASE;
    END;
    /

    DECLARE
    v_Hire_date date :='12-Dec-2005';
    v_five_years BOOLEAN;
    BEGIN
    IF Months_between(v_Hire_date,to_date(sysdate,'DD-MON-YYYY'))/12 > 5 then
    print_boolean(TRUE);
    ELSE
    print_boolean(FALSE);
    END IF;
    --dbms_output.put_line(v_five_year);
    --dbms_output.put_line( sys.dbms_sqltcb_internal.i_convert_from_boolean(v_five_years));
    END;

    Thanks!
  • 13. Re: Plsql
    EdStevens Guru
    Currently Being Moderated
    Arild wrote:
    EdStevens wrote:
    there is no BOOLEAN data type in oracle
    There is certainly a BOOLEAN datatype in PL/SQL, only not in Oracle SQL.

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/datatypes.htm

    Edited by: Arild on Dec 27, 2012 6:15 AM
    I stand corrected. Thanks.
  • 14. Re: Plsql
    858631 Newbie
    Currently Being Moderated
    Hi ALL

    Thanks For all your Reply which helps me in learning ....this below code works fine
    Hi EdStevens

    Your saying there is no BOOLEAN data type in oracle

    But in the text book Oracle 9i
    I found Base scalar data type -- char , varchar2, long ,long Ram, Number, Binary_Interger,pls_integer,BOOLEAN

    And this Example I found in Chapter 4 - Control structure

    And One more doubt
    Here i have passed like this ... v_Hire_date date :='12-Dec-2005';

    I learnt Date and Char must be passed with single quotation in the book also they have passed with single quotes?
    Please advice me

    DECLARE
    v_Hire_date date :=to_date ('12-Dec-2010','DD-MON-YYYY');
    v_five_years BOOLEAN;
     
    BEGIN
    IF Months_between(sysdate,v_Hire_date)/12 > 5 then 
    v_five_years := True;
    ELSE
     v_five_years := False;
    END IF;
    --Dbms_output.put_line (v_five_years);
    dbms_output.put_line(CASE WHEN v_five_years THEN 'True' ELSE 'False' END);
    --dbms_output.put_line( sys.dbms_sqltcb_internal.i_convert_from_boolean(v_five_years));
     
    END;
    Thanks
    Suresh
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points