COLLECT is used with the CAST function to create a nested table of the specified column name and cast it to the type specified in the CAST function. Example: SELECT CAST(COLLECT(some_column) as some_type) FROM table_name;
Set – Oracle SQL Function
SET converts the nested table passed as an argument toa SET by removing any duplicate records from the table. Example: SELECT ID,NAME,SET(some_nested_table) FROM TABLE_NAME;
Chartorowid – Oracle SQL Function
CHARTOROWID converts a string representation of a ROWID to a ROWID value. The string argument can be a CHAR,VARCHAR2,NCHAR, or NVARCHAR2 data type to ROWID data type. Example: SELECT ID from some_table WHERE ROWID = CHARTOROWID(‘AAAAtmAAEAAAAFmAAA’); ID —— 22
Compose – Oracle SQL Function
COMPOSE allows you to compose a UNICODE string by passing in one or more code points. COMPOSE will add them all together and return the result. Example: SELECT COMPOSE(‘Let”s go to the cafe301 to get some tea.’)) as RESULT from
Convert – Oracle SQL Function
CONVERT converts the string argument from one character set to another one. Syntax: CONVERT(string,destination_set, source_set) The source_set argument specifies the character set of the stored data in the database if ‘string’ is a column name, or the character set of
To_clob – Oracle SQL Function
TO_CLOB converts character data to a CLOB. The string argument can be a CHAR, VARCHAR2, NCHAR NVARCHAR2, CLOB, or NCLOB data type. Example: SELECT TO_CLOB(‘SOME DATA’) as RESULT from DUAL; RESULT ———— SOME DATA
To_date – Oracle SQL Function
TO_DATE converts a string to a DATE. The string argument can be a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. The format argument can be any valid date format. You can also specify the NLS_DATE_LANGUAGE parameter to change the language
To_number – Oracle SQL Function
TO_NUMBER converts argument 1 toa NUMBER value. Syntax: TO_NUMBER(arg1,format,params) The arg1 argument can be a BINARY_FLOAT, BINARY_DOUBLE,CHAR,VARCHAR2,NCHAR, or NVARCHAR2 data type. The format parameter can be any valid date format. Valid options for the ‘params’ argument: NLS_NUMERIC_CHARACTERS = To specify
Cardinality – Oracle SQL Function
CARDINALITY returns the number of records in a nested table. The function returns a NUMBER result. Example: SELECT ID,NAME,CARDINALITY(some_nested_table) FROM TABLE_NAME;
Lower – Oracle SQL Function
LOWER returns a string that is the same as the 1st argument with all the characters in lowercase. The argument to LOWER can be a CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB. Example: SELECT LOWER(‘Oracle Rocks’) as RESULT FROM DUAL;