Skip to Content
avatar image
Former Member

Error at calling string function from SQL Script

On SPS12 I want to use a string function to assign a value to a variable in a SQL Script Procedure. Here is an example of the code that reproduces the error I'm getting:

--Debug/Log table
create table P_TEST (textvarchar(100));


--Debug/Log procedure
create procedure debug_proc (IN p_text varchar(100))
AS
BEGIN 
BEGIN AUTONOMOUS TRANSACTION
INSERT INTO P_TEST( text) VALUES (:p_text);
COMMIT;
END;
END;


--Actual procedure 
CREATE PROCEDURE test_case
AS
BEGIN
declare l_testVARCHAR(100);
DECLARE l_result_2  VARCHAR(512);
debug_proc('** Begin debug ***');

  l_test := '123456.56 123456.56';

/* Section 1: this does not work */
  l_result_2 := OCCURRENCES_REGEXPR('([[:digit:]]){6}(\.){0,1}([[:digit:]]{0,2})' IN :l_test );
  select l_result_2 from dummy;

  /* section 2: This works!!
  select OCCURRENCES_REGEXPR('([[:digit:]]){6}(\.){0,1}([[:digit:]]{0,2})' IN l_test) 
into l_result_2
from dummy;

select l_result_2 from dummy;
  */

END;

Running the test_case procedure returns the error:

"SAP DBTech JDBC: [403]: internal error: Compile failed: CompilationFailedException: No details"

I found that calling the debug_proc somewhere in the body of the test_case procedure is the cause that section 1 failes. Remove this call and it works. But I need these kind of calls to other procedures. The fact that de debug_proc is doing an autonomous transaction is not the cause for the error.

When I comment section 1 and un-comment section 2, it works! Also with the call to the debug_proc, so I have a work-around (just don't like these "select from dummy" constructions...)

Question is: why do I get the error at my initial approach?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 20, 2016 at 11:16 PM

    This question has been answered: Cannot create scalar function with SUBSTR_REGEXPR

    SAP HANA Product Management declared that this will be enhanced in an upcoming release.

    Add comment
    10|10000 characters needed characters exceeded