I'm migrate from MSSQL to Oracle by using Oracle SQL Developer Version 3.0.04.
I got a lot of store procedures looking (simplified) like this:
+CREATE PROCEDURE [dbo].TestProc+
+ @QueryMode int+
+ )+ AS DECLARE @SelectString VARCHAR(100) SET @SelectString = 'SELECT * from tab ' IF @QueryMode = 0
+ EXEC (@SelectString)+ ELSE IF @QueryMode = 1 BEGIN
+ EXEC (@SelectString)+ END
The Translation Scratch Editor translates this to CREATE OR REPLACE PROCEDURE /*dbo.*/TestProc
+(+ v_QueryMode IN NUMBER DEFAULT NULL , cv_1 IN OUT SYS_REFCURSOR
+)+ AS v_SelectString VARCHAR2(100); v_QueryBody VARCHAR2(516); v_FromPart VARCHAR2(35); BEGIN v_SelectString := 'SELECT * from tab ' ; IF v_QueryMode = 0 THEN OPEN cv_1 FOR v_SelectString; ELSE IF v_QueryMode = 1 THEN BEGIN EXECUTE IMMEDIATE v_SelectString; END; END IF; END IF; END;
The part in QueryMode = 1 migrates to execute immediate because of the BEGIN/END statements.
Without this, it migrates to a cursor as shown in QueryMode = 0.
The BEGIN/END is necessary because there is more then one statement in this block. Can one influences the migration result to a cursor instead to execute immediate?
I did not yet find the time to try it out, but perhaps the following option might be helpful: Query Assignment Translation: Option to determine what is generated for a query assignment: only the assignment, assignment with exception handling logic, or assignment using a cursor LOOP ... END LOOP structure to fetch each row of the query into variables.
You will find it under Tools - Preferences - Migrations - Translators. The default is "Assignment", is there any difference if you use "Assignment using cursor loop"?
Thanks for the feedback.
I tested the output of all options "Assignment", "Assignment using cursor loop", "Assignment with exception handling" with the Translation Scratch Editor,
It does not show any influence for the store procedure.
SQL Developer 3.1 has some improvements in handling T-SQL translations. But even with that version I am getting the same result as you, and I have no idea so far how to influence this.
What is the impact of getting EXECUTE IMMEDIATE? Does this result in a wrong behavior of the stored procedure? If that is the case I can log a bug or enhancement request to get this changed in the future.
We have a .NET/C# application which calls the stored procedure and fetches the data with the cursor cv_1.
So in the example this does not work for different QueryModes.
According my opinion a select statement should always return to a cursor if an out cursor is supplied in the signature of the stored procedure. Independent of used BEGIN/END blocks.
Of course Update/Insert statements result in execute immediate.
As a result: We look for a workaround in our application.
Please let me known your decision if a bug is opened. Afterwards I close the question as answered