Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Help Needed. (SQL, PL/SQL)
| Procedure Help Needed. (SQL, PL/SQL) [message #208232] |
Fri, 08 December 2006 13:38  |
OlyN
Messages: 5
Registered: December 2006
|
Junior Member |
|
|
Hey guys. Ive used SQL before but admittingly im not the greatest at it so I was hoping to get some help. Ive checked the last few pages or so to see if there was a similar situation but couldnt find one, so any help would be most definately appreciated.
Basically ive been attempting to write a procedure for my system to display a birthday card whenever its a students birthday. I created the inserts (the whole of my system can be seen via the attachment)
For student table I put:
| Quote: | CREATE Table Asg_Student(
Student_Id integer,
Student_Name Varchar2(20),
Date_of_Birth Date,
Address Varchar2(30)
);
ALTER TABLE Asg_Student add CONSTRAINT Asg_Student_Id_pk primary
key(Student_Id);
create Sequence Student_Id_seq
increment by 1
start with 100;
Insert into Asg_Student values(1,'Mary Moses', '08-DEC-85','28 Landsdown Road');
Insert into Asg_Student values(2,'Angela Aseye', '08-DEC-82','54 Tiger Lane');
Insert into Asg_Student values(3,'John Doe', '17-OCT-83','187 Stamford Road');
Insert into Asg_Student values(4,'Homer Simpson', '12-JAN-85','74 Lion Way');
|
Ive created other tables. I then created the procedure:
| Quote: | set serveroutput on
CREATE OR REPLACE PROCEDURE happy_birthday
IS
Id Asg_student.Student_id%Type;
Name Asg_student.Student_name%Type;
CURSOR Cur_Birthday IS SELECT student_id,Student_name FROM Asg_Student
WHERE TO_CHAR(Date_of_birth,'DD-MMM-YY') = TO_CHAR(SYSDATE,'DD-MMM-YY');
BEGIN
FOR Cur_rec IN Cur_Birthday
LOOP
DBMS_OUTPUT.PUT_LINE(Cur_rec.Student_id);
DBMS_OUTPUT.PUT_LINE(Cur_rec.Student_name);
DBMS_OUTPUT.PUT_LINE('HAPPY BIRTHDAY TO YOU');
END LOOP;
END;
/
|
When I entered the procedure into Oracle, the only message I got was
"Procedure created."
I was wondering if there was a way for the system to automatically print the card? Or if I made any mistakess? (If so what?)
The first two inserts have the DOB as todays date (8th Dec) but the system doesnt really produce anything for me.
Also if I update the system (add in another insert with the date of birth matching the systemdate) I want the system to produce a birthday card for him.
EG: If I add,
| Quote: | Insert into Asg_Student values(9,'Bart Simpson', '08-DEC-80','742 Evergreen Terrace');
|
then a card should have his name and such.
I get the same message for my other procedures so I was hoping to get help for this one first, so hopefully I can sort out the other ones.
Thanks in advance if anybody can help (or attempt to help) me.
PS: Im using Oracle SQL* Plus - Version 9.2
-
Attachment: SQL.sql
(Size: 18.26KB, Downloaded 469 times)
|
|
|
|
| Re: Procedure Help Needed. (SQL, PL/SQL) [message #208244 is a reply to message #208232] |
Fri, 08 December 2006 15:08  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Some things to help you.
Using dbms_output in procedures is not a good idea as you will only see the output if you run it from a SQL prompt.
You'll want to run the procedure using DBMS_JOB or a cron job daily to check for the birthdays.
Most importantly, use DATES in DATE columns, not character strings. '08-DEC-85' might be a implicitly converted correctly for you, but If I ran your code, notifying this person of their birthday would be moot, as they would have died over 1900 years ago.
Do not convert a DATE to character to compare it to a charatcer string. Just compare the trunc(DATE column) to TRUNC(sysdate), which is a date itself.
|
|
|
|
| Re: Procedure Help Needed. (SQL, PL/SQL) [message #208257 is a reply to message #208244] |
Fri, 08 December 2006 20:10 |
OlyN
Messages: 5
Registered: December 2006
|
Junior Member |
|
|
Thanks Joy. I just got home and had to try it out. Ive been using books and the net to learn how to do this (manipulating code examples), so im quite bad at this. But alas:
I tried to change the date format in the table inserts using date not characters (12 instead of Dec) as you said, but I got 'Not valid month' response.
Ie. - My change and response:
| Quote: | SQL> Insert into Asg_Student values(1,'Mary Moses', '09/12/1985','28 Landsdown Road');
Insert into Asg_Student values(1,'Mary Moses', '09/12/1985','28 Landsdown Road');
*
ERROR at line 1:
ORA-01843: not a valid month
|
Is this a huge mistake on my part (and not what you wanted/recommended) or did I type it incorrectly?
I also changed the procedure based on your reply. Im not sure if I understood you correctly but basically my procedure was changed to (Bold parts changed):
| Quote: |
set serveroutput on
CREATE OR REPLACE PROCEDURE happy_birthday
IS
Id Asg_student.Student_id%Type;
Name Asg_student.Student_name%Type;
CURSOR Cur_Birthday IS SELECT student_id,Student_name FROM Asg_Student
WHERE trunc(Date_of_birth) = TRUNC(SYSDATE);
BEGIN
FOR Cur_rec IN Cur_Birthday
LOOP
DBMS_JOB.PUT_LINE(Cur_rec.Student_id);
DBMS_JOB.PUT_LINE(Cur_rec.Student_name);
DBMS_JOB.PUT_LINE('HAPPY BIRTHDAY TO YOU');
END LOOP;
END;
/
|
The message I get is:
"Warning: Procedure created with compilation errors."
Im guessing it has to do with the DBMS_Job part and me not typing the command in correctly? (I havent seen/learnt about the DBMS_JOB or a cron job daily so that could be my shortcoming)
Again, thanks for your time/help.
|
|
|
|
| Re: Procedure Help Needed. (SQL, PL/SQL) [message #208259 is a reply to message #208257] |
Fri, 08 December 2006 20:43 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Ohh, I think you misunderstood.
DBMS_JOB is used to schedule jobs; it is not used the way you have done so in a procedure. You'd want to use it to schedule your procedure to run daily at say noon, or some other time of your choice.
And when I said store DATES in DATE column, I meant you need to explicity use the TO_DATE function as such:
to_date('08-DEC-85','DD-MON-RR')
or
to_date('09/12/1985','MM/DD/YYYY')
Additionally, you can type show err after you compile a procedure to see exactly where your errors are.
You're making a good effort. Any more question just please post them here. Search for DBMS_JOB.
|
|
|
|
|
|
| Re: Procedure Help Needed. (SQL, PL/SQL) [message #208327 is a reply to message #208300] |
Sat, 09 December 2006 10:13 |
OlyN
Messages: 5
Registered: December 2006
|
Junior Member |
|
|
@ ab_Trivedi: - When I type
"exec happy_birthday;" the message I get is "PL/SQL procedure successfully completed." rather than the actual results.
And anyway, my system is intended to basically be done itself (daily) rather than the procedure being executed manually.
Cheers for the help though.
@joy_division:
I changed the insert back to this format:
| Quote: | Insert into Asg_Student values(1,'Mary Moses', '09-DEC-85','28 Landsdown Road');
|
I then changed the procedure (using to_date) to:
| Quote: | set serveroutput on
/
CREATE OR REPLACE PROCEDURE happy_birthday
IS
Id Asg_student.Student_id%Type;
Name Asg_student.Student_name%Type;
CURSOR Cur_Birthday IS SELECT student_id,Student_name FROM Asg_Student
WHERE TO_DATE(Date_of_birth,'DD-MON-YY') = TO_DATE(SYSDATE, 'DD-MON-YY');
BEGIN
FOR Cur_rec IN Cur_Birthday
LOOP
DBMS_OUTPUT.PUT_LINE(Cur_rec.Student_id);
DBMS_OUTPUT.PUT_LINE(Cur_rec.Student_name);
DBMS_OUTPUT.PUT_LINE('HAPPY BIRTHDAY TO YOU');
END LOOP;
END;
/
|
Ive been searching for scheduling so thanks for the tip. Im not sure how to do it though (as in do I put it with the procedure, or a seperate procedure and what to really declare)
I did find an example and wasnt sure how to manipulate it:
| Quote: | dbms_job.submit(
:jobno, 'happy_birthday;',
trunc(sysdate)+9/24,
'trunc(SYSDATE+24/24,''HH'')',
TRUE,
:instno);
|
Basically to do the procedure (Happy Birthday) at 9am (9/24) everyday (24/24)
Am I anywhere close? Ive been scratching my head over this for a while now. And this is only the first part. Im hoping if I Understand this I could probably be able to work the procedures for Student Attendances/Results.
|
|
|
|
| Re: Procedure Help Needed. (SQL, PL/SQL) [message #208334 is a reply to message #208327] |
Sat, 09 December 2006 11:00 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
You are not getting the point here about DATEs and strings.
You might want to read the concept guide concerning DATEs.
This is a string: '01-jan-80'
This is a date: to_date('01-jan-80','DD-MON-RR')
This is a string: '01/01/1980'
This is a date: to_date('01/01/1980','MM/DD/YYYY')
This is a date: sysdate
If it's already a date, you don't use TO_DATE, you might use TO_CHAR to display it the way you want.
|
|
|
|
| Re: Procedure Help Needed. (SQL, PL/SQL) [message #208423 is a reply to message #208334] |
Sun, 10 December 2006 16:41 |
OlyN
Messages: 5
Registered: December 2006
|
Junior Member |
|
|
| joy_division wrote on Sat, 09 December 2006 11:00 | You are not getting the point here
|
You can say that again.
Ive tried a lot to get the birthday card to work. But it will only work if I put somebodys D_O_B as todays date (10-Dec-06)
|
|
|
|
| Re: Procedure Help Needed. (SQL, PL/SQL) [message #208592 is a reply to message #208423] |
Mon, 11 December 2006 08:17 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| OlyN wrote on Sun, 10 December 2006 17:41 |
CURSOR Cur_Birthday IS SELECT student_id,Student_name FROM Asg_Student
WHERE TO_DATE(Date_of_birth,'DD-MON-YY') = TO_DATE(SYSDATE, 'DD-MON-YY');
|
And that is because you have a little (well a big) logic error. The above code of course will only work if the person was born on that date. you want to compare month and day, but exclude the year.
Use trunc(sysdate) instead of to_date(sysdate).
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Dec 14 14:04:32 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
