Is it possible to execute a SQLPLUS script using ODP.NET (Oracle.ManagedDataAccess)?

Question

I've got a console app that connects to a database (using Oracle.ManagedDataAccess) and then tries to execute a bunch of SQL*PLUS scripts located on my local hard drive.

Unfortunately although my app works for simple SQL Statements (SELECTS) it doesn't appear to work for SQL*PLUS.

The Code:

    var script = File.ReadAllText("C:\\Automation\\script.sql");

    using (var objConn = new OracleConnection(_connectionString))
    {
        OracleCommand objCmd = new OracleCommand();
        objCmd.Connection = objConn;
        objCmd.CommandText = script;
        objCmd.CommandType = CommandType.Text;

        try
        {
            objConn.Open();
            objCmd.ExecuteNonQuery();
        }
        catch(Exception ex)
        {
            Console.WriteLine("Exception: {0}", ex.ToString());
        }
        finally
        {
            objConn.Close();
        }
    }

Script.sql - executes without error in SQL Developer

    DEFINE CODE_PATH = C:\Automation
    @"&&CODE_PATH\Test.sql"

Test.sql - executes without error in SQL Developer\my app without SQL*PLUS

    SELECT ID FROM data.SOME_TABLE WHERE ROWNUM < 100

Error:

    ORA-00900: invalid SQL statement at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()

What am I missing? Is this even possible?

you may need reference some other library of the client. have a look at sqlserver way — Lei Yang
– Lei Yang, Commented Mar 16, 2017 at 6:21
It should work, I tried the code it is working. Try the same sql statement inside your code instead of using "script" file, if it is working it should work with that "script" file too. — imsome1
– imsome1, Commented Mar 16, 2017 at 6:23
@imsome1 that's encouraging that it works for you. So if I remove the script element and pass a string instead: "DEFINE CODE_PATH = C:\\Automation\r\n@\"&&CODE_PATH\\TEST.sql\"" I still get the error — Fred James
– Fred James, Commented Mar 16, 2017 at 6:32
Ok, print the "script" value using Console.WriteLine(script); and check the output — imsome1
– imsome1, Commented Mar 16, 2017 at 6:36
Looks okay printed to the Console. Even if I pass: "@C:\\Automation\\test.sql" I still get the error — Fred James
– Fred James, Commented Mar 16, 2017 at 6:46

Christian Shay · Accepted Answer · 2017-05-15 17:03:29Z

1

The answer is no.

ODP.NET and other Oracle programming interfaces (OCI, OCCI, JDBC etc) are designed to execute SQL, PL/SQL and calls to stored procedures and functions. They are not designed to process SQL*Plus syntax (which BTW contains lots of other non-SQL, non-PL/SQL commands aside from just the "@" symbol - for example "show errors").

SQL * Plus scripts were designed to be executed by SQL*Plus alone, but in recent years SQL Developer, Oracle Developer Tools for Visual Studio, and some other Oracle tools have added parsers for it. These parsers are not exposed via a public API however.

answered May 15, 2017 at 17:03

Christian Shay

2,63315 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:31:09Z

0

I'm still not sure if it's possible to execute slqplus through ODP.NET, I suspect it's not.

So as a workaround, I'm going to call the sqlplus.exe directly.

Code:

    static void execString(string userName, string password, string scriptFileName, string dataSource)
    {
        ProcessStartInfo processInfo = new ProcessStartInfo();
        processInfo.FileName = "sqlplus.exe";
        processInfo.Arguments = String.Format("{0}/{1}@{2} @{3}", userName, password, dataSource, scriptFileName);

        Process process = Process.Start(processInfo);
    }

Thanks to Rich's post from 8 years ago for the workaround

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Mar 16, 2017 at 23:57

Fred James

693 silver badges11 bronze badges

Collectives™ on Stack Overflow

Is it possible to execute a SQLPLUS script using ODP.NET (Oracle.ManagedDataAccess)?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related