Click here to Skip to main content
15,877,939 members
Please Sign up or sign in to vote.
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
Updated 19-Jul-20 5:47am
v2
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.

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.
 
Share this answer
 
Look at the name of your procedure, and the name you use to call it.
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900