SQL Server calling Oracle SP - ignores parameter length, must be 4000

Spiratec
Spiratec used Ask the Experts™
on
I am an Oracle Developer and know nothing about SQL Server but someone is trying to call one of my procedures from SQL Server and generally it works ok apart from one problem.  There is one out parameter - a refcursor, and most fields in the refcursor return fine (tested one by one to see where failing), but where there was a field created from a function in the select statement (eg. display_name in code section below), then it attempts to return it as a varchar2(4000) (which I think is the default in PL/SQL) even though the record type in the spec specifies it as varchar2(45). Now we can get it to work by SQL Server setting their parameter to 4000 also, but it seems a risky implementation, possible performance problems maybe, not sure will test. But wondering if anyone has come across this and knows how to address it properly?

eg. ttl_abbr							title,
	  DECODE(cust_ctyp_code,100,cust_forename,NULL)			cust_forename,
	  DECODE(cust_ctyp_code,100,cust_surname,120,cust_comp_name)	cust_surname,
	  pkco_cmn_get_cust.get_display_name(cust_id)			display_name

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mohed SharfiVice CEO

Commented:
Hi Spiratec,
I hope you fine
Please see this blog:
http://blogs.objectsharp.com/cs/blogs/matt/archive/2005/06/13/2221.aspx
thanks

Author

Commented:
Thanks for this mSharfi. I have actually seen this before and this is the way I did it before but this team I am writing it for now want to use a refcursor as output rather than the pl/sql table. Now I had thought it didn't work with refcursors which is why I had used this pl/sql table approach in the past, but their test example did work and so it was opted to go with the refcursor approach - it was only when I added a column that was populated via a function that the problem arose eg. like for the display_name here.

SELECT  cust_forename, cust_surname,
        pkco_cmn_get_cust.get_display_name(cust_id) display_name      
FROM     tdco_customers...

If refcursors are not a runner then I will tell the team that we need to go back to the pl/sql table approach, it's just that the problem is very specific now and everything else works so I know they'll want to get to the bottom of it.

Commented:
There's nothing wrong with the ref-cursor approach.  The reason that one of the columns in your ref cursor is defined as VARCHAR2(4000) is because the column in question is an expression (function expression).  

When declaring a function In PL/SQL which has a VARCHAR2 return-type, you don't  specify the size.  Because of this the function pkco_cmn_get_cust.get_display_name could return a VARCHAR2(1), VARCHAR2(22), etc..up to a maximum of VARCHAR2(4000) (maximum supported size in SQL).  However, even if you define the return type as anchored (e.g. RETURN table_a.col1%TYPE), this doesn't restrict the value that the function can return.

If you are really worried about this you can use CAST to convert the expression to a VARCHAR2(45).  However, if your function returns a value bigger than this, you will get an error (PL/SQL: numeric or value error: character string buffer too small).  Because of this I would leave it as VARCHAR2(4000).
SQL> CREATE OR REPLACE VIEW view_1 AS
  2  SELECT 'abcdefghijklm' x FROM dual
  3  /
 
View created.
 
SQL> CREATE OR REPLACE FUNCTION a RETURN view_1.x%TYPE
  2  IS
  3  BEGIN
  4    return '1234567890abcde';
  5  END;
  6  /
 
Function created.
 
-- Even though "DESC" shows a RETURNS CHAR(13), calling the function returns CHAR(15)
SQL> DESC a;
FUNCTION a RETURNS CHAR(13)
 
SQL> SELECT a() FROM dual;
 
A()
--------------------------------------------------------------------------------
1234567890abcde
 
-- Use CAST to convert the expression to a sized datatype
SQL> CREATE OR REPLACE VIEW view_2 AS
  2  SELECT a() a,
  3         CAST ( a() as VARCHAR2(54) ) b
  4  FROM dual
  5  /
 
View created.
 
SQL> desc view_2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(4000)
 B                                                  VARCHAR2(2)
 
SQL>

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
opps, mistake there in the output - copy and paste error! It should be:
SQL> CREATE OR REPLACE VIEW view_2 AS
  2  SELECT a() a,
  3         CAST ( a() as VARCHAR2(54) ) b
  4  FROM dual
  5  /
 
View created.
 
SQL> desc view_2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(4000)
 B                                                  VARCHAR2(54)
 
SQL>

Open in new window

Author

Commented:
Thanks Andy, that clarifies things. I was aware that you don't specify a size on a VARCHAR2 parameter and that it takes the default but I thought things worked differently for refcursors. Actually I've just checked you can't even create a refcursor without specifying the sizes of the VARCHAR2 fields in the record type, otherwise you get an error in the compilation of the spec: PLS-00215: String length constraints must be in range (1 ..32767).  However, while you are forced to enter sizes in the record type it does appear to ignore them as you say.

Andy, I am right in thinking that if I left them all at 4000 without using CAST that it will still only take the data as is (since VARCHAR2 doesn't pad the field out like CHAR) and that there should be no performance hit for passing alot of fields like this back to the front-end? Tested for one field and no obvious problem but don't know yet the impact of lots of fields like this for multiple calls to the procedure.

Thanks for your help.
Ann

Commented:
Hi Ann,
Yes that's correct, the VARCHAR2(4000) won't pad like CHAR does.  However, there are potential
performance implications, since you need to allocate extra storage in the client
to hold the results.  The client knows that column *could* return <= 4000, so it needs
to allocate storage accordingly,  "4000 * <array fetch size>" bytes.  

This may, or may not be a problem, depending on how many fields you have like this,
and on what environemnt/language you are using for your front-end.  

If you have a number of fields like this, then I might actually go for the cast solution.  
Regardless of what you decide, you should definitely test this before you make any decisions.

Author

Commented:
Hi Andy, thanks for that, you've been very helpful.

Commented:
no problem, glad I could help.  Good luck! : -)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial