How to convert Bytes to GB using sql query

Question

Im trying to verify value that have been changed in the DB using query. Howeverm it return the value I passed (in GB) to bytes. I wanted to verify to esnure value stored in (bytes) is correct when I make comparison with gigabytes.

set serveroutput on
DECLARE
static_value VARCHAR2(4000);
BEGIN
SELECT value
INTO  static_value
FROM   v$parameter WHERE  name = '&1'; 
DBMS_OUTPUT.PUT_LINE('&1 is set to '|| RTRIM(static_value));
DBMS_OUTPUT.PUT_LINE(&2);
DBMS_OUTPUT.PUT_LINE(RTRIM(static_value));
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to set parameter because of '|| SQLCODE);DBMS_OUTPUT.PUT_LINE('Error message - '|| SUBSTR(SQLERRM,1,128));
END;/

The result above return static_value = 6442450944 and &2 = 6g (which i specified i shell script that I call and pass the parameter to the sql query above.

How can I convert from bytes to gb in this case and compare static_value IS EQUAL TO &2?

Answer 1

you can use below function :

create or replace function format_size(p_size IN NUMBER) 
return VARCHAR2
IS
  v_i number;
  type array_t is varray(3) of varchar2(10);
  v_array array_t := array_t(' Bytes', ' KB', ' MB',' GB');
BEGIN
  v_i := floor(log(1024, p_size));
  return to_char(round(p_size/power(1024, v_i), 2))||v_array(v_i+1);
END;