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 use stored procedures and functions (Read 4965 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
How to use stored procedures and functions
20.10.05 at 16:47:25
 
This question was sent to me by a client.  
 
Question:
Can I call stored procedures with SQL*XL?
 
Answer:
Yes you can. There are a few ways you can use stored procedures and functions in SQL*XL:
  • You can use the new resultset_from_proc SQL*XL command to call a stored procedure in Oracle databases and display any rows returned through a ref cursor. For example you can execute the statement: select * from resultset_from_proc('mypackage.my_proc(?)');
    For more information see:  
    http://www.oraxcel.com/projects/sqlxl/help/commands/resultset_from_proc.html
  • There is the possibility to to call stored procedures throught he SQLProcedure worksheet function. You can call the functions through the SQLFunction worksheet function. These worksheet functions are undocumented for the moment because they will only work with Oracle connections through OO4O.
  • You can also call your normal stored procedures. Just use anonymous PL/SQL blocks in the SQL dialog:
     
    begin  
      my_package.my_procedure(1,2,3,4,5);
    end;
     
    You can also bind the procedure to cells in Excel. Example:
     
    begin
      my_package.my_procedure(:A1,:A2,:A3);
     
      :B1 := my_package.my_function(1,2,3,4,5);
    end;
     
    This will work both with input and output variables. Also have a look at the SQLFunction and SQLProcedure worksheet functions. They will also help to call stored procedures and functions.
     
    Other uses I have seen are:
     
    begin
      select min(sal) into :min_sal from emp;
    end;
    select :min_sal as "Min Sal" from dual;
    select * from emp where sal = :min_sal;
     
    In the last example the min_sal parameter was created and populated with the value of the minimum salary found in the emp table.
Back to top
 
« Last Edit: 23.09.13 at 21:28:27 by Gerrit-Jan Linker »  

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




Posts: 75
Re: How to use stored procedures and functions
Reply #1 - 27.02.07 at 15:19:37
 
When using SQL Server stored procedures you can run the following commands straight from the SQL dialog. I am using the built in stored procedure sp_databases that return the details for the databases configured in the SQL Server.
 
From the SQL*XL SQL dialog run the following commands:
execute mydb.dbo.sp_databases;
mydb.dbo.sp_databases;
sp_databases;
Back to top
 
« Last Edit: 27.02.07 at 15:24:04 by Gerrit-Jan Linker »  

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