AnsweredAssumed Answered

How to change oracle schema name dynamically whene we query from Oracle DB?

Question asked by sakthiraj_r185516 on May 23, 2016
Latest reply on Apr 16, 2018 by sakthirajr

I am trying to execute sql which schema name is dynamic.I tried below options.


Option 1:

            I tried to pass schema  name dynamicallyl using parameter option in Boomi connector.But it is not working out.


           Query in Profile: select column1,column2 from ?.tablename

           Error message from Boomi : No table found error.


Option 2:

           I tried to use alter statement to change current schema before executing actual query.I had two statement in singly DB profile.First profile is trying to change schema and second profile is executing actual statement.


          Query in Profile (statement 1): ALTER SESSION SET CURRENT_SCHEMA = ?

          Query in Profile (statement 2): select column1,column2 from tablename

           Error: SQL Error: ORA-02421: missing or invalid schema authorization identifier


Option 3:

             I used dynamic sql to pass entire alter statement as parameter.This worked fine.Can anybody suggest any easy way to change schema name dynamically in Boomi ?


          Query in Profile (statement 1):     begin 

                                                                             Immediate execute ?;


                                       Parameter value : ALTER SESSION SET CURRENT_SCHEMA = schemaname

          Query in Profile (statement 2): select column1,column2 from tablename