spiral2007
asked on
How to execute a cursor from integration services
Hello Experts,
I have a cursor statement that need to execute from Integration Services. The cursor statement refers to an Oracle DB. When I execute it from Oracle SqlDeveloper everything works. In Integration services I create one sql task, I paste inside it the code from Oracle SqlDeveloper and when I execute the tast I get error : "possible failure reasons: problems with the query , ResultSet property not set correctly ,parameters not set correctly, or connection not established correctly."
any idea ?
Thanks in advance
I have a cursor statement that need to execute from Integration Services. The cursor statement refers to an Oracle DB. When I execute it from Oracle SqlDeveloper everything works. In Integration services I create one sql task, I paste inside it the code from Oracle SqlDeveloper and when I execute the tast I get error : "possible failure reasons: problems with the query , ResultSet property not set correctly ,parameters not set correctly, or connection not established correctly."
any idea ?
Thanks in advance
can you post your code pls
ASKER
DECLARE Saddresscode varchar(10);
Saddress varchar(255);
Scode1 varchar(5);
Scode2 varchar(5);
Szoniid number;
Senoriaid number;
QADDRESS NUMBER :=0;
CURSOR AD IS
SELECT addresscode,address,code1,code2,zoniid,enoriaid
FROM ( SELECT COUNT(CODINSTR.DM_ADDRESSES_TEST.ADDRESSCODE) AS TS ,CODINSTR.DM_ADDRESSES_TEST.ADDRESS,CODINSTR.DM_ADDRESSES_TEST.ADDRESSCODE,CODINSTR.DM_ADDRESSES_TEST.CODE1,CODINSTR.DM_ADDRESSES_TEST.CODE2,CODINSTR.DM_ADDRESSES_TEST.ZONIID,CODINSTR.DM_ADDRESSES_TEST.ENORIAID
FROM CODINSTR.DM_ADDRESSES_TEST
INNER JOIN CODINSTR.DM_ADDRESSES_TEST dm ON CODINSTR.DM_ADDRESSES_TEST.ADDRESSCODE = dm.ADDRESSCODE
GROUP BY CODINSTR.DM_ADDRESSES_TEST.ADDRESS, CODINSTR.DM_ADDRESSES_TEST.ADDRESSCODE, CODINSTR.DM_ADDRESSES_TEST.CODE1, CODINSTR.DM_ADDRESSES_TEST.CODE2, CODINSTR.DM_ADDRESSES_TEST.ZONIID, CODINSTR.DM_ADDRESSES_TEST.ENORIAID
)
WHERE ((TS>1 AND ENORIAID>-1) OR(TS=1)) ORDER BY ADDRESS;
BEGIN
OPEN ad;
LOOP
FETCH ad INTO Saddresscode,Saddress,Scode1,Scode2,Szoniid,Senoriaid;
EXIT WHEN ad%NOTFOUND;
SELECT COUNT(aDDRESS) into QADDRESS FROM CODINSTR.DM_ADDRESSES WHERE ADDRESS = sADDRESS;
IF qADDRESS =0 THEN
INSERT INTO CODINSTR.DM_ADDRESSES (ADDRESSCODE,ADDRESS,CODE1,CODE2,ZONiID,ENORIAID) VALUES (sADDRESSCODE , sADDRESS,sCODE1,sCODE2,sZONiID,sENORIAID);
END IF;
qADDRESS := 0;
END LOOP;
close ad;
UPDATE CODINSTR.DM_ADDRESSES SET ENORIAID = NULL WHERE ENORIAID=-1;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
[Execute SQL Task] Error: Executing the query "EXECUTE ( 'begin init_enoriaid(); end;')" failed with the following error: "ORA-00900: invalid SQL statement
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
do you have your oracle code wrapped in a procedure ?
You will need to call the procedure like execute servername.owner.storedpro cedure.
You will need to call the procedure like execute servername.owner.storedpro
ASKER
EXECUTE ( 'begin EXECUTE CODINSTR.INIT_ENORIAID(); end;')
still getting
[Execute SQL Task] Error: Executing the query "EXECUTE ( 'begin EXECUTE CODINSTR.INIT_ENORIAID();. .." failed with the following error: "ORA-00900: invalid SQL statement
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
still getting
[Execute SQL Task] Error: Executing the query "EXECUTE ( 'begin EXECUTE CODINSTR.INIT_ENORIAID();.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
from your SQL management sudio, are you currently able to query a table in your Oracle database.
Is that communication link set up ?
Is that communication link set up ?
ASKER
I am using integration services.. and in the other flows the sql statements that gets data from oracles tables works fine..
can you put a declare in at the beginning, before your BEGIN statement,
also,
can you turn on sql profiler and execute the task and post what the profiler is trying to execute.
also,
can you turn on sql profiler and execute the task and post what the profiler is trying to execute.
ASKER
nothing happens to profiler...
I am using integration services to pass data from access db to oracle db...
I am using integration services to pass data from access db to oracle db...