is this function true PL/SQL ?

Question

Hello,

is it used in the correct form in PL/SQL ?

SELECT ename
        ,hiredate
       , TO_CHAR(TRUNC(ADD_MONTHS(hiredate,6),'MONTH'),'Le DD MONTH YYYY') AS "Révision" 
FROM emp

thx

Answer 1

If you mean if it's SQL99 compliant or SQL92, use the validator http://developer.mimer.com/validator/parser99/index.tml

Answer 2

It isn't PL/SQL (which is Oracle's procedural extension to SQL).

ADD_MONTHS is, I believe, Oracle specific SQL.

Not sure whether using TRUNC to remove the day in month portion of a date/time is also Oracle specific.

Answer 3

That statement is valid Oracle SQL: you are adding six months to the hiredate and then returning the first day of that month in a specific format.

However, if you want to use it in PL/SQL you need to return it into some variable(s):

declare
    l_ename emp.ename%type;
    l_hiredate emp.hiredate%type;
    l_revision varchar2(20);
begin
    SELECT ename
        ,hiredate
       , TO_CHAR(TRUNC(ADD_MONTHS(hiredate,6),'MONTH'),'Le DD MONTH YYYY') 
    INTO l_ename
         , l_hiredate
         , l_revision
    FROM emp;
end;

Of course, it is likely you have more than one record in your EMP table. So you either need to restrict the query to return a single row, or you need to use a variable capable of handling multiple rows such as a PL/SQL collection or a cursor:

....
for emp_recs in ( SELECT ename
                          ,hiredate
                          , TO_CHAR(TRUNC(ADD_MONTHS(hiredate,6),'MONTH'),'Le DD MONTH YYYY') 
                  FROM emp )
loop
    .....

Answer 4

It may not be PL/SQL but it is (almost) valid Oracle SQL. The only problem I can see is that the 'Le' in the in format model needs to be quoted for it to work as this is just random text that Oracle would otherwise try to find some meaning for.

SELECT
  ename, 
  hiredate, 
  TO_CHAR(TRUNC(ADD_MONTHS(hiredate,6),'MONTH'),'"Le" DD MONTH YYYY') 
FROM emp