SPL1078:E(L3),User is not authorized to use the DDL/DCL/Dynamic SQL st

[GeneCampbell]

I am trying to create the below SP:

create procedure core_proddb_s.table_nme3( in p1 char(50), out p2 char(50) )
begin
call DBC.SysExecSql('Select campaign_id from ' || 1);

End;

This is the message that is being returned when I attempt to create the proc:

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

 

[tdgulbra]

Do you have the "Create Procedure/Drop Procedure" access rights for database "core_proddb_s"? It sounds like a privilege/access rights issue to me.

Tony

 

[GeneCampbell]

Yes, I verified access rights with the DBA's.

 

[tdgulbra]

From the Teradata RDBMS Messages manual:

SPL1078: "%s:E(L%d), User is not authorized to
use the DDL/DCL/Dynamic SQL statement."

Explanation: The reason is that the creator and the
immediate owner of the stored procedure is not the
same. Hence the specified DDL, DCL, or dynamic SQL
statement cannot be specified in the SPL source text.

Generated By: TSP module.

For Whom: End user.

Remedy: Either ensure that the creator and the imme-diate
owner of the stored procedure is the same, or do
not use the DDL, DCL, and/or dynamic SQL statements
in the SPL source text.

I am fairly new to SP, so I am unsure what this means exactly, but I would think you need to create the proc in a database that you are the direct owner of.

Hope this helps,
Tony

 

[tdatgod]

Hi,
Select, by itself, is not allowed in Stored procedures regardless of whether it was a Dynamic SQL or not.

typically a Stored procedure selects a bunch of rows and then operates on them internally one at a time using cursors or you Insert them into another table using Insert/Select. and then you query that table after the stored procedure returns back.

A stored procedure can only return a Single Value for an answer.

if you expect it to return a full set of rows you typically insert select that set into a Temporary table. From Bteq or queryman then you would....


first run the Stored procedure to populate the table
call sp1();

then execute a select after it completes
sel * from sp1_answer;

 

[Guest_imported]

Try adding Perm space to your login. We solved the same problem by adding 1 gig Perm space to account executing the CREATE PROCEDURE statement. Don't know why this worked.

 

[tdatgod]

Hi,
I should point out user DBC is not allowed to create stored procedures in database DBC. Also user DBC may be restricted from using Dynamic SQL.


On Teradata the only difference between a USER and a Database is Create User requires a Password. Typically DBA will Create User and not give them any perm space to create tables. Then they give them access to a Database

Create Database Userstuff as Perm = 1e9;
grant all on UserStuff to Public;

Create user Joe as perm =0 , password=secret, default database = UserStuff;


Now when Joe logons on his tables and stored procedures are created in Database UserStuff not Database Joe.

For Dynamic SQL to work you have to create the stored procedure in your Logon space so in this case Database Joe.


Now giving space to Joe database ( like ywas suggested in the last post ) probably allowed you enough space to create the stored procedure in your space rather than using someone else space.

Therefore when you logon and try to create this stored procedure, please make sure you are in the correct database.

if you are unsure execute

select database;

and see what it says.....