"Choosing a schema in Oracle via DatabaseExampleSource"

Legacy User

Hi,

first of all, i am a totally rookie regarding rapid miner but it seems to be a very good dm software...
Anyway, for my work it is absolutely necessary to connect to oracle databases. In my specific exampel, the connection to database works (testing via wizard), but the tables are missing. I assume  that the defalut connection uese the db schema of the user.So, how can i change to another database schema?
Do you have any advices

Thanks in advance

Thomas

homburg

Hi Thomas.

The Oracle database usually holds a schema for every user, defining that users privileges and view of the underlying data structure. In order to receive information from a certain table it should be sufficient to select an appropriate user or alter the users database schema in a way, that it grants acces to the table (respectively constructs it). This can be done i.e. by using the Portal Navigator software from Oracle.

Best regards,
Helge Homburg

Legacy User

Hello Helge,

thanks for your quick response. Of course, the db provides for every user an own schema. But in our company the policy for our datawarehouse database is to give every person his own (read-only) user (plus empty schema) whereas the tables are hold in schemas of artifical users (like ETL-Job user).  So selecting an apprpriate user is no choice.
Altering the users database schema sounds like an option which won't work as well from the philosophy of  (our) company. Isn't there any solution to change to another schema like for office/odbc or straight oracle/sql? This way would really help for acceptance at our company.

Cheers
Thomas

keith

I'm a little unclear on what you're asking, but let me offer a few ideas:

First, the wizard may not let you select tables not in your own schema, but you can still write SQL queries directly that do.  Try entering this in the query text field:

SELECT * FROM OtherSchema.MyTable

I haven't tested this, but I think if you issue the following commands in an Oracle session:

ALTER SESSION SET CURRENT_SCHEMA = <OtherSchema>
SELECT * FROM MyTable

It should change the default schema to OtherSchema temporarily (i.e. just for the duration of this connection), and thus the SELECT statement would point to the table you want.

Unfortunately, it looks like RM doesn't allow for issuing multiple SQL statements in sequence inside one operator, nor for allowing SQL statements that don't return a result set. 

I thought about chaining two database operators together.  But I think that two consecutive database operators would open separate database connections, so even if you could issue an ALTER SESSION with the first operator (and have it not return with an error), the effect would be lost by the time the 2nd operator was executed.

If I'm correct about the statements above, and someone from Rapid-I is listening, it would be nice to enhance one of the existing Database operators to (a) allow multiple SQL statements to be issued as part of a single operator, with the last result set returned taken to the be the example set, and/or (b) have an option for a database operator to ignore the fact that a result set might not be returned (this would allow arbitrary SQL commands to be sent to the database server without generating an error).

land

Hi guys,
we will check if thats possible using the JDBC driver and possibly include it in one of our future releases.

Greetings,
  Sebastian

Legacy User

Hi,

tanks keith. I used now directly the operator DatabaseExampleSource (using the wizard to get the notation of the databse_url only) and inserted a query with data from an other schema. Works quite well. 
In this case it did not need to use  the alter-statement (which wouldn't work in the a second oracle session (seelct-statement) at least today)

Greetings from a new convinced rm-user
Thomas

land

Hi,
as a late reply: RapidMiner did already support arbitrary SQL statements by the operator SQLExecution.

Greetings,
  Sebastian