Oracle connection and running script in c#

Question

I have got the following code

         using (OracleConnection con = new OracleConnection())
          {
            con.ConnectionString = My_connection_string;               
            FileInfo file = new FileInfo(Server.MapPath("~/Scripts/call_proc.sql"));
            string script = file.OpenText().ReadToEnd();
            con.Open();
            OracleCommand cmd = new OracleCommand(script, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.BindByName = true;
            cmd.ExecuteNonQuery();
          }

The call_proc code is //returns Hello

         set serveroutput on;
         begin
         test.tmp_test();
         end;

When I try to run the above code it gives me the following errors.

ORA-06550: line 1, column 11: PL/SQL: ORA-00922: missing or invalid option ORA-06550: line 1, column 7:

@Sathya if i remove serveroutput on; then i get the following error. ORA-06550: line 1, column 12: PLS-00103: Encountered the symbol "" when expecting one of the following: — user3202862
– user3202862, Commented Jan 7, 2015 at 12:19
do you really need to use a file? Could not you just use a OracleCommand with the procedure name? For sample: new OracleCommand("test.temp_test", con); — Felipe Oriani
– Felipe Oriani, Commented Jan 7, 2015 at 12:20
@FelipeOriani i tried this and gives me the following error. ORA-06550: line 1, column 7: PLS-00801: internal error [22503] ORA-06550: line 1, column 7: — user3202862
– user3202862, Commented Jan 7, 2015 at 12:24
@Sathya, you need to check that your SP is compiled successfully. — Justin Harvey
– Justin Harvey, Commented Jan 7, 2015 at 12:26

Felipe Oriani · Accepted Answer · 2015-01-07 12:43:14Z

2

You could just use a OracleCommand with the procedure name For sample

Try something like this:

using (OracleConnection con = new OracleConnection())
{
    con.ConnectionString = My_connection_string;               
    con.Open();

    OracleCommand cmd = new OracleCommand("temp.tmp_test", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.BindByName = true;

    var result = cmd.ExecuteScalar();

    if (result != null)
    {
        string stringResult = result.ToString();

        // show result here
    }   

}

You can use command.ExecuteScalar() if you want to take the first column and first line, it will return an object instance.

You can also use a Oracle Data Reader from command.ExecuteDataReader() if you want to read many lines and columns (very common when you want to manipulate an output from a select query).

answered Jan 7, 2015 at 12:43

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

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

Collectives™ on Stack Overflow

Oracle connection and running script in c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related