6 Replies Latest reply: Mar 15, 2012 4:22 AM by 854743 RSS

    Migration to EXECUTE IMMEDIATE

    854743
      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-Oracle
          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
            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-Oracle
              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
                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-Oracle
                  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
                    Hi Wolfgang

                    Thanks for your feedback.

                    Regards
                    Beat