Home » SQL & PL/SQL » SQL & PL/SQL » Procedure
Procedure [message #190779] Fri, 01 September 2006 03:33 Go to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Could you please help me with this procedure...
How do I insert values into table Book, and compare the Familyname of the db-table GROSSISTEN with the names of the table AUTHOR.. ??

CREATE OR REPLACE PROCEDURE "LOAD_GROSSIST_BOOK"
as
cntr number;
begin
Insert into book (isbn, titel, In_stock, Price, author_id,
type_id,supplier_id)
select distinct GROSSISTEN.isbn,
GROSSISTEN.title,
GROSSISTEN.In_store,
GROSSISTEN.price,
author.id,
type_of_book.id,
supplier.id
FROM GROSSISTEN, author, type_of_book, supplier
WHERE GROSSISTEN.author in (Begin for s in (select distinct substr(name,instr(name, ' ')+1) || ''''|| substr(name,1,instr(name,', ')-1)
AS auth from author)
LOOP
select count(*) into cntr from AUTHOR where name = s.auth;
end loop;
end);

AND GROSSISTEN.cathegory =type_of_book.name
AND GROSSISTEN.supplier = supplier.name
and GROSSISTEN.isbn not in (select isbn from book);
End;


Re: Procedure [message #190788 is a reply to message #190779] Fri, 01 September 2006 04:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is that piece of code meant to be?
You can't just embed Pl/Sql in SQL.

You seem to be trying to compare the grossiste.author with the distinct space separated words inside the author.name.

Is this correct?
Re: Procedure [message #190792 is a reply to message #190779] Fri, 01 September 2006 04:50 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

plz give the table description here and link between the tables if the field names are same in some tables

[Updated on: Fri, 01 September 2006 04:51]

Report message to a moderator

Re: Procedure [message #190818 is a reply to message #190792] Fri, 01 September 2006 06:57 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Thanks.. The relations are;

GROSSISTEN.isbn= BOOK.isbn
GROSSISTEN.title= Book.titel
GROSSISTEN.In_store Book.in_stock
GROSSISTEN.price=BOOK.price
author.id= BOOK.author_id
type_of_book.id=BOOK.type_id
supplier.id=BOOK.supplier_id

I already loaded from Grossisten.. author.name, type_of_book, supplier.name, + an ID so that every value has an unique ID.

This is how I inserted name into table author, i.e. by switching the place of their Familyname and Firstname.(Keanu Reaves) in Grossisten became (Reaves, Keanu)in Author. I need to compare these names and insert the right ID into BOOK.author_id.

CREATE OR REPLACE PROCEDURE "LOAD_GROSSIST_AUTHORS"
as
cntr number;
begin
FOR s in (select distinct substr(author,instr(author, ' ')+1) || ', '|| substr(author,1,instr(author,
' ')-1) AS auth from grossisten)
LOOP
select count(*) into cntr from AUTHOR where name = s.auth;
if cntr=0
then
insert into AUTHOR(name) values(s.auth);
end if;
end loop;
commit;
end;
Re: Procedure [message #190828 is a reply to message #190818] Fri, 01 September 2006 08:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd create a function to map the Grossisten name to the author one, like this:

CREATE OR REPLACE FUNCTION name_map (p_g_name in varchar2) return varchar2 AS
BEGIN
  return substr(p_g_name,1,instr(p_g_name,' ',1)-1)||', '||substr(p_g_name,instr(p_g_name,' ',1));
END;

and then use a where clause like
WHERE name_map(grossisten.name) =  author.name
Re: Procedure [message #190934 is a reply to message #190828] Sat, 02 September 2006 13:43 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Thank you very much.. Smile
Something is not right, but it was an enormous help..
Re: Procedure [message #191012 is a reply to message #190934] Mon, 04 September 2006 02:16 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

hi

try this one and let me know if u still have any problems...


CREATE OR REPLACE PROCEDURE "LOAD_GROSSIST_BOOK"
AS
cntr NUMBER;
BEGIN
FOR s IN ( SELECT DISTINCT SUBSTR(name,INSTR(name,' ',1)+1)||' '||SUBSTR(name,1,INSTR(name,',',1)-1) AS AUTH_NAME
FROM AUTHOR
)
LOOP
INSERT INTO book (isbn, titel, In_stock, Price, author_id, type_id,supplier_id)
SELECT DISTINCT GROSSISTEN.isbn, GROSSISTEN.title, GROSSISTEN.In_store, GROSSISTEN.price, author.id,
type_of_book.id, supplier.id
FROM GROSSISTEN, TYPE_OF_BOOK, SUPPLIER
WHERE GROSSISTEN.AUTHOR = s.AUTH_NAME;
AND GROSSISTEN.cathegory =type_of_book.name
AND GROSSISTEN.supplier = supplier.name
AND GROSSISTEN.isbn NOT IN (SELECT isbn FROM book);
COMMIT;
END LOOP;
END;

With Regards
Venkat

[Updated on: Mon, 04 September 2006 02:17]

Report message to a moderator

Re: Procedure [message #191229 is a reply to message #191012] Tue, 05 September 2006 05:04 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Thank you very much! How if I combine these two procedures loading two tables automatically? Would it be a complicated procedure?

CREATE OR REPLACE PROCEDURE "LOAD_GROSSIST_AUTHORS"
as
cntr number;
begin
FOR s in (select distinct substr(author,instr(author, ' ')+1) || ', '|| substr(author,1,instr(author,
' ')-1) AS auth from grossisten)
LOOP
select count(*) into cntr from AUTHOR where name = s.auth;
if cntr=0
then
insert into AUTHOR(name) values(s.auth);
end if;
end loop;
commit;
end;

CREATE OR REPLACE PROCEDURE "LOAD_GROSSIST_BOOK"
AS
cntr NUMBER;
BEGIN
FOR s IN ( SELECT DISTINCT SUBSTR(name,INSTR(name,' ',1)+1)||' '||SUBSTR(name,1,INSTR(name,',',1)-1) AS AUTH_NAME
FROM AUTHOR
)
LOOP
INSERT INTO book (isbn, titel, In_stock, Price, author_id, type_id,supplier_id)
SELECT DISTINCT GROSSISTEN.isbn, GROSSISTEN.title, GROSSISTEN.In_store, GROSSISTEN.price, author.id,
type_of_book.id, supplier.id
FROM GROSSISTEN, TYPE_OF_BOOK, SUPPLIER
WHERE GROSSISTEN.AUTHOR = s.AUTH_NAME;
AND GROSSISTEN.cathegory =type_of_book.name
AND GROSSISTEN.supplier = supplier.name
AND GROSSISTEN.isbn NOT IN (SELECT isbn FROM book);
COMMIT;
END LOOP;
END;
Re: Procedure [message #191367 is a reply to message #190779] Wed, 06 September 2006 00:32 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Nothing complicated here

plz find the changes I made here in Bold letters.

just try this and plz make sure that you are using the valid column names..

CREATE OR REPLACE PROCEDURE "LOAD_GROSSIST_BOOK"
AS
cntr NUMBER;
BEGIN
FOR s IN ( SELECT DISTINCT SUBSTR(AUTHOR,INSTR(AUTHOR, ' ')+1) || ', '|| SUBSTR(AUTHOR,1,INSTR(AUTHOR,' ')-1) AS auth
FROM GROSSISTEN)
LOOP
SELECT COUNT(*)
INTO cntr
FROM AUTHOR
WHERE name = s.auth;
IF cntr=0 THEN
INSERT INTO AUTHOR(name) VALUES(s.auth);
END IF;
END LOOP;

FOR s IN ( SELECT DISTINCT ID, SUBSTR(name,INSTR(name,' ',1)+1)||' '||SUBSTR(name,1,INSTR(name,',',1)-1) AS AUTH_NAME
FROM AUTHOR
)
LOOP
INSERT INTO BOOK (isbn, titel, In_stock, Price, author_id, type_id,supplier_id)
SELECT DISTINCT GROSSISTEN.isbn, GROSSISTEN.title, GROSSISTEN.In_store, GROSSISTEN.price, s.id,
type_of_book.id, SUPPLIER.id
FROM GROSSISTEN, TYPE_OF_BOOK, SUPPLIER
WHERE GROSSISTEN.AUTHOR = s.AUTH_NAME
AND GROSSISTEN.CATHEGORY =TYPE_OF_BOOK.NAME
AND GROSSISTEN.SUPPLIER = SUPPLIER.NAME
AND GROSSISTEN.ISBN NOT IN (SELECT ISBN FROM BOOK);

COMMIT;

END LOOP;
END;

Thks & Rgds
Venkat
Re: Procedure [message #191687 is a reply to message #191367] Thu, 07 September 2006 14:10 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Thank you so much... you're so kind!
I got error though... but I wonder... I would like to see the exact line where there is an error? Is is possible in sql* Plus??

CREATE OR REPLACE PROCEDURE "LOAD_GROSSIST_BOOK"
AS
cntr NUMBER;
BEGIN
FOR s IN (SELECT DISTINCT SUBSTR(AUTHOR,INSTR(AUTHOR, ' ')+1) || ', '|| SUBSTR(AUTHOR,1,INSTR(AUTHOR,' ')-1) AS auth
FROM GROSSISTEN)
LOOP
SELECT COUNT(*)
INTO cntr
FROM AUTHOR
WHERE name = s.auth;
IF cntr=0 THEN
INSERT INTO AUTHOR(name) VALUES(s.auth);
END IF;
END LOOP;

Insert into supplier(name)
select distinct supplier from grossisten
where grossisten.supplier not in (select name from supplier);

Insert into TYPE_of_BOOK(name)
select distinct CATHEGORY from grossisten
where grossisten.CATHEGORY not in (select name from TYPE_of_BOOK);

FOR s IN ( SELECT DISTINCT ID, SUBSTR(name,INSTR(name,' ',1)+1)||' '||SUBSTR(name,1,INSTR(name,',',1)-1) AS AUTH_NAME
FROM AUTHOR
)
LOOP
INSERT INTO BOOK (isbn, titel, In_stock, Price, author_id, type_id,supplier_id)
SELECT DISTINCT GROSSISTEN.isbn, GROSSISTEN.title, GROSSISTEN.In_store, GROSSISTEN.price, s.id,
type_of_book.id, SUPPLIER.id
FROM GROSSISTEN, TYPE_OF_BOOK, SUPPLIER
WHERE GROSSISTEN.AUTHOR = s.AUTH_NAME
AND GROSSISTEN.CATHEGORY =TYPE_OF_BOOK.NAME
AND GROSSISTEN.SUPPLIER = SUPPLIER.NAME
AND GROSSISTEN.ISBN NOT IN (SELECT ISBN FROM BOOK);

COMMIT;

END LOOP;
END;
Re: Procedure [message #191770 is a reply to message #191687] Fri, 08 September 2006 01:55 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

plz give the error message here

Thks & Rgds
Venkat
Re: Procedure [message #191822 is a reply to message #191770] Fri, 08 September 2006 04:39 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
thanks...
Error: Procedure created with compiling error.(translated to english)

Actually I would like to see the exact line where the error is..
I think its in the book-part.
Re: Procedure [message #191823 is a reply to message #191822] Fri, 08 September 2006 04:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Type: "show errors" (without the double quotes) in the SQL*Plus prompt.

MHE
Re: Procedure [message #191892 is a reply to message #191823] Fri, 08 September 2006 08:56 Go to previous message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Funny but it says "no errors"
????
Previous Topic: PLS-00103: Encountered the symbol "ORDER" when expecting one ofthe following: ;
Next Topic: Raise Exception
Goto Forum:
  


Current Time: Sun Dec 04 16:23:48 CST 2016

Total time taken to generate the page: 0.26042 seconds