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
Need to call stored procedures for validation (Read 4915 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Need to call stored procedures for validation
20.01.06 at 12:50:17
 
Kannan from Uganda asked the following question today:
 
Question:
I need some validation done at database level for which the stored procedures are already exists.  How do I use stored procedure with SQL*XL running, and i need to use it in XL VBA with MS forms 2.0 without any OO4O, or even ODBC.
 
Answer:
I suggest you record a few macros for the validation functions you want to use. If it is a stored function use the syntax
:my_param := my_package.my_function(1,2,3);
 
If you use a stored procedure you use something like:
my_package.my_function(:my_param,1,2,3);
 
You can check the value of the parameter in VBA to see what the outcome of the validation is.
Back to top
 
« Last Edit: 20.01.06 at 12:51:51 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
kasu
YaBB Newbies
*




Posts: 1
Re: Need to call stored procedures for validation
Reply #1 - 21.01.06 at 01:00:03
 
Hi Linker,
 
I used the following and i am still getting an error message, can you please help me ?
 
  1  declare
  2  xuser varchar2(10) := 'IMPSUNDU';
  3  xpwd  varchar2(10) := 'RESERVED';
  4  xecd  varchar2(10);
  5  xresult boolean;
  6  begin
  7  xresult := smpks.fn_check_user_password(xuser,xpwd,xecd);
  8* end;
SQL> /
 
PL/SQL procedure successfully completed.
 
SQL>  
 
but the same in SQL*XL gives the following error message.  i have pasted in the sql dialog.
 
ORA-06550:line 1, column 8:
PLS-00103: Encountered the symbol "" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
The symbol "" was ignored
ORA-06550: line2, column 107:
.....
begin case declare exit for go to if loop mod null pragma raise return select update while with <an identifier> ..
 
this shown as msgbox
where am i making mistake.  
The function verifies and returns boolean as the result  
i set the parm value of :a23 cell instead of xresult i get the similar message.
 
Kannan from uganda.
kasu
--------------------------------------------------------------------------------
-------
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Need to call stored procedures for validation
Reply #2 - 21.01.06 at 23:20:08
 
Thank you for letting me know.  
 
I found out that there was a small problem that crept in recently. I fixed the problem in SQL*XL 4.0.36.
Again, thank you for reporting it.
Back to top
 
 

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