Home » SQL & PL/SQL » SQL & PL/SQL » Creating a function (Oracle 11g)
Creating a function [message #671882] Sat, 22 September 2018 04:50 Go to next message
Odigitrium
Messages: 2
Registered: September 2018
Junior Member
A little difficult to understand.
I often needs dynamic queries. And they should output tabular data.
Here is an example. Can anybody help to make a function on the basis of this query?

sysdate = must be variable.

select  
    next_day(sysdate - (level - 1) * 7, 'SUN' ) + 1 - 14 BEGIN_OF_WEEK  
   ,next_day(sysdate - (level - 1) * 7, 'SUN' )         END_OF_WEEK  
from dual  
connect by level <= 3  
order by begin_of_week;

That is, for example, function call
test('09-sep-19')
I read the documentation, but it's very difficult so far. It would be nice to understand on one simple example
Re: Creating a function [message #671883 is a reply to message #671882] Sat, 22 September 2018 07:23 Go to previous messageGo to next message
Odigitrium
Messages: 2
Registered: September 2018
Junior Member
I'll try but not enough knowledges to write simple function with variable instead sysdate in query.
It would be desirable to penetrate and understand on the elementary example to study something more difficult further independently.

CREATE OR REPLACE FUNCTION get_empnos2
  RETURN SYS_REFCURSOR
IS
  l_rc SYS_REFCURSOR;
BEGIN
  OPEN l_rc
   FOR select 
    next_day(sysday - (level - 1) * 7, 'SUN' ) + 1 - 14 BEGIN_OF_WEEK 
   ,next_day(sysday - (level - 1) * 7, 'SUN' )         END_OF_WEEK 
from dual
connect by level <= 3;
  RETURN l_rc;
END;
Re: Creating a function [message #671884 is a reply to message #671883] Sat, 22 September 2018 07:42 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Odigitrium wrote on Sat, 22 September 2018 07:23
I'll try but not enough knowledges to write simple function with variable instead sysdate in query.
It would be desirable to penetrate and understand on the elementary example to study something more difficult further independently.

CREATE OR REPLACE FUNCTION get_empnos2
  RETURN SYS_REFCURSOR
IS
  l_rc SYS_REFCURSOR;
BEGIN
  OPEN l_rc
   FOR select 
    next_day(sysday - (level - 1) * 7, 'SUN' ) + 1 - 14 BEGIN_OF_WEEK 
   ,next_day(sysday - (level - 1) * 7, 'SUN' )         END_OF_WEEK 
from dual
connect by level <= 3;
  RETURN l_rc;
END;
Well, aren't you going to need to provide a value for 'sysday' as in input parameter to the function?

https://docs.oracle.com/database/121/LNPLS/create_function.htm#LNPLS01370
Re: Creating a function [message #671885 is a reply to message #671882] Sat, 22 September 2018 07:47 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Also asked at https://community.oracle.com/thread/4173763
Re: Creating a function [message #671886 is a reply to message #671883] Sat, 22 September 2018 07:48 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I always start with something very simple, such as
pdby1>
pdby1> create function f1(p1 date) return date as begin
  2  return p1+1;
  3  end;
  4  /

Function created.

pdby1> select f1(to_date('2018-01-01','yyyy-mm-dd')) from dual;

F1(TO_DATE('2018-01
-------------------
2018-01-02:00:00:00

pdby1>
and then add the functionality I want one step at a time.
Re: Creating a function [message #672077 is a reply to message #671886] Mon, 01 October 2018 13:00 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
CREATE OR REPLACE FUNCTION Test_fun(P_date IN VARCHAR2)
    RETURN SYS_REFCURSOR
IS
    Ret_cur   SYS_REFCURSOR;
BEGIN
    OPEN Ret_cur FOR
        SELECT NEXT_DAY(TO_DATE(P_date, 'DD-MON-RR') - (LEVEL - 1) * 7, 'SUN'
               ) + 1 - 14
                   Begin_of_week,
               NEXT_DAY(TO_DATE(P_date, 'DD-MON-RR') - (LEVEL - 1) * 7,
                        'SUN')
                   End_of_week
        FROM DUAL
        CONNECT BY LEVEL <= 3
        ORDER BY Begin_of_week;

    RETURN Ret_cur;
END Test_fun;
/

[Updated on: Mon, 01 October 2018 13:00]

Report message to a moderator

Re: Creating a function [message #672078 is a reply to message #672077] Mon, 01 October 2018 14:08 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
NEXT_DAY is NLS dependent function:

SQL> select next_day(sysdate,'SUN') from dual;

NEXT_DAY(
---------
07-OCT-18

SQL> alter session set nls_language='turkish';

Session altered.

SQL> select next_day(sysdate,'SUN') from dual;
select next_day(sysdate,'SUN') from dual
                        *
ERROR at line 1:
ORA-01846: gecerli bir hafta gunu de?il


SQL> 

It is better to use IW format:

SQL> select trunc(sysdate + 1,'IW') + 6 from dual;

TRUNC(SYS
---------
07-EKI-18

SQL> 

SY.
Previous Topic: PL/SQL: could not find program unit being called when calling packages
Next Topic: regular expression substring to get the string that is delimited by a comma
Goto Forum:
  


Current Time: Fri Mar 29 07:49:04 CDT 2024