All Forums Database
juanalfonso 41 posts Joined 01/16
17 Feb 2016
How to make an anonymous piece of code to execute it once?

Hello everybody,

I would like to know how to make a piece of code like an stored procedure, but just for one execution.

For example, if I already have a stored procedure with a header like:

PROCEDURE TESTPROC (IN cCadena VARCHAR(252), IN delchar CHAR, INOUT cSubCad VARCHAR(252))

and I just want to make a sort code to test if it works ok. Something like:

BEGIN
  DECLARE Resul VARCHAR(252);
  CALL TESTPROC('XXXblablablaXXX', 'X', Resul);
END;

How can I do it? I can't find the syntax anywhere to do this...

Thanks in advance and best regards

dnoeth 4628 posts Joined 11/04
17 Feb 2016

You simply CALL the procedure, no need to declare the result:

CALL TESTPROC('XXXblablablaXXX', 'X', Resul);

This returns a result row with "Resul"

Dieter

juanalfonso 41 posts Joined 01/16
17 Feb 2016

I've tried with a procedure I've just created and that doesn't work:

CALL F_QUITA_REPES('   Hola     amigos qué tal?   ', ' ', Resul);

I obtain the error message:

  • CALL Failed.  [3810] Column/Parameter 'NOVATD.F_QUITA_REPES.Resul' does not exist.

Any alternative?

dnoeth 4628 posts Joined 11/04
17 Feb 2016

You're calling the SP within another SP?
Then you need to declare resul.
 
Standalone (outside of an SP) there will be no error when you simply call it.

Dieter

CarlosAL 512 posts Joined 04/08
17 Feb 2016

Hi.
1. There is no PL/SQL anonymous blocks in TD.
2. You can call SPs as Dieter explained. If the param is INOUT you must provide a value, not a name:
CALL F_QUITA_REPES(' Hola ', ' ', 'Dummy');
3. Remove characters from strings can be done with built-in functions like oRELPACE, oTRANSLATE or REGEXP_REPLACE.
4. You'd better start thinking in Teradata, instead of how to do Oracle things in Teradata.
HTH.
Cheers.
Carlos

juanalfonso 41 posts Joined 01/16
17 Feb 2016

Thanks Dieter and Carlos!

Dieter, I'm calling it standalone... I guess it fails, as Carlos said, because it's an INOUT parameter and not just an OUT parameter.

Altho giving it a value instead of a name has a problem:

  • CALL F_QUITA_REPES('   Hola amigos qué tal?   ', ' ', 'salida');  --> returns 'salida' = 'Hola a'
    • (the out string is "substringed" to the length of 'salida')

The workaround I made is (I guess there is not a quicker/better way, right?):

  • CALL F_QUITA_REPES('   Hola amigos qué tal?   ', ' ', cast('salida' as varchar(252))); --> returns 'salida' = 'Hola amigos qué tal?'

Also, I wanted to add something else... about the procedure I commented before, F_QUITA_REPES.

I tried to declare the third parameter as OUT but I receiced errors (see the comments in the code):

REPLACE PROCEDURE F_QUITA_REPES (IN cCadena varchar(252), IN delchar char, OUT cSubCad varchar(252)) SQL SECURITY CREATOR
  
BEGIN
    
  SET cSubCad = cCadena;
    
  IF (delchar = chr(39)) THEN
    SET cSubCad = LTRIM(cSubCad, delchar); --> REPLACE PROCEDURE Failed. [5526] SPL1028:E(L23), Illegal attempt to modify symbol 'cSubCad'
  ELSE
    SET cSubCad = LTRIM(RTRIM(cSubCad, delchar), delchar);
        --> [5526] SPL1028:E(L25), Illegal attempt to modify symbol 'cSubCad'.
  END IF;
    
  IF (cSubCad IS NOT NULL) THEN
    WHILE cSubCad LIKE '%'||delchar||delchar||'%' DO
    BEGIN
      SET cSubCad = OREPLACE(cSubCad, delchar||delchar, delchar);
    END;
    END WHILE;
  END IF;
  
END;

Why is the procedure failing?

Thanks and regards,

dnoeth 4628 posts Joined 11/04
17 Feb 2016

You can only write, but not read an OUT parameter.
You need to declare a variable holding the intermediate result:

REPLACE PROCEDURE F_QUITA_REPES (IN cCadena VARCHAR(252), IN delchar CHAR, OUT cSubCad VARCHAR(252)) SQL SECURITY CREATOR
  
BEGIN
   
  DECLARE varSubCad VARCHAR(252);
  SET varSubCad = cCadena;
    
  IF (delchar = CHR(39)) THEN
    SET varSubCad = LTRIM(varSubCad, delchar); --> REPLACE PROCEDURE Failed. [5526] SPL1028:E(L23), Illegal attempt to modify symbol 'cSubCad'
  ELSE
    SET varSubCad = LTRIM(RTRIM(varSubCad, delchar), delchar);
        --> [5526] SPL1028:E(L25), Illegal attempt to modify symbol 'cSubCad'.
  END IF;
    
  IF (varSubCad IS NOT NULL) THEN
    WHILE varSubCad LIKE '%'||delchar||delchar||'%' DO
    BEGIN
      SET varSubCad = OREPLACE(varSubCad, delchar||delchar, delchar);
    END;
    END WHILE;
  END IF;
  SET cSubCad = varSubCad;
END;

 
Please read the "Stored Procedures and Embedded SQL" manual, there are some differences between Oracle and Teradata/Standard SQL SPs. Just watch out for "Embedded SQL only" tags to skip.

Dieter

juanalfonso 41 posts Joined 01/16
17 Feb 2016

Ok, I'll have a look at the manual!
And well, respecting to cSubCad, also, I can just convert it to an INOUT parameter... Both alternatives can be fine for me :)
Thanks!

You must sign in to leave a comment.