Home » Other » Training & Certification » SQL%FOUND (SQL*PLUS/10.2/XP)
SQL%FOUND [message #315210] Sat, 19 April 2008 22:46 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #315219 is a reply to message #315210] Sun, 20 April 2008 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Update table if input id is in the table, otherwise insert a new row into table.

Unless this a PL/SQL exercise, MERGE statement is made for that.

Regards
Michel
Re: SQL%FOUND [message #315227 is a reply to message #315213] Sun, 20 April 2008 02:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: trigger
Next Topic: database design
Goto Forum:
  


Current Time: Wed Dec 07 22:28:07 CST 2016

Total time taken to generate the page: 0.06565 seconds