Home » SQL & PL/SQL » SQL & PL/SQL » exec a procedure?
exec a procedure? [message #189315] Thu, 24 August 2006 02:41 Go to next message
davidG
Messages: 4
Registered: August 2006
Junior Member
How do I execute a stored Procedure?

Get following message:ORA-01403: no data found
ORA-06512: at "PROC2", line 5

DECLARE
invoice_nr NUMBER := 103;
SUM NUMBER;
BEGIN
proc2(invoice_nr, SUM);
dbms_output.put_line(SUM);
END;



Re: exec a procedure? [message #189319 is a reply to message #189315] Thu, 24 August 2006 02:47 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Your procedure proc2 has a select into clause where the select is not returning any data. The procedure is being called, it's just that it is erroring out. Post the proc2 procedure

Jim
Re: exec a procedure? [message #189376 is a reply to message #189319] Thu, 24 August 2006 05:55 Go to previous messageGo to next message
davidG
Messages: 4
Registered: August 2006
Junior Member
Thank you very much! It looks like this.

CREATE OR REPLACE PROCEDURE Proc2
(INVOICE_NR IN number, SUM OUT number)
is
BEGIN
SELECT amount
into SUM
FROM invoice
where INVOICE_NR=INVOICES_NR;
END proc2;
Re: exec a procedure? [message #189377 is a reply to message #189376] Thu, 24 August 2006 05:57 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
and if you run

select amount
from invoice
where invoices_nr = 103;

what result do you get?
Re: exec a procedure? [message #189445 is a reply to message #189377] Thu, 24 August 2006 11:05 Go to previous messageGo to next message
davidG
Messages: 4
Registered: August 2006
Junior Member
AMOUNT
----------
164

1 row.
Re: exec a procedure? [message #189446 is a reply to message #189445] Thu, 24 August 2006 11:21 Go to previous message
davidG
Messages: 4
Registered: August 2006
Junior Member
It works!!! Thanks Smile)

How about this procedure... something is wrong...
CREATE OR REPLACE PROCEDURE "LOAD_GROSSIST_BOOK"
as
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 (select distinct substr(name,instr(name, ' ')+1) || ''''|| substr(name,1,instr(name,', ')-1) AS auth from author)
AND GROSSISTEN.cathegory =type_of_book.name
AND GROSSISTEN.supplier = supplier.name
and GROSSISTEN.isbn not in (select isbn from book);
End;

I have inserted names into a Author from Grossisten, by switching the places of(Familyname, name) ... using the procedure below. So I need to check table Author, Grossisten and Insert correct values into Book.

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);
Previous Topic: Finding Error Line Numbers in 9i
Next Topic: ORA-03113 when using descending index
Goto Forum:
  


Current Time: Fri Dec 02 22:48:11 CST 2016

Total time taken to generate the page: 0.09575 seconds