myscripts: 2016

Sunday 11 December 2016

How to create a table with Blob data type and insert data

Working with Blob Datatypes

Step 1:

To work with lobs you must Create a table with columns of lob data types.

SQL> create table tab1 (col1 number(4),
2 col2 blob);

Table created.

Step 2:

Insert a row and initialize the blob with Empty_blob() so that it will generate a lob locator.

SQL> insert into tab1 values(1,empty_blob());

1 row created.

Step 3:

Create a procedure / anonymous block to write the data into the blob. Here i am creating an anonymous block. The below procedure will load the data into blob.

SQL> declare

2 v_lob blob;

3 vamt integer:=32767;

4 offst integer:=1;

5 vbuff raw(32767):=utl_raw.cast_to_raw(rpad('NUTHAN',32767,'A'));

6 begin

7 select col2 into v_lob from tab1 for update;

8 dbms_lob.write(v_lob,vamt,offst,vbuff);

9 exception

10 when others then

11 dbms_output.put_line(dbms_utility.format_error_stack);

12 end;

13 /

PL/SQL procedure successfully completed.

Step 4:

Query the table now you can see data is populated into the blob and it will show the data in binary format.

Key points to remember:

Blob's store the data only in binary format. Hence the parameters like(amount,offset,length ) are specified in terms of bytes.
The values of amount , length, offset values must not be more than maxlobsize (264).
Only Positive values of parameters are allowed. negative numbers from the tail of a lob are not allowed.
Buffer used for Blob is of Raw datatype. and max size of raw allowed is (32767).
Must lock the row of the blob that needs to be modified by using any of the procedure (Write, Append, Copy, Erase, Trim) else oracle will raise an error.

Saturday 10 December 2016

PLSQL CODE for creating a login page and validating the credentials

--
--#############################################################################
--# Name : pa_gl_login
--# Description : This procedure generates login page for authentication
--#
--#############################################################################
PROCEDURE pa_gl_login
AS
BEGIN
HTP.p ('<head>');
HTP.p ('<STYLE TYPE="text/css">');

HTP.P (
'TH{font-family: Arial; font-size: 9pt; background-color: lightblue;}');
HTP.p ('TD{font-family: Arial; font-size: 9pt;}');
--->
HTP.p ('</STYLE>');

HTP.p ('</head>');
HTP.p ('<BODY bgcolor="#FFFFFF">');
HTP.p ('<CENTER>');
HTP.p ('<TABLE BORDER=0>');
HTP.p (' <TR>');
HTP.p (
' <TD align="center"><IMG "PATH OF YOUR IMAGE".gif" border=0></TD>'); --width=250 height=110
HTP.p (' </TR>');
HTP.p (' <TR>');
HTP.p (
' <TD align="right" ><font size="5"><b>'
|| 'XXX PAGL REJECTIONS'
|| '<b></TD>');

HTP.p ('</TR>');
HTP.p ('<TR><TD> </TD></TR>');
HTP.formOpen ('XXX_FND_WEB_PKG.Rejection_validate_data',
'post',
NULL,
NULL);
-- Title bar
-- Parameter block
HTP.p (
'<TABLE width="fixed" align="center" border="0" cellpadding="0" cellspacing="0">');
-- p_context
HTP.p ('<TR><TD width="35%" align="left"><b>USERNAME:</b></TD>');

HTP.p (
' <TD align="left"><INPUT TYPE="text" NAME="p_username" style="height:25px;width: 170px;" value="" required autocomplete="off" ></TD>');
HTP.p ('</TR>');
HTP.P ('<Tr><TD>  </TD></Tr>');

HTP.p ('<TR><TD width="35%" align="left"><b>PASSWORD:</b></TD>');
HTP.p (
' <TD align="left"><INPUT TYPE="password" NAME="p_password" style="height:25px;width: 170px;" value="" required autocomplete="off"></TD>');
HTP.p ('</TR>');
HTP.p ('<TR><TD></TD></TR>');
HTP.p ('</TABLE>');
HTP.p ('<br>');
HTP.p (
' <INPUT TYPE="submit" VALUE="Login"><INPUT TYPE="reset" VALUE="Clear">');

