Oracle Identity Columns
Posted 13 July 2006 - 06:05 AM
I like the idea, and will do some testing on it. How would get the actual key back in to proiv?
What if I have a parent-child relation, and have to inserted a couple of records in to a detail file, for which I would need the key being generated for the header record?
Posted 13 July 2006 - 05:45 PM
SET ALPHA_VALUE = IDENT_CURRENT(‘TABLE_NAME’)
In SQL Server, you cannot directly read a SQL variable from PRO-IV logic, so you have to store it in a SQL table and then read a row from that table in PRO-IV. So at this point, you would need to read temp_table and get the value of ALPHA_VALUE. That would be the identity column for the current row.
I don't know if there is an equivalent function to IDENT_CURRENT in Oracle (I would guess that there would). If so, I think you can do an SELECT INTO a PRO-IV scratch variable, so you might be able to do something like the following (except that you would need the Oracle equivalent of IDENT_CURRENT):
SELECT IDENT_CURRENT('TABLE_NAME') INTO :$VAR
Hope this helps. Mark Dexter
Posted 20 July 2006 - 09:59 PM
A note of caution about my previous post using the SQL Server Identity column and an INSERT trigger. We tested this in a multi-user simulation and it appears to only allow one user at a time to INSERT rows. We aren't sure whether this is because of the trigger or just a consequence of using the IDENTITY column in SQL Server. So we are going to use a different method (the NEWID() function in SQL Server). Mark Dexter
Posted 28 July 2006 - 01:33 PM
CREATE SEQUENCE testseq
ALTER TABLE testseq OWNER TO edb;
CREATE TABLE tblseq
seq_id int4 default nextval('testseq'),
It works fine and I can select seq_id from tblsq where seq_id = ?.
I am busy testing on Oracle DB using the ff. sql
CREATE SEQUENCE testseq
INCREMENT BY 1
START WITH 10
with a trigger inserting values into seq_id.
Posted 14 August 2006 - 09:53 AM
MAXVALUE 999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE TABLE dbname.MATTHEWS_TAB
COL_A NUMBER(10) NOT NULL,
COL_B VARCHAR2(10) NOT NULL,
CREATE OR REPLACE TRIGGER dbname.MATTHEWS_TRG BEFORE
INSERT ON MATTHEWS_TAB FOR EACH ROW DECLARE
SELECT MATTHEWS_SEQ.NEXTVAL INTO generator FROM DUAL;
(insert into matthews_tab values 'me','you');
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users