Learning Oracle Integration Cloud Services SOA | BPM

Database Column Alias name caused the query to fail problem when try to Microsoft Query to return data from some third-party database into Microsoft Excel. The error message that you receive depends on the data source that you use. I am using Oracle Database 10g and because the use of column alias name Microsoft Query generate ‘ORA-00923: FROM keyword not found where expected’ error. A supported hotfix is available from Microsoft and this hotfix is prebuilt in Microsoft Excel 2007. In the following steps I will show you how to enable hotfix for Excel 2007. 1) Exit all office programs 2) Click start, and then click run 3) In the open box, type regedit and then click ok 4) Locate, and then click to select the following registry subkey: HEKY_CURRENT_USER\Software\Microsoft\Office\12.0\query\options 5) After you select the subkey that is specified in step 4, point to New on the Edit menu, and then click DWORD value. 6) Type AllowAlias, and then press ENTER. 7) Reghit-click AllowAl...

Recently my Team Lead gave me the task to fetching the data from Oracle Database in Microsoft Excel through Microsoft Query. After spending some time in R&D I was successfully completed this task and now I am sharing my knowledge and steps to fetch data from Oracle Database to Microsoft Excel. 1) From the Data tab in MS Excel, click From Other Source icon 2) From Choose Data Source window Select New Data Source and click OK 3) From Create New Data Source window give the Data Source Name and select Microsoft ODBC for Oracle as Driver from the list. 4) Enter Databse User Name, Password and Server 5) Select Oracle Databsae tables and its Columns from Query Wizard-Choose Columns 6) Filter the data to specify which rows to include in your query. 7) Specify how you want your data sorted. 8) Select Retrun data to Microsoft Office Excel 9) From Import Data window - Select how you want to view this data in your workbook 10) Extracted Data