oracle tips & tricks

Oracle Tips & Tricks

 
Monday, 17 March 2008
Oracle Package Initialization
Did you notice that Oracle PL/SQL packages can have an initialization section? The initialization part of a package is run only once, the first time you reference the package. Here is an example:
CREATE PACKAGE emp_actions AS
/* Declare externally callable subprograms. */
FUNCTION hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr REAL,
sal REAL,
comm REAL,
deptno REAL) RETURN INT;
END emp_actions;

CREATE PACKAGE BODY emp_actions AS
number_hired INT; -- visible only in this package

/* Fully define subprograms specified in package. */
FUNCTION hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr REAL,
sal REAL,
comm REAL,
deptno REAL) RETURN INT IS
new_empno INT;
BEGIN
SELECT empno_seq.NEXTVAL INTO new_empno
FROM dual;
INSERT INTO emp VALUES (new_empno, ename, job,
mgr, SYSDATE, sal, comm, deptno);
number_hired := number_hired + 1;
RETURN new_empno;
END hire_employee;

BEGIN -- initialization part starts here
INSERT INTO emp_audit
VALUES (SYSDATE, USER, 'EMP_ACTIONS');
number_hired := 0;
END emp_actions;


The initialization part of a package is run just once, the first time you reference the package. So, in the last example, only one row is inserted into the database table emp_audit. Likewise, the variable number_hired is initialized only once.

Every time the procedure hire_employee is called, the variable number_hired is updated. However, the count kept by number_hired is session specific. That is, the count reflects the number of new employees processed by one user, not the number processed by all users.

Labels:

posted by popo @ 23:53   0 comments
Monday, 10 March 2008
Using rsync to Copy Oracle Home Directories
If you're using UNIX for your ORACLE database server, it is possible to copy the Oracle Database Software (the Oracle Home directory) from one server to another using rsync.

Here is the example rsync syntax to copy the Oracle Home from one server (svr1) to another server (svr2). This process will ensure that all symbolic links and files owned by root get copied over correctly. Login to the source database server (svr1) as the root user account and run the following:

[root@svr1 ~]# rsync -auvzpogl -e ssh /u01/app/oracle/product/10.2.0/db_1/ svr2:/u01/app/oracle/product/10.2.0/db_1/

Labels:

posted by popo @ 23:17   0 comments
Sending E-Mail in PL/SQL
Here some example code you can use for sending email from within PL/SQL
CREATE OR REPLACE PROCEDURE SEND_MAIL_TCP (
msg_from VARCHAR2 := 'sender@testing.com'
, msg_to VARCHAR
, msg_subject VARCHAR2 := 'E-Mail message from your database'
, msg_text VARCHAR2 := ''
)
IS
c UTL_TCP.CONNECTION;
rc INTEGER;
BEGIN
c := UTL_TCP.OPEN_CONNECTION('localhost', 25); -- open the SMTP port 25 on local machine
rc := UTL_TCP.WRITE_LINE(c, 'HELO localhost');
rc := UTL_TCP.WRITE_LINE(c, 'MAIL FROM: '||msg_from);
rc := UTL_TCP.WRITE_LINE(c, 'RCPT TO: '||msg_to);
rc := UTL_TCP.WRITE_LINE(c, 'DATA'); -- Start message body
rc := UTL_TCP.WRITE_LINE(c, 'Subject: '||msg_subject);
rc := UTL_TCP.WRITE_LINE(c, '');
rc := UTL_TCP.WRITE_LINE(c, msg_text);
rc := UTL_TCP.WRITE_LINE(c, '.'); -- End of message body
rc := UTL_TCP.WRITE_LINE(c, 'QUIT');
UTL_TCP.CLOSE_CONNECTION(c); -- Close the connection
EXCEPTION
WHEN others THEN
RAISE_APPLICATION_ERROR(-20000, 'Unable to send e-mail message from PL/SQL routine.');
END;

Labels:

posted by popo @ 23:12   0 comments
 
Search

Google
Previous Post
Category
Blogrush
Blogroll
Legal
Archives
Template by
Free Blogger templates