This discussion is archived
7 Replies Latest reply: Jul 31, 2012 2:21 AM by 949535 RSS

variable database name in SQL Server query using Oracle database link

949535 Newbie
Currently Being Moderated
Hi All,

I have an ApEx 4.1 app running on 11g x64 (11.2.0.1) on Windows Server 2008 x64, and I have some data integration points with a SQL Server (2005 and 2008) that I need to establish. I have configured the database link with dg4odbc and it works beautifully... I can execute queries against the SQL Server database without any problems using the database link.

However, there is a scenario where the SQL Server database name is dynamic, and I need to generate it on the fly in a PL/SQL block, and then use that in a dynamic SQL query (all of this in ApEx). This is where I run into problems... when I am querying the default database based on the ODBC connection and I don't have to specify the database name, there is no issue. But when I need to access one of several other non-default databases, I keep receiving the "invalid table" error.

This runs fine:* (note that "fv" is the name of my database link)

v_query1 := 'select "ReleaseDate" from dbo.Schedules@fv where dbo.Schedules."SchedID" = :schedule';
EXECUTE IMMEDIATE v_query1 into rel_date using schedule;




I then take that rel_date variable, convert to a varchar2 (rel_date_char), and then use it as the database name in the next query...


This returns an error_ (Error ORA-00903: invalid table name)

v_query2 := 'select "PARTNO" from :rel_date_char.dbo.ProdDetails@fv where "SchedID" = :schedule and "UnitID" = :unit
and "MasterKey" = :master and "ParentKey" = :parent';

EXECUTE IMMEDIATE v_query2 into part_number using schedule, unit, master, parent;



I have also tried using all of the following to no avail:

'select "PARTNO" from ' || :rel_date_char || '.dbo.ProdDetails@fv where "SchedID"...
'select "PARTNO" from ' || rel_date_char || '.dbo.ProdDetails@fv where "SchedID"...
'select "PARTNO" from ' || @rel_date_char || '.dbo.ProdDetails@fv where "SchedID"...
'select "PARTNO" from @rel_date_char.dbo.ProdDetails@fv where "SchedID"...


Is there a way to do this in PL/SQL?

Thanks for any help!
-Ian C.

