FUNCTION in WHERE clause of SQL query

Question

I have the following (private) PL/SQL function (inside PACKAGE) returning the previous month in 'YYYYMM' format:

FUNCTION GET_PREV_MONTH RETURN VARCHAR2 IS
BEGIN
  RETURN TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM');
END GET_PREV_MONTH;

I need to compare if records are from previous month in several my queries so I wanted to make private function returning previous month. Why I cannot use this return value from GET_PREV_MONTH() function in WHERE clause of my other SQL queries like:

UPDATE mytable t SET t.col=1
WHERE TO_CHAR(t.created,'YYYYMM')=GET_PREV_MONTH();

Oracle says PLS_00231: function 'GET_PREV_MONTH' may not be used in SQL.

If I create this, this works!

UPDATE mytable t SET t.col=1
WHERE TO_CHAR(t.created,'YYYYMM')=TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM');

Don't compare dates in string form, compare dates as dates. If the column is a date and it is indexed you will lose the ability to make use of the index. WHERE t.created >= TRUNC(ADD_MONTHS(SYSDATE,-1)) AND t.created < TRUNC(ADD_MONTHS(SYSDATE,-1)) + 1 — unleashed
– unleashed, Commented May 4, 2017 at 14:28
You are correct, to cover the entire month, adjust the from and to dates like the following WHERE t.created >= TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') -- greater than the start of the previous month AND t.created < TRUNC(SYSDATE, 'MON') -- less than the start of the current month — unleashed
– unleashed, Commented May 5, 2017 at 14:49

MT0 · Accepted Answer · 2017-05-04 10:09:44Z

1

I wanted to make private function returning previous month. Why I cannot use this return value from GET_PREV_MONTH() function in WHERE clause of my other SQL queries

It is a private function - you cannot reference it outside the package.

If you want to use it in SQL then declare it in the package specification so that it is public.

This is private function of this package! This function is used in procedures from the same package. It is not referenced outside this package.

It CANNOT be used in the SQL context if it is not a public function; regardless of whether the SQL is being called from inside or outside the same package.

edited May 4, 2017 at 10:09

answered May 4, 2017 at 9:36

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thomas G Over a year ago

To expand on your answer : stackoverflow.com/questions/16130165/…

sbrbot Over a year ago

This is private function of this package! This function is used in procedures from the same package. It is not referenced outside this package.

MT0 Over a year ago

@sbrbot That makes no difference. You can use it in a PL/SQL context within the same package but if you are using it in an SQL context then the function must be public (regardless of whether that SQL context is instantiated from a PL/SQL context inside the package).

sbrbot Over a year ago

OK, thanks, I was not aware of different contexts (PL/SQL and SQL contexts). I will check this now.

Collectives™ on Stack Overflow

FUNCTION in WHERE clause of SQL query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related