Work around to simulate the use of Records
This page is deprecated but was left for compatibility purposes. If you want to see the updated section, please refer to Collections And Records
Description
This section describes how to simulate the behavior of Oracle records in SELECT and INSERT Statements, using RESULTSET and CURSORS of Snowflake Scripting.
Snowflake Scripting RESULTSET and CURSOR
<resultset_name> RESULTSET [ DEFAULT ( <query> ) ] ;
LET <resultset_name> RESULTSET [ { DEFAULT | := } ( <query> ) ] ;
LET <resultset_name> RESULTSET [ { DEFAULT | := } ( <query> ) ] ;
Recommendations
CREATE TABLE numbers_table(num integer, word varchar2(20));
INSERT INTO numbers_table VALUES (1, 'one');
CREATE TABLE aux_numbers_table(aux_num integer, aux_word varchar2(20));
Using RESULTSET and Cursors instead of Records
Oracle
CREATE OR REPLACE PROCEDURE proc_insert_select_resultset
AS
TYPE number_record_definition IS RECORD(
rec_num numbers_table.num%type,
rec_word numbers_table.word%type
);
number_record number_record_definition;
BEGIN
SELECT * INTO number_record FROM numbers_table;
INSERT INTO aux_numbers_table VALUES number_record;
END;
CALL proc_insert_select_resultset();
SELECT * FROM aux_numbers_table;
Snowflake
CREATE OR REPLACE PROCEDURE proc_insert_select_resultset ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
TYPE number_record_definition IS RECORD(
rec_num numbers_table.num%type,
rec_word numbers_table.word%type
);
number_record OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - number_record_definition DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
BEGIN
SELECT
OBJECT_CONSTRUCT( *) INTO
:number_record
FROM
numbers_table;
INSERT INTO aux_numbers_table
SELECT
:number_record:REC_NUM,
:number_record:REC_WORD;
END;
$$;
CALL proc_insert_select_resultset();
SELECT * FROM
aux_numbers_table;
Known Issues
1. Limitation in the use of RESULTSET
RESULTSET is very limited in its use. If table(result_scan(last_query_id()))
statement, should be used just after the RESULTSET's query is executed. For further information check this link.
Related EWIs
SSC-EWI-0036: Data type converted to another data type.
SSC-EWI-0056: Create Type Not Supported.
Last updated