2

I am doing data insert into a table in Oracle which is having a sequence set to it in one of the columns say Id column. I would like to know how to do data loads into such tables.

I followed the below link -

It's possible to use OleDbConnections with the Script Component?

and tried to create a function to get the .nextval from the Oracle table but I am getting the following error -

Error while trying to retrieve text for error ORA-01019

I realized that manually setting the value via the package i.e. by using the Script task to enumerate the values but is not incrementing the sequence and that is causing the problem. How do we deal with it? Any links that can help me solve it?

I am using SSIS-2014 but I am not able to tag it as I don't due to paucity of reputation points.

Community
  • 1
  • 1
VKarthik
  • 1,379
  • 2
  • 15
  • 30

2 Answers2

1

I created a workaround to cater to this problem. I have created staging tables of the destination without the column that takes the Sequence Id. After the data gets inserted, I am then calling SQL statement to get the data into the main tables from staging table and using the .nextval function. Finally truncating/dropping the table depending on the need. It would still be interesting to know how this same thing can be handled via script rather having this workaround.

For instance something like below - 

insert into table_main
select table_main_sequence_name.nextval
,*
from (
select *
from table_stg
)
VKarthik
  • 1,379
  • 2
  • 15
  • 30
0

ORA-01019 may be related to fact you have multiple Oracle clients installed. Please check ORACLE_HOME variable if it contains only one client.

One workaround I'm thinking about is creating two procedures for handling sequence. One to get value you start with:

create or replace function get_first from seq as return number
seqid number;
begin
select seq_name.nexval into seqid from dual;
return seqid;
end;
/

Then do your incrementation in script. And after that call second procedure to increment sequence:

create or replace procedure setseq(val number) as
begin 
execute immediate 'ALTER SEQUENCE seq_name INCREMENT BY ' || val;
end;
/

This is not good approach but maybe it will solve your problem

Kacper
  • 4,798
  • 2
  • 19
  • 34
  • ORACLE_HOME variable wasn't present in the system variables. I added it with the client path but still I am facing the same error. I will now try it by specifiying the connection string within the script considering it being with a password. – VKarthik Nov 06 '16 at 19:28
  • @VKarthik I have no more ideas about ORA error. But posted one workaroud that may help you – Kacper Nov 06 '16 at 19:45
  • thanks for the update and the workaround. The problem I have is getting any database call done within the script component because of that error so I don't think I can go with that. I have figured out another method as listed below – VKarthik Nov 06 '16 at 22:26