How to call a stored procedure from a function?

Database
Enthusiast

How to call a stored procedure from a function?

Hello everybody,

I'm doing a migration of code from Oracle PL-SQL to Teradata Stored Procedure Language.

I have a long and complex function in Oracle that I can't convert in a function in Teradata due to the very few flexibility they give.

The alternative is to convert it into a procedure, but the problem here is that I'm calling that function (TRATA_LITERAL) from many select queries.

For example:

select
  COD_PROVIN,
  ENTIDADSINGULAR,
  MUNICIPIO,
  TRATA_LITERAL(ENTIDADSINGULAR, 1, 1, 1) AS ENTIDADRETOC,
from
  UNPOB_BASE;

Is there any way to be able to call a procedure as a function in a select query?

If not, If I convert it into a procedure (for example, TRATA_LITERAL_AUX), could it be possible to encapsulate and call it from a function (TRATA_LITERAL) to return the out parameter I need?

If not... Any other suggestion?

Thanks and regards

Juan
  • Stored Procedure Language

Accepted Solutions
Junior Contributor

Re: How to call a stored procedure from a function?

You might be able to do this, but as soon as there's a larger number of rows it will be horribly slow.
1 ACCEPTED SOLUTION
7 REPLIES
Junior Contributor

Re: How to call a stored procedure from a function?

Can you share the source code of this function?
Enthusiast

Re: How to call a stored procedure from a function?

Hello,

Here is the code (inside I call another procedures/functions, but I don't think you'll need them):

CREATE OR REPLACE FUNCTION "TRATA_LITERAL" (
  LITERAL IN VARCHAR2,
  ALFAOALFANUM IN NUMBER,
  SINPREPOS IN NUMBER,
  SINNOTAS IN NUMBER
) RETURN VARCHAR2 IS

  -- VARIABLES
  --------------
  nIni number := 1; -- Apuntador de cadena
  nPalabras number := 0; -- Contador de palabras de la cadena
  literalCad VARCHAR2(250) := LITERAL; -- Variable para trabajar con la cadena de entrada
  literalCadAux VARCHAR2(250) := ''; -- Variable auxiliar para trabajar con la cadena de entrada
  literalTab CORREOS_PARSE_DIRES.array_cadena; -- Variable para tablificar la cadena de entrada

