Home » SQL & PL/SQL » SQL & PL/SQL » Procedure not functioning properly (Oracle 11g R2, pl/sql)
icon5.gif  Procedure not functioning properly [message #662977] Thu, 18 May 2017 02:37 Go to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I have the following procedure which inserts data into three tables:

CUSTOMER(cust_id(PK), cust_name, cust_phone(UNIQUE))
ADDRESS(address_id(PK), address, area)
CUSTOMER_ADDRESS(cust_id (PK), address_id(PK))--------------------> This is a junction table between CUSTOMER and ADDRESS
CUSTOMER_ORDER(order_id (PK), cust_id(FK to CUSTOMER))
create or replace procedure INSERT_ADDRESS(
p_cust_phone customer.cust_phone%type, 
p_address address.address%type, 
p_area address.area%type,
p_ord_id customer_order.order_id%type
)
is
l_cust_id customer.cust_id%type;
l_address_id address.address_id%type;
begin
  begin
        select cust_id 
        into l_cust_id 
        from customer c
        where c.cust_phone=p_cust_phone;
  exception
        when no_data_found then
        insert into customer 
        values (cust_id_seq.nextval, 'No Name', p_cust_phone)
        returning cust_id into l_cust_id;
  end;
  
  insert into address
  values (address_id_seq.nextval, UPPER(p_address), UPPER(p_area))
  returning address_id into l_address_id;
  
  insert into customer_address
  values (l_cust_id, l_address_id);
  
  insert into customer_order (order_id, cust_id)
  values (p_ord_id,l_cust_id);
  
end;
The procedure checks weather the cust_phone exists or not and if not then enters a new cust_id otherwise selects the cust_id associated with the given cust_phone. The problem is that this condition works properly for CUSTOMER but not for ADDRESS table. Multiple address_ids are generated with the same address.

Sample Data which is passed into the procedure along with other values(not mentioned here):
cust_phone------address
9873128600 K-1C
9811120000 J-20 3RD FLOOR
9873128600 K-1
9810320000 N-2B
9810390000 J-25 GF
9873500000 M-3 1ST FLOOR
8447910000 J-6 GF
9873128600 K-1C

The result I am getting in the ADDRESS table
ADDRESS_ID-----ADDRESS
1 K-1C
2 J-20 3RD FLOOR
3 K-1C
4 N-2B
5 J-25 GF
6 M-3 1ST FLOOR
7 J-6 GF
8 K-1C

Desired result in the ADDRESS table:
ADDRESS_ID------ADDRESS
1 K-1C[/b]
2 J-20 3RD FLOOR
3 N-2B
4 J-25 GF
5 M-3 1ST FLOOR
6 J-6 GF

The problem is that in the CUSTOMER table everything gets inserted as planned but in the ADDRESS table 'K-1C' is inserted 3 times with different address_id for each. How to check for the address?

[Updated on: Thu, 18 May 2017 03:05]

Report message to a moderator

Re: Procedure not functioning properly [message #662978 is a reply to message #662977] Thu, 18 May 2017 03:29 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That procedure can only ever insert one row into each table every time it's called.
To get the same address multiple times you have to call it with the same address multiple times.
Presumably at some point, either in this procedure or before it's called, you need to check if that address already exists for that customer, in much the same way you're checking if the customer exists.

You should also get into the habit of explicitly listing the columns to be inserted into in the insert statement. You've done it for customer_order but not the others.
Re: Procedure not functioning properly [message #662979 is a reply to message #662978] Thu, 18 May 2017 03:46 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
 select address_id
          into l_address_id
          from address a
          where a.address=p_address;
  exception
          when no_data_found then
          insert into address
          values (address_id_seq.nextval, UPPER(p_address), UPPER(p_area))
          returning address_id into l_address_id;
I guess this should do it. And yeah I will take care about the explicit naming of columns. Thanks
Re: Procedure not functioning properly [message #662981 is a reply to message #662979] Thu, 18 May 2017 04:30 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You probably also need to check if customer_address already exists.
Re: Procedure not functioning properly [message #662982 is a reply to message #662981] Thu, 18 May 2017 05:50 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
A row will be inserted in CUSTOMER_ADDRESS with only valid values of CUST_ID and ADDRESS_ID since both are checked. Am I right?

[Updated on: Thu, 18 May 2017 05:59]

Report message to a moderator

Re: Procedure not functioning properly [message #662984 is a reply to message #662982] Thu, 18 May 2017 06:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
kaos.tissue wrote on Thu, 18 May 2017 03:50
A row will be inserted in CUSTOMER_ADDRESS with only valid values of CUST_ID and ADDRESS_ID since both are checked. Am I right?
what about address CaSe MiSMatCh?
Re: Procedure not functioning properly [message #662986 is a reply to message #662984] Thu, 18 May 2017 06:13 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I didn't get you. Mismatch as in? Could you give an example?

To insert a row in CUSTOMER:
======> Phone_no(from the parameter list) is checked, if it exists then the cust_id is taken corresponding to that phone otherwise new cust_id is inserted in CUSTOMER.
The cust_id is stored in a variable.
To insert a row in ADDRESS
======> Address(address from the parameter list) is checked, if exists then address_id is taken corresponding to that address otherwise new address_id is inserted in ADDRESS.
The address_id is stored in a variable.
Finally both the variable are inserted into the CUSTOMER_ADDRESS table. So, how will the mismatch occur?

I guess you are talking about the case, like upper case or lower case or mixed case? Well, that's a minor thing and can be dealt with.

[Updated on: Thu, 18 May 2017 06:16]

Report message to a moderator

Re: Procedure not functioning properly [message #662987 is a reply to message #662986] Thu, 18 May 2017 06:38 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the address record already exists and the customer record already exists then seems likely that the customer_address record already exists.
Re: Procedure not functioning properly [message #662988 is a reply to message #662986] Thu, 18 May 2017 06:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You can lead some folks to knowledge, but you can't make them THINK.
> UPPER(p_address)
above is done by INSERT, but what happens when p_address='505 La Salle Place"?

>Well, that's a minor thing and can be dealt with.
Correct but until it is corrected, the code won't behave as you desire.
Re: Procedure not functioning properly [message #662990 is a reply to message #662988] Thu, 18 May 2017 06:41 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Blackswan - I'm not sure what problem you have in mind either. Maybe you should spell it out.
Re: Procedure not functioning properly [message #662993 is a reply to message #662990] Thu, 18 May 2017 06:45 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@Blackswann I'll take care next time. The thing is that I was focusing on the main problem rather than the minor issues. But yeah I agree. You gotta be accurate in whatever you do.
Re: Procedure not functioning properly [message #663000 is a reply to message #662993] Thu, 18 May 2017 07:09 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
actually i would setup the CUSTOMER_ADDRESS table as the following

CREATE TABLE CUSTOMER_ADDRESS
(
  I_CUST_ID     NUMBER                          NOT NULL,
  I_ADDRESS_ID  NUMBER                          NOT NULL, 
  CONSTRAINT CUSTOMER_ADDRESS_PK
  PRIMARY KEY
  (I_CUST_ID, I_ADDRESS_ID)
  ENABLE VALIDATE
)
ORGANIZATION INDEX;
This would allow it to be self indexing and will not allow duplicate addresses. You also don't have to maintain both a table and an index

[Updated on: Thu, 18 May 2017 07:10]

Report message to a moderator

Re: Procedure not functioning properly [message #663009 is a reply to message #663000] Thu, 18 May 2017 07:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd add foreign keys to customer and address.
Re: Procedure not functioning properly [message #663011 is a reply to message #663009] Thu, 18 May 2017 07:38 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
 CREATE TABLE "KARAN"."CUSTOMER_ADDRESS" 
   (	"CUST_ID" NUMBER, 
	"ADDRESS_ID" NUMBER, 

	 CONSTRAINT "CUST_ADDR_PK" PRIMARY KEY ("CUST_ID", "ADDRESS_ID")
  
	 CONSTRAINT "ADDRESS_FK" FOREIGN KEY ("ADDRESS_ID")
	  REFERENCES "KARAN"."ADDRESS" ("ADDRESS_ID") ENABLE, 
	 
         CONSTRAINT "CUSTOMER_FK" FOREIGN KEY ("CUST_ID")
	  REFERENCES "KARAN"."CUSTOMER" ("CUST_ID") ENABLE
   )
Thats the way I've done it.
Re: Procedure not functioning properly [message #663012 is a reply to message #663011] Thu, 18 May 2017 07:41 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
And that is fine. Your command creates a table and then build an additional index. The beauty of the ORGANIZATION INDEX is that you have no table, only an index. uses less space and is faster
Previous Topic: Operating with columns within a table
Next Topic: Use ":NEW" in Dynamic SQL in Trigger?
Goto Forum:
  


Current Time: Thu Mar 28 12:26:18 CDT 2024