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

Application Express Views Search SELECT * FROM apex_dictionary WHERE column_id = 0; View Comment Parent View APEX_APPLICATIONS Applications defined in the current workspace or database user. APEX_WORKSPACES APEX_APPLICATION_ALL_AUTH All authorization schemes for all components by Application APEX_APPLICATIONS

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 Oracle SQL/XML

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 IS NULL)     THEN '<img src="#WORKSPACE_IMAGES#not_found_female.jpg" width="75px;" height="75px;"/>

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_buffer_raw), v_buffer_raw);       v_offset := v_offset + v_buffer_size;     END loop;     RETURN v_blob;   END clob_to_blob ; Step 2: Call the function and test it . DECLARE   l_xml CLOB;   l_blob BLOB;   l_clob_to_blob BLOB;  

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 data from relational content Convert CLOB (XML data) to BLOB Convert BLOB to CLOB Print the CLOB DECLARE   l_xml CLOB;   l_clob_to_blob BLOB;   l_blob_to_clob CLOB; BEGIN   -- Generate XML