Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to execute a cursor from integration services

Posted on 2010-11-24
11
Medium Priority
?
828 Views
Last Modified: 2012-06-26
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
0
Comment
Question by:spiral2007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34203466
can you post your code pls
0
 

Author Comment

by:spiral2007
ID: 34203520

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
 
LVL 10

Accepted Solution

by:
Humpdy earned 2000 total points
ID: 34203844
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 10

Expert Comment

by:Humpdy
ID: 34203862
0
 

Author Comment

by:spiral2007
ID: 34203902
[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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34203961
do you have your oracle code wrapped in a procedure ?
You will need to call the procedure like execute servername.owner.storedprocedure.
0
 

Author Comment

by:spiral2007
ID: 34204117
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34204125
from your SQL management sudio, are you currently able to query a table in your Oracle database.
Is that communication link set up ?
0
 

Author Comment

by:spiral2007
ID: 34204148
I am using integration services.. and in the other flows the sql statements that gets data from oracles tables works fine..
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34204195
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
 

Author Comment

by:spiral2007
ID: 34204681
nothing happens to profiler...
I am using integration services to pass data from access db to oracle db...
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question