Oracle, Orakle, PL/SQL sintaksa, DDL, DML

Zeljko Tomic : Sokolac Broj: 11102 Datum: 30.12.2006

How to setup UTL_FILE_DIR in 10g

Connect as SYSTEM:

To see where you spfile is located type:

select name, value from v$parameter where name = 'spfile';

To see a specific parameter type the parameter name:
select *
from v$parameter
where name like 'utl_file_dir'

To modify the parameter type:

alter system set utl_file_dir='C:/temp', 'C:/temp2' scope=spfile;

The file on location ex. C:/ORACLE/PRODUCT/10. 2. 0/DB_2/DBS/SPFILESNET10G. ORA will be updated automatically. It means you don't need to open the file and type the parameter value. You have to restart the database in order for this parameter to take effect.

Oracle recommandation is to use DIRECTORIES instead UTL_FILE_DIR parameters.

Zeljko Tomic : Sokolac Broj: 10768 Datum: 25.10.2006

SQL*Loader - Ucitavanje podataka u vise tabela

prvo, drugo i cetvrto polje idu u tabelu "A"

trece, sesto polje idu u tabelu "B"

peto polje ide u tabelu "C"
Delimiter je ";"

Definicija tabela:

create table t1 (polje1 int, polje2 int, polje4 int );
create table t2 (polje3 int, polje6 int);
create table t3 (polje5 int );

Kontrolni fajl:

LOAD DATA
INFILE *
REPLACE
INTO TABLE T1
(
FIELD1 position(1:1024) "delimited. word(:polje1, 1, NULL, ';')",
FIELD2 position(1:1) "delimited. word(:polje1, 2, NULL, ';')",
FIELD4 position(1:1) "delimited. word(:polje1, 4, NULL, ';')"
)
INTO TABLE T2
(
FIELD3 position(1:1024) "delimited. word(:polje3, 3, NULL, ';')",
FIELD6 position(1:1) "delimited. word(:polje3, 6, NULL, ';')"
)
INTO TABLE T3
(
FIELD5 position(1:1024) "delimited. word(:polje5, 5, NULL, ';')"
)
BEGINDATA
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22

Zeljko Tomic : Sokolac Broj: 10138 Datum: 06.08.2006

SQL*Loader Example

The following example shows how to use SQL*Loader to load mixed format documents from the operating system to a BLOB column. The example has two steps:

create the table
issue the SQL*Loader command that reads control file and loads data into table
See Also:
For a complete discussion on using SQL*Loader, see Oracle9i Database Utilities

Creating the Table
This example loads to a table articles_formatted created as follows:

CREATE TABLE articles_formatted (
ARTICLE_ID NUMBER PRIMARY KEY ,
AUTHOR VARCHAR2(30),
FORMAT VARCHAR2(30),
PUB_DATE DATE,
TITLE VARCHAR2(256),
TEXT BLOB
);

The article_id column is the primary key. Documents are loaded in the text column, which is of type BLOB.

Issuing the SQL*Loader Command
The following command starts the loader, which reads the control file LOADER1. DAT:

sqlldr userid=demo/demo control=loader1. dat log=loader. log

Example Control File: loader1. dat
This SQL*Loader control file defines the columns to be loaded and instructs the loader to load the data line by line from loader2. dat into the articles_formatted table. Each line in loader2. dat holds a comma separated list of fields to be loaded.

-- load file example
load data
INFILE 'loader2. dat'
INTO TABLE articles_formatted
APPEND
FIELDS TERMINATED BY ','
(article_id SEQUENCE (MAX,1),
author CHAR(30),
format,
pub_date SYSDATE,
title,
ext_fname FILLER CHAR(80),
text LOBFILE(ext_fname) TERMINATED BY EOF)

This control file instructs the loader to load data from loader2. dat to the articles_formatted table in the following way:

The ordinal position of the line describing the document fields in loader2. dat is written to the article_id column.
The first field on the line is written to author column.
The second field on the line is written to the format column.
The current date given by SYSDATE is written to the pub_date column.
The title of the document, which is the third field on the line, is written to the title column.
The name of each document to be loaded is read into the ext_fname temporary variable, and the actual document is loaded in the text BLOB column:
Example Data File: loader2. dat
This file contains the data to be loaded into each row of the table, articles_formatted.

Each line contains a comma separated list of the fields to be loaded in articles_formatted. The last field of every line names the file to be loaded in to the text column:

Ben Kanobi, plaintext,Kawasaki news article,. . /sample_docs/kawasaki. txt,
Joe Bloggs, plaintext,Java plug-in,. . /sample_docs/javaplugin. txt,
John Hancock, plaintext,Declaration of Independence,. . /sample_docs/indep. txt,
M. S. Developer, Word7,Newsletter example,. . /sample_docs/newsletter. doc,
M. S. Developer, Word7,Resume example,. . /sample_docs/resume. doc,
X. L. Developer, Excel7,Common example,. . /sample_docs/common. xls,
X. L. Developer, Excel7,Complex example,. . /sample_docs/solvsamp. xls,
Pow R. Point, Powerpoint7,Generic presentation,. . /sample_docs/generic. ppt,
Pow R. Point, Powerpoint7,Meeting presentation,. . /sample_docs/meeting. ppt,
Java Man, PDF,Java Beans paper,. . /sample_docs/j_bean. pdf,
Java Man, PDF,Java on the server paper,. . /sample_docs/j_svr. pdf,
Ora Webmaster, HTML,Oracle home page,. . /sample_docs/oramnu97. html

