-
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.
-
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
-
Hi Kailash,
How do you call a SQL script from within a PL/SQL block?
Will you please let us know?
-amar
-
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;
}
}
}
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|