Object_Id and Object_Name Functions for HANA SQLScript Developers
Object_Id and Object_Name SQL functions are missing on SAP HANA SQLScript for database developers. Similar functions exist on SQL Server and other database management systems. I'ld like to share SQL programmers two sample user-defined functions that can be used to convert object_id to object_name and object_name to object_id in this HANA database tutorial.
Object_Id SQL User Defined Function for HANA Database
For SQLScript developers coding on SAP HANA database, mapping for object_oid from object_name is more complex when compared with mapping to object_name from object_oid. The reason behind this complexity is on a SAP HANA database, there may be multiple objects that have been created in different schemas. For example database tables. Two different tables with the same name could be created in different schemas.
Following SQL function Object_ID checks the availibility of a database object validating the object name criteria first on the current schema. If there is a database object with given object name passed using the UDF function input parameter, the Object_OID of that object from SYS.OBJECTS system view is returned.
If there is no object matching the given parameter for object name in the current database schema, other schema objects are queried. If there is more than one which means multiple objects sharing the same name, then 0 is returned by the SQLScript Object_Id() function.
On the other hand, if there is no object using the given object name NULL value is returned by the user-defined SQL function.
CREATE FUNCTION OBJECT_ID(object_name NVARCHAR(256))
RETURNS object_id bigint
LANGUAGE SQLSCRIPT AS
BEGIN
/*
Returns Object_OID for object that is in current schema preferably
Returns 0 if there are multiple objects with the same name
Returns NULL if object does not exist
*/
declare inSchema int;
declare rowCount int;
select
sum(case when schema_name = current_schema then 1 else 0 end),
count(*)
into "INSCHEMA", "ROWCOUNT"
from sys.objects
where object_name = :object_name;
if rowCount = 1 then
select OBJECT_OID into "OBJECT_ID" from sys.objects where object_name = :object_name;
elseif rowCount > 1 then
if inSchema = 1 then
select OBJECT_OID into "OBJECT_ID" from sys.objects where object_name = :object_name and schema_name = current_schema;
else
-- select top 1 OBJECT_OID into "OBJECT_ID" from sys.objects where object_name = :object_name;
object_id := 0;
end if;
end if;
END;
In the above SQL user function code, ABAP programmers can realize easily multiple SELECT list items are mapped into multiple SQL variables.
Object_Name SQL User Defined Function for HANA Database
Since Object_OId value is unique, Object_Name() user function code is easier to read. Using the object_oid field, SQL function filters system view and if an object row is returned, the Object_Name is used as the output of the HANA database function.
CREATE FUNCTION OBJECT_NAME(object_id bigint)
RETURNS object_name nvarchar(256)
LANGUAGE SQLSCRIPT AS
BEGIN
declare rowCount int;
select count(*) into "ROWCOUNT" from sys.objects where object_oid = :object_id;
if rowCount > 0 then
select object_name into "OBJECT_NAME" from sys.objects where object_oid = :object_id;
end if;
END;
Object_Name and Object_Id SQLScript Functions Sample
Let's now see how SAP SQLScript programmers can use Object_Id() and Object_Name() user-defined functions on HANA database.
select
table_name,
Object_ID(table_name) as object_id,
table_oid,
Object_Name(table_oid) as object_name
from sys.tables
where
schema_name = current_schema and
is_column_table = 'TRUE'
The output of above SQL Select statement is as seen in below screenshot. object_id and object_name column values are produced by the SQLScript user functions created in this HANA database tutorial.
