Home » SQL & PL/SQL » SQL & PL/SQL » Dont know how to write it... (10g, 11.5.10 Apps)
Dont know how to write it... [message #317636] Fri, 02 May 2008 04:53 Go to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
Wrote a function which goes off and selects a customer billing address in AR based off feeding it 'Province' and 'Customer ID' ...

Basically, for some customers it returns 2 rows, and when it returns two rows, i need the concurrent program its used in to output an error - I.e 'This customer has 2 addresses' etc..

I've written it like this, but the concurrent program errors.. Im really stuck..

FUNCTION GET_CUST_BILL_ADDR ( p_cust_id IN PLS_INTEGER
, p_primary IN VARCHAR2 DEFAULT 'Y'
, p_context IN VARCHAR2 DEFAULT 'REF'
, p_debtor_ref IN VARCHAR2 )
RETURN VARCHAR2
IS
CURSOR C1_ADD ( cv_external_ref IN VARCHAR2, cv_primary_flag IN VARCHAR2 )
IS
SELECT a.ADDRESS_ID
, a.ORIG_SYSTEM_REFERENCE
, Arp_Addr_Pkg.FORMAT_ADDRESS('',a.ADDRESS1, a.ADDRESS2,a.ADDRESS3,a.ADDRESS4,a.CITY,a.COUNTY
, a.STATE, a.PROVINCE, a.POSTAL_CODE, a.territory ) CONCATENATED_ADDRESS
, s.SITE_USE_ID
FROM hz_site_uses_v s
, ra_addresses a
, ra_customers c
WHERE s.address_id = a.address_id
AND a.customer_id = c.customer_id
AND c.customer_id = cv_external_ref
AND a.province = p_debtor_ref
AND s.site_use_code = 'BILL_TO'
AND s.status = 'A'
;

add_rec C1_ADD%ROWTYPE;

BEGIN
OPEN C1_ADD (p_cust_id , p_primary );
FETCH C1_ADD
INTO add_rec;
CLOSE C1_ADD;

IF add_rec.address_id IS NULL
THEN
RETURN 'ERROR';
END IF;

IF c1_add%rowcount >= 2
THEN
RETURN 'TOO_MANY_ROWS';
END IF;

IF p_context = 'REF'
THEN
RETURN add_rec.ORIG_SYSTEM_REFERENCE;

ELSIF p_context = 'ID'
THEN
RETURN add_rec.ADDRESS_ID;

ELSIF p_context = 'SITE'
THEN
RETURN add_rec.SITE_USE_ID;
ELSE
RETURN add_rec.CONCATENATED_ADDRESS;
END IF;

EXCEPTION
WHEN TOO_MANY_ROWS THEN
RETURN 'ERROR - 2 OR MORE ADDRESSES FOR ' || p_debtor_ref ;

WHEN OTHERS THEN
RETURN 'ERROR- ' || SQLERRM;
END GET_CUST_BILL_ADDR;




Can anyone help?

It works fine if i remove the rowcount bit :

IF c1_add%rowcount >= 2
THEN
RETURN 'TOO_MANY_ROWS';
END IF;


Many thanks,

Gary

[Updated on: Fri, 02 May 2008 04:55]

Report message to a moderator

Re: Dont know how to write it... [message #317648 is a reply to message #317636] Fri, 02 May 2008 05:08 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Just to clarify, you are saying that what you want is that if this query:
 SELECT a.ADDRESS_ID
, a.ORIG_SYSTEM_REFERENCE
, Arp_Addr_Pkg.FORMAT_ADDRESS('',a.ADDRESS1, a.ADDRESS2,a.ADDRESS3,a.ADDRESS4,a.CITY,a.COUNTY
, a.STATE, a.PROVINCE, a.POSTAL_CODE, a.territory ) CONCATENATED_ADDRESS
, s.SITE_USE_ID
FROM hz_site_uses_v s
, ra_addresses a
, ra_customers c
WHERE s.address_id = a.address_id
AND a.customer_id = c.customer_id
AND c.customer_id = cv_external_ref
AND a.province = p_debtor_ref
AND s.site_use_code = 'BILL_TO'
AND s.status = 'A'
;

returns more than 1 row, then you want an error raised? Is that correct?
Re: Dont know how to write it... [message #317653 is a reply to message #317636] Fri, 02 May 2008 05:12 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
pablolee, thanks for your reply!

Yes that is exactly what i want, sorry if it was misleading! Embarassed

Gary
Re: Dont know how to write it... [message #317656 is a reply to message #317653] Fri, 02 May 2008 05:15 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Then just use standard SELECT INTO instead of an explicit cursor, if more than one row is returned then the TOO_MANY_ROWS exception is raised.

pseudo code:
BEGIN
  SELECT col1, col2, col3
  INTO record
  FROM tab1, tab2
  WHERE ...

Re: Dont know how to write it... [message #317683 is a reply to message #317636] Fri, 02 May 2008 08:18 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
How would i do that with the above? I generally only use cursors?
Re: Dont know how to write it... [message #317684 is a reply to message #317683] Fri, 02 May 2008 08:23 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
oracle documentation
Re: Dont know how to write it... [message #319161 is a reply to message #317683] Fri, 09 May 2008 04:18 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I received a PM from the OP asking for further help on this topic. I'm in a good mood today so Gary, here is an example of what I am talking about:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


Session altered.

SQL> CREATE TABLE g_demo (a varchar2(10), b varchar2(10), c varchar2(10));

Table created.

SQL> insert into g_demo values('a', 'aa', 'aaa');

1 row created.

SQL> insert into g_demo values('a', 'ab', 'abc');

1 row created.

SQL> insert into g_demo values('b', 'bb', 'bbb');

1 row created.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  l_val varchar2(10);
  3  begin
  4  select a into l_val
  5  from g_demo
  6  where a = 'a';
  7* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4


SQL> ed
Wrote file afiedt.buf

  1  declare
  2  l_val varchar2(10);
  3  begin
  4  select a into l_val
  5  from g_demo
  6  where a = 'b';
  7* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  l_val varchar2(10);
  3  begin
  4  select a into l_val
  5  from g_demo
  6  where a = 'c';
  7* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

Notice that when I select no rows into the variable the block automatically raises an error, when I select more than one row it raises a different error, when only 1 row is returned by the select, it is successful. You can use exception handling to handle the NO_DATA_FOUND and TOO_MANY_ROWS errors to return that which you want (I would use something more descriptive than 'Error' though.
Please note, I am absolutely not going to write this function for you, you are going to have to do that, I will however help you along the way. Next time you post, make sure that it is with your attempt to implement my example with your logic (remember, keep it simple, you don't have to get the whole thing done, just get the select into working correctly)
When you do post code, make SURE that it is formatted and placed between [code][/code] tags (like I did with my code example.
Re: Dont know how to write it... [message #319196 is a reply to message #317636] Fri, 09 May 2008 05:32 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
Thank you so much for your reply, this is what i've written :

FUNCTION GET_CUST_BILL_ADDR ( p_cust_id IN PLS_INTEGER
                                    , p_primary IN VARCHAR2 DEFAULT 'Y'
                                    , p_context IN VARCHAR2 DEFAULT 'REF'
									, p_debtor_ref IN VARCHAR2  )
		RETURN VARCHAR2
        IS
									
	cust_ad_id   		 VARCHAR2(20);
	cust_orig_sys_ref    VARCHAR2(20);
	cust_concat_ad		 VARCHAR2(240);
	cust_site_use_id	 VARCHAR2(20);											        
        BEGIN
		SELECT 		  a.ADDRESS_ID
                     , a.ORIG_SYSTEM_REFERENCE
                     , Arp_Addr_Pkg.FORMAT_ADDRESS('',a.ADDRESS1, a.ADDRESS2,a.ADDRESS3,a.ADDRESS4,a.CITY,a.COUNTY
                                                   , a.STATE, a.PROVINCE, a.POSTAL_CODE, a.territory ) CONCATENATED_ADDRESS
                     , s.SITE_USE_ID
		  INTO cust_ad_id,
		       cust_orig_sys_ref,
		       cust_concat_ad,
		       cust_site_use_id		
                  FROM hz_site_uses_v s
                     , ra_addresses a                           
                     , ra_customers c
                 WHERE s.address_id       = a.address_id
                   AND a.customer_id      = c.customer_id
                   AND c.customer_id      = p_cust_id
		   AND a.province     	  = p_debtor_ref     
                   --AND s.primary_flag     = cv_primary_flag
                   AND s.site_use_code    = 'BILL_TO'
                   AND s.status           = 'A';
				   
			RETURN cust_orig_sys_ref; 
				   
		EXCEPTION
		    WHEN NO_DATA_FOUND THEN
				 RETURN 'ERROR NO ADDRESS FOUND FOR ' || p_debtor_ref ;
				 
			WHEN TOO_MANY_ROWS THEN
                 RETURN 'ERROR - 2 OR MORE ADDRESSES FOR ' || p_debtor_ref ;
						
            WHEN OTHERS THEN
                 RETURN 'ERROR- ' ||  SQLERRM;
	  END GET_CUST_BILL_ADDR; 


The concurrent request completes now, but it doesnt display any errors Sad

How does that look?
Re: Dont know how to write it... [message #319199 is a reply to message #319196] Fri, 09 May 2008 05:36 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
How does that look?
Good job. I'm liking it so far Thumbs Up
Quote:
The concurrent request completes now, but it doesnt display any errors

Show me how you call it and what the result is (just a copy and paste of your session (again in between code tags)
Cheers
Jim
Re: Dont know how to write it... [message #319202 is a reply to message #317636] Fri, 09 May 2008 05:42 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
I use Toad to write and compile, and the Oracle Apps to call the main proc which then calls the function (along with LOADS of others).

Here is the line in the main proc which calls this function:

v_cust_bill_address := CCC_AR_UTIL_PKG.GET_CUST_BILL_ADDR(v_customer_ref,'Y','ID', v_hd_debtor_number);		-- GL 29/04/08 - Brought in debtor no					
IF v_cust_bill_address = 'ERROR'
OR v_cust_bill_address IS NULL
THEN
RAISE no_bill_add;
END IF;


Is that what you needed? Im very appreciative of your help, and feel quite chuffed i got the select into work Laughing Cool

Thanks,

Gary
Re: Dont know how to write it... [message #319208 is a reply to message #319202] Fri, 09 May 2008 06:15 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
OK, I don't tend to use TOAD myself (company is too stingey Smile )
Do this for me:
Make sure that serveroutput is on (I don't know how this is done in TOAD, but I'm sure that you do.

EXECUTE DBMS_OUTPUT.PUT_LINE(CCC_AR_UTIL_PKG.GET_CUST_BILL_ADDR(v_customer_ref,'Y','ID', v_hd_debtor_number))
and tell me what happens.

Quote:
feel quite chuffed i got the select into work

You should be. We'll talk about a bit of tidying up when we get the overall function operating for you.
Re: Dont know how to write it... [message #319212 is a reply to message #319199] Fri, 09 May 2008 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pablolee wrote on Fri, 09 May 2008 12:36
Quote:
How does that look?
Good job. I'm liking it so far Thumbs Up

No No NO.
The WHEN OTHERS clause is a BUG.

And what is the purpose of the other error handlers?
Do you do more you let the errors raise? No.
Get rif of them!

Regards
Michel

Re: Dont know how to write it... [message #319217 is a reply to message #319212] Fri, 09 May 2008 06:24 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Reel your neck in Michel Wink. I said SO FAR. I also pointed out that
Quote:
We'll talk about a bit of tidying up when we get the overall function operating for you.
And the when others would absolutely have been one of the things that I would have talked about. The OP is a newbie and I decided to take the process one step at a time. If you list a catalog of 'errors' some people may get disheartened. I wanted to get the SELECT INTO, and the overall concept working, then build from there.

[Updated on: Fri, 09 May 2008 06:25]

Report message to a moderator

Re: Dont know how to write it... [message #319220 is a reply to message #319217] Fri, 09 May 2008 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pablolee wrote on Fri, 09 May 2008 13:24
Reel your neck in Michel Wink. I said SO FAR.

OK, when I see a "WHEN OTHERS" I can't see anything else. It is the biggest error that can be done (once the syntax is correct) and then the first to be fixed.

Regards
Michel

Re: Dont know how to write it... [message #319221 is a reply to message #319220] Fri, 09 May 2008 06:33 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I don't necessarily agree with you (obviously) that it MUST be the first thing to fix. ONE of the first things? Yes, I agree, but a decided to treat this thread as a good learning experience for the OP and leaving in a WHEN OTHERS when there may well be a nice little error occurring and not being caught, would prove a very useful, not soon to be forgotten, exercise.
Regards
Re: Dont know how to write it... [message #319241 is a reply to message #317636] Fri, 09 May 2008 08:13 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
Eek, apologies for causing a slight rift.

I ran this:

EXECUTE DBMS_OUTPUT.PUT_LINE(CCC_AR_UTIL_PKG.GET_CUST_BILL_ADDR(80564,'Y','ID', 'CP312184'))


And in toad i get :

ORA-00900: invalid SQL statement

This is strange seeing as it let me compile it and the Concurrent Request completes without error?
Re: Dont know how to write it... [message #319242 is a reply to message #319241] Fri, 09 May 2008 08:16 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
A, there will likely be a tool that will run the code as a script rather than as a statement, find it and run it as a script.
Re: Dont know how to write it... [message #319244 is a reply to message #317636] Fri, 09 May 2008 08:21 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
As you replied, i used 'run in sql*plus' and it works a treat, i get my error back!

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE(CCC_AR_UTIL_PKG.GET_CUST_BILL_ADDR(80564,'Y','ID', 'CP312184'))
ERROR - 2 OR MORE ADDRESSES FOR CP312184

PL/SQL procedure successfully completed.


But its strange how its not returning in the concurrent request? Sad
Re: Dont know how to write it... [message #319250 is a reply to message #319244] Fri, 09 May 2008 08:39 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
But its strange how its not returning in the concurrent request?

What concurrent request? It is doing exactly what you have told it.
Re: Dont know how to write it... [message #319251 is a reply to message #317636] Fri, 09 May 2008 08:40 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
v_cust_bill_address := CCC_AR_UTIL_PKG.GET_CUST_BILL_ADDR(v_customer_ref,'Y','ID', v_hd_debtor_number);		-- GL 29/04/08 - Brought in debtor no
IF v_cust_bill_address = 'ERROR'
OR v_cust_bill_address IS NULL
THEN
RAISE no_bill_add;
END IF;
SQL> EXECUTE DBMS_OUTPUT.PUT_LINE(CCC_AR_UTIL_PKG.GET_CUST_BILL_ADDR(80564,'Y','ID', 'CP312184'))
ERROR - 2 OR MORE ADDRESSES FOR CP312184

PL/SQL procedure successfully completed.
Are you aware, that 'ERROR - 2 OR MORE ADDRESSES FOR CP312184' = 'ERROR' condition is false? So the exception is not raised.

Michel gave you right suggestion here - do not catch errors in the function; let it propagate to the calling block. Or, at least, if you desperately want to catch them, re-raise them immediately (except OTHERS exception, it shall not be catched anymore).
Re: Dont know how to write it... [message #319254 is a reply to message #317636] Fri, 09 May 2008 08:45 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
Are you saying i need to take the errors out of my function and put them in the main procedure?

I dont see how the function can tell the main procedure that theres 2 or more addresses if it can only return one?

When you say re-raise immediately, back in the main procedure? Under the IF v_cust_bill_address = 'ERROR' line?

Re: Dont know how to write it... [message #319264 is a reply to message #317636] Fri, 09 May 2008 09:09 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Are you saying i need to take the errors out of my function and put them in the main procedure?
I do not understand this question. Seems to me you misunderstand the concept of exceptions. It is described eg. in PL/SQL User's Guide and Reference, http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#i7014.

The CCC_AR_UTIL_PKG.GET_CUST_BILL_ADDR code may end successfully or raise an exception. You catch all of them and return some (strange) string.
In the caller, you decipher that string and eventually raise some exception not really connected to the origin of the error.
Would not it be better not to do these (almost meaningless) steps?

> I dont see how the function can tell the main procedure that theres 2 or more addresses if it can only return one?
The same way as you decipher it in the exception handling - detecting TOO_MANY_ROWS exception. But the main procedure does not work with this information, so why do you worry?

> When you say re-raise immediately, back in the main procedure? Under the IF v_cust_bill_address = 'ERROR' line?
No, in the EXCEPTION section of CCC_AR_UTIL_PKG.GET_CUST_BILL_ADDR function. But you should rather avoid doing it.

[Updated on: Fri, 09 May 2008 09:10]

Report message to a moderator

Re: Dont know how to write it... [message #319295 is a reply to message #319202] Fri, 09 May 2008 11:44 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
garylythgoe wrote on Fri, 09 May 2008 06:42

...				
IF v_cust_bill_address = 'ERROR'
...





Is this ever true?


BTW, in TOAD there's a DBMS OUTPUT tab. Click that, then click the RED DOT button so that it changes to GREEN. This screen will then display output.

Handle exceptions. The rest are errors. If you feel you need to handle errors (for logging, etc.), be sure to re-raise (with RAISE).


[Updated on: Fri, 09 May 2008 11:45]

Report message to a moderator

Re: Dont know how to write it... [message #319304 is a reply to message #317636] Fri, 09 May 2008 13:28 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Is this ever true?
Yes, when ORIG_SYSTEM_REFERENCE column contains value 'ERROR'.
Which I wanted to point out too - mixing return values with error messages is not very lucky and leads to unexpected (and unwanted) behaviour with very little chances to fix.

It is much more better to either reserve special variable for error messages, or, best, let the exceptions propagate without catching them.
Previous Topic: Need SQL Query
Next Topic: Column Header row
Goto Forum:
  


Current Time: Sat Dec 03 01:26:20 CST 2016

Total time taken to generate the page: 0.05731 seconds