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
spiral2007Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
HumpdyConnect With a Mentor Commented:
ok, so this is oracle sql code which is slightly different to the sql code which your execute task is expecting.

What you will have to do is call the oracle procedure from your t-sql task, not execute your oracle code through the task.
Maybe through linked server or ado.net is the best way
0
 
HumpdyCommented:
can you post your code pls
0
 
spiral2007Author Commented:

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

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
spiral2007Author Commented:
[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.
0
 
HumpdyCommented:
do you have your oracle code wrapped in a procedure ?
You will need to call the procedure like execute servername.owner.storedprocedure.
0
 
spiral2007Author Commented:
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.
0
 
HumpdyCommented:
from your SQL management sudio, are you currently able to query a table in your Oracle database.
Is that communication link set up ?
0
 
spiral2007Author Commented:
I am using integration services.. and in the other flows the sql statements that gets data from oracles tables works fine..
0
 
HumpdyCommented:
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.
0
 
spiral2007Author Commented:
nothing happens to profiler...
I am using integration services to pass data from access db to oracle db...
0
All Courses

From novice to tech pro — start learning today.