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

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

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.

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>

Select allOpen in new window

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

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.

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

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