This discussion is archived
5 Replies Latest reply: Aug 2, 2012 2:36 AM by 953210 RSS

BRIDGE statement in a loop : dynamic destination and source table names ...

953210 Newbie
Currently Being Moderated
Hello,
I can't find the right syntax to do what I need, if it's possible :
Context :
I work actualy on a migration from MS Access applications to Oracle (datas only). So I copied all MS Access Tables into Oracle and created manualy all the relationnal constraints like primary and foreign keys ('cause constraints are not included in 'Copy to Oracle').

I Have often to refresh my datas, because the MS access applications are still in use. Therefore I wrote PL/SQL scripts. They do the folowing, using dynamic SQL with 'Execute Immediate' statement :
Script 1
- disable all user's constraints
- disable all user's triggers
- truncate all user's tables

(Here, I have to do a manual copy of all MS Access tables to Oracle, checking the Append check-box, because the BRIDGE statement doesn't support 'Execute Immediate', and wait...)

Script 2
- enable all user's constraints
- enable all user's triggers


Could someone let me know how Il could do the same as :
--------------------------------------------------------------------------------------------------------------------------
For t in (select table_name from user_tables) loop
-- Copy the datas from an Access table into the same Oracle table
execute immediate ('BRIDGE ' || t.table_name || ' AS MyAccessConnName(select * from ' || t.table_name || ') APPEND') ;
-- News flash ...
dbms_output.put_line('Table ' || t.table_name || ' filled') ;
end loop;
-------------------------------------------------------------------------------------------------------------------------- ==> 00900. 00000 - "invalid SQL statement"

If a dynamic table name substitution is applicable in the Bridge statement from a query, I take it with joice!
Thank you for helping me...

Daniel
  • 1. Re: BRIDGE statement in a loop : dynamic destination and source table names ...
    Dermot ONeill Journeyer
    Currently Being Moderated
    Hi Daniel,

    The BRIDGE command is a SQL Developer command.
    The Oracle database would not recognize it, so passing a bridge command into an execute immediate will not work.
    The BRIDGE functionality is only available when running a script in SQL Developer (as it explicitly references SQL Developer connection names (ex: MyAccessConnName))

    Here is a little more detail
    http://dermotoneill.blogspot.com/2010/11/cross-database-bridge-statement.html

    One way of doing what you want is to build up the BRIDGE statements as the script output. Then run the script output in SQL Developer.


    select 'BRIDGE ' || table_name || ' AS MyAccessConnName(select * from ' || table_name||')APPEND;' sqlCommand
    from user_tables ;

    Regards,
    Dermot
    SQL Developer Team.
  • 2. Re: BRIDGE statement in a loop : dynamic destination and source table names ...
    Dermot ONeill Journeyer
    Currently Being Moderated
    Hi Daniel,

    The BRIDGE command is a SQL Developer command.
    The Oracle database would not recognize it, so passing a bridge command into an execute immediate will not work.
    The BRIDGE functionality is only available when running a script in SQL Developer (as it explicitly references SQL Developer connection names (ex: MyAccessConnName))

    Here is a little more detail
    http://dermotoneill.blogspot.com/2010/11/cross-database-bridge-statement.html

    One way of doing what you want is to build up the BRIDGE statements as the script output. Then run the script output in SQL Developer.

    select 'BRIDGE ' || table_name || ' AS MyAccessConnName(select * from ' || table_name||')APPEND;' sqlCommand
    from user_tables ;

    Regards,
    Dermot
    SQL Developer Team.
  • 3. Re: BRIDGE statement in a loop : dynamic destination and source table names ...
    953210 Newbie
    Currently Being Moderated
    Hi Dermot,
    Thank you for your quick answer !

    I read your blog many times, and all what google gave me about the Bridge statement : not so much information indeed !
    If I understand you, I have to run the script output 'con la mano' in sql developer ? Does it means I can't automate it ?
    What I want is to run just one program to refresh my datas, It doesn't matter if it's a stored procedure, a script (run in SQL developer worksheet) or something else.
    When you say +"One way of doing what you want"+, do you mean that it's another way ?

    Thank you again.
    Regards,
    Daniel
  • 4. Re: BRIDGE statement in a loop : dynamic destination and source table names ...
    Dermot ONeill Journeyer
    Currently Being Moderated
    Hi Daniel,

    The BRIDGE statement is just an extra command I implemented in the SQL Developer worksheet script runner.
    It gets interpreted by SQL Developer and it dynamically creates (CREATE TABLE , INSERT INTO , SELECT ... ) statements and runs them against the connections specified.
    It was developed to improve certain migration features of SQL Developer. We haven't really spent any time developing it into a customer friendly statement to be used in custom scripts.
    Hence the lack of doc. But it is there and if you can make it work for yourself all the better.

    When I say "One way of doing what you want". I mean I haven't thought about your particular problem exhaustively and I wouldn't want you to take my solution as gospel :)

    If you are happy running a script in SQL Developer, but would rather not run 2 scripts , or cut and paste results around, you could SPOOL the results and execute them.

    --call your other scripts to disable constraints during the data move
    set echo off;
    set feedback off;
    set linesize 1000;
    set pagesize 0;
    set headsep off;
    set termout off;
    set verify off;
    set heading off;
    SET PAGES 0;
    SET HEAD OFF;

    spool c:\mydynamicscript.sql
    select 'BRIDGE ' || table_name || ' AS MyAccessConnName(select * from ' || table_name||');' from user_tables ;
    spool off
    @c:\mydynamicscript.sql

    --call another script to enable your constraints again

    Regards,
    Dermot.
    SQL Developer Team.
  • 5. Re: BRIDGE statement in a loop : dynamic destination and source table names ...
    953210 Newbie
    Currently Being Moderated
    Hi Dermot,

    Thank you very much for your help. I realy needed it: the Oracle Univers is a new one for me and I can now implement what I wished.

    Intends to the community, there is something that can be usefull to know: the connection name in BRIDGE statement is case sensitive.

    -> my Access connection is called 'ConnAcc'

    this line doesn't work:
    -----------------------
    BRIDGE APPLICATIONS AS connacc (select * from APPLICATIONS) ;
    ------------------------------

    But this one does:
    ---------------------
    BRIDGE APPLICATIONS AS ConnAcc (select * from APPLICATIONS) ;
    --------------------

    Thank you again.
    Best regards,
    Daniel

Legend

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