Oracle PL/SQL Generates XML Output for supplied SQL.

Following PL/SQL Generates XML Output for supplied SQL.




CREATE OR REPLACE PACKAGE rac_gen_xml



AS



FUNCTION gen_xml_for_sql (

p_sql_string IN VARCHAR2,

p_row_tag IN VARCHAR2 DEFAULT NULL,

p_row_set_tag IN VARCHAR2 DEFAULT NULL

)

RETURN CLOB;

PROCEDURE print_clob_out (l_result IN CLOB);

PROCEDURE convert_clob_to_file (

p_clob IN CLOB,

p_dir IN VARCHAR2,

p_file IN VARCHAR2

);

END rac_gen_xml;

/



CREATE OR REPLACE PACKAGE BODY rac_gen_xml

AS

FUNCTION gen_xml_for_sql (

p_sql_string IN VARCHAR2,

p_row_tag IN VARCHAR2 DEFAULT NULL,

p_row_set_tag IN VARCHAR2 DEFAULT NULL

)

RETURN CLOB

AS

l_queryctx DBMS_XMLQUERY.ctxtype;

l_result CLOB;

BEGIN

-- set up the query context

l_queryctx := DBMS_XMLQUERY.newcontext (p_sql_string);

IF p_row_tag IS NOT NULL

THEN

DBMS_XMLQUERY.setrowtag (l_queryctx, p_row_tag);

END IF;

IF p_row_set_tag IS NOT NULL

THEN

DBMS_XMLQUERY.setrowsettag (l_queryctx, p_row_set_tag);

END IF;

l_result := DBMS_XMLQUERY.getxml (l_queryctx);

DBMS_XMLQUERY.closecontext (l_queryctx);

RETURN l_result;

END gen_xml_for_sql;

PROCEDURE print_clob_out (l_result IN CLOB)

IS

l_xmlstr VARCHAR2 (32767);

l_line VARCHAR2 (2000);

BEGIN

l_xmlstr := DBMS_LOB.SUBSTR (l_result, 32767);

LOOP

EXIT WHEN l_xmlstr IS NULL;

l_line := SUBSTR (l_xmlstr, 1, INSTR (l_xmlstr, CHR (10)) - 1);

DBMS_OUTPUT.put_line (l_line);

l_xmlstr := SUBSTR (l_xmlstr, INSTR (l_xmlstr, CHR (10)) + 1);

END LOOP;

END print_clob_out;

PROCEDURE convert_clob_to_file (

p_clob IN CLOB,

p_dir IN VARCHAR2,

p_file IN VARCHAR2

)

IS

l_start NUMBER := 1;

l_bytelen NUMBER := 32000;

l_len NUMBER;

l_my_vr VARCHAR2 (32000);

l_num NUMBER;

l_output UTL_FILE.file_type;

BEGIN

-- define output directory

l_output := UTL_FILE.fopen (p_dir, p_file, 'w', 32760);

l_start := 1;

l_bytelen := 32000;

-- get length of blob

l_len := DBMS_LOB.getlength (p_clob);

-- save blob length

l_num := l_len;

-- if small enough for a single write



IF l_len < 32760

THEN

DBMS_LOB.READ (p_clob, l_len, l_start, l_my_vr);

UTL_FILE.put (l_output, l_my_vr);

UTL_FILE.fflush (l_output);

ELSE -- write in pieces

l_start := 1;

WHILE l_start < l_len AND l_bytelen > 0

LOOP

DBMS_LOB.READ (p_clob, l_bytelen, l_start, l_my_vr);

UTL_FILE.put (l_output, l_my_vr);

UTL_FILE.fflush (l_output);

-- set the start position for the next cut

l_start := l_start + l_bytelen;

-- set the end position if less than 32000 bytes

l_num := l_num - l_bytelen;

IF l_num < 32000

THEN

l_bytelen := l_num;

END IF;

END LOOP;

END IF;

UTL_FILE.fclose (l_output);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END convert_clob_to_file;

END rac_gen_xml;

/

Uploading multiple vCards into Gmail from Outlook, Mail or Thunderbird

Uploading multiple vCards into Gmail from Outlook, Mail or Thunderbird

1.     Go to your contacts in Outlook and highlight them all (ctrl-A). Right click and "Send Full Contact" then "In Internet Format (vCard)".  Depending on how many contacts you have, you may have to split them up into groups.  I did...
2.     An email should pop up with a bunch of attachments.  Send that email to yourself.
3.     From the email you just sent to yourself, highlight all the attachments, right-click, save as. Save them somewhere easy to access.
4.     Open up a command line. (Windows Key + R, then type "cmd" and press OK)
5.     Navigate to the folder where you saved the contacts.  I saved them in C:\contacts, so we'll use that.
6.     The command to type from the directory you have saved your contacts is:
copy   /B   *.vcf   single_contact_file.vcf
      Same with csv file also.
7.     This will create a file in that directory called " single_contact_file.vcf".  In that file will be the vCards of all of your contacts.
8.     Now you just go to Google.com/contacts, click "Import" on the right side of the page, and upload that file!

Mozilla Firefox Prompts To Install The J2SE Plugin When Trying To Access Oracle Applications Forms (R12, 11i)

When trying to access Forms using Mozilla Firefox, it gives the following message:


"In order to access this application, you must install the J2SE Plugin version 1.5.0_13. To install this plugin, click here to download the oaj2se.exe executable. Once the download is complete, double-click the oaj2se.exe file to install the plugin. You will be prompted to restart your browser when the installation "

The fix is based on what J2SE Plugin is being requested by Oracle E-Business Suite. In this example, it is looking for J2SE Plugin version 1.5.0_13.

To enable Mozilla Firefox to use the J2SE Plugin version 1.5.0_13, do the following:

1. Copy '\bin\NPJPI150_13.dll to '\plugins'
2. Also, make sure that JRE plugin is enabled, Help ->Add-Ons -> plugin ->
3. Restart Mozilla Firefox

Note: If Oracle E-Business Suite is prompting for a different version of J2SE Plugin, you need a different npjpi.dll to copy to your Firefox install folder.