Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Unanswered: call sql*plus from inside pl/sql

    Hello,
    I'm looking for a way to start a sql*plus command from inside a pl/sql
    function or procedure.

    It seems to me that I can't use something like this in pl/sql:

    ##
    comstring := 'sqlplus aradmin/*****@KADB <<end
    SET SERVEROUTPUT ON
    SET LONG 65535
    SET ARRAYSIZE 30
    SET COPYCOMMIT 10
    COPY FROM aradmin/****@KADB
    TO aradmin/****@KADB
    CREATE Y_TEST USING SELECT * FROM X_TEST;
    commit;
    exit;
    end';
    execute immediate comstring;
    ##

    Is it possible in a different way?
    How is it possible to start an unix script
    from inside pl/sql?
    I could put the sql*plus command into
    this script and call it from pl/sql...

    Thanx for hints.

    best regards
    Ruediger

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Sure, use the host command. See

    http://download-west.oracle.com/docs...h8.htm#1001125

    But for what you want to do, simply run the KADB script directly. by issuing the
    @KADB
    in your current session.
    Last edited by beilstwh; 09-23-04 at 09:52.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2004
    Posts
    2

    Re: call sql*plus from inside pl/sql

    Hi Bill,
    thanks for the information.

    But I wasn't accurate with my problem description.

    I work with Oracle 8i.

    1) I want to use the sql*plus COPY command to transfer
    a remote table with a LONG field to a table inside
    my local database.

    2) I want to use a procedure or package, written in pl/sql.

    In this procedure I want to call the COPY statement in 1)
    directly using dbms_sql (or an alternative built-in) OR

    3) I want to call a unix shell script that contains the sql*plus
    statement in 1)

    With the HOST command you've mentioned I'm able to interrupt an
    active sql*plus session (I can return to sql*plus with exit).
    but I want to activate this sql*plus session from my procedure.

    Thanks. :-)

    Regards
    Ruediger

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    a long column and version 8. well, I guess you've shown just cause for using the COPY command (my least favorite, most overused command).

    http://asktom.oracle.com/pls/ask/f?p...A:952229840241,
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you are saying that you want to start the sql-plus session and NOT wait for it to finish, then use issue the following command.

    host sqlplus aradmin/*****@KADB <<end &

    The "&" command on the unix host command forces the invoked process to run as a detached job that is not connected to the calling program
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    he wants to issue the host call from within pl/sql:

    create procedure
    ...
    begin
    ...
    host('sqlplus ...')
    ...
    end;
    /


    of course, HOST is a SQLPLUS command, not a SQL command, therefore it can not be done as written above.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

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