Forum Stats

  • 3,770,167 Users
  • 2,253,079 Discussions
  • 7,875,357 Comments

Discussions

T-Sql Procedure to PL/Sql - Using the Oracle SQL Developer Editor Scratch to translate the scripts,

4141322
4141322 Member Posts: 1
edited Nov 28, 2019 12:28AM in SQL & PL/SQL

This is my original script create from SQL Server:

CREATE PROCEDURE [dbo].[SP_ObterDadosApontamentosOperacional]@dataInicio as dateTime = null,@dataFim as datetime = null,@embarcacaoId as varchar(50) = nullASBEGINif(@dataInicio is null)beginset @dataInicio = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)endif(@dataFim is null)beginset @dataFim = (DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0)) +1)endselectcodigoOperacao.Id as 'Id',codigoOperacao.Descricao as 'Descricao',codigoOperacao.Codigo as 'CodigoOperacao',count(codigoOperacao.Codigo) as 'TotalUnitario'into #parcialfrom INFO_APONTAMENTO as apontamentoinner join CODIGOS_OPERACAO as codigoOperacao on codigoOperacao.Id = apontamento.CodigosDeOperacao_Idwhere apontamento.DataCadastro between @dataInicio and @dataFimand ((apontamento.Barco_Id = @embarcacaoId) or @embarcacaoId is null)group bycodigoOperacao.Codigo,codigoOperacao.Descricao,codigoOperacao.Iddeclare @somaTotal as int = (select sum(totalUnitario) from #parcial)select *, @somaTotal as TotalAgregado from #parcialend

And This is the script generated by the oracle sql developer migration tool.

CREATE GLOBAL TEMPORARY TABLE tt_parcialAS (   SELECT codigoOperacao.Id Id  ,   codigoOperacao.Descricao Descricao  ,   codigoOperacao.Codigo CodigoOperacao  ,   0  TotalUnitario       FROM INFO_APONTAMENTO apontamento     JOIN CODIGOS_OPERACAO codigoOperacao   ON codigoOperacao.Id = apontamento.CodigosDeOperacao_Id   WHERE 1=2);/                        CREATE OR REPLACE PROCEDURE SP_ObterDadosApontamentosOperacional(  iv_dataInicio IN DATE DEFAULT NULL ,  iv_dataFim IN DATE DEFAULT NULL ,  v_embarcacaoId IN VARCHAR2 DEFAULT NULL )AS   v_dataInicio DATE := iv_dataInicio;   v_dataFim DATE := iv_dataFim;   v_somaTotal NUMBER(10,0) := ( SELECT SUM(totalUnitario)       FROM tt_parcial  );   v_cursor SYS_REFCURSOR;BEGIN   IF ( v_dataInicio IS NULL ) THEN       BEGIN      v_dataInicio := utils.dateadd('DAY', utils.datediff('DAY', 0, SYSDATE), 0) ;      END;   END IF;   IF ( v_dataFim IS NULL ) THEN       BEGIN      v_dataFim := (utils.dateadd('MS', -3, utils.dateadd('DD', utils.datediff('DD', 0, SYSDATE), 0)) + 1) ;      END;   END IF;   DELETE FROM tt_parcial;   UTILS.IDENTITY_RESET('tt_parcial');      INSERT INTO tt_parcial (     SELECT codigoOperacao.Id Id  ,           codigoOperacao.Descricao Descricao  ,           codigoOperacao.Codigo CodigoOperacao  ,           COUNT(codigoOperacao.Codigo)  TotalUnitario        FROM INFO_APONTAMENTO apontamento             JOIN CODIGOS_OPERACAO codigoOperacao   ON codigoOperacao.Id = apontamento.CodigosDeOperacao_Id    WHERE  apontamento.DataCadastro BETWEEN v_dataInicio AND v_dataFim              AND ( ( apontamento.Barco_Id = v_embarcacaoId )              OR v_embarcacaoId IS NULL )      GROUP BY codigoOperacao.Codigo,codigoOperacao.Descricao,codigoOperacao.Id );   OPEN  v_cursor FOR      SELECT * ,             v_somaTotal TotalAgregado          FROM tt_parcial  ;      DBMS_SQL.RETURN_RESULT(v_cursor);EXCEPTION WHEN OTHERS THEN utils.handleerror(SQLCODE,SQLERRM);END;

And thats the output erros:

Error(29,34): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:     ( - + case mod new not null <an identifier>    <a double-quoted delimited-identifier> <a bind variable>    continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date <a string literal with character set specification>    <a number> <a single-quoted SQL string> pipe    <an alternatively-quoted string literal with character set specification>Error(30,23): PLS-00103: Encountered the symbol ")" when expecting one of the following: . , @ ; for     group having intersect minus order partition start subpartition union where connect sample  Error(57,14): PLS-00103: Encountered the symbol "JOIN" when expecting one of the following: ) , group having intersect minus start union where connect

I noticed that there are errors in line 21 select, in line 50 join