: Broj: 10076 Datum: 17.07.2006

To create CHAR like "123.30"

-- To make a number to have trailing zeroes (always two decimal places)
select case when instr('&1', '. ') = 0 then '123'đđ'. 00'
when LENGTH('&1') = instr('&1', '. ') + 1 then '&1'đđ'0'
else '123. 3'
end
from DUAL;

Zeljko Tomic : Sokolac Broj: 9958 Datum: 29.06.2006

Monitoring Oracle Tablespaces

SELECT Tablespace_Name,
Sum_Alloc_Blocks * DB_BLOCK_FACTOR,
Sum_Free_Blocks * DB_BLOCK_FACTOR,
100-(100*Sum_Free_Blocks/Sum_Alloc_Blocks) AS PCT_USED,
Max_Blocks * DB_BLOCK_FACTOR
FROM
(SELECT Tablespace_Name,
SUM(Blocks) Sum_Alloc_Blocks
FROM DBA_DATA_FILES
GROUP BY Tablespace_Name),
(SELECT Tablespace_Name FS_TS_NAME,
MAX(Blocks) AS Max_Blocks,
SUM(Blocks) AS Sum_Free_Blocks
FROM DBA_FREE_SPACE
GROUP BY Tablespace_Name),
(SELECT (value / 1024 / 1024) DB_BLOCK_FACTOR
FROM v$parameter
WHERE name = 'db_block_size')
WHERE Tablespace_Name = FS_TS_NAME
ORDER BY PCT_USED DESC

: Broj: 9223 Datum: 25.04.2006

Dynamic SQL by Example

DECLARE
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7, 2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := 'PERSONNEL';
location VARCHAR2(13) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

plsql_block := 'BEGIN emp_pkg. raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;

sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;

EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';

END;

: Broj: 9222 Datum: 25.04.2006

Populate Table of Records

DECLARE
v_userrec users%ROWTYPE;
user_recs user_util. user_rec_tab_type;

CURSOR user_cur
IS
SELECT *
FROM users
WHERE register_date < TO_DATE(
'25-JUN-1997', 'DD-MON-YYYY');

i BINARY_INTEGER := 0;
BEGIN
OPEN user_cur;

LOOP
FETCH user_cur INTO user_rec;
EXIT WHEN User_cur%NOTFOUND OR user_cur%ROWCOUNT > 10;
i := i + 1;
user_recs (i) := user_rec;
END LOOP;

user_util. do_something (user_recs);
END;

Zeljko Tomic : Sokolac Broj: 9067 Datum: 24.04.2006

Updating a Row Using a Record

DECLARE
dept_info dept%ROWTYPE;
BEGIN
dept_info. deptno := 30;
dept_info. dname := 'MARKETING';
dept_info. loc := 'ATLANTA';
-- The row will have values for the filled-in columns, and null
-- for any other columns.
UPDATE dept SET ROW = dept_info WHERE deptno = 30;
END;

Zeljko Tomic : Sokolac Broj: 9066 Datum: 24.04.2006

Oracle9i Release 2 can index-by a string value:

SET SERVEROUTPUT ON
DECLARE
TYPE country_tab IS TABLE OF VARCHAR2(50)
INDEX BY VARCHAR2(5);

t_country country_tab;
BEGIN

-- Populate lookup
t_country('UK') := 'United Kingdom';
t_country('US') := 'United States of America';
t_country('FR') := 'France';
t_country('DE') := 'Germany';

-- Find country name for ISO code "DE"
DBMS_OUTPUT. PUT_LINE('ISO code "DE" = ' đđ t_country('DE'));

END;

Zeljko Tomic : Sokolac Broj: 8880 Datum: 22.04.2006

Oracle Autonomous Transaction

In the example, the function is autonomous:

CREATE PACKAGE glasinac AS
. . .
FUNCTION balance (acct_id INTEGER) RETURN REAL;
END glasinac;

CREATE PACKAGE BODY glasinac AS
. . .
FUNCTION krmkov_do ( acct_id INTEGER ) RETURN REAL IS
PRAGMA AUTONOMOUS_TRANSACTION;
brezjak REAL;
BEGIN
. . .
RETURN brezjak;
END;
END glasinac;

Zeljko Tomic : Sokolac Broj: 2690 Datum: 24.11.2005

Database Link

CREATE DATABASE LINK db_link_name
CONNECT TO database_user
IDENTIFIED BY ""
USING 'ConnectString'

Zeljko Tomic : Sokolac Broj: 2689 Datum: 24.11.2005

Oracle Jobs

