Get Significant Figures in Oracle

Basic Idea

You can use common logarithm (i.e. log base 10) to evaluate the number of digits.
FLOOR(LOG(10, n)) indicates the decimal places of the first non-zero digit as follows.

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, FLOOR(LOG(10, N)) FROM DATA;

         N FLOOR(LOG(10,N))
---------- ----------------
      1234                3
       234                2
        34                1
         4                0
      .123               -1
     .0234               -2
    .00345               -3

Note: LOG(10, 0) causes an error: ORA-01428: argument '0' is out of range

Therefore, you can get the value rounded to the first digit of a given number n by ROUND(n, - FLOOR(LOG(10, n))).

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, ROUND(n, - FLOOR(LOG(10, N))) FROM DATA;

         N ROUND(N,-FLOOR(LOG(10,N)))
---------- --------------------------
      1234                       1000
       234                        200
        34                         30
         4                          4
      .123                         .1
     .0234                        .02
    .00345                       .003

In order to round to first d digits, you can modify as ROUND(n, d - FLOOR(LOG(10, n)) - 1).

Function

These can be summarized as the following function.

CREATE OR REPLACE FUNCTION SIGNIFICANT_FIGURES(
    n   NUMBER,
    d   NUMBER
) RETURN NUMBER
IS
BEGIN
    IF n = 0 THEN
        RETURN 0;
    ELSE
        RETURN ROUND(n, d - FLOOR(LOG(10, n)) - 1);
    END IF;
END;

If you want to round half to even, use ROUND_HALF_EVEN function instead of the standard ROUND function.
Note: From Oracle Database 18c, you can use ROUND_TIES_TO_EVEN function.