All Forums Database
barni07 8 posts Joined 02/06
27 Sep 2006
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!

Fred 1096 posts Joined 08/04
28 Sep 2006

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.

barni07 8 posts Joined 02/06
29 Sep 2006

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

You must sign in to leave a comment.