HELP PROCEDURE ATTRIBUTES

Database
Enthusiast

HELP PROCEDURE ATTRIBUTES

I had a very strange error yesterday, which I fixed but about which I am seeking more information.

 

I had a stored procedure calling function octet_length(), which accepts a string value, but also an optional parameter character_set_name

The manual says: The character set in which the result is to be returned. If character_set_name is not provided,
the session character set is assumed.

However, I was calling this function inside a stored procedure, and what I discovered was that the (default) character_set used by octet_length, was not set by my session, but rather it was determined by the stored procedure

 

As per below, it seems that a character set is somehow stored as an attribute of the procedure:

HELP PROCEDURE D1P_PP.GCFR_PP_TfmTxn ATTRIBUTES;
/*
Transaction Semantics | Print Mode | Platform | Character Set | Default Character DataType | Collation 
TERADATA              | N          | LINUX    | UTF16         | LATIN                      | ASCII     
*/

And, from what I can see, the "Character set" assigned to the proc, is the one which was in force for the session when the PROC was compiled.

So, if you compile a proc with a session charset = ASCII, then the PROC will potentially behave differently from the same SQL source compiled with  a session where the charset = UTF16 (certainly it will, if your procedure calls octet_length)

 

So, two questions to this forum please;
1. Is it possible to set the char set insude the PROC code itself (I mean similar to the way you can set SQL SECURITY OWNER) so it's the same for all compilations.
2. Is there anywhere in the documentation where it explains about how the compile-time session properties are forever linked bound to the stored procedure?

 

 

 

2 REPLIES 2
Teradata Employee

Re: HELP PROCEDURE ATTRIBUTES

So, two questions to this forum please;
1. Is it possible to set the char set insude the PROC code itself (I mean similar to the way you can set SQL SECURITY OWNER) so it's the same for all compilations.

[Response] Its not possible.
2. Is there anywhere in the documentation where it explains about how the compile-time session properties are forever linked bound to the stored procedure?

[Response] The compile time session properties are used during runtime of stored procedures. You can reference SQL Stored Procedures and Embedded SQL manual or DDL/DML documents

Highlighted
Teradata Employee

Re: HELP PROCEDURE ATTRIBUTES

2. You can find the documentation here: https://docs.teradata.com