.net - Create C# routines that calls an ORACLE database to get the DDL for objects of a specified type

Question

I'd like to be able to generate the DDL for objects of a specific type. The following query will return the DDL for all tables in the schema 'MYSCHEMA'.

SELECT 
    O.OWNER, 
    O.OBJECT_TYPE, 
    O.OBJECT_NAME, 
    DBMS_METADATA.GET_DDL(OBJECT_TYPE, O.OBJECT_NAME, O.OWNER)  As DDL
FROM
    ALL_OBJECTS O
    WHERE
        O.OWNER = 'MYSCHEMA' AND O.OBJECT_TYPE = 'TABLE'
ORDER BY 
    O.OWNER, O.OBJECT_TYPE, O.OBJECT_NAME

However, I'd like to exclude physical attributes, storage attributes, tablespace, and logging. I know that if I run the following SQL statement prior to the above one that the SQL will be generated as desired, without this extra info.

exec DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE );

I'd like to be able to create a C# function that returns the DDL for a specific OWNER (schema) and object type, such as TABLE. I assume that the first statement, the execution of the stored procedure, sets an option for the current session, so I assume that I need to do the same two steps from a C# program on the same open connection. I am under the impression that DBMS_METADATA.SESSION_TRANSFORM evaluates to -1. I'm not sure about this. When I try to execute the SP using the following C# routine, I get this error:

Unspecified error: E_FAIL(0x80004005)
    

What do I need to do to be able to execute the two Oracle SQL statements using using C# code?

         public DataTable GetDdlForObjectsOfTypeInSchema(string objectType, string owner, string connectionString)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();

                ////exec DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE );
                OleDbCommand command = connection.CreateCommand();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "DBMS_METADATA.SET_TRANSFORM_PARAM";
                command.Parameters.Add(new OleDbParameter("@TRANSFORM_HANDLE", OleDbType.Numeric) { Value = -1 });
                command.Parameters.Add(new OleDbParameter("@NAME", OleDbType.VarChar) { Value = "SEGMENT_ATTRIBUTES" });
                command.Parameters.Add(new OleDbParameter("@VALUE", OleDbType.Boolean) { Value = false });

                try
                {
                    command.ExecuteNonQuery();
                }
                catch (System.Exception e2)
                {

                    string msg = e2.Message;
                }


                //Query
                command = connection.CreateCommand();
                command.Connection = connection;
                command.CommandText = @"
SELECT
    O.OWNER,
    O.OBJECT_TYPE,
    O.OBJECT_NAME,
    DBMS_METADATA.GET_DDL(REPLACE(OBJECT_TYPE, ' ', '_'), O.OBJECT_NAME, O.OWNER)  As DDL
FROM
    ALL_OBJECTS O
    WHERE
        O.OWNER = ? AND O.OBJECT_TYPE = ?
ORDER BY
    O.OWNER, O.OBJECT_TYPE, O.OBJECT_NAME
";

                var oleDbDataAdapter = new OleDbDataAdapter(command);
                DataSet dataset = new DataSet();
                command.Parameters.Add(new OleDbParameter("@OWNER", OleDbType.Char) { Value = owner });
                command.Parameters.Add(new OleDbParameter("@OBJECT_TYPE", OleDbType.Char) { Value = objectType });

                try
                {
                    oleDbDataAdapter.Fill(dataset);
                    return dataset.Tables[0];

                }
                catch (System.Exception e)
                {

                    return null;
                }

                return null;
            }
        }

question from:https://stackoverflow.com/questions/65866420/create-c-sharp-routines-that-calls-an-oracle-database-to-get-the-ddl-for-objects

Answer 1

you need to execute your 2 statements in one shot. your options are

1. Create stored procedure
2. Use anonymous block

Thankfully there is a way to do it without stored procedure - use anonymous block. And here is a good example

And your code will look like

command.CommandType = CommandType.Text;
command.CommandText  = @"
  begin
    DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE );
    open :rcursor for
      SELECT 
        O.OWNER, 
        O.OBJECT_TYPE, 
        O.OBJECT_NAME, 
        DBMS_METADATA.GET_DDL(OBJECT_TYPE, O.OBJECT_NAME, O.OWNER)  As DDL
      FROM
        ALL_OBJECTS O
      WHERE
        O.OWNER = 'MYSCHEMA' AND O.OBJECT_TYPE = 'TABLE'
      ORDER BY 
        O.OWNER, O.OBJECT_TYPE, O.OBJECT_NAME;
      
  end;";