1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Example of Oracle Supplied Package

Oracle Supplied Package
To support various processes Oracle provides in-built packages in PL/SQL and Java. All the packages can be executed from the normal user logins by granting the public rights on them.
DBMS_SESSION
This is package that allows you to use ALTER SESSION and SET ROLE commands into programs. The example show you how to grant a role R1 which has the privileges to create a tablespaces
Before you start let us know certain details regarding our database. The following command will display the databasename.
SQL> SHOW PARAMETER DB_NAME
NAME TYPE VALUE
------------------------------------ ----------- -------------------
db_name string pune

SQL> SELECT STATUS FROM V$INSTANCE;
The result is as follows :
STATUS
------------
OPEN

To list the DBA user details you can issue the following command.
SQL> SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SYS OPEN
SYSTEM OPEN
OUTLN OPEN
DBSNMP OPEN

Now let us create a Role using a package PACK1
SQL> CREATE ROLE R1 IDENTIFIED USING PACK1;
Now let us grant the create TABLSPACE privilege to the newly created Role R1
SQL> GRANT CREATE TABLESPACE TO R1;
Now let us create the Package PACK1 as follows
SQL>
1 CREATE OR REPLACE PACKAGE PACK1
2 AUTHID CURRENT_USER
3 IS
4 PROCEDURE PROC1;
5 END;
6 /

SQL> CREATE OR REPLACE PACKAGE BODY PACK1
2 IS
3 PROCEDURE PROC1
4 IS
5 V1 VARCHAR2(20);
6 V2 VARCHAR2(20):='TUESDAY';
7 BEGIN
8 SELECT TO_CHAR(SYSDATE,'DAY') INTO V1 FROM DUAL;
9 IF TRIM(V1)=TRIM(V2) THEN
10 DBMS_SESSION.SET_ROLE('R1');
11 DBMS_OUTPUT.PUT_LINE('****** WORK DONE ******');
12 ELSE
13 DBMS_OUTPUT.PUT_LINE('****** TODAY IS NOT A TUESDAY ****');
14 END IF;
15 END;
16 END;
17    /

Once the package is created, now let us login as normal user SCOTT and try creating TABLESPACE.
SQL> CONN SCOTT/TIGER
SQL> CREATE TABLESPACE T12
2 DATAFILE 'C:\T12.DBF' SIZE 1M;
The Create TABLESPACE command fails as SCOTT is normal user and has no privileges. Now let us connect as SYS user and grant the privileges on the package PACK1 and then retry the same thing.

SQL> CONN / AS SYSDBA
SQL> GRANT EXECUTE ON SYS.PACK1 TO SCOTT;
SQL> CONN SCOTT/TIGER
SQL> CREATE TABLESPACE T12
2 DATAFILE 'C:\T12.DBF' SIZE 1M;


The command still fails, Now let us try the same thing using the package.

SQL> EXEC SYS.PACK1.PROC1;
SQL> CREATE TABLESPACE T12
2 DATAFILE 'C:\T12.DBF' SIZE 1M;

Now you are able to create the same. You can check whether the role is enabled or not. You can do this by trying the following procedure.

SQL> Declare
2 a boolean;
3 begin
4 a:= dbms_session.IS_ROLE_ENABLED('R1');
5 if a=true then
6 dbms_output.put_line('Role R1 enabled');
7 else
8 dbms_output.put_line('Role R1 not enabled');
9 end if;
10 end;
11 /
The result will describe whether the Role R1 is Enabled or Not. The package gives you a lot of options too.
dbms_lock Package
These routines allow the user to request, convert and release locks. The locks are managed by the rdbms lock management services. All lock ids are prepended with the 'UL' prefix so that they cannot conflict with DBMS locks. These locks will show up in the SQL*DBA lock monitor screen and in the appropriate fixed views.
Deadlock detection is performed on these locks. Locks are automatically released when the session terminates. It is up to the clients to agree on the use of these locks. The lock identifier is a number in the range of 0 to 1073741823.
The allocate_unique call can be used to allocate a unique lockid (in the range of 1073741824 to 1999999999) given a lock name. This is provided since it may be easier for applications to coordinate their use of locks based on lock names rather than lock numbers. The first session to call allocate_unique with a new lock name will cause a unique lockid to be generated and stored in the dbms_lock_allocated table. Subsequent calls (usually by other sessions) will return the lockid previously generated. A lock name will be associated with the returned lockid for at least 'expiration_secs' (defaults to 10 days) past the last call to allocate_unique with the given lock name. After this time, the row in the dbms_lock_allocated table for this lock name may be deleted in order to recover space. Allocate_unique performs a commit.
A sleep procedure is also provided which causes the caller to sleep for the given interval.
LIMITATIONS
The implementation does not support large numbers of locks efficiently. A few hundred locks per session should be the limit.
SECURITY
There may be OS-specific limits on the maximum number of total locks available. You will need to consider this when using locks, or making this package available to users. You may wish to only grant execute to those users or roles that you trust. An alternative is to create a cover package for this package which limits those locks used. Then, instead of granting execute on this package to public, grant execute on the cover package only to specific users.
UTL_SMTP Package
This package provides SMTP client-side access functionality in PL/SQL. With this package, a PL/SQL program can send electronic mails via SMTP. This package does not allow the PL/SQL program to receive e-mails via SMTP. The user of this package should be familiar with the SMTP protocol as defined in RFC 821 and RFC 1869.
This package is meant to provide an API to SMTP protocol directly. Users may find it useful to define additional helper routines to encapsulate the interaction with a SMTP server.
A SMTP connection is initiated by a call to open_connection, which returns a SMTP connection. After a connection is established, the following calls are required to send a mail:
1.    helo() - identify the domain of the sender
2.    mail() - start a mail, specify the sender
3.    rcpt() - specify the recipient
4.    open_data() - start the mail body
5.    write_data() - write the mail body (multiple calls allowed)
6.    close_data() - close the mail body and send the mail
The SMTP connection is closed by calling quit().
Example
create or replace procedure send_mail(Sender in varchar2,Recipient in varchar2)
is
v_connection UTL_SMTP.CONNECTION;
db varchar(200);
Message VARCHAR2(32767);
crlf VARCHAR2(2):=chr(13)||chr(10);
Subj varchar2(100);
BODY varchar2(100):=' work done ';
BEGIN
select ora_database_name into db from dual;
select ' Backup done for the database :- '||db||' at '||to_char(sysdate,'dd-mon-yy:hh:mi:ss am') into subj from dual;
v_connection := UTL_SMTP.OPEN_CONNECTION('mail.sqlstar.com',25);
UTL_SMTP.HELO(v_connection,'mail.sqlstar.com');
UTL_SMTP.MAIL(v_connection,'pratap.ghogare@sqlstar.com');
UTL_SMTP.RCPT(v_connection,'pratap.ghogare@sqlstar.com');
Message :='Date: '||to_char(sysdate,'DD-MON-YYYY HH:MI PM')||crlf||
'From: '||sender||crlf||
'To: '||recipient||crlf||
'Subject: '||subj||crlf||
' '||crlf||
BODY;
--UTL_SMTP.DATA(v_Connection,'MIME-Version: 1.0'||crlf||'Content-type: text/html' || crlf||Message);
UTL_SMTP.DATA(v_connection,' Backup done for the database :- '||db||' at '||to_char(sysdate,'dd-mon-yy:hh:mi:ss am'));
UTL_SMTP.QUIT(v_connection);
END;
/

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy