Questions tagged [user-defined-type]

User-Defined Types (UDTs) are custom datatypes in Microsoft SQL Server 2005 or newer. UDTs are one of the five SQLCLR object types and rely on the .NET Framework. They are most commonly written in C# or VB.NET.

Filter by
Sorted by
Tagged with
0 votes
1 answer
147 views

DB<>Fiddle: Custom type and function (ORA-24344)

I've made a custom version of Oracle's GetVertices() function. The solution involves creating a user-defined type and a function. Here is the code (working): CREATE TYPE vertex_type_cust AS object ( ...
user avatar
  • 1,389
-1 votes
3 answers
492 views

How can I cast to a user-defined type in SQL Server?

SQL Server provides an ability to create a user defined type with CREATE TYPE CREATE TYPE foo FROM int NOT NULL ; However, there is no mention how I construct a value with that type. Is this of use ...
user avatar
  • 56.2k
0 votes
1 answer
54 views

I am wanting to use UDTT in my database, but we have CLR turned off, and according to the docs for create type, all types require a CLR assembly

Is this true? Or can I use User defined Table Types with having CLR off? (I.e. they don't require the use of CLR) If they do require CLR, is there an alternative I can use for my stored procedure ...
user avatar
3 votes
1 answer
176 views

SQL Server sp_describe_first_result_set: find user defined types

I need to find the column definitions from a stored procedure.The docs for sp_describe_first_result_set show an entry for user_type_name where I'd expect to see my User Defined Types. Sadly, data for ...
user avatar
  • 235
1 vote
2 answers
223 views

Oracle MV requires object type to be defined as FINAL?

I want to create an Oracle 18c materialized view (MV) on a table that has a user-defined datatype called ST_GEOMETRY: The MV would do a COMPLETE refresh on a schedule — on a local table. Some info ...
user avatar
  • 1,389
0 votes
4 answers
45 views

use a string array without CREATE privileges

I have a set of tables where I need to determine if a particular set of about 1000 row IDs are present in one specific field in each table. select 'Accounts', row_id Acct_id, pr_postn_id from schema....
user avatar
  • 109
0 votes
2 answers
83 views

Do user defined data types always cause implicit conversion to occur when fields of those UDDTs are used as predicates?

I have a database (from a vendor application) that for some unknown reason has a UDDT on almost every column, even when the UDDT is logically the same as the underlying type it implements. E.g. a ...
user avatar
  • 24.6k
-1 votes
1 answer
583 views

Unable to declare user-defined variable in MySQL

I'm creating procedure called min_max to find minimum and maximum for every column in a table. But I'm encountering an error for declaring the variable. See below: DELIMITER // DROP PROCEDURE IF ...
user avatar
2 votes
1 answer
325 views

How to insert nested UDT types

I have all these case classes :- case class Role(id:UUID, roleName:String, description:Option[String], isVisible:Boolean, isDefault:...
user avatar
  • 21
0 votes
1 answer
163 views

Are user defined types (UDTs) available in an Azure SQL database managed instance?

Is it possible to create user defined types and user defined table types in an Azure SQL database managed instance?
user avatar
  • 24.6k
2 votes
1 answer
278 views

Can't use CAST, Convert with UDT in Sql Server 2014 [closed]

My tables consist of many decimal columns with different precision. I wanted to standardize the usage of precision by creating several UDTs with predefined precision and use them as column type ...
user avatar
  • 123
3 votes
3 answers
808 views

How do I create a bounded range type in the SQL Server?

I want to create a bible verse table that has one field representing a range of bible verses. Currently I represent this range as pair of values (VerseBegId and VerseEndId). I want something like ...
user avatar
1 vote
0 answers
71 views

PostgreSQL- user-defined types: note sure what internallength/alignment is for linked list type

I'm currently trying to implement at user-defined data type in Postgres in the form of a linked list with each node holding an int and a pointer to the next node, here are my input functions: // ...
user avatar
6 votes
2 answers
823 views

Determine if user-defined type is ENUM

Is there a way to determine whether or not a user-defined type in PostgreSQL is an ENUM? Essentially we have the following: CREATE TYPE foo AS ENUM ( 'Sometimes', 'You', 'Wanna', 'Go', '...
user avatar
0 votes
1 answer
34 views

Noob question about user accounts

I'm new to this whole database thing. Got a uni project which I was hoping y'all could help with. So I'm creating a webapp that is essentially an online art gallery store. The database will contain ...
user avatar
1 vote
1 answer
256 views

Need an efficient routine to pass object name, with or without "[" and "]" brackets, to get ObjectID

Someone in their wisdom created a table called user: select schema_name(schema_id) ,* from sys.tables where type = 'U' and name like '%user%' select top 100 * from usr.user Msg 156, Level 15, ...
user avatar
3 votes
1 answer
287 views

UPDATE STATISTICS and User-Defined Types (UDT) from Assemblies

I have a problem with UPDATE STATISTICS where the column is a user-defined type (UDT) from an assembly. Step by step: Assembly: CREATE ASSEMBLY [UDT.TParams] FROM 0x4D...(stripped) WITH ...
user avatar
0 votes
1 answer
2k views

Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?

I have a function which returns: TABLE (id TEXT, collected TIMESTAMPTZ) In another function, I would like to be able to pass the result of that first function as a parameter. For example: CREATE ...
user avatar
3 votes
1 answer
3k views

Supertypes and subtypes

I am trying to design a database that has supertype/subtypes. I have the following users: - basic user - business user - administrator (super user). All three users share a common table (users) ...
user avatar
  • 203
2 votes
2 answers
233 views

SQLCLR User-Defined Type (UDT) SCH-M blocking

I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# SQLCLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without ...
user avatar
0 votes
0 answers
2k views

PostgreSQL and array of custom type

One of the table I have looks like this CREATE TABLE orders_suppliers_contacts ( supplier_id bigint NOT NULL, name character varying(64) NOT NULL, phone character varying(18), notes character ...
user avatar
0 votes
0 answers
1k views

Proper way to handle NULL (empty) values for user-defined table types passed as params to stored procedure

My stored procedures accepts table value parameters for querying data. Usually those are custom user-defined table types for where clause. Business logic of stored procedures push me to these @...
user avatar
  • 2,323
5 votes
1 answer
8k views

How to list the permissions on User-Defined Table Types?

I am using some user-defined table types. They are very useful. you can see information about them by running this simple script: SELECT o.* from sys.table_types o I have a procedure that shows me ...
user avatar
16 votes
1 answer
15k views

Multiple User Types - DB Design Advice

I'm developing a web application that will support user authentication with corresponding roles for each user. Also, my users can be of different types and have different fields associated with them. ...
user avatar
  • 263
6 votes
1 answer
22k views

Operand type clash: my first User Defined Type with list of Integers, how to test via SSMS

I found this wonderful code here CREATE TYPE [dbo].[StringList] AS TABLE( [Item] [NVARCHAR](MAX) NULL ); GO CREATE PROCEDURE [dbo].[sp_UseStringList] @list StringList ...
user avatar
0 votes
1 answer
240 views

User defined data types causing implicit conversions

user defined datatypes, i'm looking at some execution plans and seeing implicit conversions happening in the background when a var is declared of type user defined data type and pulling a value into ...
user avatar
  • 319
3 votes
2 answers
3k views

User defined table type with a check constraint

I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on ...
user avatar
  • 2,747
7 votes
1 answer
1k views

Understanding MS SQL Server Date Types

Consider the following: declare @dt datetime, @dt2 datetime2, @d date set @dt = '2013-01-01' set @dt2 = '2013-01-01' set @d = '2013-01-01' select convert(varbinary, @dt) as dt, convert(...
user avatar
3 votes
1 answer
599 views

PostgresSQL: Get single attribute of UDT in SELECT statement

I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work. Creation of type ...
user avatar
  • 489
27 votes
6 answers
43k views

Display user-defined types and their details

I've created a few new UDTs in PostgreSQL. However, now I have two problems: how to see which UDTs have been defined? how to see the columns defined within these UDTs? Unfortunately, I couldn't ...
user avatar
  • 489
1 vote
2 answers
378 views

Postgresql: type_modifier in UDT

In the manual for creating UDTs in postgresql I found the following statement: PostgreSQL allows user-defined types to take one or more simple constants or identifiers as modifiers. However, this ...
user avatar
  • 489
9 votes
2 answers
7k views

MySQL Permissions Necessary To Reset Password

On my team there is a desire for some users to periodically update their MySQL password. Our MySQL server is shared between a few projects (and certain users are assigned to certain projects). If I'm ...
user avatar
  • 192
3 votes
2 answers
232 views

How can I mimic functionality of SQL Server `TYPE` in Oracle?

I apologize if question title is vague, but I was not able to formulate it better. What I'd like to have is an alternative to TSQL CREATE TYPE, so I can use it in column definition. For instance, I ...
user avatar
  • 8,701
4 votes
3 answers
33k views

What's the difference between DBA and DA?

In categorization of users I have seen two different categorizations: First one: End user DBA DBP and Second one: End user DBA DA What's the difference between DA and DBA ?
user avatar
  • 143
5 votes
2 answers
7k views

Does mysql SUPER privilege include ALL?

I'm granting the following rights to a user: grant super on . to 'user'@'host'; does this include the rights on dbname given by: grant all on dbname.* to 'user'@'host'; thank you
user avatar
  • 713
14 votes
1 answer
13k views

Why does SQL*PLUS need a slash after CREATE TYPE?

I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error. Example: CREATE OR REPLACE TYPE MyType AS OBJECT ( Item1 NUMBER, ...
user avatar
  • 11.8k