Are you guys here just to criticize ppl. Try to understand my situation. My prof. yells at everyone that asks for help. He discourages the students, the book is no help, and I HAVE NO MONEY LEFT TO SPEND 50 BUCKS ON A BOOK!!!!!!!

Hello,

the best way to learn PL/SQL is to do it on your own. Anyway, here is my suggestion.


**************************
**** STORED FUNCTION *****
**************************
CREATE OR REPLACE FUNCTION CalculateGPA(
nSSN IN students.ssn%TYPE)
RETURN students.gpa%TYPE IS
BEGIN

vRetGPA students.gpa%TYPE := -1;

-- Start selection the ssn value
BEGIN
SELECT gpa INTO vRetGPA FROM students WHERE ssn = nSSN;
END;

EXCEPTION
WHEN NO_DATA_FOUND
RETURN vRetGPA;

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE("Error = " || SQLERRM || " Code = " || SQLCODE);
RETURN vRetGPA;

END;

****************************
**** STORED PROCEDURE *****
****************************
CREATE OR REPLACE PROCEDURE CalculateGPA(
nSSN IN students.ssn%TYPE,
nGPA OUT students.gpa%TYPE) IS
BEGIN

vRetGPA students.gpa%TYPE := -1;

-- Start selection the ssn value
BEGIN
SELECT nGPA INTO vRetGPA FROM students WHERE ssn = nSSN;
END;

EXCEPTION
WHEN NO_DATA_FOUND
nGPA := vRetGPA;

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE("Error = " || SQLERRM || " Code = " || SQLCODE);
nGPA := vRetGPA;
END;


That´s it ...

If you want, test this with AlligatorSQL at http://www.alligatorsql.com/download/alligator.zip

Hope that helps ?

Best regards
Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com

THANKS SO MUCH!!

Are you actually trying to help this student or are you just flogging your product?

Even if a complete solution was in any way an appropriate answer to this question, If YOU had tested this with (drum roll) Alligator SQL you would have spotted that it does not compile for more than one reason. Nice advert.

Let's hope the he doesn't get kicked off the course when he hands it in eh?

You have been spoonfed a specific solution here, but have you learned how to write PL/SQL? No.

Have you considered buying a book on PL/SQL? There are several available - try searching Amazon or similar for "Oracle PL/SQL".

Dear padderz,

this is as you know a forum for PL/SQL questions and everybody who has a question about PL/SQL gets an answer. I told the guy, that the best way to learn PL/SQL is to do it on his own, but this is not my decision or yours !!!!
Next time you have a question, I hope, that you will get the answer ... do it on your own or buy you a book and all the other things ...
really helpful ...

By the way ... this is not a store for PL/SQL code ... I do not always test my suggestions ... But of course you do ...

Perhaps next time, you post the mistake and do not criticize the people who wants to help people and of course flogging their products !!!

Where is you problem ?

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

Hello padderz,

by the way ... what´s about your signature and all the things about Quest and so on .... are you flogging your "products" too ?

now I understand your post ... that´s typical

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

Apologies Peter for obvious offence. I stand by the points in my reply but I would certainly concede that the tone was unhelpful. Next time I will count to ten first.

Peter with respect - I don't think you do understand my post. I am not (nor have I ever been) an employee of Quest Software. Rather I am lucky enough to administer the 100% free Quest PL/SQL Pipeline Webboard with others by invitation of Quest and I contribute of my time on a strictly no-benefit basis (thanks for taking the time to check that out, btw.). And yes we do strongly encourage testing of ALL posts - why on earth wouldn't you?

Also I'm afraid I still do not understand your standpoint in this thread. If you feel that the poster should learn this himself, why post a complete answer? There are plenty of other options. When I tell my son not to smoke I appreciate that he must make his own decision but I will not be finishing the lecture by handing out cigarettes and matches and neither would I tolerate someone else doing it either, which I guess is why I replied.

Apologies also to Djayaraj. I am not trying to get in the way of your education but do you really think that copying down an answer from the internet improves your understanding? Contact me off-line at jpadfiel@cellops.com and I will be happy to discuss your situation.