When creating an Oracle-table to contain the Exact Online-table PjtTimeTransactionsIncremental, the following error occurs:
itgenase094: The table can not be created.
Maintenance of the table 'EOLPJTTIMETRANSACTIONS' failed.
ORA-00936: Ausdruck fehlt
ORA-06512: in Zeile 19
ORA-01403: Keine Daten gefunden
ORA-06512: in Zeile 4.
The statement is:
create or replace table EolPjtTimeTransactions@ora
as
select *
from PjtTimeTransactionsIncremental@eol
The call stack is:
Oracle.ManagedDataAccess.Client.OracleException
ValidationException
ValidationException
bei OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
bei OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, Boolean isFromEF)
bei Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
bei Invantive.Data.AnsiSqlProviderBase.PassthroughSqlAction(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String loggingTableName)
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
bei Invantive.Data.AnsiSqlProviderBase.PassthroughSqlAction(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String loggingTableName)
bei Invantive.Data.Windows.Providers.OracleManaged.OracleManagedProvider.EnsureTableExists(GlobalState owner, ExecutionOptions executionOptions, String nonStandardizedPartitionCode, TableSpecification tableSpecification, Int32 preferredSqlDdlPerBatch)
The problem can not be reproduced on the current BETA and 23.0 release on Oracle 19 using:
create or replace table EolPjtTimeTransactions@ora
as
select *
from PjtTimeTransactionsIncremental@eol
The cause might be the use of data containers on Oracle. The Oracle-statement sequence executed can be found below.
Can you please run this statement using TOAD or SQL*Plus on the target environment?
Does it also fail using ORA-00936 and if so, what line number is referred to in the error?
To use the statement, use the Copy-icon on the top right of the code block. It appears when the mouse hovers over the top right corner of the code block.
Oracle migration statement
declare
l_dummy pls_integer;
l_column_missing boolean;
l_column_data_type_correct boolean;
l_column_incorrect boolean;
begin
--
-- Check column Timestamp (type: int64, not null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'TIMESTAMP'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( TIMESTAMP number(19,0) not null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'TIMESTAMP'
and tcn.nullable = 'N'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify TIMESTAMP number(19,0) not null';
end if;
end if;
end;
--
-- Check column Account (type: guid, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ACCOUNT'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( ACCOUNT raw(16) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ACCOUNT'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify ACCOUNT raw(16) null';
end if;
end if;
end;
--
-- Check column AmountFC (type: double, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'AMOUNTFC'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( AMOUNTFC double precision null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'AMOUNTFC'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify AMOUNTFC double precision null';
end if;
end if;
end;
--
-- Check column Attachment (type: guid, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ATTACHMENT'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( ATTACHMENT raw(16) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ATTACHMENT'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify ATTACHMENT raw(16) null';
end if;
end if;
end;
--
-- Check column Created (type: datetime, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'CREATED'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( CREATED date null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'CREATED'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify CREATED date null';
end if;
end if;
end;
--
-- Check column Creator (type: guid, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'CREATOR'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( CREATOR raw(16) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'CREATOR'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify CREATOR raw(16) null';
end if;
end if;
end;
--
-- Check column Currency (type: string, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'CURRENCY'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( CURRENCY clob null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'CURRENCY'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify CURRENCY null';
end if;
end if;
end;
--
-- Check column Date (type: datetime, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'DATE'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( \"DATE\" date null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'DATE'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify \"DATE\" date null';
end if;
end if;
end;
--
-- Check column Division (type: int32, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'DIVISION'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( DIVISION number(10,0) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'DIVISION'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify DIVISION number(10,0) null';
end if;
end if;
end;
--
-- Check column Employee (type: guid, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'EMPLOYEE'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( EMPLOYEE raw(16) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'EMPLOYEE'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify EMPLOYEE raw(16) null';
end if;
end if;
end;
--
-- Check column EndTime (type: datetime, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ENDTIME'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( ENDTIME date null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ENDTIME'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify ENDTIME date null';
end if;
end if;
end;
--
-- Check column EntryNumber (type: int32, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ENTRYNUMBER'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( ENTRYNUMBER number(10,0) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ENTRYNUMBER'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify ENTRYNUMBER number(10,0) null';
end if;
end if;
end;
--
-- Check column ErrorText (type: string, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ERRORTEXT'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( ERRORTEXT clob null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ERRORTEXT'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify ERRORTEXT null';
end if;
end if;
end;
--
-- Check column HourStatus (type: int16, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'HOURSTATUS'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( HOURSTATUS number(5,0) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'HOURSTATUS'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify HOURSTATUS number(5,0) null';
end if;
end if;
end;
--
-- Check column ID (type: guid, not null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ID'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( ID raw(16) not null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ID'
and tcn.nullable = 'N'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify ID raw(16) not null';
end if;
end if;
end;
--
-- Check column Item (type: guid, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ITEM'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( ITEM raw(16) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ITEM'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify ITEM raw(16) null';
end if;
end if;
end;
--
-- Check column ItemDivisable (type: boolean, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ITEMDIVISABLE'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( ITEMDIVISABLE char(1) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'ITEMDIVISABLE'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify ITEMDIVISABLE char(1) null';
end if;
end if;
end;
--
-- Check column Modified (type: datetime, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'MODIFIED'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( MODIFIED date null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'MODIFIED'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify MODIFIED date null';
end if;
end if;
end;
--
-- Check column Modifier (type: guid, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'MODIFIER'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( MODIFIER raw(16) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'MODIFIER'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify MODIFIER raw(16) null';
end if;
end if;
end;
--
-- Check column Notes (type: string, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'NOTES'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( NOTES clob null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'NOTES'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify NOTES null';
end if;
end if;
end;
--
-- Check column PriceFC (type: double, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'PRICEFC'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( PRICEFC double precision null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'PRICEFC'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify PRICEFC double precision null';
end if;
end if;
end;
--
-- Check column Project (type: guid, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'PROJECT'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( PROJECT raw(16) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'PROJECT'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify PROJECT raw(16) null';
end if;
end if;
end;
--
-- Check column Quantity (type: double, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'QUANTITY'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( QUANTITY double precision null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'QUANTITY'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify QUANTITY double precision null';
end if;
end if;
end;
--
-- Check column StartTime (type: datetime, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'STARTTIME'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( STARTTIME date null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'STARTTIME'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify STARTTIME date null';
end if;
end if;
end;
--
-- Check column Subscription (type: guid, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'SUBSCRIPTION'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( SUBSCRIPTION raw(16) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'SUBSCRIPTION'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify SUBSCRIPTION raw(16) null';
end if;
end if;
end;
--
-- Check column Type (type: int16, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'TYPE'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( TYPE number(5,0) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'TYPE'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify TYPE number(5,0) null';
end if;
end if;
end;
--
-- Check column WBS (type: guid, null)
--
begin /* itgenora099 */
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'WBS'
;
l_column_missing := false;
exception
when no_data_found
then
l_column_missing := true;
end;
if l_column_missing
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS add ( WBS raw(16) null )';
else
begin
select 1
into l_dummy
from user_tab_columns tcn
where tcn.table_name = 'EOLPJTTIMETRANSACTIONS'
and tcn.column_name = 'WBS'
and tcn.nullable = 'Y'
;
l_column_incorrect := false;
exception
when no_data_found
then
l_column_incorrect := true;
end;
if l_column_incorrect
then
execute immediate 'alter table EOLPJTTIMETRANSACTIONS modify WBS raw(16) null';
end if;
end if;
end;
end;
This question was automatically closed after at least 1 week of inactivity after a possible solution was provided. The last answer given has been marked as a solution.
Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.