SQL%FOUND [message #315210] |
Sat, 19 April 2008 22:46  |
learnSQL
Messages: 19 Registered: April 2008 Location: Texas
|
Junior Member |
|
|
This is a second problem that I could not figure out the solution. It was compiled without error, but the result is not what I expected.
An anonymous block to update a table using named parameters. Update table if input id is in the table, otherwise insert a new row into table. Below is my block with tables before and after running the block.
PHY PHYS_NAME PHYS_PHONE PHYS_SPECIALTY
--- -------------------- ------------ --------------------
101 Wilcox, Chris 512-329-1848 Eyes, Ears, Throat
102 Nusca, Jane 512-516-3947 Cardiovascular
103 Gomez, Juan 512-382-4947 Orthopedics
SQL> SET SERVEROUTPUT on
SQL> DECLARE
2 v_Physician Physician%ROWTYPE;
3 v_Phys_ID Physician.Phys_ID%TYPE := &v_Phys_ID;
4 v_Phys_Name Physician.Phys_Name%TYPE;
5 v_Phys_Phone Physician.Phys_Phone%TYPE;
6 v_Phys_Specialty Physician.Phys_Specialty%TYPE;
7 v_err_code NUMBER;
8 v_err_msg VARCHAR2(200);
9 BEGIN
10 SELECT *
11 INTO v_Physician
12 FROM Physician
13 WHERE Phys_ID = v_Phys_ID;
14 IF SQL%FOUND THEN
15 UPDATE Physicians
16 SET Phys_Name = '&v_Phys_Name',
17 Phys_Phone = '&v_Phys_Phone',
18 Phys_Specialty = '&v_Phys_Specialty'
19 WHERE Phys_ID = v_Physician.Phys_ID;
20 END IF;
21 EXCEPTION
22 WHEN NO_DATA_FOUND THEN
23 INSERT INTO Physicians
24
25 VALUES('&v_Phys_ID','&v_Phys_Name','&v_Phys_Phone','&v_Phys_Specialty');
26 WHEN OTHERS THEN
27 v_err_code := SQLCODE;
28 v_err_msg := SUBSTR(SQLERRM, 1, 200);
29 DBMS_OUTPUT.PUT_LINE('Error code: '||v_err_code);
30 DBMS_OUTPUT.PUT_LINE('Error message: '||v_err_msg);
31 END;
32 /
Enter value for v_phys_id: 105
old 3: v_Phys_ID Physician.Phys_ID%TYPE := &v_Phys_ID;
new 3: v_Phys_ID Physician.Phys_ID%TYPE := 105;
Enter value for v_phys_name:
old 16: SET Phys_Name = '&v_Phys_Name',
new 16: SET Phys_Name = '',
Enter value for v_phys_phone:
old 17: Phys_Phone = '&v_Phys_Phone',
new 17: Phys_Phone = '',
Enter value for v_phys_specialty:
old 18: Phys_Specialty = '&v_Phys_Specialty'
new 18: Phys_Specialty = ''
Enter value for v_phys_id: 105
Enter value for v_phys_name: Richard, Casino
Enter value for v_phys_phone: 214-412-1234
Enter value for v_phys_specialty: Black jack
old 25: VALUES('&v_Phys_ID','&v_Phys_Name','&v_Phys_Phone','&v_Phys_Specialty')
;
new 25: VALUES('105','Richard, Casino','214-412-1234','Black jack');
PL/SQL procedure successfully completed.
SQL> select * from physicians;
PHY PHYS_NAME PHYS_PHONE PHYS_SPECIALTY
--- -------------------- ------------ --------------------
101 Wilcox, Chris 512-329-1848 Eyes, Ears, Throat
102 Palace, Buffet 222-111-1234 spicy food
103 Gomez, Juan 512-382-4947 Orthopedics
105 Richard, Casino 214-412-1234 Black jack
I suspected the error is somewhere from line 14 to 25. When I entered the id # within or outside the table, it always went thru 14 to 25.
Thanks in advance
|
|
|
Re: SQL%FOUND [message #315213 is a reply to message #315210] |
Sun, 20 April 2008 01:34   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
What output did you expect?
Why did you create a when_no_data_found exception handler? Somehow you knew that a select into would fail when no matching records could be found, yet you added a test on SQL%FOUND.
[Updated on: Sun, 20 April 2008 01:34] Report message to a moderator
|
|
|
|
Re: SQL%FOUND [message #315227 is a reply to message #315213] |
Sun, 20 April 2008 02:38   |
learnSQL
Messages: 19 Registered: April 2008 Location: Texas
|
Junior Member |
|
|
Thanks for your input Frank. When No_Data_found, I will insert a new row into the table. When SQL%FOUND, I will update the table with named parameter input ID. The original table has only 3 ID's 101-103. The row with id 105 was inserted with new info. The way I expect is if SQL%FOUND, then It will do the update and then exit. If No_Data_Found then It will insert new row then exit.
when I ran that code, It always went thru Update and Insert before exiting out.
|
|
|
Re: SQL%FOUND [message #315229 is a reply to message #315219] |
Sun, 20 April 2008 02:44   |
learnSQL
Messages: 19 Registered: April 2008 Location: Texas
|
Junior Member |
|
|
Hi Michel,
Thanks for your advice, but Merge is not covered in class so I do not think I can use it for this exercise.
|
|
|
Re: SQL%FOUND [message #315242 is a reply to message #315227] |
Sun, 20 April 2008 05:30   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Quote: | when I ran that code, It always went thru Update and Insert before exiting out.
| Why do you say that It always went through update and Insert before exiting out ? I think you didn't understand how the substituion variable works.
Regards
Raj
[Updated on: Sun, 20 April 2008 05:30] Report message to a moderator
|
|
|
Re: SQL%FOUND [message #315243 is a reply to message #315210] |
Sun, 20 April 2008 05:58   |
ritvikd
Messages: 11 Registered: January 2007
|
Junior Member |
|
|
Please check the declaration on line 2 and what you are using for SET statements!
I believe that the Where clause of update statement is proper way to do the same for set statement.
Please correct me if I am wrong.
Quote: |
SQL> DECLARE
2 v_Physician Physician%ROWTYPE;
3 v_Phys_ID Physician.Phys_ID%TYPE := &v_Phys_ID;
4 v_Phys_Name Physician.Phys_Name%TYPE;
5 v_Phys_Phone Physician.Phys_Phone%TYPE;
6 v_Phys_Specialty Physician.Phys_Specialty%TYPE;
7 v_err_code NUMBER;
8 v_err_msg VARCHAR2(200);
|
14 IF SQL%FOUND THEN
15 UPDATE Physicians
16 SET Phys_Name = '&v_Phys_Name',
17 Phys_Phone = '&v_Phys_Phone',
18 Phys_Specialty = '&v_Phys_Specialty'
19 WHERE Phys_ID = v_Physician.Phys_ID;
20 END IF;
|
|
|
Re: SQL%FOUND [message #315262 is a reply to message #315242] |
Sun, 20 April 2008 10:09   |
learnSQL
Messages: 19 Registered: April 2008 Location: Texas
|
Junior Member |
|
|
Hi raj,
I said so because my code is intended to work this way:
If SQL%FOUND (true) then
run these statements in here(update)
......
if SQL%FOUND (false) --this means No_Data_Found
then go to Exception Handler when No_Data_Found and execute those statements in there.
As you saw at the end of the code, when I entered ID 105(not in table) It did not skip the update section. I had to entered info twice even though only one set of info was updated or inserted into table.
Regards
learnSQL
|
|
|
Re: SQL%FOUND [message #315265 is a reply to message #315243] |
Sun, 20 April 2008 10:39   |
learnSQL
Messages: 19 Registered: April 2008 Location: Texas
|
Junior Member |
|
|
Hi ritvikd,
Thanks for for input, but I do not sure if I understand your point. I think update is to set new values for specific columns in the row where the ID is provided. The where clause of update specifies which row I'd like the info updated if it exist.
What change(s) should I nake?
Regards
learnSQL
|
|
|
Re: SQL%FOUND [message #315266 is a reply to message #315262] |
Sun, 20 April 2008 11:42  |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Quote: | As you saw at the end of the code, when I entered ID 105(not in table) It did not skip the update section. I had to entered info twice even though only one set of info was updated or inserted into table.
|
As I said earlier you didn't understand how substitution variable works in oracle. Please read the oracle reference manual.
Regards
Raj
|
|
|