procedure call in data services

[Former Member]

Hi All,

I have written a sql procedure to truncate a table as below.

create or replace

procedure TRUNCATE_TABLE (TABLE_NAME IN VARCHAR2)

AS

BEGIN

EXECUTE IMMEDIATE ('truncate table ' || TABLE_NAME);

END;

I have imported this procedure in data services. I am trying to execute it in a script as follows.

begin

sql('DS_SEL_PEM_SDB','BODS_ODS_SEL.TRUNCATE_TABLE{''LES_PS_PRODUCT_DATA''}');

end

But I am getting below error

Identifier <LES_PS_PRODUCT_DATA> is unrecognized. Check its spelling and usage. If it is a variable, it needs a preceding

'$'; if a constant, it must be quoted; if a column, verify that such a column exists in a table.

The string is already quoted. Still this error. Anyone has idea how to call procedure with parameter in script?

[Former Member · Accepted solution]

Hello

If you have imported the metadata for the procedure, you don't need to use the SQL function to invoke it - try dragging it into a script from the Datastore.

Michael

[Former Member · Accepted solution]

perfecto.. it worked!!!

begin

    $LV_TruncateTableName = 'ABC';

DS_NAME.SCHEMA_NAME.TRUNCATE_TABLE($LV_TruncateTableName);

end

[severin_thelen]

Hello Swati,

please try to use single quotes. You could use single quotes like sql('DS_SEL_PEM_SDB','BODS_ODS_SEL.TRUNCATE_TABLE{\'LES_PS_PRODUCT_DATA\'}');.

Maybe you have to escape the parmeter like (sorry I can not find the job where I have do this)

sql('DS_SEL_PEM_SDB','BODS_ODS_SEL.TRUNCATE_TABLE{\'' || LES_PS_PRODUCT_DATA || '\'}');.

or with $

sql('DS_SEL_PEM_SDB','BODS_ODS_SEL.TRUNCATE_TABLE{\'' || $LES_PS_PRODUCT_DATA || '\'}');.

Regards

Severin

[Former Member]

If I use a variable, then also do I have to use escape characters.

begin

$LV_TruncateTableName = 'LES_PS_PRODUCT_DATA';

sql('DS_SEL_PEM_SDB','BODS_ODS_SEL.TRUNCATE_TABLE{$LV_TruncateTableName}');

end

in this case also I am getting the same error

[Former Member]

somehow just nothing works!!! neither with table name nor with local variable

begin

$LV_TruncateTableName = 'LVS_PS_PRODUCT_SERVICE';

sql('DS_LVS_GHA_BQ_SDB','BODS_ODS_SFX.TRUNCATE_TABLE{\'' || $LV_TruncateTableName || '\'}');

end

[severin_thelen]

The variable have not to be in the string. You have to escape the variable (see previous post), if you want to access the variable string. Otherwise the SQL Statement will get the variable name.

sql('DS_SEL_PEM_SDB','BODS_ODS_SEL.TRUNCATE_TABLE{\'' || $LV_TruncateTableName || '\'}');.

[severin_thelen]

At first you need semicolon in the SQL statement, too.

Please try a normal truncate statement until it works.

sql('database', 'truncate table table_name;');

after this, replace the table name with the variable.

[former_member187605]

What's the syntax of a procedure call in your database?

If it is

     BODS_ODS_SFX.TRUNCATE_TABLE('LVS_PS_PRODUCT_SERVICE')

then your sql-function call should be like:

     sql('DS_LVS_GHA_BQ_SDB','BODS_ODS_SFX.TRUNCATE_TABLE(\'LVS_PS_PRODUCT_SERVICE\')');

The only difference is that you've got to escape your single quotes with a backslash.

If you want to use a global variable, no quotes, no escape characters, and curly brackets:

     sql('DS_LVS_GHA_BQ_SDB','BODS_ODS_SFX.TRUNCATE_TABLE({$LV_TruncateTableName})');

[Former Member]

Hi Dirk,

In both the above cases, I am getting below error.

<ORA-00900: invalid SQL statement>.

I think the parameter is not getting passed because procedure is working fine at the backend.