Home » SQL & PL/SQL » SQL & PL/SQL » HELP ASAP
HELP ASAP [message #204346] Mon, 20 November 2006 04:15 Go to next message
ora-mark
Messages: 1
Registered: November 2006
Junior Member
hi everyone,

can anyone help me on this one..

i created and compiled this procedure in the SQL plus environment and it works right. ive also tested it and it works.

but when i try to call the same procedure in the concurrent request manager, it doesnt work. it gives me this results.

1. when i exclude the error and exception handler, it gives a "completed-normal remarks" but it didnt execute the delete command.

2. when i include the error and exception handler, it always immediately throws the "no data found exception" even though the data is present in the data base

im suspecting that im not using the errbuf and retcode correctly

heres is the syntax of my procedure.

CREATE OR REPLACE PROCEDURE zedi_delete_invoices
(p_invoice_nbr IN varchar2,errbuf out varchar2, retcode out varchar2) is

l_inv_invoice_id number(22);

BEGIN

BEGIN
SELECT zid.inv_invoice_id
INTO l_inv_invoice_id
FROM zedi_inv_details zid,
zedi_invoices zi
WHERE zid.inv_invoice_id = zi.invoice_id
AND zi.invoice_nbr = p_invoice_nbr
AND zid.it104 = 0
AND ROWNUM = 1;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20017,'MISSING ZEDI INVOICE' ||
SQLERRM);
END;

BEGIN

DELETE zedi_inv_details
WHERE inv_invoice_id = l_inv_invoice_id;


EXCEPTION
WHEN OTHERS THEN
ROLLBACK WORK;
RAISE_APPLICATION_ERROR(-20001,'DELETE FROM ' ||
'ZEDI_INV_DETAILS FAIL ' ||
SQLERRM);
END;

END;

/

please someone help me out as soon as possible.. badly need this codes tommorrow..

thanks.

Re: HELP ASAP [message #204367 is a reply to message #204346] Mon, 20 November 2006 06:03 Go to previous messageGo to next message
dawood
Messages: 5
Registered: November 2006
Junior Member
if you dont mind i have some doubt on your question
i.e
why you are using errbuf out varchar2, retcode out varchar2
in that procedure and are not using it out
&
why using two table in the from clause
FROM zedi_inv_details zid,
zedi_invoices zi
..

[Updated on: Mon, 20 November 2006 06:05]

Report message to a moderator

Re: HELP ASAP [message #204646 is a reply to message #204346] Tue, 21 November 2006 08:45 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
If this is a concurrent manager problem you might want to post it on an Oracle Apps Forum somewhere.

Re: HELP ASAP [message #204676 is a reply to message #204646] Tue, 21 November 2006 11:54 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Retcode should be set to 1 if an error occurs (0 being ok, 1 being not ok to the conc request manager).
So, probably something did actually go wrong, but the request manager doesn't know (retcode still being 0).
Re: HELP ASAP [message #204754 is a reply to message #204346] Tue, 21 November 2006 23:53 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi,
Can you try with this query?

SELECT zid.inv_invoice_id
INTO l_inv_invoice_id
FROM zedi_inv_details zid, 
zedi_invoices zi
WHERE zid.inv_invoice_id = zi.invoice_id
AND zi.invoice_nbr = p_invoice_nbr
AND zid.it104 = 0
AND ROWNUM < 2 ;
Re: HELP ASAP [message #204788 is a reply to message #204754] Wed, 22 November 2006 02:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm intrigued - have you ever actually seen a situation where changing ROWNUM=1 to ROWNUM<2 makes a difference.

I'm a little doubtful that you have.....
Re: HELP ASAP [message #204802 is a reply to message #204788] Wed, 22 November 2006 02:48 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
I've looked it up in more detail. This is the way to work with the concurrent manager:

CREATE OR REPLACE PROCEDURE < your_procedure > 
(errbuf OUT VARCHAR2, retcode OUT VARCHAR2, < your_other_params >) IS
 
BEGIN retcode := 0;
<do_your_thing >;
  EXCEPTION WHEN < something_went_wrong > THEN 
  retcode := 1;
  fnd_file.put_line(fnd_file.log
                   ,'< error_message >');
  END;
fnd_file.put_line(fnd_file.log
                   ,'Retcode = ' || retcode);
END;


So, in your code you're (a) not telling the conc manager what the result is and (b) you're not telling it what went wrong. Only if you have add this, you will be able to find out what has happened.

PS Intriguing, rownum < 2 instead of rownum = 1, negative rownumbers? a rownum of 0.5? Surprise me! Wink
Re: HELP ASAP [message #204806 is a reply to message #204788] Wed, 22 November 2006 03:07 Go to previous messageGo to next message
dawood
Messages: 5
Registered: November 2006
Junior Member
In a query we can able to use rownum = symbol for checking any value instead we can use has rownum < or <= symbol for checking any value using where clause...
Re: HELP ASAP [message #204808 is a reply to message #204806] Wed, 22 November 2006 03:23 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, sort of.

You can use Rownum=1 in a query to get the first row.
Rownum=<any value except 1> will return no rows at all.

Previous Topic: Database Trigger
Next Topic: How many 'a' we have before 's'
Goto Forum:
  


Current Time: Fri Dec 09 15:37:22 CST 2016

Total time taken to generate the page: 0.14307 seconds