how to add unique sequence number along with another table's data

Question

I am running a PL/SQL code on oracle which is supposed to insert data from table 'A' into a table 'B' along with a unique sequence being returned from a function. My sql query is like this

SEQUENCE_COLUMN:=SEQ_COL_FUNC('WTPARTMASTER', 'ELEM10',SEQUENCE_COLUMN);
sequence_value:=seq_val_func('WTPARTMASTER', 'ELEM10',sequence_value);
dest_Columns:=dest_Columns || ',' || sequence_column;

QUERY_TEST :=  'insert into wbm.' || DESTINATION_TABLE || '(' || DEST_COLUMNS || ') select ' || SRC_COLUMNS || ', :value from ' || SOURCE_TABLE;

execute immediate query_test USING sequence_value;

Now the issue is in this way, select statement brings whole data in one go and I get same sequence no. in each row instaead of unique sequence number.

How can manipulate the query here such that I get the data as well as sequence inserted in my destination table one by one such that sequence no is unique.

Thanks for your help.

Answer 1

Why do you have seq_val_func()?

Just use sequence_name.nextval and sequence_name.currval to access the values from the sequence. It looks like you're trying to dynamically determine the correct sequence_name and then use it in a piece of dynamic SQL. That's ok (seems overly complicated to me, but I don't know your business requirements), but, you should directly reference the nextval or currval in your dynamically generated SQL statement.