run a sql script from plsql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: run a sql script from plsql

  1. #1
    Join Date
    Jan 2001
    Posts
    216
    Hi,

    Is it possible to run a sql script from an anonymous plsql block ?

    I am looking for something like this :

    connect user/password@db_name.node_name
    DECLARE

    BEGIN
    if (&app_svr = 'Y') then
    @install_app_svr_schema
    end if;
    if (&web_svr = 'Y') then
    @install_web_svr_schema
    end if;

    END;
    /

    Thanks a lot in advance.

  2. #2
    Join Date
    Feb 2001
    Posts
    75

    SQL script in PL/SQL

    Yes. You can so far you do not use select statement. In case of that you may have to use into clause to get the select result in PL/SQL variables.

    Kailash Pareek

  3. #3
    Join Date
    Mar 2001
    Posts
    314
    Hi Kailash,

    How do you call a SQL script from within a PL/SQL block?
    Will you please let us know?

    -amar

  4. #4
    Join Date
    Apr 2001
    Posts
    219
    Here is a piece of code that I got off the internet that allows you to run OS commands. I recommend you do the following to accomplish your task:
    sqlplus user/pword @sql.file

    Here is the instructions and code:
    This tip comes from Dave Sienknecht, Programmer for Genesys Software Consulting in Sunnyvale, CA.

    Calling Unix commands from PL/SQL isn't a new idea, but this technique allows the user or program to execute any given OS command, view the printed output, and capture a success/fail argument.

    Directions:

    Compile the java program.
    Load it to the DB using LOADJAVA.
    Create a function call spec to call the program.
    Turn serveroutput and java output ON.

    Function Call Spec:
    ----------------------------------------
    CREATE OR REPLACE FUNCTION shellcmd (v_cmd IN VARCHAR2) RETURN NUMBER
    AS LANGUAGE JAVA
    NAME 'oscmd.mainrun(java.lang.String[]) return int';
    /


    Java Source:
    ----------------------------------------
    import java.io.*;
    import java.util.*;

    public class oscmd{
    static public int runCommand(String cmd)
    throws IOException {

    int vSubResult;
    vSubResult = 0;

    // set up list to capture command output lines
    ArrayList list = new ArrayList();

    // start command running
    Process proc = Runtime.getRuntime().exec(cmd);

    // get command's output stream and
    // put a buffered reader input stream on it
    InputStream istr = proc.getInputStream();
    BufferedReader br =
    new BufferedReader(new InputStreamReader(istr));

    // read output lines from command
    String str;
    while ((str = br.readLine()) != null)
    list.add(str);

    // wait for command to terminate
    try {
    proc.waitFor();
    }
    catch (InterruptedException e) {
    System.err.println("process was interrupted");
    vSubResult = 1;
    }

    // check its exit value
    if (proc.exitValue() != 0) {
    System.err.println("exit value was non-zero");
    vSubResult = 1;
    }

    // close stream
    br.close();

    // read all result strings into variable outlist
    String outlist1[] = (String[])list.toArray(new String[0]);

    // display the output
    for (int i = 0; i < outlist1.length; i++)
    System.out.println(outlist1[i]);

    return vSubResult;
    }

    public static int mainrun(String args[]) throws IOException {
    try {
    int vResult;

    // run the given command
    vResult = runCommand(args[0]);

    // return 0 on success, 1 on failure
    return vResult;
    }
    catch (IOException e) {
    System.err.println(e);
    return 1;
    }
    }
    }

  5. #5
    Join Date
    Mar 2001
    Posts
    314
    Thanks Zaggy for your response!

    But I was interested in finding out if there is a way to call a SQL script from within a pl/sql block.

    We know that we can call a java/c/c++ procedure from pl/sql. There are numerous references in this forum in the past in this topic.

    -amar

Posting Permissions

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


Click Here to Expand Forum to Full Width