1 2 Previous Next 18 Replies Latest reply: Jul 24, 2012 2:47 AM by BluShadow Go to original post RSS
      • 15. Re: Problem with ref cusror date data type.
        BluShadow
        user7540156 wrote:
        i am trying to execute the below code.
        but it's returning date only.
        i want date and time.
        declare
        type r_cursor is REF CURSOR;
        c_emp r_cursor;
        en date;
        begin
        open c_emp for select sysdate from dual;
        loop
        fetch c_emp into en;
        exit when c_emp%notfound;
        dbms_output.put_line(en);
        end loop;
        close c_emp;
        end;

        O/P:25-JAN-12

        i want output as date with time.
        That has absolutely nothing to do with ref cursors.

        a) you should declare ref cursors using the inbuilt type SYS_REFCURSOR. There's absolutely no need to decalre your own type for it.
        b) the display format of your date is exactly that, a display format issue.
        SQL> declare
          2    c_emp SYS_REFCURSOR;
          3    en    date;
          4  begin
          5    open c_emp for select sysdate from dual;
          6    loop
          7      fetch c_emp into en;
          8      exit when c_emp%notfound;
          9      dbms_output.put_line(en);
         10    end loop;
         11    close c_emp;
         12  end;
         13  /
        25-JAN-12
        
        PL/SQL procedure successfully completed.
        The date variable is being implicitly converted to a varchar2 datetype by the put_line statement which is expecting a varchar2 input parameter. This implicit conversion is being done based on your sessions NLS_DATE_FORMAT setting.

        You can either change your sessions NLS_DATE_FORMAT...
        SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
        
        Session altered.
        
        SQL> declare
          2    c_emp SYS_REFCURSOR;
          3    en    date;
          4  begin
          5    open c_emp for select sysdate from dual;
          6    loop
          7      fetch c_emp into en;
          8      exit when c_emp%notfound;
          9      dbms_output.put_line(en);
         10    end loop;
         11    close c_emp;
         12  end;
         13  /
        25-JAN-2012 09:29:13
        
        PL/SQL procedure successfully completed.
        or you can explicitly convert the date to a varchar2 in your code using the to_char function, specifying the display format you want...
        SQL> ed
        Wrote file afiedt.buf
        
          1  declare
          2    c_emp SYS_REFCURSOR;
          3    en    date;
          4  begin
          5    open c_emp for select sysdate from dual;
          6    loop
          7      fetch c_emp into en;
          8      exit when c_emp%notfound;
          9      dbms_output.put_line(to_char(en,'DD-MM-YYYY HH24:MI'));
         10    end loop;
         11    close c_emp;
         12* end;
        SQL> /
        25-01-2012 09:29
        
        PL/SQL procedure successfully completed.
        As I said, absolutely nothing to do with ref cursors and all to do with a complete lack of understanding of basic datatypes and implicit conversions.
        • 16. Re: PL/SQL 101 : Understanding Ref Cursors
          SamFisher
          Good one, Blue.

          Thx.
          • 17. Re: PL/SQL 101 : Understanding Ref Cursors
            gaverill
            Nicely done. I would suggest adding a section with a "pipelined" version of your "populate_emps" table function, as often there's no need to fully materialize the table...
            SQL> ed
            Wrote file afiedt.buf
             
              1  create or replace function /*populate*/pipe_emps(deptno in number := null)
              2  return t_emptype pipelined is
              3    -- v_emptype t_emptype := t_emptype();  -- Declare a local table structure and initialize it
              4    -- v_cnt     number := 0;
              5    v_rc      sys_refcursor;
              6    v_empno   number;
              7    v_ename   varchar2(10);
              8    v_mgr     number;
              9    v_sal     number;
             10  begin
             11    v_rc := get_dept_emps(deptno);
             12    loop
             13      fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
             14      exit when v_rc%NOTFOUND;
             15      --v_emptype.extend;
             16      --v_cnt := v_cnt + 1;
             17      /*v_emptype(v_cnt) :=*/ pipe row ( emptype(v_empno, v_ename, v_mgr, v_sal) );
             18    end loop;
             19    close v_rc;
             20    return /*v_emptype*/;
             21* end;
            SQL> / 
             
            Function created.
            Gerard
            • 18. Re: PL/SQL 101 : Understanding Ref Cursors
              BluShadow
              gaverill wrote:
              Nicely done. I would suggest adding a section with a "pipelined" version of your "populate_emps" table function, as often there's no need to fully materialize the table...
              Thanks for the suggestion, however pipelining the data is beyond the 101 basics of ref cursors which is what the article is designed to demonstrate. ;)
              1 2 Previous Next