307-310

Previous Table of Contents Next


Using The DBMS_SQL Package

A common request from users is the ability to change their password. Unfortunately, Oracle does not allow a user to change an account password without having the ALTER USER privilege. This privilege also allows users to change information other than the password and for any user. So, unless you want to grant this level of access to users, the DBA has the responsibility of assigning and changing passwords.

Let s use the DBMS_SQL package to create a Change_Password() function, which the DBA can compile and make accessible to all users. This function can be called from a system s front end, usually via a button on a form or a menu item, to allow the user to change a password (and only a password). The Change_Password() function is shown in Listing 9.3.

Listing 9.3 The Change_Password() function.

 CREATE OR REPLACE FUNCTION Change_Password (vUsername IN    varchar2,                           vPassword IN    varchar2) RETURN integer IS    iCursorID           integer;    vCommand            varchar2 (80);    iReturned           integer;    xMISSING_PARAMETER  EXCEPTION; BEGIN    IF (vUserName IS NULL OR vPassword IS NULL) THEN       RAISE xMISSING_PARAMETER;    END IF;    vCommand := 'ALTER USER '                    vUsername                        'identified by '                 vPassword;    iCursorID := DBMS_SQL.Open_Cursor;    DBMS_SQL.Parse (iCursorID,                    vCommand,                    DBMS_SQL.v7);    iReturned := DBMS_SQL.Execute (iCursorID);    DBMS_SQL.Close_Cursor (iCursorID);    RETURN 1; EXCEPTION    WHEN OTHERS THEN         RETURN 0; END Change_Password; / 

This function must be run by a user who has the ALTER USER privilege. In this implementation, the function allows any user s password to be changed. This particular implementation assumes that you will pass only the login name of the current user as a parameter.

The function first builds the proper SQL*Plus command and stores it in the vCommand string. The function then calls functions and procedures in the DBMS_SQL package to parse and execute the command.

This function requires a username and password to be passed as parameters. If the user s password is successfully changed, the function returns 1. Otherwise, the function returns 0.

DBMS_Utility

There is one especially useful function in the DBMS_Utility package that merits attention. This is the Get_Time() function, which returns the current time in hundredths of seconds. If you use this function to implement time-sensitive code, make sure you are prepared for the value to roll over to 0.

UTL_File

The UTL_File package, implemented with Oracle 7.3, provides server-side file I/O to text files. There are several basic steps required to do file I/O with the package:

1.     Open one or more files for reading or writing using the FOpen() function.
2.    Read data from a file using the Get_Line() procedure or write data to a file using the Put_Line() , PutF() , Put() , and/or New_Line() procedures.
3.    Close files using the FClose() or FClose_All() procedures.

The package contains the following 10 procedures and functions:

   The FClose() procedure
   The FClose_All() procedure
   The FFlush() procedure
   The FOpen() function
   The Get_Line() procedure
   The Is_Open() function
   The New_Line() procedure
   The Put() procedure
   The PutF() procedure
   The Put_Line() procedure

Each of these procedures and functions is described in detail in the following sections. There is also a brief example of using the UTL_File package.

The FClose() Procedure

The FClose() procedure is provided to close a text file after you have finished reading data. The procedure definition is as follows :

 PROCEDURE FClose (file_handle IN OUT FILE_TYPE) 

The file_handle parameter is a pointer to an open file. This parameter is of type FILE_TYPE , a user-defined datatype in the package spec for the UTL_File package.

The FClose_All() Procedure

The FClose_All() procedure is used to close all files that are currently open. This is commonly used for cleaning up during error handling or shutdown operations. The procedure accepts no parameters.

All changes to a file are flushed from the buffer before the file is closed.

The FFlush() Procedure

The FFlush() procedure is used to force all buffered changes to files to be written to the files immediately. The procedure definition is as follows:

 PROCEDURE FFlush (file_handle IN     FILE_TYPE) 

The FOpen() Function

The FOpen() function is used to open a text file so that data can be read from or written to the file. The function definition is as follows:

 FUNCTION FOpen (location  IN     varchar2,                 filename  IN     varchar2,                 open_mode IN     varchar2) 

The location parameter identifies the directory where the file is located or where the file is to be created. The values for this parameter are constrained by an init.ora parameter. Check with your DBA to find out precisely which directories you can use with this parameter.

The open_mode parameter determines how the package will handle contents of an existing file. The values for this parameter are shown in Table 9.6.

Table 9.6 Values for the open_mode parameter of the FOpen ( ) function.

Parameter Value Interpretation
a Append text to an existing file.
r Read text from an existing file.
w Write text to a new file, or write over an existing file.


Previous Table of Contents Next


High Performance Oracle Database Automation
High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL
ISBN: 1576101525
EAN: 2147483647
Year: 2005
Pages: 92

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net