Take the Challenge!

Take the Challenge!

http://www.oracle.com/technetwork/issue-archive/2012/12-jan/o12plsql-1408561.html

Each PL/SQL 101 article offers a quiz to test your knowledge of the information provided in the article. The quiz questions are shown below and also at PL/SQL Challenge (plsqlchallenge.com), a Website that offers online quizzes for the PL/SQL language. You can read and take the quiz here in Oracle Magazine and then check your answers in the next issue. If, however, you take the quiz at PL/SQL Challenge, you will be entered into a raffle to win an e-book from O’Reilly Media (oreilly.com).

Question 1

Oracle Database provides a function for returning the date of the last day of the month. It does not, however, provide a function for returning the date of the first day. Which of the following can be used to do this?

1. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
      RETURN DATE
   IS
   BEGIN
      RETURN TRUNC (date_in);
   END;
   /


2. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
      RETURN DATE
   IS
   BEGIN
      RETURN TRUNC (date_in, 'MM');
   END;
   /


3. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
      RETURN DATE
   IS
   BEGIN
      RETURN TRUNC (date_in, 'MONTH');
   END;
   /


4. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
      RETURN DATE
   IS
   BEGIN
      RETURN TO_DATE (TO_CHAR (date_in, 'YYYY-MM')
   || '-01', 'YYYY-MM-DD');
   END;
   /

Question 2
Given this declaration section:

DECLARE
   c_format   CONSTANT VARCHAR2 (22)
      := 'YYYY-MM-DD HH24:MI:SS' ;
   l_new_year          DATE
      := TO_DATE (
            '2012-01-02 00:00:01'
          ,  c_format);
which of the following blocks offers an exception section so that after that block is executed, the date and time 2012-01-01 00:00:01 will be displayed on the screen?

1.  BEGIN
      DBMS_OUTPUT.put_line (
         TO_CHAR (
            l_new_year - 24
          ,  c_format));
   END;
2. BEGIN
      DBMS_OUTPUT.put_line (
         TO_CHAR (l_new_year - 1
                ,  c_format));
   END;
3. BEGIN
      DBMS_OUTPUT.put_line (
         TO_CHAR (
              l_new_year
            - 24 * 60 * 60
          ,  c_format));
   END;
4. BEGIN
      DBMS_OUTPUT.put_line (
         TO_CHAR (
              TRUNC (l_new_year)
            - 1
            + 1 / (24 * 60 * 60)
          ,  c_format));
   END;