Solved

How to execute a cursor from integration services

Posted on 2010-11-24
11
802 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
  • 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 500 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Write a function 5 25
Oracle 12c patching 1 37
Usage Scenarios for Extended Events? 1 12
Oracle and DateTime math 6 15
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

757 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now