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)
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;';
SET SQL_QRY2 = 'INSERT INTO '||TGT_DB||'.SRC_VNDR_FNDTN_STG SELECT * FROM '||SRC_DB||'.SRC_VNDR_FNDTN;';
Procedure call goes as follows:
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.
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.
Another point to consider is the SESSION MODE between Informatica and SQL Assistant. Make sure the session is TERADATA and not ANSI.