1 2 Previous Next 17 Replies Latest reply: Dec 27, 2012 10:56 AM by EdStevens Go to original post RSS
      • 15. Re: Plsql
        You should be learning on Oracle 11g. We have no idea what book you're reading, except that it is probably very old.

        Look up explicit vs implicit conversion. To write robust code you must go EXPLICIT, that is to say you should use TO_DATE, TO_CHAR etc when
        converting between data types, assigning a value to a variable, comparing values and so on. Never rely on implicit conversions in PL/SQL code.
        • 16. Re: Plsql
          Hi Arild

          Thanks for your valuable advice , I just learning from oracle 9i , Can you Plz suggest me any pdf output link were i can download and learn oracle plsql 11g

          Thanks for helping me out in learning

          • 17. Re: Plsql
            Suresh*** wrote:
            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
            Yes, you can see I've already been corrected and acknowledged that correction.
            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
            I don't understand your question.
            Anything enclosed in single quotes is considered a string (ie: varachar2). When you assign a string (like '12-Dec-2005') to a date (like v_Hire_date date) that string has to be converted to a DATE data type. If you don't use the to_date function to do it, oracle will implicitly make the conversion, and will will have to make some assumptions about the format of the string. Remember, a string is a string is a string. The string '12-Dec-2005' is fundamentally no different than the string 'heres your sign'. So if you think the string represents a date, oracle has to have some rules about what part of the string represents the year, the month, the day of the month, possibly the hours, minutes, and seconds. If you don't give it the rules via the to_date function, it will have to fall back to the current setting of NLS_DATE_FORMAT, which may or my not correctly describe the string you supplied. So you should always use explicit conversion functions. See http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/ for a more thorough discussion.

            v_Hire_date date :=to_date ('12-Dec-2010','DD-MON-YYYY');
            v_five_years BOOLEAN;
            IF Months_between(sysdate,v_Hire_date)/12 > 5 then 
            v_five_years := True;
            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));
            1 2 Previous Next