How to run sql script from store procedure / or ajava

[cincocielos]

Hi all....

I need to run an script that contents creation table statement.

I hope someone help.

[davey23uk]

why not put the create statements in the stored proc?

you cant do it from plsql, you can do it from java if you wish

[cincocielos]

the promes is that the scrip file must content create table sentence or create procedure sentece or some like that... then I just want to run for example objetc.sql.... one time object.sql have create table another time object.sql have create trigger...

I hope you help us.

[WilliamR]

Stored procedures are not an ideal for installing applications. Normally a script is used.

[davey23uk]

and plsql cannot read a script file anyway, so you cant do what you want

[ebrian]

and plsql cannot read a script file anyway, so you cant do what you want

PL/SQL can actually read a script file, however in this case I think you may want to consider an external table or UTL_FILE to read the file from the O/S.

[tabreaz]

The following Java class runs the SQLPLUS command through the OS.

create or replace and compile java source named script as
import java.io.*;
import java.util.*;

public class Script{

public static void run(String filename) throws IOException
{
	String [] Command = new String[2];

	Command[0] = "sqlplus";
	Command[1] = " @"+filename;

	Process Findspace = Runtime.getRuntime().exec(Command);
}
}

The wrapper function

create or replace procedure runSQLScript(filename varchar2)
as language java
name 'Script.run(java.lang.String)';

--------contents of test.sql script can be----------

scott/tiger@testdb
set linesize 120

create global temporary table temp_tab
(col1 varchar2(10))
on commit preserve rows;

exit;
------------------------------------------------------
--to execute the script
------------------------------------------------------
exec runSQLScript('c:\test.sql');