OraFAQ Forum: SQL & PL/SQL » Creating a function

Home » SQL & PL/SQL » SQL & PL/SQL » Creating a function (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Creating a function [message #671882]

Sat, 22 September 2018 04:50

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

Report message to a moderator

Re: Creating a function [message #671883 is a reply to message #671882]

Sat, 22 September 2018 07:23

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;

Report message to a moderator

Re: Creating a function [message #671884 is a reply to message #671883]

Sat, 22 September 2018 07:42

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

Report message to a moderator

Re: Creating a function [message #671885 is a reply to message #671882]

Sat, 22 September 2018 07:47

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Also asked at https://community.oracle.com/thread/4173763

Report message to a moderator

Re: Creating a function [message #671886 is a reply to message #671883]

Sat, 22 September 2018 07:48

John Watson
Messages: 8836
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.

Report message to a moderator

Re: Creating a function [message #672077 is a reply to message #671886]

Mon, 01 October 2018 13:00

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

Solomon Yakobson
Messages: 3227
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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 28 16:55:47 CDT 2023