DECLARE
X NUMBER;
BEGIN
SYS. DBMS_JOB. SUBMIT
( job => X
, what => 'Sp_Refresh_Customer_All;'
, next_date => TO_DATE('25/11/2005 01:30:00', 'dd/mm/yyyy hh24:mi:ss')
, INTERVAL => 'TRUNC(SYSDATE+1)+1. 5/24'
, no_parse => TRUE
);
SYS. DBMS_OUTPUT. PUT_LINE('Job Number is: ' đđ TO_CHAR(x));
END;
/

COMMIT;

Zeljko Tomic : Sokolac Broj: 1881 Datum: 06.07.2005

TIMESTAMP data type - Oracle 9i

Convert DATE datatype to TIMESTAMP datatype
SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;

Formatting of the TIMESTAMP datatype
SELECT TO_CHAR(time1, 'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table

Formatting of the TIMESTAMP datatype with fractional seconds
SELECT TO_CHAR(time1, 'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table

Straight subtraction of two TIMESTAMP datatypes
SELECT time1, time2, (time2-time1) FROM date_table

Determine the interval breakdown between two dates for a TIMESTAMP datatype
SELECT time1,
time2,
substr((time2-time1), instr((time2-time1), ' ')+7, 2) seconds,
substr((time2-time1), instr((time2-time1), ' ')+4, 2) minutes,
substr((time2-time1), instr((time2-time1), ' ')+1, 2) hours,
trunc(to_number(substr((time2-time1), 1, instr(time2-time1, ' ')))) days,
trunc(to_number(substr((time2-time1), 1, instr(time2-time1, ' ')))/7) weeks
FROM date_table

Setting FIXED_DATE and effects on SYSDATE and SYSTIMESTAMP
SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
System altered.

SQL> select sysdate from dual;
SYSDATE
---------
01-JAN-03

SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------
09-JUL-03 11. 05. 02. 519000 AM -06:00

System Date and Time
In order to get the system date and time returned in a DATE datatype, you can use the SYSDATE function such as :

SQL> SELECT SYSDATE FROM DUAL;

In order to get the system date and time returned in a TIMESTAMP datatype, you can use the SYSTIMESTAMP function such as:

SQL> SELECT SYSTIMESTAMP FROM DUAL;

You can set the initialization parameter FIXED_DATE to return a constant value for what is returned from the SYSDATE function. This is a great tool for testing date and time sensitive code. Just beware that this parameter has no effect on the SYSTIMESTAMP function.

Zeljko Tomic : Sokolac Broj: 1762 Datum: 16.06.2005

DBMS_PROFILER package

Starting a Profiling Session

The profiler does not begin capturing performance information until the call to start_profiler is executed.

exec dbms_profiler. start_profiler('Test of raise procedure by Scott');

The profiler captures data on a session-by-session basis. This means that if the user SCOTT started the profiler by executing the command above, only PL/SQL objects that were executed and owned by SCOTT will be profiled, and consequently have data in the profiler tables described earlier. The SCOTT user is only used as an example; it could be any database user.

Flushing Data during a Profiling Session

The flush command enables the developer to dump statistics during program execution without stopping the profiling utility. The only other time Oracle saves data to the underlying tables is when the profiling session is stopped, as shown below:

exec dbms_profiler. flush_data();

A developer could use the flush procedure with dbms_debug and step, line by line, through a procedure, flushing performance benchmarks along the way. Or, if you have a very long running PL/SQL program, flushing data can be very useful in the performance tuning process.

Stopping a Profiling Session

Stopping a profiler execution is done after an adequate period of time of gathering performance benchmarks – determined by the developer. Once the developer stops the profiler, all the remaining (unflushed) data is loaded into the profiler tables.

exec dbms_profiler. stop_profiler();

The dbms_profiler package also provides procedures that suspend and resume profiling (pause_profiler(), resume_profiler()).

Now that the profiler has stopped, the data is available for diagnostics from within Oracle, and we can begin working with it.

Working with Captured Profiler Data

The profiler utility populates three tables with information, plsql_profiler_runs, plsql_profiler_units, and plsql_profiler_data. Each “run” is initiated by a user and contains zero or more “units”. Each unit contains “data” about its execution – the guts of the performance data benchmarks.

The performance information for a line in a unit needs to be tied back to the line source in user_source. Once that join is made, the developer will have all of the information that they need to optimize, enhance, and tune their application code, as well as the SQL.

Zeljko Tomic : Sokolac Broj: 1761 Datum: 16.06.2005

DBMS_SESSION package

PL/SQL Packages DBMS_SESSION sets session preferences and security levels. Equivalent to ALTER SESSION and SET ROLE statements.

Subprocedures:

SET_IDENTIFIER - Set the indentifier.
SET_CONTEXT - Set the context.
CLEAR_CONTEXT - Clear the context.
CLEAR_IDENTIFIER - Clear the identifier.
SET_ROLE - Set role.
SET_SQL_TRACE - Turn tracing on or off.
SET_NLS - Set national language support (NLS).
CLOSE_DATABASE_LINK - Close database link.
RESET_PACKAGE - Deinstantiates all packages in the session.
UNIQUE_SESSION_ID Function - Returns an identifier that is unique for all sessions currently connected to this database.
IS_ROLE_ENABLED Function - Determines if the named role is enabled for the session.
IS_SESSION_ALIVE Function - Determine if the specified session is alive.
SET_CLOSE_CACHED_OPEN_CURSORS - Turn close_cached_open_cursors on or off.
FREE_UNUSED_USER_MEMORY - Reclaim unused memory - use after performing an operation that required a large amount of memory.
SET_CONTEXT - Set or resets the value of a context attribute.
LIST_CONTEXT - Returns a list of active namespace and context for the current session.
SWITCH_CURRENT_CONSUMER_GROUP - Change the current resource consumer group of a user's current session.

Zeljko Tomic : Sokolac Broj: 1566 Datum: 17.05.2005

UNION

Disply all employees (current and former)

SELECT emp_id, emp_name
FROM employees
UNION
SELECT emp_id, emp_name
FROM employees_archive

UNION ALL

returns all records from both queries

SELECT emp_id, emp_name
FROM employees
UNION ALL
SELECT emp_id, emp_name
FROM employees_archive

INTERSECT

Values šemp_id, emp_nameć must be identical in both tables:

SELECT emp_id, emp_name
FROM employees
INTERSECT
SELECT emp_id, emp_name
FROM employees_archive

The MINUS Operator

This operator returns rows returned by the first query that are not present in the second query. For example, to list all employees who never changed their job:

SELECT emp_id, emp_name
FROM employees
MINUS
SELECT emp_id, emp_name
FROM employees_archive

Zeljko Tomic : Sokolac Broj: 1558 Datum: 16.05.2005

OWA_PATTERN.MATCH

page_pieces UTL_HTTP. HTML_PIECES;
arr_components OWA_TEXT. VC_ARR;
vv_bigpage VARCHAR2(32767);
vv_session_id VARCHAR2(64);

BEGIN
page_pieces := UTL_HTTP. REQUEST_PIECES('http://www.slavicnet.com');
FOR i IN page_pieces. FIRST.. page_pieces. LAST LOOP
vv_bigpage := SUBSTR(vv_bigpage đđ page_pieces(i), 1, 32767);
END LOOP;

IF OWA_PATTERN. MATCH(vv_bigpage, 'NAME="SESSION_ID VALUE="(Ðd*)"', arr_coponents) THEN
vv_session_id := arr_components(1);
ELSE
RAISE ex_cannot_get_session_id;
END IF;

-- the other way would be:
vb_match := OWA_PATTERN. MATCH(vv_text, '<tr>(. *)</tr>', arr_components);

END;

Zeljko Tomic : Sokolac Broj: 1556 Datum: 16.05.2005

UTL_HTTP package

DECLARE
page_pieces UTL_HTTP. HTML_PIECES;
my_url VARCHAR2(100) DEFAULT 'http://www.slavicnet.com/';
BEGIN
page_pieces := UTL_HTTP. REQUEST_PIECES(my_url);
FOR i IN page_pieces. FIRST.. page_pieces. LAST LOOP
NULL;
--do something with each individual piece in page_pieces(i)
END LOOP;
EXCEPTION
WHEN UTL_HTTP. INIT_FAILED THEN
htp. print('Procedure 001: 'đđsqlerrm);
WHEN UTL_HTTP. REQUEST_FAILED THEN
htp. print('Procedure 001: 'đđsqlerrm);
END;

Zeljko Tomic : Sokolac Broj: 1515 Datum: 09.05.2005

External Tables(2)

Step V: Creating Views
SQL> CREATE VIEW v_empext_dev AS
SELECT * FROM emp_ext
WHERE deptname='DEVELOPMENT';
View created.

SQL> SELECT * FROM v_empext_dev;

EMPCODE EMPNAME DEPTNAME HIREDATE
------------ ------------- ---------------------- ---------
103 Rob DEVELOPMENT 01-JUN-96
104 Joe DEVELOPMENT 01-JUN-96
107 Katie DEVELOPMENT 01-JUN-98
108 Jay DEVELOPMENT 01-JUN-98
You can get the information of the objects you have created through DBA_OBJECTS, ALL_OBJECTS or USER_OBJECTS.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'EMP_EXT';

OBJECT_NAME OBJECT_TYPE
---------------------- ------------------
EMP_EXT TABLE

1 row selected.

SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'EXT_TABLES';

OBJECT_NAME OBJECT_TYPE
---------------------- ------------------
EXT_TABLES DIRECTORY

1 row selected.
Populating Tables using the INSERT command
You can populate data from external files using an "insert into … select from" statement instead of using SQL*Loader. This method provides very fast data loads.
Example:
Consider a table EMPLOYEES:
SQL> desc EMPLOYEES;

Name Null? Type
--------------------------------- -------- --------------

EMPCODE NUMBER(4)
EMPNAME VARCHAR2(25)
DEPTNAME VARCHAR2(25)
HIREDATE DATE

SQL> INSERT INTO employees
(empcode, empname, deptname, hiredate) SELECT * FROM emp_ext;

8 rows created.

SQL> SELECT * FROM employees;

Dropping External Tables
For an external table, the DROP TABLE statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.

Zeljko Tomic : Sokolac Broj: 1514 Datum: 09.05.2005

External Tables(1)

External Tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. External table is more like a view that allows running any SQL query against external data without requiring that the external data first be loaded into the database.
You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and indexes cannot be created on external tables.
Oracle provides the means of defining the metadata for external tables through the CREATE TABLE... ORGANIZATION EXTERNAL statement.
Before firing the above command we need to create a directory object where the external files will reside.
CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:ÐEXT_TABLESÐ';
Example: The example below describes how to create external files, create external tables, query external tables and create views.
Step I: Creating the flat files, which will be queried
The file "ext1. txt" contains the following sample data:
101, Andy, FINANCE, 15-DEC-1995
102, Jack, HRD, 01-MAY-1996
103, Rob, DEVELOPMENT, 01-JUN-1996
104, Joe, DEVELOPMENT, 01-JUN-1996
The file "ext2. txt" contains the following sample data:
105, Mark, FINANCE, 15-DEC-1997
106, John, HRD, 01-MAY-1998
107, Peter, DEVELOPMENT, 01-JUN-1998
108, Julie, DEVELOPMENT, 01-JUN-1998
Copy these files under "C:ÐEXT_DIR"
Step II: Create a Directory Object where the flat files will reside
SQL> CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:ÐEXT_TABLES';

Directory created.
Step III: Create metadata for the external table
SQL> CREATE TABLE emp_ext
(
empcode NUMBER(4),
empname VARCHAR2(25),
deptname VARCHAR2(25),
hiredate date
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tables
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ', '
MISSING FIELD VALUES ARE NULL
)
LOCATION ('emp_ext1. dat', 'emp_ext2. dat')
)
REJECT LIMIT UNLIMITED;

Table created.
The REJECT LIMIT clause specifies that there is no limit on the number of errors that can occur during a query of the external data.
"The ORACLE_LOADER is an access driver for loading data from the external files into the tables. "
Step IV: Querying Data
SQL> SELECT * FROM emp_ext;

EMPCODE EMPNAME DEPTNAME HIREDATE
--------- ------------------- ---------------------- ---------
101 Andy FINANCE 15-DEC-95
102 Jack HRD 01-MAY-96
103 Rob DEVELOPMENT 01-JUN-96
104 Joe DEVELOPMENT 01-JUN-96
105 Maggie FINANCE 15-DEC-97
106 Russell HRD 01-MAY-98
107 Katie DEVELOPMENT 01-JUN-98
108 Jay DEVELOPMENT 01-JUN-98

8 rows selected.

Zeljko Tomic : Sokolac Broj: 1513 Datum: 09.05.2005

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead, EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more user friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.

Usage tips:
1. EXECUTE IMMEDIATE requires explicit commit.
2. To return multiple rows, use a temporary table to store the records (see example below) or make use of REF cursors.
3. Do not use a semi-colon when executing SQL statements, and use semi-colon at the end when executing a PL/SQL block.
4. This feature is not covered at large in the Oracle Manuals. Below are examples of all possible ways of using Execute immediate.
5. For Forms Developers, this feature will not work in Forms 6i front-end as it is on PL/SQL 8.0.6.3.

Example of EXECUTE IMMEDIATE usage
1. To run a DDL statement in PL/SQL.

begin
execute immediate 'set role all';
end;

2. To pass values to a dynamic statement (USING clause).

declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;

3. To retrieve values from a dynamic statement (INTO clause).

declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;

4. To call a routine dynamically: The bind variables used for parameters of the routine have to be specified along with the parameter type. IN type is the default, others have to be specified explicitly.

declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' đđ l_routin đđ '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;

if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;

5. To return value into a PL/SQL record type: The same option can be used for %rowtype variables also.

declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' đđ
'from emp where empno = 7934'
into empdtl;
end;

6. To pass and retrieve values: The INTO clause should precede the USING clause.

declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;

7. Multi-row query option. Use the insert statement to populate a temp table for this option. Use the temporary table to carry out further processing. Alternatively, you may use REF cursors to by-pass this drawback.

declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' đđ
' select empno, ename from emp ' đđ
' where sal > :1'
using l_sal;
commit;
end;

Care should be taken to trap all possible exceptions.

Zeljko Tomic : Sokolac Broj: 1511 Datum: 09.05.2005

CONSTRAINTS

USER_CONSTRAINTS

USER_COL_CONSTRAINTS

ALL_COLL_COMMENTS

USER_COL_COMMENTS

ALL_TAB_COMMENTS

USER_TAB_COMMENTS

COMMENT ON TABLE table_name
ŠCOLUMN column_name?
IS 'some text';

SEQUENCES

CREATE SEQUENCE Department_Seq
START WITH 1
INCREMENT BY 2
MAXVALUE 9999
MINVALUE 1
CYCLE
CACHE 10;

get info about sequences from Data Dictionary
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;

USER_VIEWS

SELECT view_name, text
FROM user_views;

INDEXES

USER_INDEXES

USER_IND_COLUMNS

SELECT ui. index_name, ui. column_name,
ui. column_position col_pos uic. uniqueness
FROM user_indexes ui, user_ind_columns uic
WHERE ui. index_name = uic. index_name
AND ui. table_name = 'EMPLOYEES';

Data Dictionary and User privilages

ROLE_SYS_PRIVS

USER_ROLE_PRIVS

USER_TAB_PRIVS_MADE

USER_TAB_PRIVS_RECD

USER_COL_PRIVS_MADE

USER_COL_PRIVS_RECD

REVOKE insert, update
ON table_name
FROM user_name;

Zeljko Tomic : Sokolac Broj: 1509 Datum: 08.05.2005

Multiple-column Subqueries

SELECT name, job_title, hire_date
FROM employees
WHERE (job_title, hire_date) IN
(SELECT job_title, MIN(hire_date)
FROM employees
GROUP BY job_title)

Creating temp. table using subqueries

SELECT e. name, e. salary, e. department, b. salary_avg
FROM employees e, (SELECT department, AVG(salary) avg_sal
FROM employees
GROUP BY department) b
WHERE e. department = b. department
AND e. salary > b. avg_sal

Zeljko Tomic : Sokolac Broj: 1503 Datum: 08.05.2005

Case Conversion Functions

INITCAP(par)

LOWER(parm)

UPPER(parm)

Other string functions

LENGTH(columnđexpression)

SUBSTR(string, start_pos, length)

INSTR(string, search_string) - returns position of the search string, 0 if not found.

LPAD(string, 10, '*') - creates a string 10 char with leading *, eg. '****zeljko'

RPAD(string, 10, '*') - same like LPAD but from the right, eg. 'zeljko****'

Number functions

ROUND(sal, 2) - round a number to two decimal places

ROUND(sal) - round an integer

ROUND(sal, -2) - round to hundreds ex. 1200

TRUNC(sal, 2) - truncates a number to two decimal places

TRUNC(sal) - truncates an integer

TRUNC(sal, -2) - truncates to hundreds ex. 1200

MOD(slary, commision) - returns reminder of the devision

Date functions

SYSDATE - function without arguments

MONTHS_BETWEEN('24-MAY-2004', '23-JAN-1991') - Finds number of months between two dates

ADD_MONTHS('11-JAN-2004', 6) - adds calendar month to date, returns '11-JUL-2004'

NEXT_DAY('01-SEP-95', 'FRIDAY') - finds next day of the date specified, returns 08-SEP-95

LAST_DAY('01-MAY-2005') - finds last day of the month, returns date '31-MAY-2005'

ROUND() - rounds date; ROUND('25-JUL-95', 'MONTH') returns '01-AUG-95' (param: DAY, MONTH, YEAR);

TRUNC() - truncates date

Conversion functions

TO_DATE()

TO_CHAR() ex. TO_CHAR(date, 'fmDD Month YYY) or TO_CHAR(date, 'DAY, Month DD, YYY) or TO_CHAR(date, 'DD/MM/YY');

TO_NUMBER() ex. , TO_NUMBER(number, '$99999. 99) or TO_NUMBER(number, '999, 999') or TO_NUMBER(number, '000999, 999')

NVL functions

NVL(column, '0') -> converts all NULL values to zero

DECODE functions

ex. DECODE(job, 'ANALYST', sal*1. 2, 'MANAGER', sal*1. 4, sal*1. 02);

Zeljko Tomic : Sokolac Broj: 1498 Datum: 06.05.2005

System Development Life Cycle

The following phases are:

Strategy and Analysis

Design

Build and Document

Transition

Production

Zeljko Tomic : Sokolac Broj: 1497 Datum: 06.05.2005

Cursor

Loop using WHILE loop
OPEN Prod_Cur;
FETCH Prod_Cur INTO vn_Prod_id, vv_Prod_Name;
WHILE Prod_Cur%FOUND LOOP
-- some code here
FETCH Prod_Cur INTO vn_Prod_id, vv_Prod_Name;
END LOOP;

Cursor with %NOTFOUND attribute
OPEN Prod_Cur;
LOOP
FETCH Prod_Cur INTO vn_Prod_id, vv_Prod_Name;
EXIT WHEN Prod_Cur%NOTFOUND;
-- some code here
END LOOP;

Cursor with %ROWCOUNT attribute - used to fetch a specified number of records
OPEN Prod_Cur;
LOOP
FETCH Prod_Cur INTO vn_Prod_id, vv_Prod_Name;
EXIT WHEN Prod_Cur%ROWCOUNT > 5;
-- some code here
END LOOP;

Passing parameters to a cursor
CURSOR Employee_Cur ( vn_Department NUMBER, vv_Job VARCHAR2) IS
SELECT First_Name, Last_Name, Salary
FROM Employees
WHERE dept_id = vn_Department
AND title = vv_Job;

FOR LOOP Cursor
CURSOR Prod_Cur IS
SELECT Prod_id, price, quantity
FROM products
WHERE ord_id = vv_ord_id
FOR UPDATE OF quantity;

FOR Prod_Rec IN Prod_Cur LOOP
DBMS_OUTPUT. PUT_LINE('Product ID = 'đđProd_Rec. Prod_id);
UPDATE products
SET quantity = 10
WHERE CURRENT OF Prod_Cur;
END LOOP;
commit;

Zeljko Tomic : Sokolac Broj: 1495 Datum: 06.05.2005

Looping Constructs

The basic infinite loop
LOOP
statement1;
statement2;
END LOOP;

The basic conditional loop
LOOP
statement1;
IF Šcondition? THEN EXIT;
END LOOP;

The basic conditional loop
LOOP
statement1;
EXIT WHEN Šcondition? ;
END LOOP;

FOR loop
FOR int i=1.. 10 LOOP
DBMS_OUTPUT. PUT_LINE(TO_CHAR(I));
END LOOP;

While loop
WHILE vn_counter <= 10 LOOP
statement1;
END LOOP;

Exit from nested loops - WITH LABEL

<>
LOOP
command1;
LOOP
command2;
EXIT outer WHEN Šcondition? ;
END LOOP;
commands3;
END LOOP;

Zeljko Tomic : Sokolac Broj: 1494 Datum: 06.05.2005

SQL Cursor Attributes

SQL%ROWCOUNT - used to determine the number of rows affected by the most recent SQL statement

SQL%FOUND - this boolean attribute evaluates to TRUE if the most recent SQL statement affects one of more rows.

SQL%NOTFOUND - this boolean attribute evaluates to TRUE if the most recent SQL statement does not affect one of more rows.

SQL%ISOPEN - determines if a cursor is open.

Save Points

INSERT INTO tbl_names(name) VALUES ('Sokolac');
SAVEPOINT a;

INSERT INTO tbl_names(name) VALUES ('Zeljko');
SAVEPOINT b;

INSERT INTO tbl_names(name) VALUES ('Kanada');
SAVEPOINT c;

ROLLBACK TO SAVEPOINT b;

Zeljko Tomic : Sokolac Broj: 1492 Datum: 05.05.2005

PL/SQL Records, PL/SQL Tables, PL/SQL Tables of Records

1. Declare the table type

TYPE type_name IS TABLE OF scalar_datatype ŠNOT NULL? INDEX BY BINARY_INTEGER;

2. Declare the table

first_name_table type_name;

PL/SQL Record Type

TYPE Employee_Rec_Type IS RECORD
(ID NUMBER,
First_Name VARCHAR2(40) DEFAULT 'X',
Last_Name VARCHAR2(40));

To declare record of record type
myEmployeeRecord Employee_Rec_Type;

To declare a record of a table row type
myEmployeeRecord Employee_Tab%ROWTYPE;

Referencing variable in a record

myEmployeeRecord. FirstName = 'Zeljko';

Working with PL/SQL Tables

Example 1:
DECLARE
TYPE countdown_tabtype IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER;
countdown_lst countdown_tabtype;
BEGIN
countdown_lst (1) := 'one';
countdown_lst (43) := 'two';
countdown_lst (255) := 'three';
END;

Example 2:
--Define a PL/SQL record type representing a book:
TYPE book_rec IS RECORD
(title book. title%TYPE,
author book. author_last_name%TYPE,
year_published published_date. %TYPE));

--define a PL/SQL table containing entries of type book_rec:
Type book_rec_tab IS TABLE OF book_rec%TYPE
INDEX BY BINARY_INTEGER;

my_book_rec book_rec%TYPE;
my_book_rec_tab book_rec_tab%TYPE;
...
...
my_book_rec := my_book_rec_tab(5);
find_authors_books(my_book_rec. author);
...
dbms_output. put_line('Ex:'đđmy_book_rec_tab(5). title);

Zeljko Tomic : Sokolac Broj: 1490 Datum: 05.05.2005

Triggers

CREATE OR REPLACE TRIGGER check_salary
AFTER UPDATE OF salary ON tab_employees
DECLARE

BEGIN
SELECT sal, max_sal INTO v_sal, max_sal
FROM tab_emp_a WHERE user_name = user
AND table_name = 'tab_Emp'
AND column_name = 'Slaary';

IF v_sal > max_sal THEN
RAISE_APPILICATION_ERROR ( -20501, 'The maximum number of changes')
END IF;
END;

to disable trigger: ALTER TRIGGER t_name DISABLE;

to disable all triggers on a table
ALTER TABLE tab_name DESABLE ALL TRIGGERS;

Zeljko Tomic : Sokolac Broj: 1489 Datum: 05.05.2005

Data Dictionary Views

USER_OBJECTS - columns: OBJECT_NAME, OBJECT_TYPE, STATUS

USER_SOURCE - columns: name, type, line, text

USER_ERRORS - columns: NAME, TYPE, LINE, POSITION, TEXT

USER_DEPENDENCIES - columns: NAME, TYPE, REFERENCED_NAME, REFERENCED_TYPE

USER_TRIGGERS - columns: TRIGGER_NAME, TRIGGER_TYPE, DESCRIPTION, TRIGGER_BODY

Oracle PL/SQL Datatypes

Zeljko Tomic : Sokolac Broj: 1488 Datum: 05.05.2005

Oracle Supplied Packages(2)

DBMS_LOCK
This supplied package is used to perform operations, such as requesting, converting and releasing user locks, which are managed by the RDBMS lock management services.

DBMS_MAIL
This supplied package is used to perform operations such as sendin messages from the Oracle server directly o an Oracle*Mail identifier.

DBMS_OUTPUT
This supplied package ouptuts values and messages from triggers, stored procedures or functions.

PUT -> Enables you to append text to the line output buffer

NEW_LINE -> Issues the end of line marker

PUT_LINE -> same as put+new_line

GET_LINE -> retrieves the current line from the output buffer

GET_LINES-> retrieves an array of lines from the output buffer

ENABLE -> enables the DBMS_OUTPUT calls

DISABLE -> disables the DBMS_OUTPUT calls

DBMS_PIPE
This package sends messages from one session to another in the same instance.

PACK_MESSAGE

SEND_MESSAGE

RECEIVE_MESSAGE

UNPACK_MESSAGE

PACK_MESSAGE_RAW -> packs a raw item into the local message buffer

UNPACK_MESSAGE_ROW

PACK_MESSAGE_ROWID -> paks a rowid item into the local mashine buffer

UNPACK_MESSAGE_ROWID

NEXT_ITEM_TYPE -> specifies the next item type in the local buffer

UNIQUE_NAME_SESSION -> returns unique name of the session

PURGE ->empties out the local message buffer

DBMS_SESSION
This supplied package provides access to SQL alter session statements and other session information.

DBMS_SHARED_POOL
This package is used to keep objects in shared memory so that they will not be aged out with the normal Least Recently Used (LRU) mechanism.

DBMS_SQL
This supplied package is used to enable the execution of DDL statements and use dynamic SQL to access the database.

OPEN_CURSOR

PARSE

BIND_VARIABLE

DEFINE_COLUMN

EXECUTE

FETCH_ROWS

VARIABLE_VALUE

COLUMN_VALUE

CLOSE_CURSOR

DBMS_TRANSACTION
This package controls logical transactions and improves the performance of short, non-didtributed transactions by creating them as discrete.

DBMS_UTILITY
This supplied package performs functions such as analyzing objects in a particular schema, checking whether or not the server is running in parallel mode, and returneng the time.

Zeljko Tomic : Sokolac Broj: 1487 Datum: 05.05.2005

Oracle Supplied Packages(1)

Oracle Supplied Packages

Oracle supplied packages improve the functionality of the database.

DBMS_ALERT
Provides notification of database events

REMOVE - Enables a session that is no longer interested in an alert to unregistration the alert dbms_alert. remove( IN VARCHAR2);
exec dbms_alert. remove('emptab_alert');

REMOVEALL - Removes all alerts for this session from the registration list dbms_alert. removeall;
exec dbms_alert. removeall;

SET_DEFAULTS - Set the polling interval dbms_alert. set_defaults( IN NUMBER);
exec dbms_alert. set_defaults(3);

SIGNAL Signals an Alert; (up to 1800 bytes) dbms_alert. signal( IN VARCHAR2,
IN VARCHAR2);

WAIT_ANY - Wait for an alert to occur for any of the alerts for which the current session is registered dbms_alert. waitany( OUT VARCHAR2,
OUT VARCHAR2, OUT INTEGER, IN NUMBER DEFAULT MAXWAIT);

DBMS_APPLICATION_INFO
This package enables application tools and application developers to inform the database of the high level of action currently performed.

DBMS_DDL
This package is used for recompiling procedures, functions and packages and analysin indexes, tables and clusters.

ALTER_COMPILE procedure -> Compiles the PL/SQL object.

ANALYZE_OBJECT procedure -> Provides statistics for the database object.

DBMS_DESCRIBE
This supplied package performs the function of returning a description of the arguments for a given stored procedure.

DBMS_JOBS
This stored procedure is used to schedule the periodic execution of the PL/SQL code.

Zeljko Tomic : Sokolac Broj: 1486 Datum: 05.05.2005

1. Predefined Oracle Server Exceptions

NO_DATA_FOUND

TOO_MANY_ROWS

INVALID_CURSOR

ZERO_DEVIDE

DUP_VAL_ON_INDEX

(about 12 exceptions)

2. NON-Predefined Oracle server exceptions

declare the exception
products_remaining_100 EXCEPTION;

Associate the exception with the Oracle server error number -2292
PRAGMA EXCEPTION_INIT (products_remaining_100, -2292);
....
WHEN products_remaining_100 THEN
DBMS_OUTPUT. PUT_LINE('Error - integrity constraint violated');

Error Number -2291 Parent key doesnt exist

Eror Number -1031 Insufficient privileges

3. User-definde exception
This type of exception is raised when the code violates any specific condition set by a programmer.

products_remaining_100 EXCEPTION;

....
DELETE FROM Inventory WHERE product_id = v_product_id;
IF SQL%FOUND THEN
RAISE products_remaining_100;
END IF;
....
EXCEPTION
WHEN products_remaining_100 THEN
DBMS_OUTPUT. PUT_LINE('Error - integrity constraint violated');

Oracle built-in Error functions

SQLCODE ->Returns the numeric value of the error code
SQLERRM ->Returns the message associated with the error number

Toad 9 Debuger Setup

plsql_warnings