HTP.formClose;
HTP.p ('<br>');
HTP.PRINT (
'
<font color="#ff0000"><sup>*</sup><i>Please login with your oracle credentials</i>');

HTP.p ('</BODY>');
EXCEPTION
WHEN OTHERS
THEN
HTP.PRINT (
'There are errors while processing the program. Please contact tecnical team'
|| SQLERRM);
END PA_gl_login;

--
--#############################################################################
--# Name : validate_login
--# Description : This procedure validates the login details

--#############################################################################
PROCEDURE Rejection_validate_data (p_username IN VARCHAR2,
p_password IN VARCHAR2)
IS
BEGIN
IF fnd_user_pkg.ValidateLogin (UPPER (p_username), p_password) = TRUE
THEN
XXX_FND_WEB_PKG.Rejection_details (UPPER (p_username));
ELSE
HTP.p ('<h1 align="center">Login failed!</h1>');
HTP.P (
'<p align="center"> Please enter a valid Username and Password.</p>');
HTP.p (
'<p align="center"><A HREF="XXX_fnd_web_pkg.pa_gl_login">Click here</A>');
HTP.p ('To Login Again.</p>');
END IF;
EXCEPTION
WHEN OTHERS
THEN
HTP.PRINT (
'Error occured while processing the program. Please contact technical team.'
|| SQLERRM);
END Rejection_validate_data;

PLSQL code to create a web page using mod_plsql / htp/htp packages

PROCEDURE XXXXXXXXXX (p_username IN VARCHAR2)
IS
v_title VARCHAR2 (50) := 'XXX PAGL REJECTIONS ('||sys_context('USERENV','DB_NAME')||')';
v_cnt VARCHAR2 (200) := 0;
parameter LONG;

CURSOR reject_rec
IS
SELECT JPGRT.*
FROM XXX_PA_GL_REJECTION_TAB JPGRT,
PA_COST_DISTRIBUTION_LINES_ALL PCDLA
WHERE JPGRT.V_EXPENDITURE_ITEM_ID = PCDLA.EXPENDITURE_ITEM_ID
AND JPGRT.Approver LIKE '%' || p_username || '%'
AND JPGRT.resolved_flag = 'N'
ORDER BY JPGRT.org_id;

BEGIN
-- HTP.P ('<!DOCTYPE html>');
HTP.p ('<TITLE>' || v_title || '</TITLE>');
HTP.p ('<head>');
HTP.p ('<STYLE TYPE="text/css">');
HTP.P (
'TH{font-family: Arial; font-size: 9pt; background-color:lightblue;}');
HTP.p ('TD{font-family: Arial; font-size: 9pt; font-weight: normal;}');
---------------->
HTP.p('
a {text-decoration:none;}');
HTP.p ('</STYLE>');
HTP.p ('</head>');
HTP.p ('<BODY bgcolor="#FFFFFF">');
HTP.p ('<CENTER>');
HTP.p (' <H1 align="center" bgcolor="#E6E6FA">' || v_title || '</H1>');

HTP.p ('<hr>');
HTP.p (
'<table width = 100%><tr><td align = left><font color="#ff0000"><sup>*</SUP><i>Choose only when you want to approve for any one of the proposed fix.</i></td><td align = right><font color="#ff0000"><sup>*</SUP><i>Enter key words related to your search (Example: ORG_ID, EMPLOYEE NUMBER etc.,)</i></td></tr></table>');

HTP.p (
'<form id="testForm" enctype="multipart/form-data" method = "post">');

--- HTP.p ('<CENTER>');
-- Title bar
-- Parameter block

HTP.p (
'<table width = 100% ><tr><td align= left><INPUT TYPE="checkbox" class="checkbox1" autocomplete="off" name="checked"> UPDATE ATTRIBUTE4
<INPUT TYPE="checkbox" class="checkbox2" autocomplete="off" name="checked"> UPDATE EXPENDITURE DATE

</td><td align= right>Filter:
<input type=text style="width: 200px;" placeholder="Enter text to search. . .    " id="searchTerm" class="search_box" onkeyup="doSearch()" />
</td></tr></table>');
------------------------------------------
HTP.p (
'<TABLE id="Table001" width=100% border=1 cellpadding="0" cellspacing="0">');

HTP.p ('<thead><TR><Th align="center"></Th>');
-------- HTP.p ('<Th align="center"><b>ORG_ID</b></Th>');
HTP.p ('<Th align="center">EMPLOYEE<br>NUMBER</Th>');
HTP.p ('<Th align="center">EMPLOYEE<br>FULLNAME</Th>');
HTP.P ('<Th align="center">EXPENDITURE_ITEM_ID</Th>');
HTP.P ('<Th align="center">CURRENT ATTRIBUTE4</Th>');
HTP.P ('<Th align="center">PROPOSED ATTRIBUTE4</Th>');
HTP.P ('<Th align="center">OLD_EXPENDITURE_DATE</Th>');
HTP.P ('<Th align="center">NEW_EXPENDITURE_DATE</Th>');
----- HTP.P ('<Th align="center">COMMENTS</Th>');
HTP.p ('</TR></thead>');
HTP.p ('<tbody>');

FOR i IN reject_rec
LOOP
---- p_context

HTP.p ('<TR>');
HTP.p (
' <TD align="center"><INPUT TYPE="checkbox" class="checkbox" autocomplete="off" name='
|| i.v_expenditure_item_id
|| '></TD>');

------------- HTP.p ('<TD align="center"><b>' || i.ORG_ID || '</b></TD>');
HTP.p ('<TD align="center"><b>' || i.V_EMP_ID || '</b></TD>');
HTP.p ('<TD align="center"><b>' || i.EMPLOYEE_NAME || '</b></TD>');
HTP.P (
' <Td align="center"><b><a class=text href="XXX_FND_WEB_PKG.Transaction_details?p_expenditure_item_id='
|| i.V_EXPENDITURE_ITEM_ID||'&p_org_id='||i.org_id
|| '" target="_blank;" style="text-decoration:none;">'
|| i.V_EXPENDITURE_ITEM_ID
|| '</a></b> </TD>');

HTP.P (
' <Td align="center"><b>'
|| NVL (TO_CHAR (i.OLD_ATTRIBUTE_VALUE), ' ')
|| '</b></TD>');
HTP.P (
' <Td align="center"><b>'
|| NVL (TO_CHAR (i.NEW_ATTRIBUTE_VALUE), ' ')
|| '</b></TD>');
HTP.P (
' <Td align="center"><b>'
|| NVL (TO_CHAR (i.OLD_EXPENDITURE_DATE,'DD-MON-YYYY'), ' ')
|| '</b></TD>');
HTP.P (
' <Td align="center"><b>'
|| NVL (TO_CHAR (i.NEW_EXPENDITURE_DATE,'DD-MON-YYYY'), ' ')
|| '</b></TD>');

------ HTP.p ('<TD align="center"><b>' || i.COMMENTS || '</b></TD>');
HTP.p ('</TR>');

v_cnt := 1;
END LOOP;

HTP.p ('</tbody></TABLE>');

-- HTP.p (
--- ' <TD align="left"><INPUT TYPE="INTEGER" NAME="parameter" style="height:20px;width: 170px;" value=" " autocomplete="off" ></TD>');

IF v_cnt = 0
THEN
HTP.p ('<br>');
HTP.PRINT ('<font size=5>There are no rejections to display');
ELSE
HTP.p ('<br>');
HTP.p (
'<input type="button" onclick= "submitfn()" width="50px" value=Approve></button>');

HTP.p (
'<input type="button" onclick= "submitnn()" width="50px" value=Reject></button>');
HTP.p ('<input type="hidden" id="refreshed" value="no">'); ------formaction="XXX_FND_WEB_PKG.pa_gl_Reject_Rejections" Value = "Reject"
END IF;

HTP.formClose;

HTP.P ('<form id="testForm1" method = "post">');
HTP.p (
'<INPUT TYPE="hidden" NAME="p_username" required autocomplete="off" value='
|| p_username
|| '>');
HTP.p ('<INPUT TYPE="hidden" name="P1" id="P1_id">');
HTP.p ('<INPUT TYPE="hidden" name="P2" id="P2_id">');
HTP.p ('<INPUT type="hidden" name="parameter" id="parameter_id">');
HTP.p ('</form>');

HTP.script (
'
function doSearch() {
var searchText = document.getElementById("searchTerm").value;
var targetTable = document.getElementById("Table001");
var targetTableColCount;

for (var rowIndex = 0; rowIndex < targetTable.rows.length; rowIndex++) {
var rowData =" ";

if (rowIndex == 0) {
targetTableColCount = targetTable.rows.item(rowIndex).cells.length;
continue;
}

for (var colIndex = 0; colIndex < targetTableColCount; colIndex++) {
rowData += targetTable.rows.item(rowIndex).cells.item(colIndex).textContent;
}

if (rowData.indexOf(searchText) == -1)
targetTable.rows.item(rowIndex).style.display = "none";
else
targetTable.rows.item(rowIndex).style.display = "table-row";
}
}

if (!document.getElementsByClassName) {
document.getElementsByClassName = function (cn) {
var rx = new RegExp("(?:^|\\s)" + cn+ "(?:$|\\s)");
var allT = document.getElementsByTagName("*"), allCN = [],ac="", i = 0, a;
while (a = allT[i=i+1]) {
ac=a.className;
if ( ac && ac.indexOf(cn) !==-1) {
if(ac===cn){ allCN[allCN.length] = a; continue; }
rx.test(ac) ? (allCN[allCN.length] = a) : 0;
}
}
return allCN;
}
}

function submitfn() {
var boxes = document.getElementsByClassName("checkbox");
var box1 = document.getElementsByClassName("checkbox1");
var box2 = document.getElementsByClassName("checkbox2");
var checked = [];
var checked1 = [];
var checked2 = [];
for(var i=0; boxes[i]; ++i){
if(boxes[i].checked){
checked.push(boxes[i].name);
}
}
for(var i=0; box1[i]; ++i){
if(box1[i].checked){
checked1.push(box1[i].name);
}
}
for(var i=0; box2[i]; ++i){
if(box2[i].checked){
checked2.push(box2[i].name);
}
}

var parameter = checked.join();
var P1 = checked1.join();
var P2 = checked2.join();
form=document.getElementById("testForm1");
document.getElementById("parameter_id").value = parameter;
document.getElementById("P1_id").value = P1;
document.getElementById("P2_id").value = P2;
form.action ="XXX_FND_WEB_PKG.pa_gl_update_Rejections";

if (confirm("Do you want to submit")) {
form.submit();
}
else
{
return false;
}
}

function submitnn() {
var boxes = document.getElementsByClassName("checkbox");
var box1 = document.getElementsByClassName("checkbox1");
var box2 = document.getElementsByClassName("checkbox2");
var checked = [];
var checked1 = [];
var checked2 = [];
for(var i=0; boxes[i]; ++i){
if(boxes[i].checked){
checked.push(boxes[i].name);
}
}
for(var i=0; box1[i]; ++i){
if(box1[i].checked){
checked1.push(box1[i].name);
}
}
for(var i=0; box2[i]; ++i){
if(box2[i].checked){
checked2.push(box2[i].name);
}
}

var parameter = checked.join();
var P1 = checked1.join();
var P2 = checked2.join();
form=document.getElementById("testForm1");
document.getElementById("parameter_id").value = parameter;
document.getElementById("P1_id").value = P1;
document.getElementById("P2_id").value = P2;
form.action ="XXX_FND_WEB_PKG.pa_gl_Reject_Rejections";




if (confirm("Do you want to submit")) {
form.submit();
}
else
{
return false;
}
}',
'javascript');

HTP.p ('</BODY>');
EXCEPTION
WHEN OTHERS
THEN
HTP.PRINT (
'Error occured while processing the program. Please contact technical team.'
|| SQLERRM);
END XXXXXXXXXX ;

Wednesday 30 November 2016

sending Notification/alert email in oracle with comma separated list of users as parameter with to address and cc address

--#############################################################################
--# Name : p_tprint
--# Description : This procedure is to send notification emails
--#
--# History
--# Date Author Ver. Comments
--# ----------- ----------------- ---- ----------------------------------------
--#############################################################################
PROCEDURE NotificationMail (p_recipent_email_id VARCHAR2, p_message VARCHAR2)
IS
v_sender_email_id VARCHAR2 (100) := 'Oracle-Support.CG-EUR@nuthan.com';---sender mail id
v_cc_email_id VARCHAR2 (100) := 'nuthanmechanical@gmail.com ';
v_subject VARCHAR2 (50) := '****Alert mail*****';
v_hostname VARCHAR2 (50) := 'webmail.nuthan.com';--server address
v_mail_conn UTL_SMTP.connection;
v_crlf VARCHAR2 (10) := CHR (13) || CHR (10);---you ca use utl_tcp as well
v_email_list VARCHAR2 (4000);
v_separator VARCHAR2 (1) := ',';
BEGIN
BEGIN
v_mail_conn := UTL_SMTP.open_connection (v_hostname, 25);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
' Error while creating connection Error: ' || SQLERRM);
END;

BEGIN
UTL_SMTP.helo (v_mail_conn, v_hostname);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
' Error while saying Helo to server Error: ' || SQLERRM);
END;

BEGIN
UTL_SMTP.mail (v_mail_conn, v_sender_email_id);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
' Error while creating mail Error: ' || SQLERRM);
END;

