How do I get all records with birthday falling today in oracle

Question

0.00/5 (No votes)

See more:

I am getting a syntax error with a PL/SQL stored procedure :

SQL

Errors: check compiler log
Error starting at line : 1 in command -
exec GET_BIRTHDAYS
Error report -
ORA-06550: line 1, column 7:
PLS-00905: object SYS.GET_BIRTHDAYS is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The requirement is to query a Oracle table for all records where the customer's birthday falls today i.e current date. For all returned rows i then want to grab the associated email address then use it elsewhere in my application.

This is my stored procedure :

SQL

create or replace PROCEDURE GET_BIRTHDAYS(returned_Email OUT VARCHAR2) AS   
   DECLARE 
   currentSystemDate VARCHAR2(128 Byte);
    DECLARE 
    custBirthday VARCHAR2(128 Byte);
     DECLARE 
    custEmail VARCHAR2(128 Byte);
BEGIN

SELECT into currentSystemDate TO_CHAR(SYSDATE,'DD-MON')  FROM dual
SELECT INTO custBirthday to_char(DATE_OF_BIRTH,'DD-MON') FROM STTM_CUST_PERSONAL WHERE DATE_OF_BIRTH=currentSystemDate
SELECT INTO custEmail E_MAIL FROM STTM_CUST_PERSONAL WHERE DATE_OF_BIRTH=custBirthday
returned_Email:= custEmail 
END GET_BIRTHDAYS;

Just for clarity in the table

STTM_CUST_PERSONAL

the DATE_OF_BIRTH is stored as 16-JUL-2020, 13-MAR-1964 etc

What am i missing?

This is my first time creating Stored Procedures with Oracle Database and i think there's some subtle things im missing.

What I have tried:

The syntax error pops up precisely on the line :

DECLARE 
   currentSystemDate VARCHAR2(128 Byte);

for the datatype declaration.

I have already verified with separate queries that i can today's date with :

SELECT TO_CHAR(SYSDATE,'DD-MON')  FROM dual

and i can get the comparison date component from the primary table using :

SELECT to_char(DATE_OF_BIRTH,'DD-MON') FROM STTM_CUST_PERSONAL

Posted 19-Jul-20 3:43am

Tshumore

Updated 19-Jul-20 5:47am

v2

Add a Solution

Comments

[no name] 19-Jul-20 10:45am

Should it not be

   
...
DECLARE 
   currentSystemDate VARCHAR2(128 Byte);
    custBirthday VARCHAR2(128 Byte);
    custEmail VARCHAR2(128 Byte);
...

Richard MacCutchan 19-Jul-20 12:30pm

That could be the answer.

[no name] 19-Jul-20 12:59pm

The more oracle documentaries i read the more confused i get. I'm not so sure anymore if the above is the problem :-)

Richard MacCutchan 19-Jul-20 13:00pm

I could not find anything useful in the Oracle documentation, but I did see some examples in answers on SO.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2020-07-19T03:51:00

Solution 1

Look at the name of your procedure, and the name you use to call it.

Posted 19-Jul-20 3:51am

Richard MacCutchan

Comments

Tshumore 19-Jul-20 10:09am

Sorry thats a typo , i have amended with the appropriate error block.

Richard MacCutchan 19-Jul-20 10:27am

Why are you using character strings as dates? Use proper Date or DateTime types so your code can do calculations and comparisons correctly.

As to the error message, it is not clear to me what it is complaining about. But see the comments at http://www.dba-oracle.com/t_pls_00905_object_is_invalid.htm[^].

Tshumore 19-Jul-20 10:30am

Just to clarify there is an error when i actually created the SP. The error was on the line
DECLARE
currentSystemDate VARCHAR2(128 Byte); (red line on VARCHAR2 which says Syntax error.Partially recognized rules (railroad diagrams))

Richard MacCutchan 19-Jul-20 12:29pm

I think the suggestion above by 0x01AA may be the answer. I cannot find the railroad diagram for the DECLARE statement.

Patrice T · Answer 2 · 2020-07-19T05:47:00

Quote:
How do I get all records with birthday falling today in oracle

Not your actual problem, but another approach.
Lets guess the procedure will be run everyday, if the table is about 20 people, no matter how you do, the workload will be about the same, if table handle millions of people, the way to do the query matters.
Your approach : runtime is linear with number of people to handle, with some computing for every people in table, the SQL server can't do any optimization.
The other approach: make a field 'DayOfBirth' containing only day and month of birth as a string, the field is constant after creation (just like date of birth), make the field indexed.
The difference: Runtime depend on number of people with the birth today, which is roughly number of people/365, with no computing for other people, The SQL server take advantage of index, thus the acceleration.

Advice: Rather than Email, return people Id in table, because you may want to personalize the Email with FirstName and Age.

Dear [FirstName],
We want to be first to whish you an happy [Age]th birthday.