TOAD does a great job of being able to take a result set and then exporting by simply clicking on export to Excel (either as instance (tab) or File (new Excel Sheet).
Is there a way to do this within PLSQL so it can be automated if one were to provide the Excel sheet as a parameter somehow?
It’s kind of hard to really suggest something without knowing what specifically you’re trying to do. My guess is you’re massaging the data in some manner, which is why you want to use PL/SQL. But, then you have to output it somewhere (table, file, etc.). If that’s the case then you could write it to a temp table and then maybe use something like below to do a normal query against it. Or maybe write the results to a comma delimited file and then import it into Excel. Might take some creativity, but Automation Designer is pretty powerful for these types of things.
Here is a similar post (http://www.toadworld.com/products/toad-for-oracle/f/10/t/23989.aspx).
is it possible to see/grab the “code” behind the export to excel and utilize it within PLSQL?
Sorry, Gerald, I can’t give you that. Take a look at this though http://www.oraexcel.com/. I’ve never used it, so I can’t vouch for it, but I do know that this product has been around for ages, so I’m guessing it works pretty well.
No problem. I had looked into it but saw a thread somewhere that said that TOAD does not recognize the oraexcel keywords. I’ll have to give it a try to verify
I’m not sure what you mean by “does not recognize”. As I understand it, this is just a package that you can buy to install in the database, so Toad should recognize it just as well as anything that anyone else writes…
Yep - that was probably someone knowing less than I do 
I just tried it and it works…sorta… in our situation. Our problem is that we have Oracle on a Database Server and we access TOAD through a diffferent server - our Web Server. Why I tried creating a drive within Oracle that it requires, I could only use drives on the Database Server. If I map a drive on the Database Server back to the webserver and use that, it doesnt work.
Sure wish I knew how TOAD sees our Web server directories.
Toad’s going to be able to see whatever folders you can see in MS Explorer on the machine that Toad is installed. I’ve never tried to look at a mapped drive from Oracle, but it sounds like the Oracle user on the server doesn’t have privileges to the mapped drive.
If you are using PL/SQL for this, unless I'm mistaken, any file activity is done on the Oracle server. Which means that not only do you need rights to the file system on the server, you have to be able to either access the file system from a local computer to get the files once they are written there, or the server needs to be able to access another server or your local computer to move said files to a location that you can then access.
Our DBAs here are very hesitant(read this to mean that we've never come up with a valid reason for them to give us this ability) to allow access us access to write to the server and our Oracle server is set up so it can't get to the other servers easily.
Unless you have a compelling reason for this process to be done via pl/sql, you might be better off finding another way to get your spreadsheets automatically, such as using the automation designer + windows scheduler or another program.
On Thu, Mar 19, 2015 at 10:20 AM, John Dorlon bounce-jdorlon@toadworld.com wrote:
Reply by John Dorlon
Toad's going to be able to see whatever folders you can see in MS Explorer on the machine that Toad is installed. I've never tried to look at a mapped drive from Oracle, but it sounds like the Oracle user on the server doesn't have privileges to the mapped drive.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
--
Phyllis Helton
Data Magician
Digital Strategies, Cru | Data Sciences & Analytics
Office 
407-515-4452
We are having our support service staff look into how we could have Oracle have the ability to write to a shared drive on another server. I’ll update when/if I find out.
The reason we cant use automation designer is because this woldnt be a scheduled task per se. It would be more of a procedure oriented ad hoc time of item " do this procedure, query and lets see the results in a spreadsheet"
Automation designer appears to be more schedule oriented and I’m not sure about the ability to use it on an adhoc basis from PLSQL.
It’s pretty simple actually. The key is once the Oracle server has access to the shared drive, just setup a db directory that points to that location and then use pl/sql to read/write files as you normally would. It can get a little tricky, but the key is just making sure Oracle can see the drive. For example, on a windows server, I had to make sure the admin user that starts the services (DB, Listener, etc.) had access to the shared server (and subfolders).
Dennis -
If you dont mind…
(sorry about off topic a bit)
What I did was map a drive on the database server to Z:
The Database Server could write to it.
I then did this inside oracle:
create or replace DIRECTORY EXPORT_DIR AS ‘Z:’;
GRANT WRITE ON DIRECTORY SYS.EXPORT_DIR TO ;
The test procedure indicated that there was a problem with writing to the area/file:
ORA-20100: Procedure save_to_file -20100 ORA-20100: Procedure blob2file -29283 ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 536
ORA-29283: invalid file operation
I’m missing a piece, I’m sure
You actually don’t need to map a drive on the server - that’s a little confusing, and actually I didn’t realize this the first time I did it either. The only thing you have to make sure of is that the account (probably ‘oracle’) has priv’s to read/write to the share. For instance, we have a shared Server (let’s say it’s called ‘Toad’). I would go on the Toad server and grant read/write to ‘oracle’ to the dir’s it should have access too. Then I would create a db directory that points to ‘\Toad{directory_name}’.
If I can help more, please feel free to email me offline (Dennis.Paulus@quest.com), or you can private message me on TW as well.
Hope this helps.
To conclude this discussion -
We created an “oracle” user that has windows rights to read/write to the other server.
Oracle now starts automatically by way of this user.
Within Oracle, we have created a DIRECTORY and given Oracle rights to schema(s) to utilize this DIRECTORY. We assign this directory to the area on the other server where we wish to output.
Works like a champ.
