This discussion is archived
11 Replies Latest reply: Mar 26, 2013 12:31 PM by 506957 RSS

Converting SQL SERVER procedure PL/SQL

506957 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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
    506957 Newbie
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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
    506957 Newbie
    Currently Being Moderated
    What is the latest version and where can I download this from ?

    Thanks
  • 5. Re: Converting SQL SERVER procedure PL/SQL
    wkobargs Journeyer
    Currently Being Moderated
    http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
  • 6. Re: Converting SQL SERVER procedure PL/SQL
    506957 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    506957 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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
    506957 Newbie
    Currently Being Moderated
    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.

Legend

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