I have never worked with oracle, but due to changes I will have to do this conversion and I have some difficulties

EdStevensBilly VerreynneL. Fernigrini

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Nov 27, 2019 10:22AM

    Select can't be used like:

       v_somaTotal NUMBER(10,0) := ( SELECT SUM(totalUnitario) FROM tt_parcial  );

    Change it to:

    CREATE OR REPLACE PROCEDURE SP_ObterDadosApontamentosOperacional

    (

      iv_dataInicio IN DATE DEFAULT NULL ,

      iv_dataFim IN DATE DEFAULT NULL ,

      v_embarcacaoId IN VARCHAR2 DEFAULT NULL 

    )

    AS

       v_dataInicio DATE := iv_dataInicio;

       v_dataFim DATE := iv_dataFim;

       v_somaTotal NUMBER(10,0);

       v_cursor SYS_REFCURSOR;

    BEGIN

        SELECT  SUM(totalUnitario)

          INTO  v_somaTotal

          FROM tt_parcial; 

    ...

    SY.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited Nov 27, 2019 10:31AM

    Given that tt_parcial is apparently empty at that point (WHERE 1=2) that assignment is actually pointless.

    If it supposed to get a value then further code changes are required.

  • Mike Kutz
    Mike Kutz Member Posts: 5,822 Silver Crown
    edited Nov 27, 2019 12:58PM

    Manual Translation to Oracle

    DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

    It appears that this can be shorten to TRUNC(SYSDATE)

    The single parameter TRUNC() will remove the time portion of a DATE data type.

    DATEADD( ms, -3, ... )

    This can be re-written as X - interval '-0.003' second

    The granularity of the Oracle DATE data type is 1 SECOND.  Adding -0.003 Seconds to a value doesn't make sense.

    If you are doing what I think you are doing,  you may be better off not using a BETWEEN clause and instead use

         DataCadastro >= nvl( iv_dateInicio, trunc(sysdate))

         and DataCadastro < nvl( iv_dateFim, trunc(sysdate) + 1)

    You will need to do some research to see if this is the correct assumption.

    IF( @dataInicio is NULL ) ...

    You can probably shorten this to v_VAR := NVL( iv_VAR, ... );

    v_dataInicio and v_dataFim

    The calculations look to be simple enough that you can place them directly inside the SQL statement.

    The usage of these variables become unnecessary.

    select ... into #parcial ...

    You are working with Oracle.  Move the SELECT portion into a CTE of your final SELECT statement.

    The CBO will "materialize" the CTE results into a temp table as needed.

    select sum(totalUnitario) from #parcial

    Analytics.  Learn them.  Love them.

    This calculation is best done within the SQL statement using SUM(TotalUnitario) over () as TotalAgregado

    REMINDER

    Database Agnostic code is a myth.

    The Best Practice in one RDBMS is Worst Practice in another.  (eg creating disposable Temp Tables )

    Brute-Force Step by Step translation will cause slow downs .. and may not work as expected.

    ALSO

    Use a PACKAGE.  (I don't think SQL Server has anything like this.)

    My $0.02

    MK

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Nov 27, 2019 1:47PM
    Cookiemonster76 wrote:Given that tt_parcial is apparently empty at that point (WHERE 1=2) that assignment is actually pointless.If it supposed to get a value then further code changes are required.

    And why do you think it is empty? WHERE 1 = 2 was used simply because all we want is to create GTT. We have no way of knowing what will be in GTT at procedure execution time.

    SY.

  • EdStevens
    EdStevens Member Posts: 28,533 Gold Crown
    edited Nov 27, 2019 1:54PM
    Mike Kutz wrote:<snip>
    REMINDERDatabase Agnostic code is a myth.The Best Practice in one RDBMS is Worst Practice in another. (eg creating disposable Temp Tables )Brute-Force Step by Step translation will cause slow downs .. and may not work as expected.

    Worth repeating.  You beat me to it.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Nov 27, 2019 5:52PM

    Using a tool to convert T-SQL to PL/SQL is completely useless - two completely different philosophies, two very different toolsets - the only reason I can think of why Oracle offers such "converter" is sales pitch to managers knowing nothing/little about both databases to lure them into switching from SQL Server to Oracle (llok how easy it is co convert your existing code). Now look at that "converter":

    1. Using SELECT in assignment

    2. Not realizing T-SQL DATETIME equivalent in Oracle is TIMESTAMP, not DATE

    I would stop right there. And, as I already mentioned - two completely different philosophies, so you should dissect logic and come up with Oracle implementation. For example, I'd rewrite that SP using ref cursor, not GTT:

    CREATE OR REPLACE

      PROCEDURE SP_ObterDadosApontamentosOper(

                                              p_dataInicio   TIMESTAMP,

                                              p_dataFim      TIMESTAMP,

                                              p_embarcacaoId VARCHAR2,

                                              p_parcial      OUT SYS$REFCURSOR

                                             )

        AS

            v_dataInicio TIMESTAMP;

            v_dataFim    TIMESTAMP;

        BEGIN

            v_dataInicio := NVL(p_dataInicio,CAST(TRUNC(SYSDATE) AS TIMESTAMP));

            v_dataFim    := NVL(p_dataFim,CAST(TRUNC(SYSDATE + 1) AS TIMESTAMP) - NUMTODSINTERVAL(.003,'SECOND'));

            OPEN p_parcial

              FOR

                SELECT  CODIGOOPERACAO.ID AS "Id",

                        CODIGOOPERACAO.DESCRICAO AS "Descricao",

                        CODIGOOPERACAO.CODIGO AS "CodigoOperacao",

                        COUNT(CODIGOOPERACAO.CODIGO) AS "TotalUnitario",

                        SUM(COUNT(CODIGOOPERACAO.CODIGO)) OVER() AS "TotalAgregado"

                  FROM  INFO_APONTAMENTO AS APONTAMENTO,

                        CODIGOS_OPERACAO AS CODIGOOPERACAO

                  WHERE CODIGOOPERACAO.ID = APONTAMENTO.CODIGOSDEOPERACAO_ID

                    AND APONTAMENTO.DATACADASTRO BETWEEN v_dataInicio AND v_dataFim

                    AND APONTAMENTO.BARCO_ID = NVL(p_embarcacaoId,APONTAMENTO.BARCO_ID)

                GROUP BY  CODIGOOPERACAO.CODIGO,

                          CODIGOOPERACAO.DESCRICAO,

                          CODIGOOPERACAO.ID;

    END;

    /

    SY.

    EdStevensBilly Verreynne
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Nov 27, 2019 5:53PM

    Also, I'd look into that .003 second which is SQL Server precision. Oracle precision is .000000001 (obviously if hardware supports it). And if your data is such that it doesn't store sub-seconds then you can use DATE instead of TIMESTAMP:

    CREATE OR REPLACE

      PROCEDURE SP_ObterDadosApontamentosOper(

                                              p_dataInicio  DATE,

                                              p_dataFim      DATE,

                                              p_embarcacaoId VARCHAR2,

                                              p_parcial      OUT SYS$REFCURSOR

                                            )

        AS

            v_dataInicio DATE;

            v_dataFim    DATE;

        BEGIN

            v_dataInicio := NVL(p_dataInicio,TRUNC(SYSDATE));

            v_dataFim    := NVL(p_dataFim,TRUNC(SYSDATE + 1) - 1 / 24 / 60 / 60);

            OPEN p_parcial

              FOR

                SELECT  CODIGOOPERACAO.ID AS "Id",

                        CODIGOOPERACAO.DESCRICAO AS "Descricao",

                        CODIGOOPERACAO.CODIGO AS "CodigoOperacao",

                        COUNT(CODIGOOPERACAO.CODIGO) AS "TotalUnitario",

                        SUM(COUNT(CODIGOOPERACAO.CODIGO)) OVER() AS "TotalAgregado"

                  FROM  INFO_APONTAMENTO AS APONTAMENTO,

                        CODIGOS_OPERACAO AS CODIGOOPERACAO

                  WHERE CODIGOOPERACAO.ID = APONTAMENTO.CODIGOSDEOPERACAO_ID

                    AND APONTAMENTO.DATACADASTRO BETWEEN v_dataInicio AND v_dataFim

                    AND APONTAMENTO.BARCO_ID = NVL(p_embarcacaoId,APONTAMENTO.BARCO_ID)

                GROUP BY  CODIGOOPERACAO.CODIGO,

                          CODIGOOPERACAO.DESCRICAO,

                          CODIGOOPERACAO.ID;

    END;

    /

    SY.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,596 Red Diamond
    edited Nov 28, 2019 12:28AM
    909cac95-f698-4da7-b458-50b72c4f4d26 wrote:I have never worked with oracle, but due to changes I will have to do this conversion and I have some difficulties

    Oracle is not SQL-Server.

    Oracle is the leading commercial RDBMS, because it is not SQL-Server.

    Oracle core concepts are fundamentally different that those of SQL-Server.

    So, why on sweet blue planet earth, are you treating Oracle, as SQL-Server?

    If you want the database to behave as SQL-Server, run code designed for SQL-Server, then use SQL-Server!

    Or do you honestly expect Oracle to do SQL-Server concepts, designs, and code, better than SQL-Server?

    Empty your cup of tea (SQL-Server), before pouring coffee (Oracle) to drink. Do not leave the teabag in when pouring coffee, because SQL-Server supports teabags.

    Trash your T-SQL code, as it does not conform to the core concepts in Oracle. Look at the business requirement addressed by that T-SQL code, then address this using PL/SQL that adheres to Oracle core concepts.

    Ignore this, and your SQL-Server solution converted to Oracle will not perform, will not scale, and most definitely be fundamentally flawed.

    L. FernigriniEdStevens