Creating a Table in a Stored Procedure

Database
Enthusiast

Creating a Table in a Stored Procedure

I'm trying to do something as simple as this:

CREATE PROCEDURE P ()
BEGIN
CREATE MULTISET TABLE Test (N SMALLINT);
END;

It says:

SPL1078:E(L2), User is not authorized to use the DDL/DCL/Dynamic SQL statement.

I've granted all the rights to the user, and it doesn't work.

If I try it with the sysdba user, it's ok, the procedure gets created in the sysdba database, and it works, but I don't want to do that. I need to do it in a particular database. I think the user has the same rights as sysdba... what am I doing wrong?

Thank you very much!
2 REPLIES
Teradata Employee

Re: Creating a Table in a Stored Procedure

To use these types of statements (DDL, DCL, dynamic SQL), the immediate owner of the SP must be the creator.

So as you noted, if you log on as SYSDBA and CREATE PROCEDURE SYSDBA.P it works as expected.

But if you log on as X and try to CREATE PROCEDURE Y.P containing one of these statement types, that won't work.
Enthusiast

Re: Creating a Table in a Stored Procedure

Thank you Fred. I see what you say, and this is what is happening. But isn't there any workaround to solve that?