Results 1 to 4 of 4

Thread: migration

  1. #1
    Join Date
    May 2010
    Posts
    2

    Unanswered: migration

    Hi all,

    I faced on problem with files how i can achive this,This is relate of migration issue i.e

    i have all scripts related source and target in directories and all scripts contain select statements,
    My question in when i am run one script all of the source and target script must be
    executed and the results are stored in the result table


    How i can achive this

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not sure I understand the question.
    • You have "scripts" (these are files that contain some SQL statements)
    • you want to run one script which will execute all "source" and "target" scripts (whatever they are). OK, no problem - put all script names into one file - don't forget the @ sign in front of them!
    • results stored in the "result table"? What is the "result table"? Is it a file that'd contain spooled output? If so - spool it!


    Here's an example: there are three files: a.sql, b.sql and caller.sql (which will call a.sql and b.sql):
    Code:
    M:\>type a.sql
    select 'A' who_am_i from dual;
    M:\>
    M:\>type b.sql
    select 'B' who_am_i from dual;
    M:\>
    M:\>type caller.sql
    @a
    @b
    M:\>
    Here's how you'd execute it:
    Code:
    SQL> column who_am_i format a10
    SQL> spool result.txt
    SQL>
    SQL> @caller
    
    WHO_AM_I
    ----------
    A
    
    
    WHO_AM_I
    ----------
    B
    
    SQL> spool off;
    And the "result.txt" file:
    Code:
    M:\>type result.txt
    SQL>
    SQL> @caller
    
    WHO_AM_I
    ----------
    A
    
    
    WHO_AM_I
    ----------
    B
    
    SQL> spool off;
    
    M:\>
    Is that what you are talking about? If not, try to explain it once again (or wait for another opinion - maybe someone did understand what you are saying).

  3. #3
    Join Date
    May 2010
    Posts
    2
    Its good idea and i am using that
    But i have faced one more problem when i am useing this,
    my script Name is ut.sql
    it contains

    spool on
    spool C:\Test\veera\result.doc
    @emp.pdc;
    @deptall.pdc;
    @empdept.pdc;
    spool off;


    it works fine when i am executing in SQL*PLUS...

    but i need to execute this script(out.sql) in plsql blook.. Is it possible?
    If yes means How? if No means Why?

    I am trying to this
    SQL> select'@'||'out.pdc' from dual;

    '@'||'OUT.SQL'
    --------------
    @out.sql

    begin
    execute immediate '@out.sql';
    end;


    It is also gives an error;;


    How i can achive this problem..
    i need to execute the Out.pdc script in plsql.....


    Thanks to all

    Veera

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, that would be a problem. Two workarounds: the first one you've already seen - call SQL scripts from another SQL script.

    Another one is to "convert" SQL scripts into PL/SQL procedures and call those procedures from your PL/SQL procedure.

    Otherwise, Google for "Oracle how to call SQL script from PL/SQL"; I'm afraid you won't be happy with the results.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •