0

I need to extract some css from a CLOB (datatype) field in the database and save it to a oracle directory. I have a quick script which uses a cursor loop through 8 different rows. All of them work, except one. One of the entries results in ORA-06502: PL/SQL: numeric or value error.

Because of earlier suggestions, I edited the code. The edited code actually produces a file, but it is empty. Below the code, I'm including a sample of the 192k css file.

To extract the data from the field, I am using this (edited) code:

DECLARE
    v_db_directory  VARCHAR2(30);
    v_filename      VARCHAR2(30);
--  v_description   VARCHAR2(120);
--  v_id            VARCHAR2(10);
--  v_owner         VARCHAR2(30);

    v_output_file   UTL_FILE.FILE_TYPE; 
    v_content       CLOB;

    l_amt number default 32000;
    l_offset number default 1;
    l_length number default
    nvl(dbms_lob.getlength(v_content),0);

BEGIN

    v_db_directory := 'STUDENT';

    SELECT constant_name, css
    INTO v_filename, v_content
    FROM css 
    WHERE constant_name NOT LIKE 'pbadm%'
    AND description IS NOT NULL
    AND constant_name like 'bootstrap-framework'
    ;

    v_output_file := utl_file.fopen(v_db_directory, v_filename, 'w', 32760);
    WHILE ( l_offset < l_length )
    LOOP
        utl_file.put(v_output_file,
        dbms_lob.substr(v_content,l_amt,l_offset) );
        utl_file.fflush(v_output_file);
        l_offset := l_offset + l_amt;
    END LOOP;
    utl_file.new_line(v_output_file);
    utl_file.fclose(v_output_file);
END;

The critical line, of course is UTL_FILE.PUT(v_output_file, v_content);

Here is a short section of the 192k css.

 * Copyright 2011-2021 The Bootstrap Authors
 * Copyright 2011-2021 Twitter, Inc.
 * Licensed under MIT (https://github.com/twbs/bootstrap/blob/main/LICENSE)
 */
:root {
  --bs-blue: #0d6efd;
  --bs-indigo: #6610f2;
  --bs-purple: #6f42c1;
  --bs-pink: #d63384;
  --bs-red: #dc3545;
  --bs-orange: #fd7e14;
  --bs-yellow: #ffc107;
  --bs-green: #198754;
  --bs-teal: #20c997;
  --bs-cyan: #0dcaf0;
  --bs-white: #fff;
  --bs-gray: #6c757d;
  --bs-gray-dark: #343a40;
  --bs-primary: #0d6efd;
  --bs-secondary: #6c757d;
  --bs-success: #198754;
  --bs-info: #0dcaf0;
  --bs-warning: #ffc107;
  --bs-danger: #dc3545;
  --bs-light: #f8f9fa;
  --bs-dark: #212529;
  --bs-font-sans-serif: system-ui, -apple-system, "Segoe UI", Roboto, "Helvetica Neue", Arial, "Noto Sans", "Liberation Sans", sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol", "Noto Color Emoji";
  --bs-font-monospace: SFMono-Regular, Menlo, Monaco, Consolas, "Liberation Mono", "Courier New", monospace;
  --bs-gradient: linear-gradient(180deg, rgba(255, 255, 255, 0.15), rgba(255, 255, 255, 0));
}

*,
*::before,
*::after {
  box-sizing: border-box;
}

@media (prefers-reduced-motion: no-preference) {
  :root {
    scroll-behavior: smooth;
  }
}

body {
  margin: 0;
  font-family: var(--bs-font-sans-serif);
  font-size: 1rem;
  font-weight: 400;
  line-height: 1.5;
  color: #212529;
  background-color: #fff;
  -webkit-text-size-adjust: 100%;
  -webkit-tap-highlight-color: rgba(0, 0, 0, 0);
}
Aaron
  • 51
  • 6

0 Answers0