Windows / .NET :: Excel VBA Macro To Fetch Data From Oracle Database
Aug 1, 2012
we are using ERP System based on Oracle DB. I have an oracle client 10.2.0 installed on my machine (Win 7/64-Bit) and trying to create Excel(2010) VBA-Macro to get datas out of the Oracle DB using a SQL query:
On my old machine (Win XP/Excel 2007) following worked fine:
All the datas in the variables coming from cells or userforms - I am 100% sure all this is correct, as as said, it works on any Win XP Machine with Excel 2007
But trying same with Win 7/64-Bit/Excel 2010 gives me following error: MS ODBC Driver for Oracle: ora-01019: unable to allocate memory in the user side.
I installed Oracle 11gR2 database for windows 7. I believe it's 32 bit. I keep getting an error when I run my macro excel: Error while trying to retrieve text for error ORA-01019.
My requirement is to export Oracle table's data into an already existing excel file with Macros (.xlsm) using a procedure. I am able to write/append the data into the simple .xls file. But I am searching whether any way of appending into .xlsm file. "how can we append the data into a Macro-enabled excel file?".
I am trying to return a value of an oracle stored procedure using Excel VBA.
OS: Win XP SP3 Excel 2003 Ora Client: 11g
By trying different things I have noticed, that I could have troubles with the ODBC-connection. Maybe I am not using the right one. To store data returned from select statements I have an ODBC-Connection (Driver: Oracle in XE), which works perfectly, e.g.:
'----------------------- Sub Extract_Data() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim db_name, UserName, Password As String cn.Open db_name, USerName, Password
[code]....
When debugging the connection string, I find the Provider=MSDASQL5.1.
i want to export excel sheet in database table, so i have converted excel file in .csv file(comma delimated)and made control file, then i started sqlldr by double clicking on it. path is-D:oracleproduct10.2.0client_1BIN
i run this command from cmd-
Microsoft Windows [Version 6.1.7600] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:UsersNeetesh>sqlldr scott/tiger@localdb control=c:/users/neetesh/scott_data. ctl SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jul 17 17:20:33 2012
[code]....
and i attached the .ctl file. and .csv file is stored on same directory as .ctl file, why oracle couldn't find the .ctl file.
We have requirement such that whenever stored procedure is executed, their resultant records has to be stored in excel file ( Just like an reports ).No third party tool or reporting tools are used.
is there any option in oracle (Stored procedure or built in packages ) which can create excel file with the resultant records.
Excel File Name : Product 01 Excel columns File Send on 13/02/2011
Arrival Date Product Code Gate Pass Quantity Inpection 01/02/2011 00002 Y 2 Y 03/02/2011 00001 Y 10 Y 04/02/2011 00005 Y 14 Y 03/02/2011 00006 Y 74 Y
File Send on 14/02/2011
Arrival Date Product Code Gate Pass Quantity Inpection 01/02/2011 00002 Y 2 Y 03/02/2011 00001 Y 10 Y 04/02/2011 00005 Y 14 Y 03/02/2011 00006 Y 74 Y ---New Updated Data 05/02/2011 00002 Y 2 Y 06/02/2011 00001 Y 10 Y 05/02/2011 00005 Y 14 Y 05/02/2011 00006 Y 74 Y
I just want to insert data according to my structure But if again the same file send with updated data it will only update the new data because previous data is imported.
Oracle Structure Arrival Date Date, Product Code char(5), Quantity Number
Now we are having 100+ sql queries and we making all those queries as procedures.after that we want to schedule those procedures and get data to export into excel file.
so we are planning to use utl_file to get data export excel. we may have rows of 30000 above.is it utl_file will be able upload all these rows into excel.any performance issue will come.
I have designed a form to import data from an excel worksheet into an Oracle Form and It works pretty fine.
I use either Ole2.get_char_property or Ole2.get_num_property depending to the property of the column to read data but there are some columns used by both property which means that database is designed as VARCHAR2. The user can maintain either a numeric or characteristic data there. So I am looking for a solution to be able to make the program more flexible that he can read the property of the cell in excel file and decide what to use between Ole2.get_char_property and Ole2.get_num_property to read from excel file properly.
solve the problem regarding reading from Varchar2 columns.
i want to ask that is there any way to use the excel formulas on oracle forms, reports and database level. for example if we want to use IRR formula of excel to use in oracle forms, database and report.
I would like to ask you if you know which built-in can I use for transferring a excel file from our Unix box to a table in oracle database, right now we are using webutil_file_transfer.Client_To_DB_with_progress using forms developer, but I need to run as an automatic process uploading form unix into oracle directly without using forms.
I am loading a Excel file into oracle database using a ORACLE form 6i and i am getting an ORA-302000. This Form run and load database file into database in many pc.
But One of the PC COuld not loading a Excel file into oracle database. Also i can't Create Excel file Database Through form 6i.
I do the codding procedur in oracle form code to export data to Excel.But when i run the form and call that procedure. The data is come to excel, but it's autometic create a new sheet(as "Sheet4").
The problem is.. How can specify the Sheetname or fix it as "Sheet1"?
---Following it's my code
PROCEDURE Export2EXCEL_FILE(P_MAXCOL_NUM NUMBER) IS cursor dtl is select * from SG_TMP_REPORT_RESULT where SOURCE_ID = :BLK_CTRL.NB_SOURCEID ORDER BY COLTYPE; row_num number; col_num number; cell_val number;
how to import data from Excel sheet (inside server) to oracle DB.
Explanation : I need to automate this work, whenever i get an excel sheets with table values, i need to import the table values in to oracle DB automatically.I need an immediate solution for this.
In fact I have around 50 excelbooks that are connect with oracle via ODBC/or any other way. My task is to build the login screen on form 10g. This form will validate the user via .dll after successfully validation, Form will connect to specific oracle user. This Login Screen will call the new form that will execute the query for the list of excel sheet [table that will keep the name, description and path of the excelbook]. User will select the one excelbook to open. As user will select Excelbook should be open. Now this excel book should be connected with specific oracle user.
Question 1: How to open the excelbook, that excelbook should be connected via form 10g login screen. After connection excel has its own micros to run he query from oracle database.
Question 2: How I can create the mirco into form 10g to run into excelbook/excelsheet.
Question 3: How I can pass the query or function/procedure to the excelbook/excelsheet.
I'm developing a new batch program retrieve data from oracle to excel.Normally i insert those data into single sheet. Is it possible if I want do this into multiple sheet.
Eg: I need to insert data base on branch category. different sheet for different branch but still in a single workbook?