Edited by: 946532 on Jul 15, 2012 7:45 PM
  • 1. Re: variable database name in SQL Server query using Oracle database link
    kgronau Guru
    Currently Being Moderated
    That's the way it works for me - first getting the owner for the EMP table from the internal MS SQL Server tables into ownr, then using the ownr and tabname to get the amount of records in this table:


    SQL> declare
    2 tabname varchar2(20) :='EMP';
    3 ownr varchar2(20);
    4 cnt number;
    5 begin
    6 EXECUTE IMMEDIATE 'SELECT su."name" FROM "sysobjects"@DG4MSQL_EMGTW_1123_DB so JOIN "sysusers"@DG4MSQL_EMGTW_1123_DB su on so."uid" = su."uid" where so."name" =''EMP'' ' into ownr;
    7 dbms_output.put_line (ownr);
    8 -- EMP is surrounded by 2 single quotes as a single quote would be used to terminate the execute immediate string ...
    9 EXECUTE IMMEDIATE 'SELECT count(*) FROM "'|| ownr || '"."'||tabname||'"@DG4MSQL_EMGTW_1123_DB' into cnt;
    10 dbms_output.put_line (cnt);
    11 END;
    12 /
    dbo
    24576

    PL/SQL procedure successfully completed.

    side note line 9 reads as: FROM <double quotes><single quote>||ownr||<single quote><double quote><dot>double quote><single quote>||...
    I have to use double quotes for MS SQl Server objects as the SQL Server objects are case sensitive.
  • 2. Re: variable database name in SQL Server query using Oracle database link
    949535 Newbie
    Currently Being Moderated
    Hi kgronau,
    Thanks, that was helpful but I am still having problems...although I am getting a more specific error now.

    Using your suggestion, my dynamic query looks like this:

    declaring new variables for the dbo owner and table name:

    prod_det_tab varchar2(32) := 'ProdDetails';
    own varchar2(8) := 'dbo';

    and dynamic query using fv database link:

    v_query2 := 'select "WIDTH", "HEIGHT", "THICKNESS", "PARTNO" from "' || rel_date_char || '"."' || own || '"."' || prod_det_tab || '"@fv where "SchedID" = :schedule and "UnitID" = :unit
    and "MasterKey" = :master and "ParentKey" = :parent';

    EXECUTE IMMEDIATE v_query2 into lite_width, lite_length, lite_thx, lite_type using schedule, unit, master, parent;

    And the error:
    ORA-00933: SQL command not properly ended.

    I also tried this:

    set table variable to:

    prod_det_tab varchar2(32) := '.dbo.ProdDetails';

    and query:

    v_query2 := 'select "WIDTH", "HEIGHT", "THICKNESS", "PARTNO" from "' || rel_date_char || prod_det_tab || '"@fv where "SchedID" = :schedule and "UnitID" = :unit
    and "MasterKey" = :master and "ParentKey" = :parent';

    And the error I get (which seems more promising because the '111208.dbo.ProdDetails' is exactly what I am trying to access):

    ORA-00942: table or view does not exist [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '111208.dbo.ProdDetails'. {42S02,NativeErr = 208}[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. {42000,NativeErr = 8180} ORA-02063: preceding 2 lines from FV


    I am positive the database I am referencing exists, and that there is a dbo.ProdDetails table in that database because I can see it in the SQL Server management studio.

    Any other ideas?
  • 3. Re: variable database name in SQL Server query using Oracle database link
    kgronau Guru
    Currently Being Moderated
    try selecting from the database/table normally:
    select count(*) from "111208"."dbo"."ProdDetails"
  • 4. Re: variable database name in SQL Server query using Oracle database link
    949535 Newbie
    Currently Being Moderated
    I went into the SQL Commands section of ApEx and tried the following:

    1) select count(*) from "111208"."dbo"."ProdDetails"@fv; --> *00933: SQL command not properly ended*

    2) select count(*) from 111208.dbo.ProdDetails@fv; --> *00903: Invalid table name*

    3) select count(*) from Schedules@fv; --> *13001 rows returned*

    Statement #3 is using the default database set in the ODBC connection for the database link, and it works fine just like in the first part of my dynamic sql block.

    As long as I have permissions to see the databases (again, this is confirmed with the SQL Server account I am using in the ODBC definition), then it shouldn't matter that I am trying to access a database on that server that is not the "default", as long as I specify it, right?

    I really appreciate the help... any other ideas?

    -Ian
  • 5. Re: variable database name in SQL Server query using Oracle database link
    kgronau Guru
    Currently Being Moderated
    Just did a test using passthrough:

    SQL> set serveroutput on
    SQL> declare
    2 val varchar2(100);
    3 c integer;
    4 nr integer;
    5 begin
    6 c:= dbms_hs_passthrough.open_cursor@FREETDS_DG4ODBC_EMGTW_11_2_0_3;
    7 dbms_hs_passthrough.parse@FREETDS_DG4ODBC_EMGTW_11_2_0_3 (c, 'select count(*) from EMP');
    8 LOOP
    9 nr:= DBMS_Hs_Passthrough.fetch_row@FREETDS_DG4ODBC_EMGTW_11_2_0_3(c);
    10 exit when nr=0;
    11 dbms_hs_passthrough.get_value@FREETDS_DG4ODBC_EMGTW_11_2_0_3(c,1,val);
    12 dbms_output.put_line(val);
    13 end loop;
    14 dbms_hs_passthrough.close_cursor@FREETDS_DG4ODBC_EMGTW_11_2_0_3(c);
    15 end;
    16 /
    24576

    PL/SQL procedure successfully completed.

    SQL> declare
    2 val varchar2(100);
    3 c integer;
    4 nr integer;
    5 begin
    6 c:= dbms_hs_passthrough.open_cursor@FREETDS_DG4ODBC_EMGTW_11_2_0_3;
    7 dbms_hs_passthrough.parse@FREETDS_DG4ODBC_EMGTW_11_2_0_3 (c, 'select count(*) from dbo.EMP');
    8 LOOP
    9 nr:= DBMS_Hs_Passthrough.fetch_row@FREETDS_DG4ODBC_EMGTW_11_2_0_3(c);
    10 exit when nr=0;
    11 dbms_hs_passthrough.get_value@FREETDS_DG4ODBC_EMGTW_11_2_0_3(c,1,val);
    12 dbms_output.put_line(val);
    13 end loop;
    14 dbms_hs_passthrough.close_cursor@FREETDS_DG4ODBC_EMGTW_11_2_0_3(c);
    15 end;
    16 /
    24576

    PL/SQL procedure successfully completed.




    So all 3 ways work for me.

    Edited by: kgronau on Jul 23, 2012 10:08 AM


    Now using variables to perform the select:

    SQL> declare
    2 val varchar2(100);
    3 c integer;
    4 nr integer;
    5 tabname varchar2(20) :='EMP';
    6 ownr varchar2(20) :='dbo';
    7 dbname varchar2(20) :='gateway';
    8 begin
    9 c:= dbms_hs_passthrough.open_cursor@FREETDS_DG4ODBC_EMGTW_11_2_0_3;
    10 dbms_hs_passthrough.parse@FREETDS_DG4ODBC_EMGTW_11_2_0_3 (c, 'SELECT count(*) FROM '||dbname||'.'|| ownr || '.'||tabname||'');
    11 LOOP
    12 nr:= DBMS_Hs_Passthrough.fetch_row@FREETDS_DG4ODBC_EMGTW_11_2_0_3(c);
    13 exit when nr=0;
    14 dbms_hs_passthrough.get_value@FREETDS_DG4ODBC_EMGTW_11_2_0_3(c,1,val);
    15 dbms_output.put_line(val);
    16 end loop;
    17 dbms_hs_passthrough.close_cursor@FREETDS_DG4ODBC_EMGTW_11_2_0_3(c);
    18 end;
    19 /
    24576

    PL/SQL procedure successfully completed.



    => instead of executing the statement using "execute Immediate" we have to use PASTHROUGH package to pass the statement to the SQL Server.

    Edited by: kgronau on Jul 23, 2012 10:10 AM
  • 6. Re: variable database name in SQL Server query using Oracle database link
    949535 Newbie
    Currently Being Moderated
    Great, thanks! This is very helpful! So I need to use the open_cursor, parse, and get_value functions in the passthrough package to make this work. I will give it a shot and let you know how it goes.
  • 7. Re: variable database name in SQL Server query using Oracle database link
    949535 Newbie
    Currently Being Moderated
    The dbms_hs_passthrough package worked! I had to put [ ] brackets around the variable database name, but it is working properly.

    Thanks so much for the help!!!
    -Ian

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points