BEGIN
v_email_list := p_recipent_email_id;

LOOP
IF (INSTR (v_email_list,
v_separator,
1,
1) > 0)
THEN
UTL_SMTP.rcpt (v_mail_conn,
SUBSTR (v_email_list,
1,
INSTR (v_email_list,
v_separator,
1,
1)
- 1));
v_email_list :=
SUBSTR (v_email_list,
INSTR (v_email_list,
v_separator,
1,
1)
+ 1);
ELSE
UTL_SMTP.rcpt (v_mail_conn, v_email_list);
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
' Error while adding recipients Error: ' || SQLERRM);
END;

UTL_SMTP.open_data (V_mail_conn);
UTL_SMTP.write_data (v_mail_conn,
'From :' || v_sender_email_id || v_crlf);
UTL_SMTP.write_data (v_mail_conn, 'Subject :' || v_subject || v_crlf);

UTL_SMTP.write_data (v_mail_conn,
'To :' || p_recipent_email_id || v_crlf);
UTL_SMTP.write_data (
v_mail_conn,
'Content-Type: text/html; charset="UTF-8"'
|| UTL_TCP.crlf
|| UTL_TCP.crlf);

UTL_SMTP.write_data (v_mail_conn, p_message || v_crlf);

UTL_SMTP.close_data (v_Mail_Conn);
UTL_SMTP.quit (v_mail_conn);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
'Error occured while processing sendmail program' || SQLERRM);
END NotifiactionMail;