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.

UDF

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;
$$;

Note:

• Note that this is using a temporary table, if you want the data to persist after a session ends, please remove TEMPORARY from the CREATE TABLE.

• The temporary tables store non-permanent transitory data. They only exist within the session in which they were created and persist only for the rest of the session. After the session ends, the data stored in the table is completely removed from the system and is therefore not recoverable, either by the user who created the table or by Snowflake.

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

Query

IN -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE PROC 
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Test');
END;

CALL PROC();

Result

|DBMS_OUTPUT.PUT_LINE('test') |
|-----------------------------|
|test                         |

Snowflake

Query

OUT -> Oracle_01.sql

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();

Result

|ROW |WHEN                    |DATABASE    |LOG      |
|----|------------------------|------------|---------|
| 1  |2022-04-25 11:16:23.844 |CODETEST    |test     |

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

1. SSC-FDM-OR0035: Check UDF implementation for DBMS_OUTPUT.PUT_LINE_UDF.