2

How to pass BOOLEAN to Oracle procedure in 12c?

I heard that it wasn't possible prior to 12c, but I still can't do it in 12c.

// https://docs.oracle.com/cd/A91202_01/901_doc/appdev.901/a89852/d_metad8.htm
// PROCEDURE set_transform_param (
//     transform_handle        IN  NUMBER,
//     name                    IN  VARCHAR2,
//     value                   IN  BOOLEAN DEFAULT TRUE,
//     object_type             IN  VARCHAR2 DEFAULT NULL);
var cmd = new OracleCommand();
cmd.Connection = new OracleConnection(this.scon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "DBMS_METADATA.SET_TRANSFORM_PARAM";
cmd.BindByName = true;
cmd.Parameters.Add("transform_handle", OracleDbType.Int64).Value = -1;
cmd.Parameters.Add("name", OracleDbType.Varchar2).Value = "STORAGE";
cmd.Parameters.Add("value", "N");
cmd.Connection.Open();
cmd.ExecuteNonQuery();

I have tried the followings but getting an error.

"0", "F", "N", '0', 'F', 'N'

Error:

ORA-31600: invalid input value "0" for parameter STORAGE in function SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8680
ORA-06512: at "SYS.DBMS_METADATA_INT", line 10027
ORA-06512: at "SYS.DBMS_METADATA", line 7458
ORA-06512: at line 1
Ray Cheng
  • 12,230
  • 14
  • 74
  • 137

1 Answers1

1

I have same problem.

I solved the problem with a workaround.

1) create a wrapped stored procedure, then you can use boolean or other parameter like pl-sql;

2) call the wrapped stored procedure in the same session ( same OracleConnection);

3) call SELECT DBMS_METADATA.GET_DDL in the same session;

...
OracleCommand command = null;
string storedprc = "CREATE OR REPLACE PROCEDURE MY_SCHEMA.SET_FK_FALSE AS BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false); END;";
command = new OracleCommand(storedprc, this.Conn);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
//       
command = new OracleCommand();
command.Connection = this.Conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "MY_SCHEMA.SET_FK_FALSE";
command.ExecuteNonQuery();
//
string queryString = "SELECT DBMS_METADATA.GET_DDL( 'TABLE','MY_TABLE_NAME','MY_SCHEMA') FROM DUAL;";
command = new OracleCommand(queryString, this.Conn);
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
string ddlScript = null;
if (reader.Read())
{
    ddlScript = reader[0].ToString();
}
reader.Close();
...

Hope it can help someone, because i get all tips from internet.

Jian
  • 11
  • 2