11 Replies Latest reply: Mar 26, 2013 2:31 PM by IQ RSS

    Converting SQL SERVER procedure PL/SQL

    IQ
      Hello,
      I am converting SQL Server procedures into Oracle Procedures using the translation scracth editor.
      Instead of converting the proecdures into Oracle proocedures the utility is converting them into Oracle functions, Any Idea why it is doing this ?

      Please advise.

      Thanks
      Fm
        • 1. Re: Converting SQL SERVER procedure PL/SQL
          wkobargs-Oracle
          Hello,

          can you post here a small example, please?
          Also, which version of SQL Developer are you using?

          Regards
          Wolfgang
          • 2. Re: Converting SQL SERVER procedure PL/SQL
            IQ
            Hi there,
            Thanksfor your reply. I am using SQL Developer (3.0.04).
            Below is a sample of the code. Please note that this is not the full code but should give you an idea of how the "Translation scratch editor" is converting a Procedure in SQL Server to a function in Oracle.

            SQL Server code :-
            CREATE PROCEDURE testg
            @p_ord INTEGER,
            @p_acct INTEGER,
            @p_met CHAR(10),
            @p_util Type CHAR(1),
            @p_remFlag CHAR(1),
            @p_dat DATETIME,
            @p_tim CHAR(5)

            AS

            SET NOCOUNT ON

            DECLARE @l_account INTEGER
            DECLARE @l_occupant SMALLINT
            DECLARE @l_accocc CHAR(10)
            DECLARE @l_category CHAR(2)
            DECLARE @l_okToProceed SMALLINT
                 
                 
                 
            PLSQL code:-     
                 
            CREATE OR REPLACE FUNCTION testg
            (
            v_p_ord IN NUMBER DEFAULT NULL ,
            v_p_acc IN NUMBER DEFAULT NULL ,
            v_p_met IN CHAR DEFAULT NULL ,
            v_p_util IN CHAR DEFAULT NULL ,
            iv_p_rem Flag IN CHAR DEFAULT NULL ,
            v_p_dat IN DATE DEFAULT NULL ,
            v_p_tim IN CHAR DEFAULT NULL
            )
            RETURN SYS_REFCURSOR
            AS
            v_p_remFlag CHAR(1) := iv_p_removalFlag;
            v_l_acct NUMBER(10,0);
            v_l_occt NUMBER(5,0);
            v_l_acc CHAR(10);
            v_l_cat CHAR(2);
            v_l_okToProceed NUMBER(5,0);
            v_l_sdp NUMBER(10,0);
            v_l_sdpc CHAR(15);
            • 3. Re: Converting SQL SERVER procedure PL/SQL
              Jeff Smith Sqldev Pm-Oracle
              Using version 3.2.20_09 I'm able to use the Translation Scratch Editor to translate a SQL Server PROCEDURE to an Oracle Procedure.

              You're using a very old version of SQL Developer. The migration support has changed drastically since that version. Can you install the latest version and try again?

              Jeff
              • 4. Re: Converting SQL SERVER procedure PL/SQL
                IQ
                What is the latest version and where can I download this from ?

                Thanks
                • 5. Re: Converting SQL SERVER procedure PL/SQL
                  wkobargs-Oracle
                  http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
                  • 6. Re: Converting SQL SERVER procedure PL/SQL
                    IQ
                    I donwloaded the latest version of SQL Developer but the third party Drivers jar file does not seem to be read. I went to preferences--> Database --> Third party JDBC Drivers
                    I still cannot see the SQL Server connections tab.

                    I downloaded the jtds-1.2.jar from the webpage as suggested by Jeff in one of his previous blogs.
                    http://www.findjar.com/jar/net.sourceforge.jtds/jars/jtds-1.2.jar.html

                    What could be wrong ?
                    • 7. Re: Converting SQL SERVER procedure PL/SQL
                      Gary Graham-Oracle
                      Hi,

                      Did you use the Check for Updates feature or download the jar file manually? Check for Updates works for me sometimes, and sometimes not. For manual installs, you then must add an entry for the jTDS jar here...
                      Tools -> Preferences -> Database -> Third Party JDBC Drivers
                      Hope this heips,
                      Gary
                      SQL Developer Team

                      Edited by: Gary Graham on Mar 26, 2013 8:32 AM
                      Seems I misread your post. If you downloaded the jar and added an entry for it in Third Party JDBC Drivers, the Sybase and SQL Server tabs should be present in the New Connection dialog. Especially if you unzipped SQL Developer into an empty directory and your user preference settings are in a directory that always remains writable to you. Make sure your install is clean.
                      • 8. Re: Converting SQL SERVER procedure PL/SQL
                        IQ
                        Thanks I can now connect to SQL Server and Oracle. So I now have SQL Developer(version 3.2.20.09)
                        However the original problem still remains the SQL Server Procedure is again getting converted to an Oracle function. I am again posting a snippet of the code down here.

                        Below is a sample of the code. Please note that this is not the full code but should give you an idea of how the "Translation scratch editor" is converting a Procedure in SQL Server to a function in Oracle.

                        SQL Server code :-

                        CREATE PROCEDURE testg
                        @p_ord INTEGER,
                        @p_acct INTEGER,
                        @p_met CHAR(10),
                        @p_util Type CHAR(1),
                        @p_remFlag CHAR(1),
                        @p_dat DATETIME,
                        @p_tim CHAR(5)

                        AS

                        SET NOCOUNT ON

                        DECLARE @l_account INTEGER
                        DECLARE @l_occupant SMALLINT
                        DECLARE @l_accocc CHAR(10)
                        DECLARE @l_category CHAR(2)
                        DECLARE @l_okToProceed SMALLINT


                        PLSQL code:-     

                        CREATE OR REPLACE FUNCTION testg
                        (
                        v_p_ord IN NUMBER DEFAULT NULL ,
                        v_p_acc IN NUMBER DEFAULT NULL ,
                        v_p_met IN CHAR DEFAULT NULL ,
                        v_p_util IN CHAR DEFAULT NULL ,
                        iv_p_rem Flag IN CHAR DEFAULT NULL ,
                        v_p_dat IN DATE DEFAULT NULL ,
                        v_p_tim IN CHAR DEFAULT NULL
                        )
                        RETURN SYS_REFCURSOR
                        AS
                        v_p_remFlag CHAR(1) := iv_p_removalFlag;
                        v_l_acct NUMBER(10,0);
                        v_l_occt NUMBER(5,0);
                        v_l_acc CHAR(10);
                        v_l_cat CHAR(2);
                        v_l_okToProceed NUMBER(5,0);
                        v_l_sdp NUMBER(10,0);
                        v_l_sdpc CHAR(15);
                        • 9. Re: Converting SQL SERVER procedure PL/SQL
                          Jim Smith
                          Does your T-SQL procedure return a result set? If so, then the way to achieve this in PL/SQL is to return a ref cursor.

                          Not that there are very major differences in how T-SQL and PL/SQL work and not all techniques in one can be easily translated to the other.
                          • 10. Re: Converting SQL SERVER procedure PL/SQL
                            rp0428
                            >
                            Below is a sample of the code. Please note that this is not the full code but should give you an idea of how the "Translation scratch editor" is converting a Procedure in SQL Server to a function in Oracle.
                            >
                            And that same sample should give you an idea as to why a function is being used for Oracle.
                            RETURN SYS_REFCURSOR
                            In SQL Server procedures can return values. In Oracle functions are used to return values.

                            If your 'procedure' returns a value it will become a 'function' in Oracle.

                            That 'RETURN' statement suggests that your 'procedure' is returning a value. But since you didn't post the code we have no way of knowing.
                            • 11. Re: Converting SQL SERVER procedure PL/SQL
                              IQ
                              Yes you are right, I checked the SQL Server procedure, the return does not do anything except return a count of records which is not used in the Application logic, not sure why it was required. So I removed the return clause and I could convert the procedure into an Oracle procedure by removing the un-necessary return clause. Thanks for your help.