Send email from with attachments oracle forms via CmdEmail

CmdEmail is command line e-mail component, it can be integrated into application that developed by multiple language. This article demonstrate how to integrate CmdEmail into oracle forms, enables oracle forms to send rich formated e-mail. Note: Since CmdEmail require Windows platform, if your forms is not running on Windows server, this article may be useless for you. How to send mail from Oracle Forms There several ways to send mail through oracle forms. -- Using javabean -- Using Ole component -- Using utl_smtp -- Using CmdEmail In this page, we only introduce the last way, a simple and effective way to send email through oracle form, using the CmdEmail send mail component. If you want to try other solutions, please refer to documents of Oracle Forms or search google for related topic. Why CmdEmail? Easy to manager Support attachment support email in html format; you can insert picture into email Only require TCP/IP protocol installed HOW CmdEmail WORKS CmdEmail support command line interface, you call the program through command line. Supposed you have configed the program, you can send e-mail to somebody just use a dos command. e.g. sendmail -to:scott@tiger.com The CmdEmail read the e-mail addr. of recipients, and then send a blank message to the recipients. The recipients scott@tiger.com (if exists) will receive a mail sent from you. Need to specify the subject and mail body? Okey, please create a message file and pass the file path to CmdEmail . The messae file includes two parts, header and body. Header : Specify the recipients, subject, location of attachment etc. Body Text: The content of the email. There is a empty line between header and body. The following is an example: to:scott@tiger.com cc:bill@microsoft.com subject:hello scott attachment:http://www.lv2000.com/sendmail.htm First line of the body. Second line. Save the text above to C:\temp_name.txt . Run CmdEmail again, e.g. sendmail -m:c:\temp_name.txt CmdEmail will read information from the message file, then send a mail to sott@tiger.com, copy to bill@microsoft.com. Installation and Setup Download latest version from www.lv2000.com Decompress installation pack to the folder where you want to install the application files. We suggest you to install CmdEmail to windows PATH so that it can be called from any current working directory. Setup your default email account settings. How to setup e-mail account Run this command line: sendmail -setup The CmdEmail Settings dialog will appear.Please input the information given to you by your Internet Service Provider (ISP) or administrator. Your Name: Friendly name of the addresser. Your email address: The addresser of the mail. Outgoing SMTP server: Please refer to your ISP or network administrator. Port of SMTP server: Default 25 Read receipt requested: For MS outlook only. User Name: User name used to login the smtp server. Password: Password used to identify the user. Note: Unless your ISP have indicated that your service uses Secure Password Authentication, do not select the "my outgoing server requires authentication" check box. Test the email account setting Please do some testing after setting e-mail account. Please use CmdEmail to send a mail manually. CmdEmail will print error message if run into any error; the error will be saved into smerror.log How to call CmdEmail from oracle form You can call CmdEmail from oracle form through HOST built-in. We have created an example demonstrate how to use HOST procedure to run CmdEmail. Please download the form from http://www.lv2000.com/examples/testmail.fmb Screenshot of the form: Sample code of sending mail via oracle forms --How to create message file. Please use TEXT_IO to create the message file. --How to call CmdEmail. Please use HOST procedure to run CmdEmail. Sample code: DECLARE v_file_path varchar2(300); v_sendmail_exe_path varchar2(300); text_file text_io.file_type; BEGIN v_sendmail_exe_path:='D:\cmdemail\sendmail.exe' ;--TODO: Change the path v_file_path := 'd:\emailmsg.txt' ;--TODO: if :mailto is null then message('Please enter the email address of the recipients'); raise form_trigger_failure; end if; text_file:=text_io.fopen(v_file_path,'w'); -- write header text_io.put_line(text_file,'to:'||:mailto); text_io.put_line(text_file,'cc:'||:copyto); text_io.put_line(text_file,'attachment:c:\temp_file1.txt'); text_io.put_line(text_file,'attachment:c:\temp_file2.doc'); text_io.put_line(text_file,'subject:'||:subject); text_io.put_line(text_file,'delete_the_file:yes');--delete the message file when the mail is sent successfully. text_io.put_line(text_file,''); -- delimiter between header and body -- write body text_io.put_line(text_file,:mailbody); text_io.fclose(text_file); host(v_sendmail_exe_path||' -m:'||v_file_path,NO_SCREEN); END ; Troubleshotting If you the mail is not sent to recipients, CmdEmail will log the error message to this file: smerror.log , please open the log file via notepad.exe and view the error message.