BEGIN

  IF rtrim(literalCad) is null THEN -- La cadena de entrada es NULL o sólo tiene espacios
    --DBMS_OUTPUT.PUT_LINE('literalCad: '|| literalCad); -- Impresión de la salida para depuración
    return literalCad; -- Retornaremos NULL
  END IF;
  
  -- Realizamos la traslación de caracteres (y conversión de UTF-8 si fuera necesario)
  literalCad := trim(CORREOS_PARSE_DIRES.F_TRASLA_CHARS(literalCad));
  
  -- Realizamos el tratamiento de los elementos entre paréntesis y/o corchetes
  literalCad := CORREOS_PARSE_DIRES.F_NOTAS_AL_FINAL(literalCad, SINNOTAS);
  
  -- Tratamiento de caracteres no alfanuméricos relevantes
  IF (ALFAOALFANUM = 0 OR ALFAOALFANUM = 1 OR ALFAOALFANUM = 8) THEN
    -- Sustituimos por espacios todos los no alfanuméricos que quedaban tras la traslación
    literalCad := TRANSLATE(literalCad, '-/()[]', '      ');
  ELSIF (ALFAOALFANUM = 4 OR ALFAOALFANUM = 5) THEN
    -- Sustituimos por espacios los "-" y "/" (por lo que nos quedamos con "(" , ")" , "[" y "]")
    literalCad := TRANSLATE(literalCad, '-/', '  ');
  ELSIF (ALFAOALFANUM = 6 OR ALFAOALFANUM = 7) THEN
    -- Sustituimos por espacios los "(" , ")" , "[" , "]" y "-" (por lo que nos quedamos con "/")
    literalCad := TRANSLATE(literalCad, '-()[]', '     ');
  END IF;

  IF rtrim(literalCad) is null THEN -- Lo que queda es NULL o sólo tiene espacios
    --DBMS_OUTPUT.PUT_LINE('literalCad: '|| literalCad); -- Impresión de la salida para depuración
    return literalCad; -- Retornaremos NULL
  END IF;

  -- A continuación realizaremos un tratamiento diferente dependiendo de si queremos
  -- los caracteres alfabéticos (letras) o alfanuméricos (letras y números)
  
  IF (ALFAOALFANUM = 1 OR ALFAOALFANUM = 3 OR ALFAOALFANUM = 5 OR ALFAOALFANUM = 7) THEN -- Queremos caracteres alfabéticos (sólo letras)
  
    -- Detectamos los ceros aislados en medio de palabra y los convertimos en O's
    FOR i IN 1..length(literalCad) LOOP -- Empezamos el bucle en la posición 2
      IF (i >= 2) AND SUBSTR(literalCad, i, 1) = '0' AND -- Si es cero y está en medio de palabra,
      SUBSTR(literalCad, i-1, 1) BETWEEN 'A' AND 'Z' AND -- se considera como una O
      SUBSTR(literalCad, i+1, 1) BETWEEN 'A' AND 'Z' THEN
        literalCadAux := literalCadAux || 'O';
      ELSE -- El resto de caracteres se no se cambian
        literalCadAux := literalCadAux || SUBSTR(literalCad, i, 1);
      END IF;
    END LOOP;
    literalCad := literalCadAux; -- Aplicamos el resultado del tratamiento
    
    -- El resto de números se sustituyen por espacios
    literalCad := TRANSLATE(literalCad, '0123456789', '          ');
  
  ELSIF (ALFAOALFANUM = 8) THEN -- Queremos sólo caracteres numéricos
  
    literalCad := regexp_replace(literalCad, '[^0-9]', ' ');
  
  ELSE -- Queremos caracteres alfanuméricos (letras y números)
  
    -- Separamos letras y números y sustituímos ceros
    -- aislados en medio de letras
    literalCad := CORREOS_PARSE_DIRES.F_SEPARA_LETRASYNUMS(literalCad, 0);
  
  END IF;
  
  -- Quitamos los espacios en blanco sobrantes
  literalCad := CORREOS_PARSE_DIRES.F_QUITA_REPES(literalCad, ' ');
  
  IF literalCad is null THEN -- Tras el segundo parseo, la cadena de entrada es NULL
    --DBMS_OUTPUT.PUT_LINE('literalCad: '|| literalCad); -- Impresión de la salida para depuración
    return literalCad; -- Retornaremos NULL
  END IF;
  
  IF (ALFAOALFANUM BETWEEN 2 AND 7) THEN
    -- Separamos paréntesis, guiones y otros elementos similares de los caracteres que les rodean
    literalCad := REPLACE(literalCad, '(', ' ( ');
    literalCad := REPLACE(literalCad, ')', ' ) ');
    literalCad := REPLACE(literalCad, '[', ' [ ');
    literalCad := REPLACE(literalCad, ']', ' ] ');
    literalCad := REPLACE(literalCad, '-', ' - ');
    literalCad := REPLACE(literalCad, '/', ' / ');
  END IF;
  
  -- Realizamos la tablificación del string de entrada en una tabla de palabras
  nPalabras := CORREOS_PARSE_DIRES.F_CADENA_A_TABLA(literalCad, literalTab);
  
  IF (SINPREPOS = 1) THEN -- Queremos suprimir artículos y preposiciones
    -- Tratamos los casos especiales de palabras con apostrofes
    nPalabras := CORREOS_PARSE_DIRES.F_TRATA_APOSTROFES(literalTab);
    -- Limpiamos artículos y preposiciones del nombre de via (si sólo quedaban preposiciones, no se suprimen)
    nPalabras := CORREOS_PARSE_DIRES.F_TRATA_PREPOS(literalTab,false);
  END IF;
  
  -- Devolvemos las palabras tratadas a un string
  literalCad := CORREOS_PARSE_DIRES.F_TABLA_A_CADENA(literalTab, nIni, nPalabras);
  
  -- Sustituimos por espacios los apóstrofes que no eran parte de artículo/preposición
  literalCad := REPLACE(literalCad,'''', ' ');
  
  -- Si queremos suprimir artículos y preposiciones y tenemos algo más que una "A" al principio o al final
  IF (SINPREPOS = 1) AND length(TRIM(REGEXP_REPLACE(literalCad, '(^A | A$)', ' '))) > 0  THEN
    -- Limpiamos las A's sueltas en los extremos. Ejemplo: "A CORUÑA", "CORUÑA A" --> "CORUÑA"
    literalCad := REGEXP_REPLACE(literalCad, '(^A | A$)', ' ');
  END IF;
  
  IF (ALFAOALFANUM BETWEEN 2 AND 7) THEN
    -- Sustituimos espacios innecesarios alrededor de paréntesis, guiones, etc
    literalCad := REPLACE(literalCad, '( ', '(');
    literalCad := REPLACE(literalCad, ' )', ')');
    literalCad := REPLACE(literalCad, '[ ', '[');
    literalCad := REPLACE(literalCad, ' ]', ']');
    literalCad := REPLACE(literalCad, '- ', '-');
    literalCad := REPLACE(literalCad, ' -', '-');
    literalCad := REPLACE(literalCad, '/ ', '/');
    literalCad := REPLACE(literalCad, ' /', '/');
    -- Suprimimos paréntesis y corchetes sin contenido
    literalCad := REPLACE(literalCad, '()', '');
    literalCad := REPLACE(literalCad, '[]', '');
  END IF;
  
  -- Quitamos los espacios en blanco sobrantes
  literalCad := CORREOS_PARSE_DIRES.F_QUITA_REPES(literalCad, ' ');
  
  -- DBMS_OUTPUT.PUT_LINE('literalCad: '|| literalCad); -- Impresión de la salida para depuración
  
  return literalCad; -- Retornaremos la cadena tratada

END "TRATA_LITERAL";

Thanks and regards

Juan
Junior Contributor

Re: How to call a stored procedure from a function?

Ok, this is actually complex, there's only a slight chance that it can be written as a SQL-UDF in Teradata (CASE plus nested REGEXP_REPLACE, etc.). You probably need to rewrite it using a C-UDF
Enthusiast

Re: How to call a stored procedure from a function?

Hello again,

Do you have a short example of using the "CASE plus nested"?

On the other hand, if I translated the code of the function to a C-UDF, could I use inside it Teradata functions/procedures? Could you provide me some example?

Also, do i need to install anything to interprete C/C++ code in my Teradata SQL Assistant to compile/execute it?

Thanks and regards,

Juan
Junior Contributor

Re: How to call a stored procedure from a function?

Regarding the CASE finally there must be one expression

CASE
   WHEN  (ALFAOALFANUM IN (1, 3, 5, 7)
   THEN CASE WHEN  (ALFAOALFANUM IN (3,5) THEN ... END
   WHEN ...
END

I usually try to get the result in a Select statement (using nested aliases first and then I replace the aliases with the source code). 

If you can implement this as a C-UDF you can use it in any place like a built-in function. I'm not a C-programmer, but the "SQL External Routine Programming" for your release includes examples. And you can have a look at the old Oracle UDFs (now built-in) or the UDFs from Ebay.

 

No need to install a C-compiler locally (but of course, you need to test the code somewhere)

 

 

Enthusiast

Re: How to call a stored procedure from a function?

Hello again,

Thanks for your answers.

I've been thinking and I had an idea:

  • First of all, code the Oracle function TRATA_LITERAL as a procedure
  • And then, create a C-UDF function that calls TRATA_LITERAL and returns the output parameter given by the procedure

I guess that would be possible, right?

I can't see another way to take the output parameter of a Teradata Procedure and return it directly in the middle of a select query... appart from encapsulate it inside "something" that can be called as a function...

Regards

Juan
Junior Contributor

Re: How to call a stored procedure from a function?

You might be able to do this, but as soon as there's a larger number of rows it will be horribly slow.