13 Replies - 702 Views - Last Post: 02 June 2016 - 08:33 AM

#1 asimkh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 07:53 AM

CREATE OR REPLACE PROCEDURE add_customer(p_id T2_customers.customer_id%type, p_name T2_customers.name%TYPE, p_num T2_customers.phone%TYPE) AS
e_exception   EXCEPTION;
e_error       VARCHAR2(200);
id_num        NUMBER;

CURSOR c1 IS
  SELECT customer_id
  FROM T2_Customers
  WHERE CUSTOMER_ID = p_id;
  
  BEGIN
  OPEN c1;
  FETCH c1 INTO id_num;
  IF (id_num%NOTFOUND) THEN
    e_error := 'Successfull';
  ELSE
    e_error := 'ERROR: The customer id ' ||p_id||' already exists';
  END IF;
  
  INSERT INTO T2_customers VALUES(p_id,p_name,p_num);
  EXCEPTION
 WHEN NO_DATA_FOUND THEN
--    e_error := 'Successful';
INSERT INTO messages  VALUES(e_error);
--  INSERT INTO messages  VALUES(e_error);
    WHEN DUP_VAL_ON_INDEX THEN
--    e_error := 'ERROR: The customer id ' ||p_id||' already exists';
    
INSERT INTO messages  VALUES(e_error);
END;



THAT's the code i have written which is causing me problems

I did have the same code without the if else which worked fine however it is a requirement for me to have that if else
How can i write a procedure so it has that if else while also having the exception handlers....

This is the add_customer being called
BEGIN
add_customer(138,'KERR', '6140 8750');    --Should fail as KERR exists
add_customer(139,'BUCCANEER','6140 8750');--Should fail as BUCCANEER exists
add_customer(140,'CROOK', '6147 7612');   --Should fail as 2 CROOKs exist
add_customer(141,'BARNEL', '6147 8277');  --Should succeed
END;


Thanks in advance for all your help and input :)

Is This A Good Question/Topic? 0
  • +

Replies To: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13389
  • View blog
  • Posts: 53,429
  • Joined: 12-June 08

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:01 AM

In what way is it not working?
Was This Post Helpful? 0
  • +
  • -

#3 asimkh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:05 AM

Error(14,3): PL/SQL: Statement ignored
Error(14,7): PLS-00324: cursor attribute may not be applied to non-cursor 'ID_NUM'


That's the error im getting
could you direct me to rewrite it in a better format if what i have written is correct in the first place...

Thanks

This post has been edited by asimkh: 02 June 2016 - 08:07 AM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13389
  • View blog
  • Posts: 53,429
  • Joined: 12-June 08

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:07 AM

Which database environment is this for?
Was This Post Helpful? 0
  • +
  • -

#5 asimkh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:08 AM

I am using oracle sql

This post has been edited by asimkh: 02 June 2016 - 08:08 AM

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13389
  • View blog
  • Posts: 53,429
  • Joined: 12-June 08

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:08 AM

SQL.. what? MSSQL? MYSQL? SQLITE? MONGO? NOSQL?
Was This Post Helpful? 0
  • +
  • -

#7 asimkh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:10 AM

ORACLE 11G
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13389
  • View blog
  • Posts: 53,429
  • Joined: 12-June 08

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:10 AM

Okay.. so maybe not edit your post after the fact and change it.. it makes things look out of order and silly.

I'll move this to the 'Oracle' section.
Was This Post Helpful? 0
  • +
  • -

#9 asimkh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:11 AM

I edited it pretty quickly...

Anyway is that another forum?
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13389
  • View blog
  • Posts: 53,429
  • Joined: 12-June 08

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:12 AM

You seem to be missing the LOOP/END LOOP and CLOSEing of the cursor.
http://www.oracle.co...ql-1906474.html
Was This Post Helpful? 0
  • +
  • -

#11 asimkh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:16 AM

Still getting the same error...
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13389
  • View blog
  • Posts: 53,429
  • Joined: 12-June 08

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:20 AM

Okay.. show your current code.
Was This Post Helpful? 0
  • +
  • -

#13 asimkh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:30 AM

This is the code i have written to create the procedure which works fine however i need an if else test in there....

CREATE OR REPLACE PROCEDURE add_customer(p_id T2_customers.customer_id%type, p_name T2_customers.name%TYPE, p_num T2_customers.phone%TYPE) AS
e_exception   EXCEPTION;
e_error       VARCHAR2(200);
  BEGIN
  INSERT INTO T2_customers VALUES(p_id,p_name,p_num);
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    e_error := 'Successful';
  INSERT INTO messages  VALUES(e_error);
  WHEN DUP_VAL_ON_INDEX THEN
    e_error := 'ERROR: The customer id ' ||p_id||' already exists';
  INSERT INTO messages  VALUES(e_error);
END;


However hen i use this code to test it it doesn't give me the desired results....
BEGIN
add_customer(138,'KERR', '6140 8750');    --Should fail as KERR exists
add_customer(139,'BUCCANEER','6140 8750');--Should fail as BUCCANEER exists
add_customer(140,'CROOK', '6147 7612');   --Should fail as 2 CROOKs exist
add_customer(141,'BARNEL', '6147 8277');  --Should succeed
END;


When i execute this select statements:
PROMPT Expect two rows displayed CLELAND and BARNEL
SELECT * FROM T2_customers WHERE customer_id >= 138;
SELECT message "Expect 3 errors + 1 success:" FROM MESSAGES;
ROLLBACK;


I get this:
138	CLELAND	6250 4087
139	BUCCANEER	6140 8750
140	CROOK	6147 7612
141	BARNEL	6147 8277

Which actually should be only displaying two rows Cleland and barnel

The other is this:
ERROR: The customer id 138 already exists
ERROR: The customer id 139 already exists
ERROR: The customer id 140 already exists
ERROR: The customer id 141 already exists


Here there should be 3 fails and one success message displayed....
Was This Post Helpful? 0
  • +
  • -

#14 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13389
  • View blog
  • Posts: 53,429
  • Joined: 12-June 08

Re: WHY MY PROCEDURE WITH IF ELSE WONT WORK?

Posted 02 June 2016 - 08:33 AM

Where is the cursor at now?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1