1 2 Previous Next 17 Replies Latest reply: Dec 27, 2012 10:56 AM by EdStevens RSS

    Plsql

    858631
      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
          but while i try to execute it fails
          No error message or number posted.
          • 2. Re: Plsql
            kendenny
            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
              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
                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
                  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
                    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
                      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
                        Hi Arild

                        While I execute I have given correct spelling only DECLARE
                        but even then it show the same error
                        • 9. Re: Plsql
                          EdStevens
                          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
                            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
                              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
                                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
                                  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
                                    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