TNT - Tips & Tricks

Function to normalize url link, substitute special characters in link: CREATE OR REPLACE FUNCTION urlencode( p_str in varchar2 ) return varchar2 AS     l_tmp varchar2(12000);     l_len number default length(p_str);     l_bad varchar2(100) default ' >%}\~];?@&<#{|^[`/:=$+''"' || chr(10);     l_char char(1); BEGIN     IF ( p_str is NULL ) then         RETURN NULL;     END IF;     FOR i IN 1 .. l_len LOOP         l_char := substr(p_str,i,1);         IF ( instr( l_bad, l_char ) > 0 )         THEN             l_tmp := l_tmp || '%' || to_char(ascii(l_char), 'fm0X');         ELSE             l_tmp := l_tmp || l_char;         END IF;     END LOOP; RETURN l_tmp; END; Example: SET ...

DBMS_OUTPUT has different default buffer sizes, depending on your Oracle version. You can increase the buffer up to 1,000,000 using the following PL/SQL statement: DBMS_OUTPUT.ENABLE(1000000); The SQL*Plus statement to achieve the same result is: SET SERVEROUTPUT ON SIZE 1000000 Starting with Oracle release 10g, it is possible to use the following unlimited buffer settings: DBMS_OUTPUT.ENABLE (buffer_size => NULL);

PL/SQL Write BLOB to file (temp file): in my case I have a table with BLOB column, I keep my pdf files inside, and I need to save them in temp directory. This procedure accepts 3 arguments: BLOB, directory name to save into and file name: CREATE OR REPLACE PROCEDURE write_pdf(v_blob IN BLOB, p_directory IN VARCHAR2, p_filename IN VARCHAR2 ) IS v_length INTEGER; v_index INTEGER := 1; v_bytecount INTEGER; v_tempraw RAW(32767); v_file UTL_FILE.file_type; BEGIN v_file := UTL_FILE.fopen(p_directory, p_filename, 'wb', 32767); v_length := DBMS_LOB.getlength(v_blob); WHILE v_index <= v_length LOOP v_bytecount := 32767; DBMS_LOB.read(v_blob, v_bytecount, v_index, v_tempraw); UTL_FILE.put_raw(v_file, v_tempraw); UTL_FILE.fflush(v_file); ...

Work with directories Oracle PL/SQL: Create(add) temp directory on c:\ referenced as DIR_TEMP CREATE DIRECTORY DIR_TEMP AS 'C:\temp'; List all directories by system user: SELECT * FROM all_directories;