HOW TO - Performing Text_IO on the client using WebUtil. Introduction

Many Forms applications utilize Text_IO to read and write data from the file system. When moving your Forms application to the Web, Text_IO works in exactly the same manner as client/server. However, you must now remember that your application is running on the application server and not the client machine.

This may infact be the behavior you desire (for example, using Text_IO to write out auditing information). However, it could be that you need Text_IO to access the machine at which the user is sitting. For example, a salesman logs onto the Forms application and wants the application to read sales data from his local machine.

WebUtil provides you the functionality to perform client side Text_IO.

Set Up

For the steps to set up WebUtil, please refer to the WebUtil Familiarization Manual available as part of the software download.

Changing code

Consider the following code:

PROCEDURE WRITE_ITEM_BLOCK (FILENAME IN VARCHAR2) IS 
  MYFILE TEXT_IO.FILE_TYPE; 
  CUR_REC NUMBER; 
BEGIN 
  GO_BLOCK('S_ITEM'); 
  CUR_REC := :SYSTEM.CURSOR_RECORD; 
  IF :SYSTEM.BLOCK_STATUS != 'NEW' THEN 
    FIRST_RECORD; 
    MYFILE := TEXT_IO.FOPEN(FILENAME, 'W'); 
    TEXT_IO.PUTF(MYFILE,'Item_ID,Proudct_ID,Description'||CHR(10)); 
    LOOP 
      TEXT_IO.PUTF(MYFILE, TO_CHAR(:S_ITEM.ITEM_ID)||','); 
      TEXT_IO.PUTF(MYFILE, TO_CHAR(:S_ITEM.PRODUCT_ID)||','); 
      TEXT_IO.PUTF(MYFILE, :S_ITEM.DESCRIPTION||','); 
      TEXT_IO.PUTF(MYFILE, CHR(10)); 
      EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE'; 
      NEXT_RECORD; 
    END LOOP; 
    TEXT_IO.FCLOSE(MYFILE); 
  END IF; 
  GO_RECORD(CUR_REC); 
END;

This code will go through the records of a block and output the data to a named file. To perform the same functionality, but on the client side, attatch the WebUtil object library and PL/SQL library, and replace any instance of TEXT_IO with CLIENT_TEXT_IO.

The resulting code will now work, as before, but write the file to the client machine.

PROCEDURE WRITE_ITEM_BLOCK (FILENAME IN VARCHAR2) IS 
  MYFILE CLIENT_TEXT_IO.FILE_TYPE; 
  CUR_REC NUMBER; 
BEGIN 
  GO_BLOCK('S_ITEM'); 
  CUR_REC := :SYSTEM.CURSOR_RECORD; 
  IF :SYSTEM.BLOCK_STATUS != 'NEW' THEN 
    FIRST_RECORD; 
    MYFILE := CLIENT_TEXT_IO.FOPEN(FILENAME, 'W'); 
    CLIENT_TEXT_IO.PUTF(MYFILE,'Item_ID,Proudct_ID,Description'||CHR(10)); 
    LOOP 
      CLIENT_TEXT_IO.PUTF(MYFILE, TO_CHAR(:S_ITEM.ITEM_ID)||','); 
      CLIENT_TEXT_IO.PUTF(MYFILE, TO_CHAR(:S_ITEM.PRODUCT_ID)||','); 
      CLIENT_TEXT_IO.PUTF(MYFILE, :S_ITEM.DESCRIPTION||','); 
      CLIENT_TEXT_IO.PUTF(MYFILE, CHR(10)); 
      EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE'; 
      NEXT_RECORD; 
    END LOOP; 
    CLIENT_TEXT_IO.FCLOSE(MYFILE); 
  END IF; 
  GO_RECORD(CUR_REC); 
END;

