CODEWITHSUNDEEP
sqloracle-databasescalascalaqueryslick
auramo
auramo

Reputation: 13357

How to use Scala's Slick sql interpolation with owner/schema name-prefixes

We have a database setup where we have a separate user for owners and users of database tables in an Oracle database. This means that in practice each query is prefixed like this: ownername.tablename

This works just fine if I just write the whole thing statically in Slick's SQLInterpolation.sql function:

(sql"select foo_owner.foo_sequence.nextval from dual").as[Long].first()

The problem is, that the owner prefix changes depending on test/prod. environment. What I'd like to do is this:

(sql"select $owner.foo_sequence.nextval from dual").as[Long].first()

But SQL interpolation doesn't work with it. I get this error Oracle: 

An exception or error caused a run to abort: ORA-22806: not an object or REF

Any suggestions? I can of course fall back to the more verbose StaticQuery, but using sql/sqlu interpolation would be much more compact.

Upvotes: 0

Views: 1451

Answers (2)

szeiger
szeiger

Reputation: 1406

Using $foo inserts foo as a bind variable. You need to prefix it with # to insert a literal:

(sql"select #$owner.foo_sequence.nextval from dual").as[Long].first()

Upvotes: 3

igr
igr

Reputation: 3499

Is removing prefix acceptable? In your code you can run
ALTER SESSION SET CURRENT_SCHEMA=yourOwner
This way you do not need to prefix SQL. Just be sure that you do not have CREATE TABLE or any other DML as it will try to run it on owner's schema.

Upvotes: 0

Related Questions