DBMS_LOCK Package

Execution

Execute DBMS_LOCK.sql as follows to use DBMS_LOCK package.

% gsql sys gliese --as sysdba --import $GOLDILOCKS_HOME/admin/packages/DBMS_LOCK.sql

Package Routines

SLEEP Procedure

It is a procedure which pauses the session for the specified time.

Procedure Definition

PROCEDURE SLEEP( seconds IN NATIVE_INTEGER )

Parameters

Example

gSQL> DECLARE
  V1 VARCHAR(20);
BEGIN
  SELECT TO_CHAR( SYSTIME, 'HH24:MI:SS' ) INTO V1 FROM DUAL;
  DBMS_OUTPUT.PUT_LINE( 'CURRENT TIME = ' || V1 );
  DBMS_LOCK.SLEEP( 3 );
  DBMS_OUTPUT.PUT_LINE( 'DBMS_LOCK.SLEEP( 3 ) ');
  SELECT TO_CHAR( SYSTIME, 'HH24:MI:SS' ) INTO V1 FROM DUAL;
  DBMS_OUTPUT.PUT_LINE( 'CURRENT TIME = ' || V1 );
END;
/
CURRENT TIME = 11:53:09
DBMS_LOCK.SLEEP( 3 ) 
CURRENT TIME = 11:53:12
Anonymous PL block executed.

DBMS_OUTPUT Package

Execution

Execute DBMS_OUTPUT.sql as follows to use DBMS_OUTPUT Package.

% gsql sys gliese --as sysdba --import $GOLDILOCKS_HOME/admin/packages/DBMS_OUTPUT.sql

Package Routines

DISABLE Procedure

It disables the message logging feature.
All previously logged messages are disposed.

Procedure Definition

PROCEDURE DISABLE

Example

gSQL> BEGIN
  DBMS_OUTPUT.DISABLE;
  DBMS_OUTPUT.PUT_LINE( 'DBMS_OUTPUT() Message' );
END;
/
Anonymous PL block executed.

ENABLE Procedure

It is a procedure which enables the message logging feature with a given buffer size.
If it is already enabled, all messages are disposed and a new buffer is created.

Procedure Definition

PROCEDURE ENABLE( buffer_size IN NATIVE_INTEGER := 20000 )

Parameters

Example

gSQL> BEGIN
  DBMS_OUTPUT.ENABLE;  
  DBMS_OUTPUT.PUT_LINE( 'DBMS_OUTPUT() Message' );
END;
/
DBMS_OUTPUT() Message
Anonymous PL block executed.

GET_LINE Procedure

It returns a oldest message line which has not been read among the messages stored in the buffer.

Procedure Definition

PROCEDURE GET_LINE( line OUT VARCHAR(4000),
                    status OUT NATIVE_INTEGER )

Parameters

Example

gSQL> var msg VARCHAR(100);
gSQL> var status INTEGER;
gSQL> BEGIN
  DBMS_OUTPUT.PUT_LINE( 'DBMS_OUTPUT() Message' );
  DBMS_OUTPUT.GET_LINE( :msg, :status );
END;
/
Anonymous PL block executed.

gSQL> \print msg

MSG                   
----------------------
DBMS_OUTPUT() Message

gSQL> \print status

STATUS
------
     0

NEW_LINE Procedure

It stores a newline character in the buffer.

Procedure Definition

PROCEDURE NEW_LINE

Example

gSQL> BEGIN
  DBMS_OUTPUT.PUT( 'DBMS_OUTPUT()' );
  DBMS_OUTPUT.PUT( ' Message' );
  DBMS_OUTPUT.NEW_LINE;
END;
/
DBMS_OUTPUT() Message
Anonymous PL block executed.

PUT Procedure

It stores the message which is created with the given expression in the buffer.

Procedure Definition

PROCEDURE PUT( item IN VARCHAR(4000) )

Parameters

Example

gSQL> var msg VARCHAR(100);
gSQL> var status INTEGER;
gSQL> BEGIN
  DBMS_OUTPUT.PUT( 'DBMS_OUTPUT()' );
  DBMS_OUTPUT.PUT( ' Message' );
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.GET_LINE( :msg, :status );
END;
/
Anonymous PL block executed.

gSQL> \print msg

MSG                  
---------------------
DBMS_OUTPUT() Message

gSQL> \print status

STATUS
------
     0

PUT_LINE Procedure

It adds the last newline character to the message created with the given expression, and stores it in the buffer.

Procedure Definition

PROCEDURE PUT_LINE( item IN VARCHAR(4000) )

Parameters

Example

gSQL> BEGIN
  DBMS_OUTPUT.PUT_LINE( 'DBMS_OUTPUT()' );
  DBMS_OUTPUT.PUT_LINE( ' Message' );
END;
/
DBMS_OUTPUT()
 Message
Anonymous PL block executed.

SET_LOG Procedure

It is simultaneously output on the file in the given path when logging the message.

Procedure Definition

PROCEDURE SET_LOG( file_path IN VARCHAR(4000), 
                   permission IN NATIVE_INTEGER := 600 )

Parameters

Example

gSQL> BEGIN
   DBMS_OUTPUT.SET_LOG('output.log', 600);
   DBMS_OUTPUT.PUT_LINE( 'DBMS_OUTPUT() Message' );
END;
/
DBMS_OUTPUT() Message
Anonymous PL block executed.

gSQL> !cat $GOLDILOCKS_DATA/trc/output.log
DBMS_OUTPUT() Message

DBMS_SQL Package

Execution

Execute DBMS_SQL.sql as follows to use DBMS_SQL package.

% gsql sys gliese --as sysdba --import $GOLDILOCKS_HOME/admin/packages/DBMS_SQL.sql

Package Routines

RETURN_RESULT Procedure

It returns the query result which was executed through the ref cursor to the client application.

Procedure Definition

PROCEDURE RETURN_RESULT( rc IN SYS_REFCURSOR )

Parameters

Example

gSQL> DECLARE
  rc1 SYS_REFCURSOR;
BEGIN
  OPEN rc1 FOR SELECT SESSION_ID(), SESSION_SERIAL(), SESSION_USER() FROM DUAL;
  DBMS_SQL.RETURN_RESULT( rc1 );
END;
/ 
Anonymous PL block executed.

ResultSet #1

SESSION_ID() SESSION_SERIAL() SESSION_USER()
------------ ---------------- --------------
          31               13 TEST

DBMS_STANDARD Package

Execution

Execute DBMS_STANDARD.sql as follows to use DBMS_STANDARD package.

% gsql sys gliese --as sysdba --import $GOLDILOCKS_HOME/admin/packages/DBMS_STANDARD.sql

Package Routines

RAISE_APPLICATION_ERROR Procedure

It raises an arbitrary user exception.

Procedure Definition

PROCEDURE RAISE_APPLICATION_ERROR( error_code    IN NATIVE_INTEGER,
                                   error_message IN VARCHAR(4000),
                                   stack_flag    IN BOOLEAN := FALSE )

Parameters

Example

gSQL> DECLARE
  V1 INTEGER;
BEGIN
  V1 := 100/0;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR (-20000, 'custom error message', TRUE);
END;
/
ERR-2F000(20000): custom error message
ERR-22012(12122): divisor is equal to zero : 
  V1 := 100/0;
        *
ERROR at line 4: