Stored procedure runs in SQL assistant but given error when triggered through Informatica

Database
Enthusiast

Stored procedure runs in SQL assistant but given error when triggered through Informatica

Hi,

I have createda simple stored procedure to truncate and load a target table and I am passing schema name as parameter.

When I run the procedure in SQL assitant it executes correctly, but when it is invoked through informatica, it gives following error.

SRC_VNDR_FNDTN_STG_LOAD:Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword or '(' between the 'FROM' keyword and the string 'MDM_D1' keyword.Unable to get catalog string.]

The procedure code is as follows:

create PROCEDURE MDM_D1.SRC_VNDR_FNDTN_STG_LOAD 
(
IN DB_NAME VARCHAR(10)
)
BEGIN

DECLARE SRC_DB VARCHAR(10);
DECLARE TGT_DB VARCHAR(15);
DECLARE SQL_QRY1 VARCHAR(100);
DECLARE SQL_QRY2 VARCHAR(1000);

SET SRC_DB = DB_NAME;
SET TGT_DB = DB_NAME||'_WORK' ;

SET SQL_QRY1 = 'DELETE FROM '||TGT_DB||'.SRC_VNDR_FNDTN_STG;';

CALL DBC.SysExecSQL(SQL_QRY1);

SET SQL_QRY2 = 'INSERT INTO '||TGT_DB||'.SRC_VNDR_FNDTN_STG SELECT * FROM '||SRC_DB||'.SRC_VNDR_FNDTN;';

CALL DBC.SysExecSQL(SQL_QRY2);

END;

Procedure call goes as follows:

call MDM_D1.SRC_VNDR_FNDTN_STG_LOAD('MDM_D1')

The error message says " between the 'FROM' keyword and the string 'MDM_D1' keyword " but MDM_D1 is just the value passed.

I am not sure if there is any systanctical change thats needed.

Please advice and thanks in advance.

Regards.

Tags (1)
2 REPLIES
Enthusiast

Re: Stored procedure runs in SQL assistant but given error when triggered through Informatica

Hi Arpit,

This sounds like Informatica is adding additional single quotes around the value when it is calling the stored procedure - so effectively generating:

CALL MDM_D1.SRC_VNDR_FNDTN_STG_LOAD(''MDM_D1'') (that is 2 sets of single quotes).

Are you using an informatica parameter to store the value MDM_D1? If so, try specifying it in the Stored Procedure call text without any quotes, as Informatica automatically puts the quotes in.

Regards,

Steve

Enthusiast

Re: Stored procedure runs in SQL assistant but given error when triggered through Informatica

Another point to consider is the SESSION MODE between Informatica and SQL Assistant. Make sure the session is TERADATA and not ANSI.