DECLARE
v_from_name VARCHAR2(100) := 'admin@falickfaisal.com';
v_to_name VARCHAR2(100) := 'falickfaisal@gmail.com';
v_subject VARCHAR2(100) := 'This is an awesome mail';
v_message_body VARCHAR2(100) := 'Hey you!! Bla Bla Bluu';
v_message_type VARCHAR2(100) := 'text/plain';
v_smtp_server VARCHAR2(200) := 'smtpout.asia.secureserver.net';
n_smtp_server_port NUMBER := 25;
conn utl_smtp.connection;
TYPE attach_info IS RECORD (
attach_name VARCHAR2(40),
data_type VARCHAR2(40) DEFAULT 'text/plain',
attach_content CLOB DEFAULT ''
);
TYPE array_attachments IS TABLE OF attach_info;
attachments array_attachments := array_attachments();
n_offset NUMBER;
n_amount NUMBER := 1900;
v_crlf VARCHAR2(5) := CHR(13) || CHR(10);
base64username VARCHAR2(40) := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('admin@falickfaisal.com')));
base64password VARCHAR2(40):= UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('123456789')));
BEGIN
-- Fill data for example
attachments.extend(5);
FOR i IN 1..5
LOOP
SELECT 'test' || to_char(i) || '.txt','text/plain','test' || to_char(i)
INTO attachments(i)
FROM dual;
END LOOP;
-- Open the SMTP connection ...
conn := utl_smtp.open_connection(v_smtp_server,n_smtp_server_port);
utl_smtp.helo(conn, v_smtp_server);
UTL_SMTP.command(conn, 'AUTH', 'LOGIN');
UTL_SMTP.command(conn, base64username );
UTL_SMTP.command(conn, base64password );
utl_smtp.mail(conn, v_from_name);
utl_smtp.rcpt(conn, v_to_name);
-- Open data
utl_smtp.open_data(conn);
-- Message info
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('To: ' || v_to_name || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('From: ' || v_from_name || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Subject: ' || v_subject || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('MIME-Version: 1.0' || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: multipart/mixed; boundary="SECBOUND"' || v_crlf || v_crlf));
-- Message body
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || v_message_type || v_crlf || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(v_message_body || v_crlf));
-- Attachment Part
FOR i IN attachments.FIRST .. attachments.LAST
LOOP
-- Attach info
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || attachments(i).data_type
|| ' name="'|| attachments(i).attach_name || '"' || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Disposition: attachment; filename="'
|| attachments(i).attach_name || '"' || v_crlf || v_crlf));
-- Attach body
n_offset := 1;
WHILE n_offset < dbms_lob.getlength(attachments(i).attach_content)
LOOP
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(dbms_lob.substr(attachments(i).attach_content, n_amount, n_offset)));
n_offset := n_offset + n_amount;
END LOOP;
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('' || v_crlf));
END LOOP;
-- Last boundry
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND--' || v_crlf));
-- Close data
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
END;
Oracle Tutorial, We have covered all Oracle articles which includes Oracle Database, Application, Framework, Oracle HRM, ADF Training and Financial Training. Further more we have queries and scripts for Oracle EBS Modules.
Monday 2 December 2019
Wednesday 20 November 2019
WorkFlow : ORA-04061: existing state of has been invalidated ORA-04061: existing state of package
RA-04061: existing state of has been invalidated
ORA-04061: existing state of package
Compile Package for Error ORA-04061 in WorkFlow
alter package Wf_Engine_Util compile;
alter package wf_engine compile;
alter package wf_notification compile;
alter package wf_standard compile;
Compile Package for Error ORA-04061 in WorkFlow
alter package Wf_Engine_Util compile;
alter package wf_engine compile;
alter package wf_notification compile;
alter package wf_standard compile;
Print Function for PLSQL Testing
For tracing PLSQL code , Print function show output in DBMS Output as per Arguments.
1) For implementation of this function simply add G_Debug_Enabled Variable and Function Specification in your package Specification. For enable tracing set G_Debug_Enabled as True before execute your code.
G_Debug_Enabled Boolean default false;
procedure print(pString in varchar2
2) Add Function Body in Package Specification Body
1) For implementation of this function simply add G_Debug_Enabled Variable and Function Specification in your package Specification. For enable tracing set G_Debug_Enabled as True before execute your code.
procedure print(pString in varchar2
, pText_1 in varchar2 default null
, pValue1 in varchar2 default null
, pText_2 in varchar2 default null
, pValue2 in varchar2 default null
, pText_3 in varchar2 default null
, pValue3 in varchar2 default null
, pText_4 in varchar2 default null
, pValue4 in varchar2 default null
, pText_5 in varchar2 default null
, pValue5 in varchar2 default null
, pText_6 in varchar2 default null
, pValue6 in varchar2 default null
, pText_7 in varchar2 default null
, pValue7 in varchar2 default null
, pText_8 in varchar2 default null
, pValue8 in varchar2 default null
, pText_9 in varchar2 default null
, pValue9 in varchar2 default null
);
procedure test_Print_function;
2) Add Function Body in Package Specification Body
procedure print(pString in varchar2
, pText_1 in varchar2 default null
, pValue1 in varchar2 default null
, pText_2 in varchar2 default null
, pValue2 in varchar2 default null
, pText_3 in varchar2 default null
, pValue3 in varchar2 default null
, pText_4 in varchar2 default null
, pValue4 in varchar2 default null
, pText_5 in varchar2 default null
, pValue5 in varchar2 default null
, pText_6 in varchar2 default null
, pValue6 in varchar2 default null
, pText_7 in varchar2 default null
, pValue7 in varchar2 default null
, pText_8 in varchar2 default null
, pValue8 in varchar2 default null
, pText_9 in varchar2 default null
, pValue9 in varchar2 default null
)
is
begin
if G_Debug_Enabled then
Dbms_output.put_line('$ '||pString||' : '||pText_1||' => '||pValue1||' '||pText_2||'
=> '||pValue2||' '||pText_3||' => '||pValue3||' '||pText_4||' => '||pValue4||' '||pText_5||' => '||pValue5||' '||pText_6||' => '||pValue6||' '||pText_7||' => '||pValue7||' '||pText_8||' => '||pValue8||' '||pText_9||' => '||pValue9);
end if;
end;
procedure test_Print_function
is
begin
print('Function Test','TransactionID',10,'EmployeeName','mFaisal');
end;
3) Now test test_print_function
begin
-- Call the procedure
PKG_wf_api.G_Debug_Enabled:=true;
PKG_wf_api.test_print_function;
end;
Output:
first_rule of "The Forty Rules of Love"
#first_rule
"How we see God is a direct reflection of how we see ourselves.
If God brings to mind mostly fear and blame, it means there is too much fear and blame welled inside
us. If we see God as full of love and compassion, so are we.”
"How we see God is a direct reflection of how we see ourselves.
If God brings to mind mostly fear and blame, it means there is too much fear and blame welled inside
us. If we see God as full of love and compassion, so are we.”
Tuesday 19 November 2019
InList Function PLSQL
CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000); / CREATE OR REPLACE FUNCTION in_list (p_in_list IN VARCHAR2) RETURN t_in_list_tab PIPELINED AS l_text VARCHAR2( 32767): = p_in_list || ','; l_idx NUMBER; BEGIN LOOP l_idx: = INSTR( l_text, ','); EXIT WHEN NVL( l_idx, 0) = 0; PIPE ROW (TRIM( SUBSTR( l_text, 1, l_idx - 1))); l_text: = SUBSTR( l_text, l_idx + 1); END LOOP; RETURN; END;
SELECT * FROM emp WHERE empno IN (SELECT * FROM TABLE( in_list( : empno ))) ORDER BYename
Value of: empno is 7876,7934,7782
Thursday 14 November 2019
Enable / Disable Field in Oracle Forms
Case Study :
There are multiple columns in Oracle tabular form and they are depend on each other as Numeric sequence. User enter cannot enter value in columns if parent of current item is null or child of current item is not null.
Add procedure in form and enter your your fields name in array as sequence you want .
after this call this code
begin
when_New_item_instance;
end;
in When-new-item-instance trigger.
PROCEDURE when_New_item_instance IS
type Items_List_t is varray(3) of Varchar2(200);
l_Items_List Items_List_t := Items_List_t('BlockName.Item1',
'BlockName.Item2',
'BlockName.Item3');
l_triggered_Item varchar2(200) default :system.trigger_item;
l_item_enabled boolean default true;
l_triggered_item_ind number;
BEGIN
for ldx in 1 .. l_Items_List.count loop
if l_triggered_Item = l_Items_List(ldx)
then
l_triggered_item_ind := ldx;
end if;
end loop;
if l_triggered_item_ind is null then
null;
end;
---Note
-- Exception overriding is requeired in inner block so Exception when Others is Replaced with if-Else condition.
Begin
if name_in(l_Items_List(l_triggered_item_ind - 1)) is null
then
l_item_enabled := false;
end if;
exception
when others
then
null;
l_item_enabled := true;
end;
---Note
-- Exception overriding is requeired in inner block so Exception when Others is Replaced with if-Else condition.
Begin
if name_in(l_Items_List(l_triggered_item_ind + 1)) is not null
then
l_item_enabled := false;
end if;
exception
when others
then
null;
l_item_enabled := true;
end;
if l_item_enabled then
app_item_property.set_property(l_triggered_Item, enabled, property_on);
else
app_item_property.set_property(l_triggered_Item, enabled, property_off);
end if;
END;
Note:
Incase of non-EBS application
replace
app_item_property.set_property(l_triggered_Item, enabled, property_on);
with
set_item_property(l_triggered_Item, enabled, property_on);
There are multiple columns in Oracle tabular form and they are depend on each other as Numeric sequence. User enter cannot enter value in columns if parent of current item is null or child of current item is not null.
Add procedure in form and enter your your fields name in array as sequence you want .
after this call this code
begin
when_New_item_instance;
end;
in When-new-item-instance trigger.
PROCEDURE when_New_item_instance IS
type Items_List_t is varray(3) of Varchar2(200);
l_Items_List Items_List_t := Items_List_t('BlockName.Item1',
'BlockName.Item2',
'BlockName.Item3');
l_triggered_Item varchar2(200) default :system.trigger_item;
l_item_enabled boolean default true;
l_triggered_item_ind number;
BEGIN
for ldx in 1 .. l_Items_List.count loop
if l_triggered_Item = l_Items_List(ldx)
then
l_triggered_item_ind := ldx;
end if;
end loop;
if l_triggered_item_ind is null then
null;
end;
---Note
-- Exception overriding is requeired in inner block so Exception when Others is Replaced with if-Else condition.
Begin
if name_in(l_Items_List(l_triggered_item_ind - 1)) is null
then
l_item_enabled := false;
end if;
exception
when others
then
null;
l_item_enabled := true;
end;
---Note
-- Exception overriding is requeired in inner block so Exception when Others is Replaced with if-Else condition.
Begin
if name_in(l_Items_List(l_triggered_item_ind + 1)) is not null
then
l_item_enabled := false;
end if;
exception
when others
then
null;
l_item_enabled := true;
end;
if l_item_enabled then
app_item_property.set_property(l_triggered_Item, enabled, property_on);
else
app_item_property.set_property(l_triggered_Item, enabled, property_off);
end if;
END;
Note:
Incase of non-EBS application
replace
app_item_property.set_property(l_triggered_Item, enabled, property_on);
with
set_item_property(l_triggered_Item, enabled, property_on);
Tuesday 12 November 2019
Alert Using JavaScript in OAF
if(pageContext.getParameter("SubmitButton")!=null)
{
StringBuffer l_buffer = new StringBuffer();
l_buffer.append("javascript:alert('hello')");
pageContext.putJavaScriptFunction("SomeName",l_buffer.toString());
}
Here SubmitButton is the id name of submitbutton
l_buffer is the variable name of StringBuffer
{
StringBuffer l_buffer = new StringBuffer();
l_buffer.append("javascript:alert('hello')");
pageContext.putJavaScriptFunction("SomeName",l_buffer.toString());
}
Here SubmitButton is the id name of submitbutton
l_buffer is the variable name of StringBuffer
Subscribe to:
Posts (Atom)
OADBTransactionImpl in Oracle Application Framework (OAF)
OADBTransactionImpl is a class in Oracle Application Framework (OAF), which is a framework for building Oracle E-Business Suite applications...
-
Create Type Loop_Type is table of Varchar2(400) Create or Replace Function Find_Loop_F (P_End_loop in NUmber Default 10) Return Loop_Typ...
-
//import oracle.cabo.ui.validate.Formatter; important Class for Formatting // Set Number fields Format public void se...
-
For single form frmcmp_batch userid=apps/apps module=/opt/oracle/fs1/EBSapps/appl/au/12.0.0/forms/US/Form.fmb output_file=/opt/oracle/fs1/...