ORA-00922: missing or invalid option | SQL*PLUS commands using OracleClient

Question

    OracleConnection conn = new OracleConnection();
    conn.ConnectionString = @"Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.206.0.23)(PORT = 1521)))(CONNECT_DATA = (SID = ORCLWEX3)));User Id= RAMNIVAS_CI;Password= RAMNIVAS_CI;";
    conn.Open();


    SetStatus(null, "CLEARING CI DATABASE");
    OracleCommand com = new OracleCommand(@"set pages 0
                    set lines 80
                    spool c:\delete_objects_CI
                    select 'drop '||object_type||' '||object_name||';'
                    from user_objects;
                    spool off
                    start c:\delete_objects_CI.lst
                    purge recyclebin;
                    set pages 100", conn);
    com.ExecuteNonQuery();

I guess there is some problem in the query I am trying to execute.

Codo · Accepted Answer · 2012-08-16 08:51:03Z

1

You cannot execute SQL*plus commands with the Oracle client. SQL*plus commands are only supported by SQL*plus and some Oracle tools like SQL Developer.

Your code basically iterates over all objects of the current user and then drops them. You should be able to achieve the same with a loop:

OracleCommand userObjCmd = new OracleCommand("select object_type, object_name from user_objects", conn);
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    OracleCommand dropCmd = new OracleCommand(
        String.Format(@"execute immedidate 'drop {0} \"{1}\"'",
            reader.GetValue(0), reader.GetValue(1)),
        conn);
    dropCmd.ExecuteNonQuery();
}

You will need to add some code for properly closing the command and reader instances and for handling errors.

And you might need to discard errors silently and try to repeat the above code several times until all objects have been successfully dropped since some objects cannot drop as long as other objects depend on it.

answered Aug 16, 2012 at 8:51

Codo

79.7k18 gold badges177 silver badges219 bronze badges

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

2 Comments

Akshay J Over a year ago

Thanks, even if I use your method for deleting objects, the problem is that I have to restore that DB(we have DMP and text file for it) and also take backup using SQL PLUS. Is there any way to do that ?

Codo Over a year ago

Is the DMP file from the exp or expdp utility? And what sort of text file do you have for restoring the DB? And how do you want to backup the DB with SQLplus? Whatever, the best approach is to run SQLplus, imp, impdb etc. in a separate process.

Rob van Laarhoven · Accepted Answer · 2012-08-16 07:00:13Z

0

You are trying to execute SQL*Plus commands through your driver. I do not think this can be done.

answered Aug 16, 2012 at 7:00

Rob van Laarhoven

8,9552 gold badges33 silver badges50 bronze badges

3 Comments

Akshay J Over a year ago

So is there any way to accomplish the same using my driver ? I think the above script just clears up everything from the specified database.

Rob van Laarhoven Over a year ago

Best way is to drop the user and recreate it. Above method errors out when parent tables are dropped before child tables, when indexes are dropped and they are not there because the table is dropped, etc.. Another way is to write a stored procedure that clears up everything and call it from your code.

Akshay J Over a year ago

I dont think we have the authority to drop user and recreate it. Can you let me know any other method ?

Collectives™ on Stack Overflow

ORA-00922: missing or invalid option | SQL*PLUS commands using OracleClient

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related