Oracle: text_io tips

Question: I want to be able to read and write to a PC client using text_io. I cannot use utl_file because I do not have Oracle database installed on the remote client. How do I invoke text_io?

Answer: The Oracle text_io package can write to the local machine rather than server side (such as utl_file).

Here is an example of using the text_io utility to write across platforms. This is invoked from the Oracle server and writes to the connected remote PC client:

declare
l_out_file text_io.file_type;
begin
l_out_file := text_io.fopen( 'c:\text.txt', 'w' ); -- w is write, r is read
text_io.put (l_out_file, 'New text' );
text_io.new_line( l_out_file, 1 ); -- 1 new line
text_io.fclose( l_out_file );
end;/
To check, if file exists or not on server's directory

DECLARE
v_file Text_IO.File_Type;
BEGIN
v_file := Text_IO.Fopen('C:\Photos\myphoto.jpg', 'r');
message('success'); message('');
EXCEPTION
WHEN OTHERS THEN
message('exception'); message('');
END;

Oracle Developer 6i forms and reports client unable to connect with oracle database 11g r2 64 bits by default

Oracle developer 6i wasn't able to connect at (11.2.0.1) oracle database with default multi-byte CHARACTER SET "AL32UTF8" .
altering the single-byte CHARACTER SET as "WE8MSWIN1252" then developer able to connected.

(From some pc's developer 6i builder just flash out (close) 
and from another pc its says form designer need to close because of  unable to run .)

Info:- 
oracle database version
---------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0    Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

OS is :- Windows enterprize server 2008 R2




Developer forms builder 6i.
 
Below are the steps to alter the CHARACTER SET.
(first test it at testing server)

SQL> shutdown immediate
SQL> startup mount
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes=0;
SQL> alter system set aq_tm_processes=0;
SQL> alter database open;

SQL> ALTER DATABASE CHARACTER SET WE8MSWIN1252;
SQL> shutdown immediate
SQL> startup


To see the value of CHARACTER SET

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;



or

SELECT * FROM NLS_DATABASE_PARAMETERS;

