Python Program running agaist Oracle Database
I am trying to write a program in Python which do the following:
Using Windows environment,
1) Run queries against an Oracle Database
2) Generate output of the Queries in Step 1 as Excel Files
3) Import the Excel file into an Access Database
Please advise.
Using Windows environment,
1) Run queries against an Oracle Database
2) Generate output of the Queries in Step 1 as Excel Files
3) Import the Excel file into an Access Database
Please advise.
Mark Geerlings
Both Excel and Access can query Oracle using ODBC. What value does Python add to this combination? It sounds simpler to me to just use Excel or Access.
ASKER
Hi Mark, You bring up a good point and I am well aware of that. I am trying to develop a process using Python (that is what is available in my environment) which would 1) run queries against an Oracle Database 2) Output result as Excel Files 3) import the excel file in an Access application (that is what is used by the end users). Currently this is a manual process. Some one has to the three steps sequentially and it takes abut an hour or more. I think with Python, I could make a program that could be scheduled to run at a particular time and would generate e-mail with a log file showing the success or failure of the process. In case of failure, human intervention would be required, else we keep going.
I am teaching my self Python. I put this question here to know if something like this has been done by some one else.. Just want learn from someone experience instead of reinventing the wheel. Any suggestions or comments will be greatly appreciated. Thanks.
I am teaching my self Python. I put this question here to know if something like this has been done by some one else.. Just want learn from someone experience instead of reinventing the wheel. Any suggestions or comments will be greatly appreciated. Thanks.
What is wrong with a sqlplus or SQLCl script to create a CSV and load that directly into Access?
I'm not seeing the need for Excel.
With SQLCl going straight to a CSV is really simple:
set sqlformat csv
set feedback off
spool some_file.csv
select * from some_table;
spool off
I'm not seeing the need for Excel.
With SQLCl going straight to a CSV is really simple:
set sqlformat csv
set feedback off
spool some_file.csv
select * from some_table;
spool off
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I will say that I experienced something very similar. We solved it a very different way though. Why are you going through so many steps and to get the data into Access? I understand wanting to learn something new, but you are seriously complicating a very simple process.
Depending on the complexity of the query on the database side, what we did, is create a materialized view (or you can have a procedure to populate tables), that holds the data on the database side. Then with a link in Access, you can easily query that view/table as if it were in Access, or even very simply pull all the data across.
Using this method, we took a 2 day manual process and made it into a scheduled job that ran overnight in Oracle and they just pulled their results in the morning.
Depending on the complexity of the query on the database side, what we did, is create a materialized view (or you can have a procedure to populate tables), that holds the data on the database side. Then with a link in Access, you can easily query that view/table as if it were in Access, or even very simply pull all the data across.
Using this method, we took a 2 day manual process and made it into a scheduled job that ran overnight in Oracle and they just pulled their results in the morning.