This discussion is archived
6 Replies Latest reply: Mar 15, 2012 2:22 AM by 854743 RSS

Migration to EXECUTE IMMEDIATE

854743 Newbie
Currently Being Moderated
Hi

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?

Thanks
Beat
  • 1. Re: Migration to EXECUTE IMMEDIATE
    wkobargs Journeyer
    Currently Being Moderated
    Hi Beat,

    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"?

    Regards
    Wolfgang
  • 2. Re: Migration to EXECUTE IMMEDIATE
    854743 Newbie
    Currently Being Moderated
    Hi Wolfgang

    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.

    Regards
    Beat
  • 3. Re: Migration to EXECUTE IMMEDIATE
    wkobargs Journeyer
    Currently Being Moderated
    Hi Beat,

    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.

    Regards
    Wolfgang
  • 4. Re: Migration to EXECUTE IMMEDIATE
    854743 Newbie
    Currently Being Moderated
    Hi Wolfgang

    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

    Thanks
    Regards
    Beat
  • 5. Re: Migration to EXECUTE IMMEDIATE
    wkobargs Journeyer
    Currently Being Moderated
    Hi Beat,

    the following bug has been opened:

    Bug 13849070 - MIGRATED STORED PROCEDURE CONTAINS EXECUTE IMMEDIATE INSTEAD OF A CURSOR

    Regards
    Wolfgang
  • 6. Re: Migration to EXECUTE IMMEDIATE
    854743 Newbie
    Currently Being Moderated
    Hi Wolfgang

    Thanks for your feedback.

    Regards
    Beat

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points