About Oracle and Oracle ā'pěks (#orclapex)

Objective : To print clob data. Solution: Step 1: Create plsql procedure to print the clob. CREATE OR REPLACE PROCEDURE print_clob_to_output (p_clob IN CLOB)  IS    l_offset     INT := 1;  BEGIN    dbms_output.put_line('Print CLOB');       loop         exit when l_offset > dbms_lob.getlength(p_clob);         dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) );         l_offset := l_offset + 255;     END LOOP;  END print_clob_to_output;  This query will print the complete clob data. Step 2: Call the procedure. DECLARE   l_xml CLOB; BEGIN   SELECT dbms_xmlgen.getxml('select * from emp') xml      INTO l_xml      FROM dual;      print_clob_to_output (l_xml); END; Output: Related Posts: Generating XML data from relational data using ...

Solution: Step 1:  Go to shared components and upload required images. Step 2:  Create Interactive/Classical Report. Step 3:  Add below SQL query in source option . SELECT employees.EMPNO,   employees.ENAME,   employees.JOB,   employees.MGR,   employees.HIREDATE,   employees.SAL,   employees.COMM,   employees.ONLEAVE,   employees.NOTES,   employees.DEPTNO,   employees.FLEX1,   employees.FLEX2,   employees.FLEX3,   employees.FLEX4,   employees.RATING,   employees.GENDER,   CASE     WHEN (employees.gender   = 'MALE'     AND employees.emp_image IS NULL)     THEN '<img src="#WORKSPACE_IMAGES#not_found_male.jpg" width="75px;" height="75px;"/>' -- Default Male ICON     WHEN (gender             = 'FEMALE'     AND employees.emp_image...

Solution: Step 1:  Create plsql function  clob_to_blob . It should return the BLOB . create or replace  FUNCTION clob_to_blob (       p_clob_in IN CLOB)     RETURN BLOB   IS     v_blob BLOB;     v_offset         INTEGER;     v_buffer_varchar VARCHAR2(32000);     v_buffer_raw RAW(32000);     v_buffer_size binary_integer := 32000;   BEGIN     IF p_clob_in IS NULL THEN       RETURN NULL;     END IF;     dbms_lob.createtemporary(v_blob, TRUE);     v_offset := 1;     FOR i IN 1 .. ceil(dbms_lob.getlength(p_clob_in) / v_buffer_size)     loop       dbms_lob.READ(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);       v_buffer_raw := utl_raw.cast_to_raw(v_buffer_varchar);       dbms_lob.writeappend(v_blob, utl_raw.LENGTH(v_b...

Solution: Step 1:  Create plsql function  blob_to_clob . It should return the CLOB . CREATE OR REPLACE   FUNCTION blob_to_clob (       blob_in IN BLOB)     RETURN CLOB   AS     v_clob CLOB;     v_varchar VARCHAR2(32767);     v_start pls_integer  := 1;     v_buffer pls_integer := 32767;   BEGIN     dbms_lob.createtemporary(v_clob, TRUE);     FOR i IN 1..ceil(dbms_lob.getlength(blob_in) / v_buffer)     loop       v_varchar := utl_raw.cast_to_varchar2(dbms_lob.substr(blob_in, v_buffer, v_start));       dbms_lob.writeappend(v_clob, LENGTH(v_varchar), v_varchar);       v_start := v_start + v_buffer;     END loop;     RETURN v_clob;   END blob_to_clob ; Step 2:  Call the function and test it . Many ways to check this funtionality, Method 1:  Generate XML d...

Objective : Generate XML data from relational data using  oracle provided  standard packages. Prerequisite : DBMS_XMLGEN.GETXML (    sqlQuery     IN VARCHAR2,    dtdOrSchema  IN number := NONE)  return clob; Converts the results from the SQL query string to XML format, and returns the XML as a temporary CLOB. Solution: SELECT dbms_xmlgen.getxml('select * from emp') xml    FROM dual;    This query will give you the complete XML structure output of the table along with its data. Output: Related Posts: Print CLOB data using Oracle PL/SQL

Solution: Step 1:  Click  Oracle Directories  to understand about directory creation. Step 2:  Move BLOB files into Oracle Directory . Step 3:  Create a PLSQL function  file_exists, which will help us to check whether the given file exists in the given directory or not. CREATE OR REPLACE   FUNCTION file_exists (       p_dirname  IN VARCHAR2, -- Put your oracle directory name       p_filename IN VARCHAR2 )     RETURN BOOLEAN   IS     l_file_loc BFILE;     l_exists NUMBER;   BEGIN     l_file_loc := bfilename(upper(p_dirname), p_filename);     l_exists   := dbms_lob.fileexists(l_file_loc); -- 1 exists; 0 - not exists         IF l_exists = 1 THEN       RETURN TRUE;     elsif l_exists = 0 THEN       RETURN FALSE;     END IF;        EXCEPTION...

Solution: Step 1: Click  Oracle Directories  to understand about directory creation. Step 2:  Create plsql procdure  blob_to_file . It move BLOB files into respected database directories . CREATE OR REPLACE PROCEDURE blob_to_file (     p_blob     IN OUT nocopy BLOB,     p_dir      IN VARCHAR2,     p_filename IN VARCHAR2) AS   l_file utl_file.file_type;   l_buffer RAW(32767);   l_amount binary_integer := 32767;   l_pos      INTEGER           := 1;   l_blob_len INTEGER; BEGIN   l_blob_len := dbms_lob.getlength(p_blob);   -- Open the destination file.   l_file := utl_file.fopen(p_dir, p_filename,'wb', 32767);   -- Read chunks of the BLOB and write them to the file until complete.   WHILE l_pos <= l_blob_len   LOOP     dbms_lob.READ(p_blob, l_amount, l_pos, l_buffer);     ...