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

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

Step 1:  Allow user to create any directory in database . GRANT CREATE ANY DIRECTORY TO scott; GRANT DROP ANY DIRECTORY TO scott; Step 2:  Create directory . CREATE OR REPLACE DIRECTORY temp AS '/d08/temp'; Step 3:  Give access to the directory . GRANT READ, WRITE ON DIRECTORY temp TO scott; optional: (REVOKE WRITE ON DIRECTORY tmp FROM scott) Step 4:  View database directories . SELECT  *    FROM all_directories; Note: You must have DBA rights, if you don't have DBA rights, you will get an error  "Invalid Directory Path". Related Posts: Move BLOB files into Oracle Directory Check if file exists in Oracle Directory

Objective : To integrate Oracle BI Publisher with Oracle Apex.  Step 1:  Create a procedure using below given PLSQL coding, which integrate Oracle BI with Oracle Apex. CREATE OR REPLACE PROCEDURE "WEB_SERVICES_CALL" (    p_name1         IN  VARCHAR2 DEFAULT NULL,    p_value1          IN   VARCHAR2 DEFAULT NULL,    p_name2            IN  VARCHAR2 DEFAULT NULL,    p_value2            IN   VARCHAR2 DEFAULT NULL,    p_name3           ...