execute immediate 'sql-statement'; execute immediate 'select-statement' into returned_1, returned_2..., returned_n; execute immediate 'sql-statement' using [in|out|in out] bind_var_1, [in|out|in out] bind_var_2 ... [in|out|in out] bind_var_n; execute immediate 'select-statement' into returned_1, returned_2..., returned_n using [in|out|in out] bind_var_1, [in|out|in out] bind_var_2 ... [in|out|in out] bind_var_n; execute immediate 'sql-statement' returning into var_1; execute immediate 'sql-statement' bulk collect into index-by-var; http://www.adp-gmbh.ch/ora/plsql/exec_immediate.html
October 22, 2008
Execute Immediate
September 9, 2008
Why am I getting errors when I try to use the utl_file package to write a flat file ?
The following is an example procedure to dump a table in one of my schemas to a flat file on the server.
CREATE OR REPLACE PROCEDURE EmployeeFlatFile
IS
file_id UTL_FILE.FILE_TYPE;
BEGIN
file_id := utl_file.FOPEN( '/temp', 'test.txt', 'w' );
FOR emp IN (SELECT employee_name FROM employee)
LOOP
utl_file.PUT_LINE( file_id, emp.employee_name );
END LOOP;
utl_file.fCLOSE(file_id);
END;
/
When I run it, I get the following not very helpful error message :
SQL> execute EmployeeFlatFile; BEGIN EmployeeFlatFile; END; * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SYS.UTL_FILE", line 98 ORA-06512: at "SYS.UTL_FILE", line 157 ORA-06512: at "CMDB.EMPLOYEEFLATFILE", line 5 ORA-06512: at line 1
The first thing I need to do is insert some exception handling so that I can see exactly which of the user defined exceptions the UTL_FILE package is raising.
CREATE OR REPLACE PROCEDURE EmployeeFlatFile
IS
file_id UTL_FILE.FILE_TYPE;
BEGIN
file_id := utl_file.FOPEN( '/temp', 'test.txt', 'w' );
FOR emp IN (SELECT employee_name FROM employee)
LOOP
utl_file.PUT_LINE( file_id, emp.employee_name );
END LOOP;
utl_file.fCLOSE(file_id);
EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
WHEN utl_file.write_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');
END;
/
Now when I run it, I get a more meaningful error message. If you are having UTL_FILE problems, then I suggest that you add the exception handling above to convert the not very helpful errors in the original version of my procedure to something like the following :
SQL> execute EmployeeFlatFile; BEGIN EmployeeFlatFile; END; * ERROR at line 1: ORA-20001: utl_file.invalid_path ORA-06512: at "CMDB.EMPLOYEEFLATFILE", line 13 ORA-06512: at line 1
Now it all becomes clear – I have an invalid path specified. At least I know where to go to look for the cause(s) of the problem. UTL_FILE is very sensitive to its setup.
Setting up UTL_FILE
Up to Oracle 9.0.1 or 9i Release 1
set the UTL_FILE_DIR initialisation parameter in initSID.ora. You can have a single parameter with all allowed directories :
UTL_FILE_DIR = (/tmp, /oracle/oracle817/admin/CMDB/utl_file)
or a list :
UTL_FILE_DIR = /tmp UTL_FILE_DIR = /oracle/oracle817/admin/CMDB/utl_file
Warning : You cannot have any other parameters between the two. If you do, then only the last directory will be used, the first will not be valid. The following is wrong :
UTL_FILE_DIR = /tmp DB_BLOCK_SIZE = 8192 UTL_FILE_DIR = /oracle/oracle817/admin/CMDB/utl_file
In this case, only /oracle/oracle817/admin/CMDB/utl_file will be acceptable. If you try to use /tmp, you’ll get an invalid path exception thrown.
Once you have changed the initSID.ora file, you must now close and restart the database.
Warning : do not set the UTL_FILE_DIR parameter to ‘*’ as this will allow writing to and reading from any file in any location on your server – at least any directory that the Oracle user has read and /or write access to. This includes the locations where you have put you logfiles, datafiles etc, so anyone using UTL_FILE can corrupt your database. Best avoided !
Oracle 9.2 or 9i Release 2
In the latest release of 9i, the use of UTL_FILE_DIR is advised against on security grounds. The advice now is to create a directory and use that instead. By default, only SYS and SYSTEM can create directories. In addition, the user now writes procedures which use the directory name in FOPEN, rather than the path name. This means that if the DBA changes the location of the path the directory relates to, the user procedures are only slightly affected, but they will still work. See below for more details.
CREATE DIRECTORY utl_file_tmp AS '/tmp'; CREATE DIRECTORY utl_file_dir AS '/oracle/oracle817/admin/CMDB/utl_file'; GRANT READ ON DIRECTORY utl_file_tmp to username; GRANT WRITE ON DIRECTORY utl_file_tmp to username; GRANT READ ON DIRECTORY utl_file_dir to username; GRANT WRITE ON DIRECTORY utl_file_dir to username;
All Oracle verions
Once the above has been sorted out according to your version of Oracle, you must ensure that the named directories exist on the server. UTL_FILE writes files to the server, not to the PC you are working on. So, over on the server create the appropriate directories, and try again :
cmdb> mkdir utl_file cmdb> ls -l drwxr-xr-x 2 cmdb dba 96 Sep 6 13:11 utl_file
So the directory exists. Can we use it yet ?
I’ve edited my procedure to change the invalid path from ‘/temp’ to the newly created ‘/oracle/oracle817/admin/CMDB/utl_file’, what happens when I run it ? Yet again, an error message :
SQL> execute EmployeeFlatfile; BEGIN EmployeeFlatfile; END; * ERROR at line 1: ORA-20001: utl_file.invalid_operation ORA-06512: at "CMDB.EMPLOYEEFLATFILE", line 19 ORA-06512: at line 1
This time, the error message is not invalid path, so obviously, I’ve got the path name correct, however, have another look above at the output from the ‘ls -l’ command, user cmdb owns the directory, and is in the dba group, but the only users with write access is the cmdb user. A quick change is required to give the dba group write privs too :
cmdb> chmod g+w utl_file cmdb> ls -l drwxrwxr-x 2 cmdb dba 96 Sep 6 13:11 utl_file
And this time, when I run the procedure, it all ‘just works’.
SQL> execute EmployeeFlatfile; PL/SQL procedure successfully completed.
A quick check on the server :
cmdb> ls -l utl_file -rw-r--r-- 1 ora817 dba 5856 Sep 6 13:25 test.txt
So, in summary :
- create a UTL_FILE_DIR parameter and bounce the system if you are running Oracle up to 9i release 1, or
- create a directory and give appropriate privileges to it if you are using Oracle 9i release 2.
- Create a directory, in the location you have named.
- Change permissions so that the Oracle user has the ability to read and write to the directory.
Possible problem areas
- The directory/folder parameter you use on calls to FOPEN, must match exactly the UTL_FILE_DIR setting, even down to letter case – if running on Unix servers.
- Has the specified directory/folder been created on the server ?
- Has the directory/folder been given correct privileges to allow the Oracle user to read and write to it ?
- Always handle exceptions in your code, translating these into application errors with a meaningful message works wonders for your bug tracking abilities.
- Windows mapped shares are not suitable for UTL_FILE_DIR to use – see below.
- Oracle 9i release 2 uses a directory name in the first parameter to FOPEN, this must be supplied in upper case. See below.
- Did someone change the UTL_FILE_DIR setting and not tell you ? All existing procedures will now fail until they are amended again.(Only up to 9i release 1)
Windows has its own problems
On Unix everything is case sensitive so the rules above regarding making sure you specify the directory name exactly as per the UTL_FILE_DIR is important. On Windows, folder names are non-case sensitive so that rule could possibly not apply – or could it ?
SQL> connect system Enter password: ****** Connected. SQL> create directory utl_file_dir as 'c:\temp\utl_file'; Directory created. SQL> grant write,read on directory utl_file_dir to scott; Grant succeeded.
Then in a DOS session, create a folder which has the name slightly different from that already used :
C:\>mkdir c:\temp\UTL_FILE C:\>
Back in SQLPlus again, we connect as Scott and create a procedute to dump the EMP table to a text file, similar to the examples above.
SQL> connect scott/tiger Connected. SQL> CREATE OR REPLACE PROCEDURE Empflatfile 2 IS 3 file_id UTL_FILE.FILE_TYPE; 4 BEGIN 5 file_id := utl_file.FOPEN( 'utl_file_dir', 'test.txt', 'w' ); 6 FOR emp IN (SELECT ename FROM emp) 7 LOOP 8 utl_file.PUT_LINE( file_id, emp.ename ); 9 END LOOP; 10 utl_file.fCLOSE(file_id); 11 EXCEPTION 12 WHEN utl_file.invalid_path THEN 13 RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path'); 14 WHEN utl_file.invalid_mode THEN 15 RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode'); 16 WHEN utl_file.invalid_filehandle THEN 17 RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle'); 18 WHEN utl_file.invalid_operation THEN 19 RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation'); 20 WHEN utl_file.read_error THEN 21 RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error'); 22 WHEN utl_file.write_error THEN 23 RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error'); 24 WHEN utl_file.internal_error THEN 25 RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error'); 26 WHEN OTHERS THEN 27 RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error'); 28 END; 29 / Procedure created. SQL> execute EmpFlatFile; BEGIN EmpFlatFile; END; * ERROR at line 1: ORA-20001: utl_file.invalid_path ORA-06512: at "SCOTT.EMPFLATFILE", line 13 ORA-06512: at line 1
So it looks like Oracle doesn’t like the fact that the folder name I used when creating a directory (I’m using Oracle 9irelease 2) is different from the one on the disc – or is it something else ?
Having tested the procedure above with both an uppercase folder name and a lower case one, neither worked. However, the manual mentions that the valid diurectory names that can be used are listed in ALL_DIRECTORIES, so a quick select shows the following :
SQL> column owner format a15 SQL> column directory_name format a20 SQL> column directory_path format a40 SQL> r 1 select * from all_directories 2* OWNER DIRECTORY_NAME DIRECTORY_PATH --------------- -------------------- ---------------------------------------- SYS UTL_FILE_DIR c:\temp\utl_file
So it looks like the directory name has been converted to the usual Oracle uppercase version. A quick change to the procedure to make the FOPEN call look like this :
file_id := utl_file.FOPEN( 'UTL_FILE_DIR', 'test.txt', 'w' );
Followed by an execute and we have success !
SQL> execute empflatfile PL/SQL procedure successfully completed.
Check it out in a DOS session :
C:\Temp\UTL_FILE>dir
Volume in drive C is LPC001813
Volume Serial Number is F4B4-BCE0
Directory of C:\Temp\UTL_FILE
06/09/2002 14:27 <DIR> .
06/09/2002 14:27 <DIR> ..
06/09/2002 14:27 98 test.txt
1 File(s) 98 bytes
2 Dir(s) 910,671,872 bytes free
As you can see, the fact that the folder name on the disc is in upper case, while Oracle thinks it is in lower case makes no difference. Remember, this is not the case in Unix.
Windows shares and mapped drives
Oracle does not understand about mapped drives. When running as a service on a Windows server (or even using Personal Oracle on your PC), the directory or UTL_FILE_DIR cannot be a windows mapped drive. The services run as the LOCAL SYSTEM user, and has no access to any mapped drives you may have set up.
The following example shows how this doesn’t work, and in addition, shows how a directory can be remapped, without causing too much hassle to users who have existing procedures which write or read from the directory – unlike if a specific UTL_FILE_DIR is used. (But, remember, the procedures will go invalid when the directory is dropped – causing a recompile on next execute!)
SQL> connect system Enter password: ****** Connected. SQL> drop directory utl_file_dir; Directory dropped. SQL> create directory utl_file_dir as 's:\temp\norman\utl_file'; Directory created. SQL> grant read,write on directory utl_file_dir to scott; Grant succeeded. SQL> connect scott/tiger Connected. SQL> execute empflatfile BEGIN empflatfile; END; * ERROR at line 1: ORA-20001: utl_file.invalid_operation ORA-06512: at "SCOTT.EMPFLATFILE", line 19 ORA-06512: at line 1
If I now change the directory to use the full UNC name of the share mapped as drive S:\ then the following shows what happens :
SQL> connect system Enter password: ****** Connected. SQL> drop directory utl_file_dir; Directory dropped. SQL> create directory utl_file_dir as '\\fp\shared\temp\norman\utl_file'; Directory created. SQL> grant read,write on directory utl_file_dir to scott; Grant succeeded. SQL> connect scott/tiger Connected. SQL> execute empflatfile BEGIN empflatfile; END; * ERROR at line 1: ORA-20001: utl_file.invalid_operation ORA-06512: at "SCOTT.EMPFLATFILE", line 19 ORA-06512: at line 1
So that didn’t work – why not ?
In control panel, services (NT) or control panel, administrative tools, services – stop the OracleServiceXXXX service. Click on the login tab, and note that it is using a local system logon. That user has no rights to any shares (at least on our NT system,) and so is unable to access the shared drives by their mapped name or full UNC name.
If I change the service to logon as my own username and restart it, I can try again :
SQL> connect scott/tiger Connected. SQL> execute empflatfile PL/SQL procedure successfully completed.
I could also go back and change the directory to use a mapped drive name (S:\temp\norman\utl_file) but if our sysadmins for NT decided to change the share name, or the mapped drive, the procedure would be broken again. It is advisable to use the full UNC name for the drive you are trying to access.
And finally, watch out for the times when you change your Windows password. When you do this, remember to amend your OracleServiceXXXX service again – otherwise the database won’t startup.
August 13, 2008
Sys_Context Function
In Oracle/PLSQL, the sys_context function can be used to retrieve information about the Oracle environment.
The syntax for the sys_context function is:
sys_context( namespace, parameter, [ length ] )
namespace is an Oracle namespace that has already been created. If the namespace of ‘USERENV’ is used, attributes describing the current Oracle session can be returned.
parameter is a valid attribute that has been set using the DBMS_SESSION.set_context procedure.
length is optional. It is the length of the return value in bytes. If this parameter is omitted or if an invalid entry is provided, the sys_context function will default to 256 bytes.
The valid parameters for the namespace called ‘USERENV’ are as follows:
Parameter Explanation Return Length AUDITED_CURSORID Returns the cursor ID of the SQL that triggered the audit N/A AUTHENTICATION_DATA Authentication data 256 AUTHENTICATION_TYPE Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy 30 BG_JOB_ID If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL. 30 CLIENT_IDENTIFIER Returns the client identifier (global context) 64 CLIENT_INFO User session information 64 CURRENT_SCHEMA Returns the default schema used in the current schema 30 CURRENT_SCHEMAID Returns the identifier of the default schema used in the current schema 30 CURRENT_SQL Returns the SQL that triggered the audit event 64 CURRENT_USER Name of the current user 30 CURRENT_USERID Userid of the current user 30 DB_DOMAIN Domain of the database from the DB_DOMAIN initialization parameter 256 DB_NAME Name of the database from the DB_NAME initialization parameter 30 ENTRYID Available auditing entry identifier 30 EXTERNAL_NAME External of the database user 256 FG_JOB_ID If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL. 30 GLOBAL_CONTEXT_MEMORY The number used in the System Global Area by the globally accessed context N/A HOST Name of the host machine from which the client has connected 54 INSTANCE The identifier number of the current instance 30 IP_ADDRESS IP address of the machine from which the client has connected 30 ISDBA Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE. 30 LANG The ISO abbreviate for the language 62 LANGUAGE The language, territory, and character of the session. In the following format:
language_territory.characterset52 NETWORK_PROTOCOL Network protocol used 256 NLS_CALENDAR The calendar of the current session 62 NLS_CURRENCY The currency of the current session 62 NLS_DATE_FORMAT The date format for the current session 62 NLS_DATE_LANGUAGE The language used for dates 62 NLS_SORT BINARY or the linguistic sort basis 62 NLS_TERRITORY The territory of the current session 62 OS_USER The OS username for the user logged in 30 PROXY_USER The name of the user who opened the current session on behalf of SESSION_USER 30 PROXY_USERID The identifier of the user who opened the current session on behalf of SESSION_USER 30 SESSION_USER The database user name of the user logged in 30 SESSION_USERID The database identifier of the user logged in 30 SESSIONID The identifier of the auditing session 30 TERMINAL The OS identifier of the current session 10
Applies To:
- Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
http://www.techonthenet.com/oracle/functions/sys_context.php
select sysdate from dual;
select USER from dual;
select UID from dual;
select * from dba_users
select
SYS_CONTEXT(‘USERENV’,'TERMINAL’) terminal,
SYS_CONTEXT(‘USERENV’,'LANGUAGE’) language,
SYS_CONTEXT(‘USERENV’,'SESSIONID’) sessionid,
SYS_CONTEXT(‘USERENV’,'INSTANCE’) instance,
SYS_CONTEXT(‘USERENV’,'ENTRYID’) entryid,
SYS_CONTEXT(‘USERENV’,'ISDBA’) isdba,
SYS_CONTEXT(‘USERENV’,'NLS_TERRITORY’) nls_territory,
SYS_CONTEXT(‘USERENV’,'NLS_CURRENCY’) nls_currency,
SYS_CONTEXT(‘USERENV’,'NLS_CALENDAR’) nls_calendar,
SYS_CONTEXT(‘USERENV’,'NLS_DATE_FORMAT’) nls_date_format,
SYS_CONTEXT(‘USERENV’,'NLS_DATE_LANGUAGE’) nls_date_language,
SYS_CONTEXT(‘USERENV’,'NLS_SORT’) nls_sort,
SYS_CONTEXT(‘USERENV’,'CURRENT_USER’) current_user,
SYS_CONTEXT(‘USERENV’,'CURRENT_USERID’) current_userid,
SYS_CONTEXT(‘USERENV’,'SESSION_USER’) session_user,
SYS_CONTEXT(‘USERENV’,'SESSION_USERID’) session_userid,
SYS_CONTEXT(‘USERENV’,'PROXY_USER’) proxy_user,
SYS_CONTEXT(‘USERENV’,'PROXY_USERID’) proxy_userid,
SYS_CONTEXT(‘USERENV’,'DB_DOMAIN’) db_domain,
SYS_CONTEXT(‘USERENV’,'DB_NAME’) db_name,
SYS_CONTEXT(‘USERENV’,'HOST’) host,
SYS_CONTEXT(‘USERENV’,'OS_USER’) os_user,
SYS_CONTEXT(‘USERENV’,'EXTERNAL_NAME’) external_name,
SYS_CONTEXT(‘USERENV’,'IP_ADDRESS’) ip_address,
SYS_CONTEXT(‘USERENV’,'NETWORK_PROTOCOL’) network_protocol,
SYS_CONTEXT(‘USERENV’,'BG_JOB_ID’) bg_job_id,
SYS_CONTEXT(‘USERENV’,'FG_JOB_ID’) fg_job_id,
SYS_CONTEXT(‘USERENV’,'AUTHENTICATION_TYPE’)
FROM DUAL
select SYS_CONTEXT(‘USERENV’,'SID’) SESSION_ID
FROM DUAL
select * from v$mystat where rownum < 2
Grant/Revoke Privileges
Grant Privileges on Tables
You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.
Privilege Description Select Ability to query the table with a select statement. Insert Ability to add new rows to the table with the insert statement. Update Ability to update rows in the table with the update statement. Delete Ability to delete rows from the table with the delete statement. References Ability to create a constraint that refers to the table. Alter Ability to change the table definition with the alter table statement. Index Ability to create an index on the table with the create index statement.
The syntax for granting privileges on a table is:
grant privileges on object to user;
For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:
grant select, insert, update, delete on suppliers to smithj;
You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
grant all on suppliers to smithj;
If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
grant select on suppliers to public;
Revoke Privileges on Tables
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
revoke privileges on object from user;
For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:
revoke delete on suppliers from anderson;
If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
revoke all on suppliers from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke all on suppliers from public;
Grant Privileges on Functions/Procedures
When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:
Privilege Description Execute Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
The syntax for granting execute privileges on a function/procedure is:
grant execute on object to user;
For example, if you had a function called Find_Value and you wanted to grant execute access to the user named smithj, you would execute the following statement:
grant execute on Find_Value to smithj;
If you wanted to grant all users the ability to execute this function, you would execute the following:
grant execute on Find_Value to public;
Revoke Privileges on Functions/Procedures
Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.
The syntax for the revoking privileges on a function or procedure is:
revoke execute on object from user;
If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:
revoke execute on Find_Value from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke execute on Find_Value from public;
May 21, 2008
PLS-00201
variable SCN number
exec :scn := dbms_flashback.get_system_change_number
ERROR at line 1:
ORA-06550: line 1, column 15:
PLS-00201: identifier ‘DBMS_FLASHBACK’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
conn / as sysdba
grant execute on sys.DBMS_FLASHBACK to hr;
conn hr/hr
exec :scn := dbms_flashback.get_system_change_number
PL/SQL procedure successfully completed.
SQL> print scn
SCN
———-
1598575
April 23, 2008
Proper Use of SELECT COUNT(*)
A common error among developers is to answer the question “Do I have at least one of ___ ?” by executing a SELECT COUNT(*) against the table/view. This is a very inefficient way to answer that question. SELECT COUNT answers a different question: “How many do I have?” If you really only need to find out if you have N rows, use an explicit cursor and FETCH once. If you need to find out if you have more than one row, use an explicit cursor and FETCH N times.
Use COUNT only when the actual number of occurrences is needed.
Do not use the COUNT function to answer either of the following questions:
- Is there at least one row matching certain criteria?
- Is there more than one row matching certain criteria?
Instead, use an explicit cursor inside a function.
You should only use COUNT when you need to answer the question: “How many rows match a certain criteria?”
Suppose that you have been asked to write a program that returns TRUE if there is at least one book in a given category. You could write it like this:
CREATE OR REPLACE FUNCTION atleastone (
category_in IN book.category%TYPE)
RETURN BOOLEAN
IS
numbooks INTEGER;
BEGIN
SELECT COUNT(*) INTO numbooks
FROM book
WHERE category = category_in;
RETURN (numbooks > 0);
END;
But you are asking the RDBMS to do lots of unnecessary work. It might find, for instance, that there are 12 million books in the NON-FICTION category. A better approach is:
CREATE OR REPLACE FUNCTION atleastone (
category_in IN book.category%TYPE)
RETURN BOOLEAN
IS
retval BOOLEAN;
CURSOR category_cur IS
SELECT 1
FROM book
WHERE category = category_in;
category_rec NUMBER;
BEGIN
OPEN category_cur;
FETCH category_cur INTO category_rec;
retval := category_cur%FOUND;
CLOSE category_cur;
RETURN retval;
END;
In other words: all you have to do is see if there is a single row and you are done. With this practice, you get optimal performance out of your query and the readability of your code also improves, since it is a more accurate translation of the requirement.
http://www.quest-pipelines.com/pipelines/plsql/tips04.htm#OCTOBER
April 7, 2008
ORA-01400
-bash-3.00$ oerr ora 01400
01400, 00000, “cannot insert NULL into (%s)”
// *Cause:
// *Action:
Primary key (a sequence number generated by a trigger) of a table. This incident occurred when trigger was disabled at the same time while some batch insertions undertook to this in question table.
April 6, 2008
Getting PLS-457 with Dynamic SQL in 10g
PLS-00457: in USING clause, expressions have to be of SQL types EXECUTE IMMEDIATE Datatypes of bind and define variables are any that are permitted in SQL NULL is not valid data type for nullable column
March 21, 2008
Definer and Invoker Rights
Before Oracle8i, Definer Right procedures execute with the privileges of their owner, not their current user, such definer’s rights sub-programs are bound to the schema in which they reside; since Oracle 8i, introduced Invoker Right, then Invoker Right procedures execute with the privileges of the current user, that is, the user who invokes the procedure, such procedures are not bound to a particular schema. They can be run by a variety of users and allow multiple users to manage their own data by using centralized application logic.
Advantages of Invoker’s Rights
Invoker’s rights subprograms let you reuse code and centralize application logic. They are especially useful in applications that store data using identical tables in different schemas. All the schemas in one instance can call procedures owned by a central schema. You can even have schemas in different instances call centralized procedures using a database link.
Specifying the Privileges for a Subprogram with the AUTHID Clause
In the CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, or CREATE TYPE statement, you can include either AUTHID CURRENT_USER or AUTHID DEFINER immediately before the IS or AS keyword that begins the declaration section.
DEFINER is the default option. In a package or object type, the AUTHID clause applies to all subprograms.
Who Is the Current User During Subprogram Execution?
In a sequence of calls, whenever control is inside an invoker’s rights subprogram, the current user is the session user. When a definer’s rights subprogram is called, the owner of that subprogram becomes the current user.
To verify who the current user is at any time, you can check the USER_USERS data dictionary view.
Using Roles with Invoker’s Rights Subprograms
The use of roles in a subprogram depends on whether it executes with definer’s rights or invoker’s rights. Within a definer’s rights subprogram, all roles are disabled. Roles are not used for privilege checking, and you cannot set roles.
Within an invoker’s rights subprogram, roles are enabled (unless the subprogram was called directly or indirectly by a definer’s rights subprogram). Roles are used for privilege checking, and you can use native dynamic SQL to set roles for the session. However, you cannot use roles to grant privileges on template objects because roles apply at run time, not at compile time.
Using Views and Database Triggers with Invoker’s Rights Subprograms
For invoker’s rights subprograms executed within a view expression, the schema that created the view, not the schema that is querying the view, is considered to be the current user. This rule also applies to database triggers.
Do a small test as follows:
SQL> conn / as sysdba
SQL> create user jenny identified by jenny default tablespace users temporary tablespace temp;
User created.
SQL> grant connect, resource to jenny;
Grant succeeded.
SQL> conn jenny/jenny
Connected.
SQL> set serveroutput on size 1000000
SQL> create or replace procedure definer_proc
2 as
3 uname varchar2(30);
4 begin
5 for x in
6 ( select sys_context( 'userenv', 'current_user' ) current_user,
7 sys_context( 'userenv', 'session_user' ) session_user,
8 sys_context( 'userenv', 'current_schema' ) current_schema
9 from dual )
10 loop
11 dbms_output.put_line( 'Current User: ' || x.current_user );
12 dbms_output.put_line( 'Session User: ' || x.session_user );
13 dbms_output.put_line( 'Current Schema: ' || x.current_schema );
14 end loop;
15 execute immediate 'select username from user_users' into uname;
16 dbms_output.put_line(' Current User: ' || uname);
17 end;
18 /
Procedure created.
SQL> exec definer_proc
Current User: JENNY
Session User: JENNY
Current Schema: JENNY
Current User: JENNY
PL/SQL procedure successfully completed.
SQL> grant execute on jenny.definer_proc to test;
Grant succeeded.
SQL> create or replace procedure invoker_proc
2 AUTHID CURRENT_USER
3 as
4 uname varchar2(30);
5 begin
6 for x in
7 ( select sys_context( 'userenv', 'current_user' ) current_user,
8 sys_context( 'userenv', 'session_user' ) session_user,
9 sys_context( 'userenv', 'current_schema' ) current_schema
10 from dual )
11 loop
12 dbms_output.put_line( 'Current User: ' || x.current_user );
13 dbms_output.put_line( 'Session User: ' || x.session_user );
14 dbms_output.put_line( 'Current Schema: ' || x.current_schema );
15 end loop;
16 execute immediate 'select username from user_users' into uname;
17 dbms_output.put_line(' Current User: ' || uname);
18 end;
19 /
Procedure created.
SQL> exec invoker_proc
Current User: JENNY
Session User: JENNY
Current Schema: JENNY
Current User: JENNY
PL/SQL procedure successfully completed.
SQL> grant execute on jenny.invoker_proc to test;
Grant succeeded.
SQL> connect test/test Connected. SQL> set serveroutput on size 1000000 SQL> exec jenny.definer_proc Current User: JENNY Session User: TEST Current Schema: JENNY Current User: JENNY PL/SQL procedure successfully completed. SQL> exec jenny.invoker_proc Current User: TEST Session User: TEST Current Schema: TEST Current User: TEST PL/SQL procedure successfully completed. SQL> alter session set current_schema = jenny; Session altered. SQL> exec jenny.definer_proc Current User: JENNY Session User: TEST Current Schema: JENNY Current User: JENNY PL/SQL procedure successfully completed. SQL> exec jenny.invoker_proc Current User: TEST Session User: TEST Current Schema: JENNY Current User: TEST PL/SQL procedure successfully completed. SQL> alter session set current_schema = system; Session altered. SQL> exec jenny.definer_proc Current User: JENNY Session User: TEST Current Schema: JENNY Current User: JENNY PL/SQL procedure successfully completed. SQL> exec jenny.invoker_proc Current User: TEST Session User: TEST Current Schema: SYSTEM Current User: TEST PL/SQL procedure successfully completed.
Reference: http://www.eygle.com/archives/2005/12/definer_and_invoker_rights.html
March 5, 2008
Predefined PL/SQL Exceptions
http://download-uk.oracle.com/docs/cd/A97630_01/appdev.920/a96624/07_errs.htm#784
Declaring PL/SQL Exceptions
SQL> declare
2 date_var varchar2(10);
3 user_entry varchar2(4) ;
4 date_err exception ;
5 begin
6 user_entry := '&user_input_value' ;
7 if (substr(user_entry,1,2) not between 1 and 12) OR
8 (substr(user_entry,3,2) not between 0 and 99) then
9 raise date_err ;
10 end if ;
11 date_var := to_date(user_entry, 'mmyy') ;
12 dbms_output.put_line(date_var) ;
13 exception
14 when date_err then
15 dbms_output.put_line('You must enter a valid date in MMYY format.') ;
16* end ;
SQL> /
Enter value for user_input_value: 1299
01-DEC-99
PL/SQL procedure successfully completed.
SQL> /
Enter value for user_input_value: 234w
You must enter a valid date in MMYY format.
PL/SQL procedure successfully completed.
From http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_cid536743_tax294551,00.html
