Link to home
Start Free TrialLog in
Avatar of spiral2007
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
Avatar of Humpdy
Humpdy

can you post your code pls
Avatar of spiral2007

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;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Humpdy
Humpdy

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
[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.
do you have your oracle code wrapped in a procedure ?
You will need to call the procedure like execute servername.owner.storedprocedure.
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.
from your SQL management sudio, are you currently able to query a table in your Oracle database.
Is that communication link set up ?
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.
nothing happens to profiler...
I am using integration services to pass data from access db to oracle db...