[Note :- 

"ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252" 

It strongly recommend that you not use the INTERNAL_USE clause unless Oracle support explicitly tells you to do so. As the name implies, INTERNAL_USE is not properly documented and is not designed to be used by customers in the field.

To change the NLS_CHARACTERSET or NLS_NCHAR_CHARACTERSET by updating props$ . This is NOT supported and WILL corrupt your database. This is one of the best way's to destroy a complete dataset. Oracle Support will TRY to help you out of this but Oracle will NOT warrant that the data can be recoverd or recovered data is correct. Most likely you WILL be asked to do a FULL export and a COMPLETE rebuild of the database. 

Oracle 6i / Developer 2000 / Forms and Reports connection with Oracle database 12c

How to connect to Oracle database 12c from Developer 2000, Forms and Reports.

A major problem is faced by the developer who are still working on Developer 2000 as their domain, but migrated to Oracle 12c as per requirement. Problem is "No matching authentication protocol".

That is they are unable to connect to Oracle 12c database. Problem is Oracle 12c itself rejects connection from Developer 6i.

It can be solved. Just need to make one changes in database server end.

I will be guiding for the methods for both linux and windows environment.

Simply open sqlnet.ora file on database server end.

In linux we assume you installed Oracle database in /u01 directory and using version 12.1.0

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

In windows we assume you installed Oracle database in c:\ drive and using version 12.1.0

C:\app\oracle\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

add this line to the end of the file

SQLNET.ALLOWED_LOGON_VERSION=8

This will surely solve connectivity problem from Developer 2000 to Oracle 12c database.

Potential Issues When Connecting Forms 6i with Oracle Database 12c

In the past few years, Oracle has released its latest version of Oracle Database: version 12c. Oracle Database 12c contains all the latest features, bug fixes, and security patches. Many applications built on Oracle Forms, ADF, APEX, or even non-Oracle applications rely on a connection to an Oracle database to query, insert, and update data. When an Oracle database is upgraded to 12c, it is important to also upgrade or modernize your application to the latest version in order to decrease the possibility of compatibility issues occurring between your application(s) and your 12c Oracle database. For Oracle Forms, Oracle has certified Forms version 11gR2 (11.1.2.2.x) and 12c (12.2.x) with Oracle Database 12c as Oracle has tested the compatibility between these versions, and both work successfully together. Without upgrading your Oracle Forms application to the latest version, the odds of running into errors when connecting an older Oracle Forms application to a 12c database increases.

For example, with Oracle Forms version 6i, the following issues have been encountered when trying to connect to a 12c Oracle database (or even to an 11g database):

• Errors such as ORA-3113 and ORA-28040 during authentication
• SQL*Plus hanging issues when using a SQL*Plus client from Oracle Forms 6i to connect to an 11gR2 or 12c database
• The possibility that a user may be asked to re-authenticate upon accessing a new form in the same application if the user was able to log in successfully

The reason for these errors is because Oracle Forms 6i is meant to run on older Oracle Database versions which are no longer supported. Newer Oracle Database versions such as 11gR2 and 12c contain newer features and settings which were not tested against older Forms versions because the older releases were replaced by newer versions of Oracle Forms. As newer versions of Oracle Forms are released, older releases are taken out of support.

There have been reports that some workarounds have been known to fix some of the issues noted above. However, they are neither recommended nor supported by Oracle. Some workarounds may even lower the security of your Oracle database. The following workarounds have been reported to work, but there are no guarantees if they will work. Some users reported that they either needed to create new configuration files, restart the database several times temporarily in restricted mode, or even re-configure database instances from scratch:

• Configure “SQLNET.ALLOWED_LOGON_VERSION_SERVER=8” and “SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8” in sqlnet.ora
• Setting the parameter “SEC_CASE_SENSITIVE_LOGIN” to FALSE
• Setting the system parameters, JOB_QUEUE_PROCESSES and AQ_TM_PROCESSES, to 0
• Changing the database character set to WE8MSWIN1252 using “ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252; or changing the character set to a UTF8 character set such as AL32UTF8

The above workarounds may work, but there is a possibility that they may not fix all problems when connecting an Oracle Forms 6i application with Oracle Database 12c. These will need to be carefully tested in a development or test environment before they are rolled out to production. PITSS will not be held responsible for any problems which happen as a result of implementing any of these workarounds to get Oracle Forms 6i to work with Oracle Database 12c due to Forms 6i being out of support for about a decade.

The safest, most secure, and best approach to have your Forms application work with the latest and greatest release of Oracle Database (version 12c) is to either upgrade to the latest version of Oracle Forms (also version 12c), or modernize it to a newer technology. PITSS has the expertise to upgrade your Oracle Forms application to the latest release which is compatible with the latest release of Oracle Database as well as to digitally transform your Forms application to a more robust technology.

ORA-02298 cannot validate parent keys Tips

Question: During a Data Pump import (impdp) I am gett the error "ORA-02298 cannot validate parent keys." How do I fix and solve the ORA-02298 error?

Answer: The ORA-02298 cannot validate parent keys error occurs when an "orpharn" child row is added to the database. The oerr ito;ity nores:

ORA-02298 cannot validate parent keys

Cause: an alter table validating constraint failed because the table has child records.

Action: Obvious

There is no doubt on the ORA-02298 error; there are orphan rows that do not have a correpponding parent row (as enforced by a foreign ket constraint). By disabling and re-enabling the foreign key you can see the ORA-02298 error appear, and the ONLY solution is one of these actions:

1 - Add the proper parent rows

2 - delete the child orphan rows (not recommended because of loss of data integrity)

This error commonly occurs during a "live" Data Pump export (expdp), when there is time period between when the parent was exported and the child was exported.

This explains for the case where there are "missing" rows in the parent table, since the parent table was already exported when the child table export was started.

You can locate foreign "orphan rows" with SQL like this, something like:

select child_key from from child_table
where
child_key not in (select parent_key from parent_table);




The best solution for the ORA-02298 error is to use FLASHBACK_SCN or FLASHBACK_TIME parameter while re-exporing the data

This approach will export all of the rows that are consistent as of this SCN or TIME