Shaukat, This is probably too simple to solve your problem, but have you thought of creating a basic SQL script? This creates a file that when clicked on will open in Excel. SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF SET TERM OFF SPOOL SQLStr.XLS @SQLStr SPOOL OFF SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT OFF SET TERM ON -----Original Message----- I want to write a procedure at database level. It accept one parameter which is SQLStr.
SQLStr is a valid SQL select statement. The procedure Open MS Excel.
Tips on exporting your Oracle Apps reports to Excel Introduction Oracle E-Business Suite provides users with a host of standard reports. Quite often I have found. With extracting Oracle data into spreadsheets are the cumbersome interfaces and slow performance of ODBC. I never recommend any product that I have not personally used and tested, and I'm very choosey who I endorse. The easiest way to export data into a format that can be read by Excel is to spool SQL*Plus output. This shows you how to query an Oracle 11g database from Excel 2007. Actually, it should work on any current version of the Oracle database. I have an Oracle 10G database and I need to write a fairly straightforward query that joins two tables and selects some data. However, I'd like to export the result. JimatNOAA wrote: This may not be relevant at this late stage in the thread, but we write the spreadsheet to a BLOB column from the PL/SQL procedure and from there you can open it and do whatever you want with it. There are plenty of write-ups on that topic: Re: 5. How do I read or write an Excel file?
Executes the SQL query in SQLStr. Write all the data retrieved by SQL Query into excel. I can import data from oracle to excel using SQL Plus *.lst file. But I want a procedure at database level to do this.
Is it possible? Can any one help me?
I had the following error occur recently. System.Runtime.InteropServices.COMException (0x80004005): ORA-01019: unable to allocate memory in the user side at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options) I managed to resolve the problem by simply modifying my connection string. From: 'Provider=MSDAORA.1;Data Source=tprss;Persist Security Info=True;User ID=myUser;Password=myPassword' To: 'Provider=MSDASQL;Data Source=tprss;Persist Security Info=True;User ID=myUser;Password=myPassword' someone modified/updated the components on the box. (This didnt fit in a Comment box) You need at least one driver. The oracle driver is best but Microsoft Driver will work too.
Lets first try to make a connection string. Right click on your desktop and the create a new.txt file. Now rename your textfile to something.udl Double click on the udl file.
Go to 'Provider' and select Microsoft OLEDB Provider for Oracle. Then click on next. In the server name field you fill in your TNS name. Then username and password and put a V inside 'Allow saving password' (we will need this) And click on test connection. Make sure this works. If it works then click on OK. Now open the UDL file with a text editor.
You will see something similar to: [oledb]; Everything after this line is an OLE DB initstring Provider=MSDAORA.1;Password=yourpw;User ID=youruser;Data Source=yourTNS;Persist Security Info=True Copy this part into your connection string: Provider=MSDAORA.1;Password=yourpw;User ID=youruser;Data Source=yourTNS Now your connection string should look like: Conn = 'Provider=MSDAORA.1;Password=yourpw;User ID=youruser;Data Source=yourTNS' I hope this works.