"All truths are easy to understand once they are discovered; the point is to discover them."

Galileo Galilei

RMAXOneNote covers a wide range of programming languages and technologies (Oracle PL/SQL, C#, Java, etc.), code examples, and solutions related to IFS ERP. The purpose of this blog is to share knowledge among developers about the problems they encounter daily.

Oracle Insights

BLOB to CLOB in PLSQL

How to get file content in a BLOB file into a CLOB in PL/SQL. The below function can be used if the BLOB file contains text format data...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Feb 252 min read

CONNECT BY PRIOR / Hierarchical Queries / Query Tree Structure

Example 1: SELECT handling_unit_id, t.shipment_id FROM handling_unit_tab t START WITH handling_unit_id = '4558' CONNECT BY PRIOR...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Dec 10, 20241 min read

Find Database Objects in Oracle

SELECT DISTINCT object_type FROM all_objects; SELECT t.* FROM all_objects t WHERE t.object_type IN ('VIEW', 'TABLE', 'INDEX',...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Dec 10, 20241 min read

Encode Decode base64 in PLSQL

Decode base64 in PLSQL: DECLARE CURSOR get_base64_clob IS SELECT neutral_file FROM c_smart_document_tab WHERE sequence_no = '42'; ...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Nov 29, 20241 min read

Ways to save CLOB to a File in PLSQL

These PL/SQL code examples show how to save a CLOB to a File. Example 1: DECLARE clob_ CLOB := 'A Character Large OBject (or...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Nov 28, 20242 min read

Convert CLOB to BLOB in PLSQL

The PL/SQL code below shows how to convert a CLOB to a BLOB and save it in a database directory. DECLARE clob_ CLOB := 'A...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Nov 28, 20241 min read

How to Read BLOB in PLSQL

Below code examples shows how to read a file in Oracle Directory into a BLOB in PLSQL. Example 1: DECLARE dir_ VARCHAR2(100)...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Nov 26, 20241 min read

Array in PLSQL

Example 1: VARRAY TYPE 1 DECLARE TYPE namesarray IS VARRAY(5) OF VARCHAR2(10); names_ namesarray; TYPE grades IS VARRAY(5) OF INTEGER;...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Nov 25, 20242 min read

Sort Numbers in PLSQL

Issue: Solution: Sort Numbers Only ORDER BY REGEXP_REPLACE( <COLUMN_NAME> , '[[:digit:]]', '~') ASC ; Sort Numbers and Text ORDER BY...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Nov 25, 20241 min read

Create Database Link in Oracle

CREATE DATABASE LINK <DB_LINK_NAME> CONNECT TO <SCHEMA> IDENTIFIED BY <PASSWORD> USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =...

Oracle Insights

Rumesh Aponso (RMAX)

Nov 25, 20241 min read

Oracle INDEX Compute Statistics

The Oracle analyze index compute statistics command: ANALYZE INDEX index_name COMPUTE STATISTICS; The Oracle analyze index validate...

Oracle Insights

Rumesh Aponso (RMAX)

Nov 22, 20241 min read

How to run .sql file in SQLPLUS

sqlplus <<service_name>> / <<password>> @ <<database_name>> @ <<filename>> .sql

Oracle Insights

Rumesh Aponso (RMAX)

Nov 22, 20241 min read

Create Sequence in IFS

DECLARE seq_exists_ NUMBER; sql_stmt_ VARCHAR2(2000); CURSOR check_exists IS SELECT 1 FROM all_objects WHERE object_name =...

IFS Tips & Tricks

Rumesh Aponso (RMAX)

Nov 20, 20241 min read

Select Database Links in Oracle

select * from dba_db_links

Oracle Insights

Rumesh Aponso (RMAX)

Nov 20, 20241 min read

How to last compile data and time in Oracle

SELECT t.last_ddl_time, t.* FROM all_objects t WHERE t.object_type = 'PACKAGE BODY' AND t.OBJECT_NAME = 'CUSTOMER_ORDER_API';

Oracle Insights

Rumesh Aponso (RMAX)

Nov 20, 20241 min read

Change NLS_TERRITORY in Oracle

ALTER SESSION SET NLS_TERRITORY = Germany;

Oracle Insights

Rumesh Aponso (RMAX)

Nov 20, 20241 min read

How to select Users in Oracle

SELECT * FROM dba_users t

Oracle Insights

Rumesh Aponso (RMAX)

Nov 19, 20241 min read

How to select Tablespaces in Oracle

SELECT t.* FROM user_tablespaces t; SELECT t.* FROM dba_tablespaces t;

Oracle Insights

Rumesh Aponso (RMAX)

Nov 19, 20241 min read

Get Oracle Version

SELECT * FROM v$version; SELECT * FROM v$version WHERE banner LIKE 'Oracle%';

Oracle Insights

Rumesh Aponso (RMAX)

Nov 13, 20241 min read

How to Grant privileges of another user in Oracle

ALTER USER DUMMYUSER ACCOUNT UNLOCK ; ALTER USER DUMMYUSER IDENTIFIED BY PASSWORD ; ALTER USER MAINUSER GRANT CONNECT THROUGH DUMMYUSER...

Oracle Insights

Rumesh Aponso (RMAX)

Nov 13, 20241 min read