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
Support for named/mixed sub-program parameters (Read 2825 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Support for named/mixed sub-program parameters
16.10.09 at 14:23:56
 
Support for named/mixed sub-program parameters
 
Quote:
Does SQL*XL supports named/mixed sub-program parameters? Bellow, is an example.    

raise_salary(amount => bonus, emp_id => emp_num); -- named parameters
raise_salary(emp_num, amount => bonus); -- mixed parameters

For more info, please refer to Oracle’s PL/SQL Guide and Reference - Passing Parameters to PL/SQL Subprograms.

 
This will work fine. As a test I have created a database procedure and using the ORAOLEDB driver (provider) I will execute a call to the procedure with different parameters.
 
The test procedure I created:
Code:
procedure DoNothing(param1 in number, param2 in number)
as
begin
  null;
end;
 


 
In SQL*XL I typed the following PL/SQL blocks. I actually typed these in the spreadsheet. Selecting the cells that contain the PL/SQL I opened the SQL dialog and I ran the code using the option that all cells make 1 statement.
 
Test 1:
Code:
begin	
	DoNothing(1,2);
end;	
 


Worked without problems.
 
Test 2:
Code:
begin	
	DoNothing(param1=>1,param2=>2);
end;	
 


Worked without problems.
 
Test 3:
Code:
begin	
	DoNothing(1,param2=>2);
end;	
 


Worked without problems.
 
Test 4:
Code:
begin	
	DoNothing(param1=>1,2);
end;	
 


An error occurred but that is ok. This statement is not correct as when you use mixed mode the fixed parameters should go first. The error received was:
ORA-6550
PLS-00312: a positional parameter association may not follow a named association
Back to top
 
« Last Edit: 16.10.09 at 14:31:14 by Gerrit-Jan Linker »  

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