Run PL/SQL from VB

[bodylojohn]

Hello,

Is it possible to run PL/SQL from VB.NET?
I know it isn't possible to run it straight from code. But is it possible to run it through SQLPLUS???

Does anybody have an example?

I googled and found this C# code but I don't quite understand it.

public int execString(string scriptFileName)
{
   int exitCode;
   ProcessStartInfo processInfo;
   Process process;
   int timeout = 5000;

   processInfo = new ProcessStartInfo("sqlplus.exe", "@" + scriptFileName);
   processInfo.CreateNoWindow = true;
   processInfo.UseShellExecute = false;
   process = process.Start(ProcessInfo);
   process.WaitForExit(timeout);
   exitCode = process.ExitCode;
   process.Close();

   return exitCode;
}

[jmcilhinney]

You're trying to complicate something that's very simple. How do you usually interact with a database? ADO.NET. You just need to use a provider that supports Oracle. You could use Odbc or OleDb if you have an Oracle ODBC driver or OLE DB provider installed. You could also use the Microsoft OracleClient provider, although it has been deprecated. The preferred option is to donwload and install ODP.NET from the Oracle web site, which is Oracle's own ADO.NET provider and some other tools as well, e.g. VS integration. You then create an OracleCommand and execute it, just as you do with a SqlCommand for SQL Server.

[jmcilhinney]

Hmmm... maybe I should have read your post more carefully. I see that you have the SQL code in a file, so what I suggested may not work, although it also might, depending on the specifics.

Anyway, that code is equivalent to opening a command prompt and running SqlPlus and passing the SQL script file path as a commandline argument.

[techgnome]

you can still run it... what's in the file? it's just a bunch of sql commands, right? so, read the file into a string, then set the commandtext of a command object to the string and execute it...

that said... it may not be quite that straight forward... for example, running t-sql like that against SQL Server generates errors when it encounters go between transactions... so I read it all into an array of string... then build it up until I hit a line with "go" ... I then execute what I have, clear out the string and keep looping until I hit the end of the array.

-tg

[bodylojohn]

Hello,

Thanks for the replies so far.

I wil specifically be executing against an oracle (9i) DB.
In the .sql file there will be SQL statesments aswell as PL/SQL

Would that be possible?

[jmcilhinney]

running t-sql like that against SQL Server generates errors when it encounters go between transactions... so I read it all into an array of string... then build it up until I hit a line with "go" ... I then execute what I have, clear out the string and keep looping until I hit the end of the array.

That was my concern as well. I don't know for sure that there are not any other potential issues like with T-SQL and I don't know at all whether there are similar potential issues with PL/SQL.

In the .sql file there will be SQL statesments aswell as PL/SQL

I'm not sure what you mean by differentiating the two because PL/SQL (Procedural SQL) is just Oracle's SQL variant, just as T-SQL (Transact SQL) is SQL Server's I hardly use Oracle so maybe I'm missing something but, as far as I'm aware, all SQL statements for Oracle are PL/SQL.

[bodylojohn]

That was my concern as well. I don't know for sure that there are not any other potential issues like with T-SQL and I don't know at all whether there are similar potential issues with PL/SQL.I'm not sure what you mean by differentiating the two because PL/SQL (Procedural SQL) is just Oracle's SQL variant, just as T-SQL (Transact SQL) is SQL Server's I hardly use Oracle so maybe I'm missing something but, as far as I'm aware, all SQL statements for Oracle are PL/SQL.

Thank you J,

You are right. I will only be using PL/SQL against an oravle DB.

So infact the only thing I want to do is run a script (pl/sql) wich resides in a file and I want to run it with sql plus.

[jmcilhinney]

So what's the problem then? You've already got code to do that. The equivalent VB code will be almost exactly the same as the C# other than adding Dim and As for the declarations and removing the semicolons. Probably any online converter could handle that although you should be able to do it yourself.

If, as you initially said, you don't understand it then what don't you understand? Don't know what a ProcessStartInfo is? Have you read the documentation for the ProcessStartInfo class? Don't know what a Process is? have you read the documentation for the Process class? Etc.