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);
}