On Fri, Mar 16, 2012 at 2:07 AM, Jeremy Evans <[email protected]> wrote:
> On Thursday, March 15, 2012 4:58:01 PM UTC-7, Christian MICHON wrote:
>>
>> Hi,
>>
>> I've a db at work (Oracle 11g) which I can access using
>> jdbc:oracle:thin. I verified using the same jdbc driver and
>> dbvisualizer that I can connect to it and see some content in several
>> table schemas.
>>
>> Yet, when using sequel (the gem and the binary), I point to the
>> default schema of my oracle login, and I seem unable to set
>> default_schema on Sequel.connect(). My DB.tables remains empty [].
>
>
> Honestly, I don't know much about Oracle. I do test Sequel on Oracle on a
> regular basis, but the test suite doesn't do anything special related to
> schemas. How are you attempting to set your default schema? Is there
> specific SQL you need to send after connecting to set the default schema
> (the Database :after_connect option handles such cases)?
I tried :after_connect and :default_schema in the Sequel connect and
it did not work. Luckily I did not give up (see below...)
>
> You may want to add a database logger and look at the logged output to see
> what queries Sequel is sending to Oracle. That way, you aren't in the dark
> about what Sequel is attempting to do:
>
> require 'logger'
> DB.loggers << Logger.new($stdout)
I always do this 1st when trouble occurs or when I want to improve my
queries. Thanks anyway.
>
>
>>
>> 1/ how can I list using sequel the list of table schemas ? ie the
>> containers containing tables and views (these can be very specific and
>> not shared among oracle logins). I may have a wrong name issue, if I
>> could see all possible table schemas, this could help.
>
>
> I'm not sure what the Oracle SQL syntax is to list schemas. Whatever it is,
> you would probably do DB['SQL Here'].all and the results should contain the
> information you are looking for.
This reminded me I can do DB.run and DB.fetch...
Let's call the default schema I want to access 'MINE' and the table I
want to access 'MYREC' (I do not wish to disclose more from my
oracle).
What I figured out is:
- DB.tables gives []
- DB['MINE.MYREC'].count works
- DB['MINE.MYREC'].first fails
- DB['MINE.MYREC'].columns fails
- DB.schema 'MINE.MYREC' fails
>
>
>>
>> 2/ how to set it properly ? are there other commands to use than
>> default_schema ?
>>
>> Thanks in advance.
>
>
> Sorry I can't be more help. If anyone else is more knowledgeable about
> Oracle, please chime in.
>
I read that the following SQL in Oracle can be use to avoid prefixing
my select queries: 'ALTER SESSION SET CURRENT_SCHEMA ='
So I did this:
- DB.run("ALTER SESSION SET CURRENT_SCHEMA = MINE")
- DB.fetch("select * from MYREC").first
and it works well enough for me to be able to grab the metadata I
need. Neat. Dirty, but it works.
Thanks Jeremy. We still have possible issues to fix (:default_schema
and :after_connect (passing the alter session command) did not work as
expected).
--
Christian
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
