Problem with calling Java table operator from stored procedure

Database

Problem with calling Java table operator from stored procedure

I'm struggling to pass data into the custom clause of a table operator, using a string literal works fine but I can't get the procedure to compile when passing a parameter.

REPLACE PROCEDURE EXAMPLE ()

BEGIN

 INSERT INTO OUTPUT SELECT * FROM TBLOP (ON (SELECT * FROM WORKING) partition by X USING latency('A=1000:B=2000')) AS J1;

END;

Works fine but

REPLACE PROCEDURE EXAMPLE ()

BEGIN

 DECLARE LC VARCHAR;

 SET LC = 'A=1000:B=2000';

 INSERT INTO OUTPUT SELECT * FROM TBLOP (ON (SELECT * FROM WORKING) partition by X USING latency(:LC)) AS J1;

END;

Fails with the following error...

Executed as Single statement.  Failed [5526 : HY000] Stored Procedure is not created/replaced due to error(s).{Nested Failure Msg [5526 : HY000] SPL1027:E(L5), Missing/Invalid SQL statement'E(9688):Table operator's execution error: Wrong Data Type is found in Table Operator's Custom Clause..'.} 


I've tried using an integer and TD_ANYTYPE as the variable type with no success. I've also run it with the debugger active and, as expected, the procedure that does compile calls the contract function but the procedure that fails to compile does not, so I don't think it's anything I'm doing wrong there.

The table operator declaration is.

REPLACE FUNCTION TBLOP()

RETURNS TABLE VARYING USING FUNCTION Tblop_contract

LANGUAGE JAVA NO SQL

PARAMETER STYLE SQLTABLE

EXTERNAL NAME 'TeradataJarFile:com.td.Tblop.execute';

Any ideas?

3 REPLIES
Teradata Employee

Re: Problem with calling Java table operator from stored procedure

At the present time, it's not supported to use a stored procedure variable as the argument for a table operator's custom clause.

Re: Problem with calling Java table operator from stored procedure

Thanks Tom,

Do you have any idea when it's likely to be supported? I'm using it to pass information that has to be calculated at run time into the table operator so any workaround is going to be quite clunky.

Teradata Employee

Re: Problem with calling Java table operator from stored procedure

I'm not aware of any plans to add support for using a stored procedure variable as the argument for a table operator's custom clause.

Have you considered using a Java stored procedure, instead of the SQL stored procedure? With a Java stored procedure, you could calculate the information, form the character literal, and then dynamically compose the query with the character literal.