Category Archives: Oracle

Bangla 3:51 pm on August 10, 2015
Tags: Create Job,   

Oracle: Create a Job Script

Hi,

Below is the syntax for creating a JOB in Oracle


CREATE OR REPLACE PROCEDURE MySchema.Create_A_JOB

AS

BEGIN

DECLARE

X NUMBER;

BEGIN

SYS.<strong><em>DBMS_JOB.SUBMIT</em></strong>

( job       => X

,what      => 'MySchema.Call_The_Procedure(''10'');'

,next_date => to_date('15/04/2015 13:35:00','dd/mm/yyyy hh24:mi:ss')

,interval  => 'SYSDATE + 1'

,no_parse  => FALSE

);

SYS.<strong><em>DBMS_OUTPUT.PUT_LINE</em></strong>('Job Number is: ' || to_char(x));

SYS.<strong><em>DBMS_JOB.BROKEN</em></strong>

(job    => X,

broken => TRUE);

COMMIT;

END;

END;

/
Advertisement
Bangla 2:05 pm on July 24, 2015
Tags: Cursors, , Procedures   

ORACLE: Looping through data using CURSORS

Below the syntax for creating the cursors in Oracle. This example also demostrates creating of procedures in Oracle


CREATE OR REPLACE PROCEDURE MYSCHEMA.GET_EMAILS AS

 EMAIL_ID VARCHAR2(100);
 
 CURSOR CUR IS SELECT EMAIL_ADDRESS FROM MYSCHEMA.USERS; /* DEFINE CURSOR */
 
BEGIN
 
 OPEN CUR; /* OPEN THE CURSOR */
 
 LOOP /* LOOP */
 
 FETCH CUR INTO EMAIL_ID; /* READ THE CURSOR VALUE */
 
 EXIT WHEN CUR%NOTFOUND; /* EXIT WHEN DATA NOT FOUND */
 
 DBMS_OUTPUT.PUT_LINE(EMAIL_ID); /* PRINT THE VALUE / PROCESS ACCORDING TO YOUR NEED */
 
 
 END LOOP; /* END OF LOOP */
 
 CLOSE CUR; /* FINALLY RELASE/CLOSE THE CURSOR */
END;
/

Happy Kooding.. Hope this helps!

Bangla 6:41 pm on June 26, 2015
Tags: Clob, Email,   

Send Email from Oracle

Below is the procedure used to send email from Oracle. This proc takes email content in varchar datatype or clob datatype.


CREATE OR REPLACE PROCEDURE myschema.send_email(
p_from in varchar2,
p_to in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null,
p_smtp_hostname in varchar2,
l_clob_html IN CLOB DEFAULT NULL)
is
l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
l_connection utl_smtp.connection;
l_body_html clob := empty_clob; --This LOB will be the email message
l_offset number;
l_ammount number;
l_temp varchar2(32767) default null;
begin
l_connection := utl_smtp.open_connection( p_smtp_hostname);
utl_smtp.helo( l_connection, p_smtp_hostname );
utl_smtp.mail( l_connection, p_from );
utl_smtp.rcpt( l_connection, p_to );

l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) ||
chr(10);

----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary( l_body_html, false, 10 );
dbms_lob.write(l_body_html,length(l_temp),1,l_temp);

----------------------------------------------------
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || chr(13)||chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

----------------------------------------------------
-- Write the plain text portion of the email
if p_text is not null and p_text != ' ' then
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);
end if;

----------------------------------------------------
-- Write the HTML boundary
l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/html;' ||
chr(13) || chr(10) || chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

----------------------------------------------------
-- Write the HTML portion of the message

if p_html is not null and p_html != ' ' then
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);
end if;

if l_clob_html is not null then
l_offset := DBMS_LOB.GETLENGTH(l_body_html) + 1;
DBMS_LOB.COPY(L_BODY_HTML, l_clob_html, DBMS_LOB.GETLENGTH(l_clob_html), L_OFFSET, 1);
end if;

----------------------------------------------------
-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
--DBMS_OUTPUT.put_line (l_body_html);

----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
while l_offset &lt; dbms_lob.getlength(l_body_html) loop
utl_smtp.write_data(l_connection,
dbms_lob.substr(l_body_html,l_ammount,l_offset));
l_offset := l_offset + l_ammount ;
l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
end loop;
utl_smtp.close_data(l_connection);
utl_smtp.quit( l_connection );
dbms_lob.freetemporary(l_body_html);
commit;
end;
/

Happy Kooding… Hope this helps!

Bangla 1:32 pm on June 18, 2015
Tags: 2nd highest salary, dense rank, nth highest salary, , row_number   

ORACLE: QUERY TO FIND Nth HIGHEST SALARY (in all or in each DEPARTMENT)

Below query will give you N highest salary wrt all Departments


SELECT * FROM
(SELECT EMP.*, ROW_NUMBER() OVER (ORDER BY SALARY DESC) RN FROM EMPLOYEE EMP) EMP_RANK
WHERE EMP_RANK.RN = N;

Below query will give you N highest salart wrt to each department


SELECT * FROM

(SELECT EMP.*, DENSE_RANK() OVER(PARTITION BY DEPT_ID ORDER BY SALARY) RN
FROM EMPLOYEE EMP) EMP_RANK

WHERE EMP_RANK.RN = N;

Happy Kooding… Hope this helps!