Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
How to pass long strings into a bind variable (Read 6113 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
How to pass long strings into a bind variable
24.02.06 at 15:34:53
 
Steve from the UK asked the following question.
 
Question:
Is there a 200 character limit (by any chance) on the length of a string (varchar2) parameter when it is passed back from an Oracle procedure back into a VBA function using the SQLXL interface?
 
I'm getting this error when the package tries to pass a longer string....
ORA-06502: PL/SQL : numeric or value error: character string buffer too small
ORA-06512: at "CONFIGDEV.SJT_SQLXL_IO_1", line 1181
 
Line 1181 contains the assinment of the return parameter:
 
  p_pump_list := v_pump_list;
 
where p_pump list is defined thus...
 
PROCEDURE GET_GA_DIMENSION_LOOKUPS(p_dimension_id in number, p_pump_list out varchar2, p_motor_list out varchar2)
 
Answer:
There is a maximum size defined. When using an ADO connection I have hardcoded a buffer of 255 characters for a string. In OO4O the driver sets a default of 126 characters.
 
I have extended the syntax for the variable command for you so you can set the size of a bind variable:
variable p_pump_list varchar2(1000);
Execute this command before you execute the call to your stored procedure.
 
This feature will be released in SQL*XL 4.0.41
 
Also see the syntax for the Variable command:
http://www.oraxcel.com/projects/sqlxl/help/commands/variable.html
Back to top
 
« Last Edit: 24.02.06 at 15:39:19 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1