Executed as Single statement. Failed [3523 : 42000]

Database

Executed as Single statement. Failed [3523 : 42000]

Hi All,

            I got the Following error.

Executed as Single statement.  Failed [3523 : 42000] SAMPLE_SP:An owner referenced by user does not have SELECT access to SCHEMA_NAME.TABLE_NAME.

Elapsed time = 00:00:00.413

STATEMENT 1: CALL  failed.

QUERY

REPLACE PROCEDURE "SCHEMA_NAME"."sample_sp" (

        IN "last_name_in" VARCHAR(50) CHARACTER SET LATIN,

        OUT "CUST_COUNT" INTEGER)

BEGIN

    SELECT count(*)

    INTO  CUST_COUNT   FROM SCHEMA_NAME.TABLE_NAME

    WHERE LAST_NM LIKE last_name_in ;

END;

Any help is appreciated.

3 REPLIES
Senior Apprentice

Re: Executed as Single statement. Failed [3523 : 42000]

As the error message implies:

"SCHEMA_NAME" needs SELECT WITH GRANT OPTION on the target table.

Dieter

Re: Executed as Single statement. Failed [3523 : 42000]

Thanks for the Quick reply... I am a newbie to TD and I should have explained it better ....  I had created the SP.

Modifying the Query with addition of the bold italic code solved the problem.

REPLACE PROCEDURE "SCHEMA_NAME"."sample_sp" (

        IN "last_name_in" VARCHAR(50) CHARACTER SET LATIN,

        OUT "CUST_COUNT" INTEGER)

SQL SECURITY INVOKER

BEGIN

    SELECT count(*)

    INTO  CUST_COUNT   FROM SCHEMA_NAME.TABLE_NAME

    WHERE LAST_NM LIKE last_name_in ;

END;

Enthusiast

Re: Executed as Single statement. Failed [3523 : 42000]

This post really helped us, adding additional info in case it assists others...  

 

TRYING TO RUN:
Replace PROCEDURE LAB.zdelperm1()
BEGIN
CREATE MULTISET TABLE LAB.zdeltest1, NO JOURNAL, NO FALLBACK as
(
select top 100 trim(LEADING '0' from FIELD1) as FIELD1
from EXAMPLEDB.TABLENAME
) WITH DATA PRIMARY INDEX(FIELD1);
END;

error received:
SPL5000:W(L13), E(5315):An owner referenced by user does not have SELECT access to EXAMPLEDB.TABLENAME.FIELD1.
CREATE PROCEDURE Command Failed.

 

This however, does work, so the immediate user can run the sel:
select top 100 trim(LEADING '0' from FIELD1) as FIELD1
from EXAMPLEDB.TABLENAME

 

Here is how to get around the proc create issue:

Replace PROCEDURE LAB.zdelperm1()
-- the fix *************
SQL SECURITY INVOKER
-- end fix *************
BEGIN
CREATE MULTISET TABLE LAB.zdeltest1, NO JOURNAL, NO FALLBACK as
(
select top 100 trim(LEADING '0' from FIELD1) as FIELD1
from EXAMPLEDB.TABLENAME
) WITH DATA PRIMARY INDEX(FIELD1);
END;

 

DOCUMENTATION:
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1144_111A/Crea...