How to make an anonymous piece of code to execute it once?

Database
Enthusiast

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

Juan
7 REPLIES
Junior Contributor

Re: How to make an anonymous piece of code to execute it once?

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

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

This returns a result row with "Resul"

Enthusiast

Re: How to make an anonymous piece of code to execute it once?

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?

Juan
Junior Contributor

Re: How to make an anonymous piece of code to execute it once?

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.

Junior Supporter

Re: How to make an anonymous piece of code to execute it once?

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

Enthusiast

Re: How to make an anonymous piece of code to execute it once?

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,

Juan
Junior Contributor

Re: How to make an anonymous piece of code to execute it once?

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.

Enthusiast

Re: How to make an anonymous piece of code to execute it once?

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!

Juan