Hi,
I created app_files table using the script below that exploits Oracle 12c Auto Increment feature and saves me from the trigger and a sequence that is needed to automatically generate the unique primary key.
CREATE TABLE app_files (
app_file_id NUMBER
GENERATED ALWAYS AS IDENTITY ( START WITH 1 NOCACHE ORDER )
NOT NULL,
app_file_name VARCHAR2(50) NOT NULL,
app_file_data BLOB,
active_yn CHAR(1) DEFAULT 'Y' NOT NULL
)
LOGGING;
Now the problem is that I can't specify the primary key column in the insert list. So if I run the following insert statements, Oracle won't me.
INSERT INTO app_files (app_file_id, app_file_name, acitve_yn) VALUES(null, 'Logo.png','Y'); --The id column is passed NULL so Oracle DB will raise erro
INSERT INTO app_files (app_file_id, app_file_name, acitve_yn) VALUES(1, 'Logo.png','Y'); --If I specify ID intentionally, it will again raise the error.
The only SQL that Oracle will allow is:
INSERT INTO app_files(app_file_name, active_yn) VALUES ('Logo.png','Y');
Now if I create an ADF entity object that selects all columns except app_file_data, and creates an updateable view on it. I can't insert using this view due to the above-stated issue that Oracle will not allow even NULL value or pass some pseudo number as generated by the framework by specifying the column as DBSequence.
Does anyone else has encountered the similar issue?
Is there any workaround in ADF or I need to use the traditional approach of the trigger/sequence with the DBSequence only.
Any help will be really appreciated.
Many Thanks and
Kind Regards,
Bilal
--