June 9, 2010 at 12:05 pm
I am trying to pass parameters to in the Execute SQL Task for an Oracle query. I am running a query like below on a Oracle server through SSIS. However, when I create the parameter for the drugname, nothing gets inserted into the temptable. The Job does complete successfully. The variable is of type string and the paramater is of type varchar. So I am not sure why it isn't working. Any ideas? BTW, I can get this to work fine when I do an integer for a field as a parameter. Its just the drugname that is giving me grief. Thanks.
insert into tempTable
select *
from table t
join table2 t2 on
t2.drugid = t.drugid
where t.date = to_date('05-01-2010', 'YYYY-MM-DD')
and t2.drugname = ?
June 10, 2010 at 2:47 pm
First thing would be to check what is getting to the Oracle side as a bind variable.
Ask your Oracle DBA to do a 10046 trace at level 4 on your session and report back.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 11, 2010 at 9:12 am
Ok I figured out what was going on. Some invisible character was getting tacked onto the end of the variable, probably a line feed. I could fix it by doing the substr function and removing the last character. But I found an even better way. I switched to using an ODBC connection, instead of a OLE DB. And I had to set the variable to WVARCHAR, since WVARCHAR is Unicode, and that is what the Oracle server is set to.
Thanks for the reply though.
June 11, 2010 at 10:50 am
Nice job. You manage to figure it out what 10046-trace was supposed to do.
Just out of curiosity, how did you figure it out without looking at the bind variable on the Oracle side?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 11, 2010 at 12:30 pm
Lots of googling and some troubleshooting with the Oracle DBA. I came across one post of this issue where someone mentioned that it looked like a line feed was getting added at the end of the variable. So I discussed with my DBA and he tried looking at the query as it was running. That didn't really show anything since Oracle just showed 001 for the variable, instead of its value.
From several different test queries, I knew that either NULL or an incorrect value was getting passed. So the post I found made sense that a line feed was added. And since trim didn't work, I figured I would try removing the last character. Once that worked, I knew someone was getting tacked on at the end, and talking to my DBA we came to the conclusion that there was some translation error for the character sets or something.
So next step was to try ODBC, which had different variable settings for the parameter. And since it had one specifically for unicode, I tried that and it worked. I also tried regular varchar, and it didn't work. So it had to be unicode. So there was some difficulty conversion of character types, it seemed.
So in a nutshell, lots of trial and error allowed me to figure it out.
August 17, 2011 at 4:59 am
sorry before. if i posted in wrong thread.
i just need help, i have problem when binding value in an input variable with pl/sql script in SSIS package.
here the illustration script that i put into 2 execute sql tasks :
let's say :
Execute sql task 1 (sql svr 2005 db)
select Colskey1, Colskey2 From sqlsvrTable Where Colskey1=1
Execute sql task 2 (oracle10g db)
insert into tranTbl
select col0,col1,col2
from sysadm.oracleTable
where col0 = ?
and col1 <= ?
I tried to bind input value (Execute sql task 1 to Execute sql task 2) but those variables are "like" always
empty (0 rows executed), but those script run well in SSMS2005/2008. i also tried with EvaluateAsExpression but the character is exceeded (more than 4000 chars).
so in my case, is this caused as "like" you mentions for a line feed ?? if you change varchar to WVARCHAR as an Unicode, is this also will impact for all type such as integer and money, so i should change with those unicode type, isn't it ?
August 17, 2011 at 1:59 pm
amir.mochtar (8/17/2011)
sorry before. if i posted in wrong thread.i just need help, i have problem when binding value in an input variable with pl/sql script in SSIS package.
here the illustration script that i put into 2 execute sql tasks :
let's say :
Execute sql task 1 (sql svr 2005 db)
select Colskey1, Colskey2 From sqlsvrTable Where Colskey1=1
Execute sql task 2 (oracle10g db)
insert into tranTbl
select col0,col1,col2
from sysadm.oracleTable
where col0 = ?
and col1 <= ?
I tried to bind input value (Execute sql task 1 to Execute sql task 2) but those variables are "like" always
empty (0 rows executed), but those script run well in SSMS2005/2008. i also tried with EvaluateAsExpression but the character is exceeded (more than 4000 chars).
so in my case, is this caused as "like" you mentions for a line feed ?? if you change varchar to WVARCHAR as an Unicode, is this also will impact for all type such as integer and money, so i should change with those unicode type, isn't it ?
Well - you did it again! 😀
Please open a NEW thread - do not append to an existing one if what you have is an original inquire.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 17, 2011 at 10:13 pm
PaulB-TheOneAndOnly (8/17/2011)
amir.mochtar (8/17/2011)
sorry before. if i posted in wrong thread.i just need help, i have problem when binding value in an input variable with pl/sql script in SSIS package.
here the illustration script that i put into 2 execute sql tasks :
let's say :
Execute sql task 1 (sql svr 2005 db)
select Colskey1, Colskey2 From sqlsvrTable Where Colskey1=1
Execute sql task 2 (oracle10g db)
insert into tranTbl
select col0,col1,col2
from sysadm.oracleTable
where col0 = ?
and col1 <= ?
I tried to bind input value (Execute sql task 1 to Execute sql task 2) but those variables are "like" always
empty (0 rows executed), but those script run well in SSMS2005/2008. i also tried with EvaluateAsExpression but the character is exceeded (more than 4000 chars).
so in my case, is this caused as "like" you mentions for a line feed ?? if you change varchar to WVARCHAR as an Unicode, is this also will impact for all type such as integer and money, so i should change with those unicode type, isn't it ?
Well - you did it again! 😀
Please open a NEW thread - do not append to an existing one if what you have is an original inquire.
sorry for that, i thought it's not necessary to open new one, because my problem here is basically similar as coder_t2's that tried to pass parameters to in the Execute SQL Task for an Oracle query. so should i open a NEW thread ??
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply