CREATE FUNCTION

Oracle Create Function to Snowflake Snow Scripting

Description

Some parts in the output code are omitted for clarity reasons.

A stored function (also called a user function or user-defined function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.

A call specification declares a Java method or a third-generation language (3GL) routine so that it can be called from PL/SQL. You can also use the CALL SQL statement to call such a method or routine. The call specification tells Oracle Database which Java method, or which named function in which shared library, to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value. Oracle SQL Language Reference Create Function.

Oracle Syntax

For more information regarding Oracle Create Function, check here.

Oracle Create Function Syntax

CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ]
FUNCTION
[ schema. ] function_name
  [ ( parameter_declaration [, parameter_declaration]... ) ] RETURN datatype 
[ sharing_clause ]
  [ { invoker_rights_clause
    | accessible_by_clause
    | default_collation_clause    
    | deterministic_clause
    | parallel_enable_clause
    | result_cache_clause 
    | aggregate_clause
    | pipelined_clause
    | sql_macro_clause
       }...
  ]
{ IS | AS } { [ declare_section ] 
    BEGIN statement ...
    [ EXCEPTION exception_handler [ exception_handler ]... ]
    END [ name ] ;
      |
    { java_declaration | c_declaration } } ;

Snowflake Syntax

Snowflake allows 3 different languages in their user-defined functions:

• SQL

• JavaScript

• Java

For now, SnowConvert will support only SQL and JavaScript as target languages.

For more information regarding Snowflake Create Function, check here.

SQL

SQL user-defined functions only support one query as their body. They can read from the database but are not allowed to write to or modify it (Scalar SQL UDFs).

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

JavaScript

JavaScript user-defined functions allow multiple statements in their bodies but cannot perform queries to the database. (Scalar JavaScript UDFs).

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

Sample Source Patterns

Sample auxiliary data

This code was executed for a better understanding of the examples:

Oracle

IN -> Oracle_01.sql

CREATE TABLE table1 (col1 int, col2 int, col3 varchar2(250), col4 varchar2(250), col5 date);

INSERT INTO table1 VALUES (1, 11, 'val1_1', 'val1_2', TO_DATE('2004/05/03', 'yyyy-MM-dd'));
INSERT INTO table1 VALUES (2, 22, 'val2_1', 'val2_2', TO_DATE('2014/05/03', 'yyyy-MM-dd'));
INSERT INTO table1 VALUES (3, 33, 'val3_1', 'val3_2', TO_DATE('2024/05/03', 'yyyy-MM-dd'));

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE table1 (col1 int,
col2 int,
col3 VARCHAR(250),
col4 VARCHAR(250),
col5 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/25/2024" }}'
;

INSERT INTO table1
VALUES (1, 11, 'val1_1', 'val1_2', TO_DATE('2004/05/03', 'yyyy-MM-dd'));

INSERT INTO table1
VALUES (2, 22, 'val2_1', 'val2_2', TO_DATE('2014/05/03', 'yyyy-MM-dd'));

INSERT INTO table1
VALUES (3, 33, 'val3_1', 'val3_2', TO_DATE('2024/05/03', 'yyyy-MM-dd'));

Known Issues

No issues were found.

Related EWIS

1. SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior