PUT_LINE procedure
Translation reference for DBMS_OUTPUT.PUT_LINE.
Description
This procedure places a line in the buffer. (Oracle PL/SQL DBMSOUTPUT.PUT_LINE)
This UDF is implemented using a temporary table to insert the data to be displayed to replicate the functionality of Oracle DBMS_OUTPUT.PUT_LINE
function.
Syntax
DBMS_OUTPUT.PUT_LINE(LOG VARCHAR);
Custom procedure
Setup data
The DBMS_OUTPUT
schema must be created.
CREATE SCHEMA IF NOT EXISTS DBMS_OUTPUT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
DBMS_OUTPUT.PUT_LINE(VARCHAR)
Parameters
LOG: Item in a buffer that you want to display.
CREATE OR REPLACE procedure DBMS_OUTPUT.PUT_LINE_UDF(LOG VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS $$
//Performance may be affected by using this UDF.
//If you want to start logging information, please uncomment the implementation.
//Once the calls of DBMS_OUTPUT.PUT_LINE have been done, please use
//the following query to read all the logs:
//SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG.
//snowflake.execute({sqlText:`
//CREATE TEMPORARY TABLE IF NOT EXISTS DBMS_OUTPUT_LOG
//(
// WHEN TIMESTAMP,
// DATABASE VARCHAR,
// LOG VARCHAR
//);`});
//snowflake.execute({sqlText:`INSERT INTO DBMS_OUTPUT_LOG(WHEN, DATABASE, LOG) VALUES (CURRENT_TIMESTAMP,CURRENT_DATABASE(),?)`, binds:[LOG]});
return LOG;
$$;
If you do not use the temporary table, keep in mind that you may need another column in the table where the USER running DBMS_OUTPUT.PUT_LINE UDF is inserted to avoid confusion.
Usage example
Oracle
CREATE OR REPLACE PROCEDURE PROC
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Test');
END;
CALL PROC();
Snowflake
CREATE OR REPLACE PROCEDURE PROC ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
CALL DBMS_OUTPUT.PUT_LINE_UDF('Test');
END;
$$;
CALL PROC();
Known Issues
The UDF code will remain commented out because it can affect performance, if the user decides to use it, they just need to uncomment the code.
The user can modify the UDF so that the necessary information is inserted into the DBMS_OUTPUT.PUT_LINE table.
Related EWIs
SSC-FDM-OR0035: Check UDF implementation for DBMS_OUTPUT.PUT_LINE_UDF.
Last updated