creating sequence ?????? 1

[bilalch]

can any1 plz help me with an XMPLE,how to create and attach a sequence to a field in the table.

thanks in advance

 

[sem]

You can not attach sequence to the field, though you may assign the value in trigger. Sequence has many options, but the simpliest (most common) command is

CREATE SEQUENCE <sequence name>

Then you may create &quot;before insert row-level&quot; trigger and assign sequence value to the field you need to be &quot;attached&quot;:

CREATE SEQUENCE EMP_SEQ

CREATE TRIGGER EMP_BRI_TRG
BEFORE INSERT ON EMP
FOR EACH ROW
BEGIN
select emp_seq.nextval into :new.empno from dual;
END;


Regards, Dima

 

[bilalch]

HI DEAR SEM,
THANX FOR UR RESPONSE,
PLZ TELL ME WUT'S THIS

:new.empno

IS IT THE TEXTBOX IN THE DATA BLOCK.

AND THAT WHERE TO WRITE THIS TRIGGER

THANX
BYE

 

[sem]

You should write it in sql*plus or any other sql command tool. :new is obviously not a text field in Forms and trigger is obviously not a Forms trigger, because you've asked about &quot;field in the table&quot;, not about field in Forms block. If you need to make it in Forms, you may create pre-insert trigger and select sequence value into text item, but that's completely another story

Regards, Dima

 

[bilalch]

hi dear sem,
thanx for ur reply, but i want it on the form as i'm inserting values from the form.

now i've a trigger &quot;pre-text-item&quot; like this

begin
select emp_seq.nextval into :block.txtitem from dual
end;

but it returns compilation error

plz help me , i want the sequence no to b inserted when i'm going to insert the row.

 

[sem]

If the code provided is EXACT, add semicolon after dual at least. It would be much better if you provide a little bit more information about error, at least its code, because it may be caused by dozens of your mistakes: wrong field/block/table names, no access to sequence etc.

You may also set Default Value property for block.textitem as :SEQUENCE.emp_seq.NEXTVAL instead. The drawback is that you may have a lot of gaps in your values.

Regards, Dima

 

[bilalch]

thank u dear Dima,
there was a little bit problem in my code now i've removed and tis working

i'm so grateful to u , u really helped me a lot
i didn't have the idea of sequences.

thanks
bye

 

[SheepDog]

I would like to know if I have 100 records how can I bring them back in a sequentially? Like record 1, record 2, record 3, record 4, record 5 and so on.

 

[sem]

You may use ROWNUM pseudocolumn:

select ename, ROWNUM from emp

Note, that the records are &quot;numbered&quot; at the moment the result set already exists but before sorting it according to ORDER BY clause Thus conditions like rownum><some value> make no sence.



Regards, Dima

 

[bilalch]

if u hav sequence applied to your PK column, now how'll u refer this column as FK while u dont know wut's the PK value for some record.