Question Accessing a Remote Oracle Database

[Cecil]

The Requirement:
My requirement is that I need to be able to query a remote Oracle database in Japan which is maintained by another IT division within our organisation. They have little or no English speaking IT staff. I need to have method/process which is unobtrusive and hassle free solution for the Japanese IT staff to implement which takes no longer than 1 day (tops).

The Current Process:
The current process is for one of our Business Analysts to VPN and the Remote Desktop control a PC within the Japan's network and use a Database Browser Query tool. Copy & Paste the result set (via the RDP session to his local computer) into Excel which then can be emailed to me. As you can imagine this is f***ing stupid.

Possible Solution:
Could DataDirect Cloud and DataDirect On-Premise be a possible solution to maintain a up-to-date virtual presence on the remote Oracle Database?

The Problem with DataDirect:
But the problem I see with DataDirect cloud it I can't find a way from the ABL to access the data stored in 'DD cloud'. Is there a solution to this?

The Japanese IT staff are not Programmers nor are they Database Administrators so they are not able to implement a Web solution like Web Services or REST etc.

So you can see my problem how would you go about fixing this? I'm open to suggestions.

BTW:
I have until Tuesday, 26 May 2015 to come up with a proposal.

 

[Cringer]

No idea how it would work over that sort of distance, or with Oracle for that matter, but we use Dataserver to interface with remote SQL databases. I've not had anything to do with the implementation, but essentially you have a Progress schema holder for the SQL database and can read/write to it as you would with Progress. The Dataserver product then manages the rest.

 

[RealHeavyDude]

Just an idea that immediately comes to my mind:

