From the datawarehouse to the chart (part 1) After Access lets use Excel as a client for Oracle.

I work for a real estate company where assets managers always want to make charts or calculus based on data stored in the Oracle dataWarehouse. Most od the time they want to do this with Excel because well known this tool. Sometimes they want to use Excel just because its easy to format data and then to put it in a Powerpoint document.

The problem is that we do not want to use VB macro in those Excel files . Those files may be sent by e-mail to clients. Any good firewall will destroy such files.

One solution is to generate files on a server. For instance you install Oracle's client and office on a Windows 2003/IIS5 server and implement complex ASP/ASPX pages which generate Excel files .
You may also use a Excel intepreted ASP page. Both of this solutions are hard to set up and need a lot of code.

There is another solution: lets use MS Query!

First of all your users need:
- Oracle connectivity install and MS Excel on there computer.
- A Data Source Name configure on there computer .
- A login and password which they can use to access the database.

I assume you work with Windows NT/XP and got an Oracle Server.
Here is how to get data from Oracle:
- create a new Excel document.
- in the "data" menu choose "External data" then "new query"
-Choose the appropriate Data Source Name
- Logon
- A wizard will guide you to make your select statement.
- choose return to Excel and validate.

surprise you've got your datas into Excel !

That's all folks! Any comments are welcome!

Next time I will try to expose how to fine tune this feature.













  ¶ 9:24 AM 0 comments

  Using updatable views in Access These "updatable views" feature in Oracle is interesting. You can specify triggers on a view to make it behaves like a table. Its interesting when different processes or applications use the same data but not the same way.
How to make this working with Microsoft Access?


First define your view with a statement like the following:
create view MyView as select .......

then add your trigger(s) :
create or replace trigger MyUpdateTrigger
INSTEAD OF UPDATE ON MyView as
begin
update MyUnderlyingTable ut
set ut.Field = new.ViewField
where ut.keyfield new.keyfield ;
end;

notice the "instead of " statement . You may also add "delete" or "insert" triggers the same way.

Now, how do you make Access 2003, your favorite client side application , works with this?
Open your Access database and link the view as you would for a table.
To do this:

- In the file menu choose "external data" and "link table" . Then choose or create the Data Source Name which map your Oracle Database.

- You will then be prompt to log with your Oracle Profile. You need to be familiar with ODBC and DSN (that is an idea for a new post).

-Then Access display a list of all views and tables available. Depending of your profile you may see system tables and views. Choose "MyView".

- You are then prompt to define the unique identifier of this "table". Choose it regarding the trigger's code you've implement. It should be the logical equivalent of a primary key.


Now your view is available in Access. And it works!

No it dosen't. In fact it works but it is not a practical way to build an application and test it.

If you make a change to your view's definition the linked table in Access is no more available.
So debugging a complex application with this is like a turtle race: you need to pe patient ;-).

Here is a sample VB Access code which may help you :


Sub ConnectTable(strNomTable, strNomtablesource, strODBC, strKEy)
Dim bds As Database, dft As TableDef
Dim strSql
Dim oFi As Field

Set bds = CurrentDb

'delete the existing table
If TableExists(strNomTable) Then
CurrentDb.TableDefs.Delete (strNomTable)
End If

Set dft = bds.CreateTableDef(strNomTable)

dft.Connect = strODBC
dft.SourceTableName = strNomtablesource

bds.TableDefs.Append dft
If Strings.Trim(strKEy) <> "" Then
strSql = "CREATE INDEX " & strNomTable & "_PK" & " ON " & strNomTable & "(" & strKEy & ") WITH PRIMARY"
bds.Execute strSql
End If
fin:
Set dft = Nothing
Set bds = Nothing
End Sub

Put this in a module and call it with the following parameters:

• strNomTable is the name of the Access linked table object.
• strNomtablesource is the Oracle view 's name, it may include a schema prefix.
• strODBC is the ODBC connection string matching you oracle database.
  it should be like the following : "ODBC;DSN=DSN_name;DBQ=oracle_schema_name;PWD=Password;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;DATABASE=" notice that here you have to provide a password, the one matching the user name provided in the DSN.
• strKey is the coma separated list of fields defining the unique identifier for the view.
  

That's all folks! Comments are welcome!

  ¶ 7:58 AM 0 comments

  Introduction Hello,
I'm a french developper. I spend spend most of my time at work searching solutions to problems like :
" How can we get the data( in Oracle database) into the document (Microsoft Office file)"
or
".Net studio is cool use it! You won't use SQL Server as as Database use Oracle for your applications"





  ¶ 6:25 AM 0 comments