DSXchange

Posted: **Wed Nov 28, 2007 10:37 am**

I'm trying to use the stored procedure stage to kick off....a stored procedure. It's aborting returning the following error message:

Code: Select all

LOAD_ACC_HIST_CHMX_STP..CREATE_EXTERNAL_TABLE: ORA-01036: illegal variable name/number

I can kick off the exact statement in SQL*Plus and it runs without errors. The statement is:

Code: Select all

BEGIN CREATE_DDL_XT_ACC_HIST_CHMX('#MMYY#'); END;

The MMYY being the variable. I'm not sure what's going on here. Does anyone have any ideas?

Please let me know if I'm providing enough information.

Posted: **Wed Nov 28, 2007 12:57 pm**

This is how I am calling a SP successfully:

Send the parameter down the link to the Stored Procedure stage. My generated code is:

Code: Select all

BEGIN #$DW_SCHEMA#DMR_PKG_LD_PREMIER.PRC_CALC_DAILY_DISC(:1); END;

The value you send down the input link becomes the :1 in the stored procedure call. I actually read this value from a text file. The text file is written from a transformer once all the records have been committed.

Hope this helps,
John

Posted: **Wed Nov 28, 2007 1:15 pm**

What is the sql data type of your variable MMDD?

Posted: **Wed Nov 28, 2007 3:00 pm**

What is the data type of your stored procedure argument? If it's not a string, then the quote characters may be causing the error. You also need to investigate the CREATE EXTERNAL TABLE statement within the stored procedure, since it is this that is throwing the error.

DSXchange

Illegal variable in a Stored Procedure Stage???

Illegal variable in a Stored Procedure Stage???