Write the program that extracts the data from the Oracle database in a language that can access the database with standard JDBC/ODBC drivers like Java or some .NET language ( most prominently C# ). With such a programm you can then access the Progress AppServer and store the data via the AppServer in the Progress Database. Of course this will also work the other way round. If you already have an AppServer license than you would not have any additional costs and the ( Java or C# ) program is a piece of cake. I did such things - integrate the Progress world with something else - in Java several times. The Java Open Client to the AppServer really rocks ...

Heavy Regards, RealHeavyDude.

 

[Cecil]

All good suggestions so far. I'm not sure about using dataserver because as I understand it the the schema holder has to be rebuilt each time the source database changes. Since the oracle db is not maintained by me I might have a continuing dataserver administration role to play.

Custom code to query the database is also possible and possibly the cheapest.

I had completely for gotten about Peter Judge's Ooabl code which acts as a wrapper for the odbc which enables you to send SQL statements to an odbc data source.

https://github.com/PeterJudge-PSC/abl_odbc_api

 

[TheMadDBA]

You can also pass freeform queries with the dataserver and get the result set back. This would protect you from unknown schema changes... assuming they don't drop columns you expect or change their usage.

You could also just install the Oracle client on your side and build a simple script to run the query and redirect the output to a file.

 

[Cecil]

I'll put in a request today to see if I can have VPN access. I'll try out the oracle client and the same time. More investigation needed.

Do you know if you can create batch script calls to the oracle client?

 

[Cecil]

I've spoken to Progress Spark (apparently they are dropping the 'Software' part of their name) this morning and as an alternative to using DataDirect Cloud, they suggested to use a alternative product from syncsort .

So as I understand it syncsort ETL will act as a middleware between my OpenEdge application and an Oracle database. Syncsort ETL will create a dataset based upon a set of query and expose that dataset as web API.

Has anyone hear or used Syncsort? It seems like it's getting expensive.

 

[TheMadDBA]

Creating batch scripts with sqlplus is very easy to do and there are tons of examples via google to show exact usage (fixed length, csv, tab, xml, etc).

Basically you just create a text file with the commands you want to execute in it and run sqlplus with a few command line options (to read from that file, output to a file, set login, etc). It will be the lowest cost option for sure since it doesn't require any new licenses and you can do almost everything on your side without involving the oracle people. You just need an oracle login with the appropriate permission.

 

[Cecil]

I've been exploring using the Oracle Client SQL*Plus command line tool. What a beast to install! <rant>Two days of installing and uninstalling just to get the Oracle Instant Client application to install correctly. </rant>

Anyway, despite my install fiasco I was able to execute some SQL statement and get a response but I would like to the information into a XML format. I know this forum is not the best place to ask about Oracle products (this is like taking an ice cream scoop to a knife fight). But I was wondering if anyone has a sample batch/shell script witch will execute the SQL*PLus command line and generate an XML file???

I've done some Google search on generating XML and there seams to be a bit of an mindful consideration as the Oracle XML tools process the returned data into memory then outputs the XML.
There are complex hacks which breaks down the SQL query into chucks and then concatenate them back together as a complete XML document.

 

[TheMadDBA]

I dislike the instant client. I haven't really had issues installing the normal client or the DB software as long as you have X-Windows.

See this link for the "right" way to do XML from sqlplus https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3512822500346787661

Cliff notes:
set long=100000;
select dbms_xmlgen.getxml('select * from your_table') xml from dual;

All of the XML generation will happen on the Oracle DB server and you will just get back XML text across the wire.

 

[Cecil]

I dislike the instant client. I haven't really had issues installing the normal client or the DB software as long as you have X-Windows.

See this link for the "right" way to do XML from sqlplus https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3512822500346787661

Cliff notes:
set long=100000;
select dbms_xmlgen.getxml('select * from your_table') xml from dual;

All of the XML generation will happen on the Oracle DB server and you will just get back XML text across the wire.

So How do I get the output to file? I done some research and it's gotten complicated again. This should not be this hard?

 

[TheMadDBA]

When you do your searches for information make sure to use sqlplus instead of Oracle. Things done in the Oracle DB itself (stored procedures and PL-SQL) are different from sqlplus which is basically a simple command line tool.

SPOOL <somefilename>;

SPOOL OFF;

You probably want to use SET with the LINESIZE, PAGESIZE and TRIMSPOOL options to get a cleaner file.

https://www.google.com/search?q=sql...7.4465j0j7&sourceid=chrome&es_sm=122&ie=UTF-8

 

[TheMadDBA]

Also the Oracle documentation is pretty detailed.

http://docs.oracle.com/cd/B19306_01/server.102/b14357/toc.htm

 

[Cecil]

SQLPlus is very slow at 2 seconds per record. Am starting to think SQLPlus is not going to be a viable solution. Also it unable to handle Unicode characters is just showing ¿¿¿¿¿ ¿¿¿.

Update:
Using the XML devloper tools dbms_xmlgen.getxml() seams to add a massive overhead in the time to output;

 

[TheMadDBA]

SQLPlus isn't your issue. All of the processing happens on the Oracle server itself (other than creating the output file and reading the result set over the network). Think of it as a terminal emulator.

You can handle Unicode characters if you set up the codepages properly (just like Progress).

Do you have somebody to help you tune the SQL query? GETXML will add some time because it is making XML instead of simple text.. how much depends on how much data you are returning and how big the XML gets.

 

[Cecil]

Unfortunately there is only one person who can help me and he is in Japan and can't speak English.

I fixed the code page issue and now the contents is now written with correct code page.

The reason why I want to output to XML is so it's easier to import using the ABL SAX parser. The flat text format (which is quicker) does not have any clear rules about how to file is delimited. All the columns are all over the place.

 

[TheMadDBA]

By default it will be fixed length. You can set the colsep to some other character "|" and it will be delimited (but sadly with spaces).

If you want true delimited (tab in this case) you can do this:

select col1 || chr(9) || col2, etc from mytable;

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056#74643125201557

 

[Cecil]

By default it will be fixed length. You can set the colsep to some other character "|" and it will be delimited (but sadly with spaces).

If you want true delimited (tab in this case) you can do this:

select col1 || chr(9) || col2, etc from mytable;

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056#74643125201557

Thanks for the insight. I could be wrong but I think the fixed length fields are corrupted because the field values are double byte characters which possible mucking up the column positioning.

 

[TheMadDBA]

Hmmm. That is possible. I have never tried to do fixed length and double byte before.

If you had access to the Oracle side (to make a stored procedure and some automated jobs) this would be about 1000 times easier. sqlplus is like using simple shell scripts and PL-SQL is like writing 4GL code.