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:

strConOracle = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=" & strHost & ")(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"
Set oConOracle = CreateObject("ADODB.Connection")
Set oRsOracle = CreateObject("ADODB.Recordset")
oConOracle.Open strConOracle
Set oRsOracle = oConOracle.Execute(strSQL)

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.

View 0 Replies


ADVERTISEMENT

Windows :: Error When Running Macro Excel

Jan 13, 2012

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.

View 1 Replies View Related

SQL & PL/SQL :: How To Append Data Into Macro-enabled Excel File

Aug 18, 2010

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?".

View 3 Replies View Related

Forms :: Create Macro In Excel With OLE2

Apr 15, 2010

URL.... I have one query.Aim of Program: Create Macro in Excel with OLE2 in ORACLE FORM.I mention below codingDeclare

application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
WorkFont OLE2.OBJ_TYPE;
vbproject OLE2.OBJ_TYPE;
vbcomponents OLE2.OBJ_TYPE;
item OLE2.OBJ_TYPE;
codemodule OLE2.OBJ_TYPE;
args OLE2.LIST_TYPE;
[code]...

View 2 Replies View Related

Client Tools :: Not Able To Make ORA Connection Through Macro In Excel

Mar 15, 2012

i have windows 7 on machine. When i am trying to connect to oracle client using macros from excel like

OraSession = CreateObject("OracleInProcServer.XOraSession")

its giving me error saying "activex component cant create the object."

View 3 Replies View Related

Client Tools :: Export Data From Oracle Database To Excel File?

Aug 21, 2012

I am using SQLTools 1.5 for writing Oracle SQL scripts.

I have to import data from excel file to oracle database. How can I do it?

Also how can I export data from Oracle database to Excel file?

View 2 Replies View Related

Windows :: Import Ms Project Data Into Oracle Database?

Sep 5, 2012

Is there any method to import Ms Project data into Oracle Database.

View 1 Replies View Related

PL/SQL :: Query With Group By - Fetch Data From Database

Jul 26, 2013

I am trying to write a proper query to fetch data from database. Scenario:

I need to retrieve employees who are not working in multiple departments. scott@TESTCRM> select * from emp1;     

EMPNO     DEPTNO
---------- ----------     
7654         30      7698         30      7788         20      7788         30      7876         20      7900         10      7900         30    7902     20    7934     10 

scott@TESTCRM>  

Ouput Expected is      

EMPNO     DEPTNO
---------- ---------- 
7654         30      7698         30      7876         20      7902         20      7934         10

View 9 Replies View Related

Windows / .NET :: Return String From Oracle Stored Procedure Using Excel 2003 VBA

Apr 12, 2013

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.

View 2 Replies View Related

Windows :: Does Oracle Database 10.1.0.2 (64-bit) Compatible With Windows 2003 R2

Jan 9, 2011

Does Oracle Database 10.1.0.2 (64-bit) compatible with windows 2003 R2 standard X64 edition with Service pack 2.

View 1 Replies View Related

Join Two Oracle Tables For Fast Fetch Of Data From Table?

Jul 2, 2012

join two oracle tables for fast fetch of data from table.

View 1 Replies View Related

Server Utilities :: Export Excel Sheet Data In Database Table?

Jul 17, 2012

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.

View 21 Replies View Related

Load Excel Data To Oracle?

Jul 3, 2008

How can i convert my ms excel record into oracle records??

View 3 Replies View Related

SQL & PL/SQL :: Loading Data From Oracle Into Excel

Jul 18, 2012

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.

View 5 Replies View Related

SQL & PL/SQL :: Excel Data Import Into Oracle?

Jun 25, 2012

how excel data import into oracle

View 1 Replies View Related

SQL & PL/SQL :: How To Load Excel Data Into Oracle

Dec 28, 2010

how to load a XL sheet data into oracleDB.

View 7 Replies View Related

SQL & PL/SQL :: Inserting Excel Data Into Oracle?

Sep 24, 2011

I want to insert excel data into Oracle.

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

View 9 Replies View Related

SQL & PL/SQL :: Reg Data Exporting To Excel From Oracle?

Dec 8, 2010

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.

View 4 Replies View Related

Forms :: Import Data From Excel To Oracle?

Jun 21, 2013

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.

View 1 Replies View Related

Forms :: Use Excel Formulas In Oracle Database / Report

Jul 21, 2010

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.

View 3 Replies View Related

SQL & PL/SQL :: Transfer Excel Files From Unix To Oracle Database?

Jun 6, 2013

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.

View 22 Replies View Related

Getting Error While Loading Excel File Into Oracle Database

Dec 30, 2010

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.

View 1 Replies View Related

Forms :: Data Load From Excel To Oracle 10g Through 10g Client

Aug 13, 2011

how to load data from excel sheet to oracle 10g form from client.

View 1 Replies View Related

SQL & PL/SQL :: How To Load Oracle Table Data Into EXCEL Sheet

Jan 3, 2011

how to load oracle table data into EXCEL Sheet .

View 5 Replies View Related

Forms :: Oracle Code To Export Data To Excel

Jan 8, 2010

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;

[code]....

View 13 Replies View Related

Import Data From Excel To Oracle Table Using Toad

Dec 26, 2012

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.

View 4 Replies View Related

Forms :: How To Connect Excel With Oracle Database Via 10g Login Screen

Jul 20, 2010

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.

View 3 Replies View Related

Forms :: Batch Program Retrieve Data From Oracle To Excel

Jul 5, 2011

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?

View 2 Replies View Related

SQL & PL/SQL :: Procedure To Load Data In Oracle Table Into Excel File

Dec 29, 2010

send me the procedure for loading the data in an oracle table into an excel file.

View 5 Replies View Related

Server Utilities :: Import Millions Of Data From Excel To Oracle 11g?

Mar 6, 2013

how to import millions of data from excel to oracle?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved