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;

/

Advertisements

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!

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!

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!