Create a table with boolean field

I'm trying to create a table via the exe_sql statement which needs to contain a boolean field.

IMPORTANT: DFC needs to identify this column as boolean so that I can invoke the IDfCollection.getBoolean() method on it.

The back end is Oracle and I'm executing the command as dmadmin.

Here is my statement:

EXECUTE exec_sql with query='CREATE TABLE my_policy (
policy_name char(32) not null,
description char(64),
policy_enabled boolean not null)'

I get the following error:

Error occured during query execution :[DM_SERVER_E_EXEC_SQL]error: "The execsql method failed due to a database error: ORA-00902: invalid datatype

Any ideas?

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

Tord

Hi Udeleng,

There is no datatype boolean in SQL. In SQL Server the boolean is represented as a tinyint, I don't have access to an Oracle system at the moment so I can't check but I would assume it is represented as an integer or number there as well. You can check the type of for example the a_is_hidden column of the dm_sysobject_s table with the sqlplus command line tool that comes with Oracle by using the describe command.

Hope it helps

Thanks

--Tord

All comments

Tord

Hi Udeleng,

Hope it helps

Thanks

--Tord

udeleng

I checked dm_sysobject_s table for a Boolean field and determined that the underlying type is number(1,0).

The query would be:

EXECUTE exec_sql with query='CREATE TABLE my_policy (
policy_name char(32) not null,
description char(64),
policy_enabled number(1,0) not null)'

I also changed char to varchar2.