The reference has to be schema-local in "PROCEDURE"

[former_member623123]

Hi,

I am new to XSA and trying to create a procedure in XSA and getting an error message "The reference has to be schema-local in "PROCEDURE".

My procedure looks like this.

PROCEDURE "DataIntegration.db.exchangerates::prc_acq_exchange_rates"( )

LANGUAGE SQLSCRIPT

-- SQL SECURITY INVOKER --

--DEFAULT SCHEMA <default_schema_name>

- -READS SQL DATA

AS

BEGIN

INSERT INTO "DataIntegration.db.synonyms::iml_exchange_rates"

SELECT

"BATCH_ID",

"SEQUENCE_NO",

"TIMESTAMP",

"FROM_CURRENCY",

"TO_CURRENCY",

"EXCHANGE_RATE",

"STATUS"

FROM "DataIntegration.db.synonyms::vt_exchange_rates";

END

It is something to do with the table reference "DataIntegration.db.synonyms::iml_exchange_rates". It will be great if somebody can help me to identify what I am doing wrong.

Thanks

Shantha

[ghoshsayantan3]

Hi Shantha,

Are the tables in the same container? If not create one synonym for it and try using that here.

Regards

Sayantan

[former_member623123]

Hi Sayantan,

Thanks for getting back to me. Yes they are in the sam HDI container.

Thanks & Regards

Shantha

[ghoshsayantan3]

Hi Shantha,

Can you try adding a synonym on top of that table and see if your able to access it.Also keep the sql security of procedure as definer as the container user #oo would need to access it.

Regards

Sayantan