fix
Logo
fix
Nalazite se na Region-ORACLE
Za dodavanje novih poruka na ovu stranicu kliknite ovdje
Za pregled svih najnovijih poruka kliknete ovdje

oracle- 65743 - 26.02.2012 : Zeljko Tomic Sokolac - best (0)

Oracle Wrap Utility


Oracle WRAP Utility je program koji se izvršava sa komandne linije i omogućava programerima da zaštite sadržaj svojih PL/SQL programa. Za korištenje ovog alata koristite sledeću sintaksu:

wrap iname=input_file Šoname=output_fileĆ

Ovaj alati je . exe tipa i nalazi se u BIN folderu, na primjer:

C:ĐoracleĐproductĐ10. 2. 0Đdb_1ĐBIN
oracle- 33402 - 20.06.2011 : Zeljko Tomic Sokolac - best (0)

Oracle Import Utility


execute dbms_stats. gather_system_stats('start');
execute dbms_stats. gather_system_stats('stop');

SELECT pname, pval1, pval2 from aux_stats$;

NLS_LENGTH_SEMANTICS = CHAR;

alter system set nls_length_semantics=char scope=both

Oracle Note: 788156. 1
Oracle Note: 257722. 1 for WE81SO8859P1
Oracle Note: 260893. 1

oracle- 22766 - 22.10.2009 : Zeljko Tomic Sokolac - best (0)

Kill Session


SELECT * FROM V$SESSION WHERE USERNAME = 'SCOTT';

alter system kill session '145, 3755';
alter system kill session 'session-id, session-serial';
oracle- 22655 - 05.10.2009 : Zeljko Tomic Sokolac - best (0)

Delete Duplicates in a Table


CREATE TABLE except_table (
row_id ROWID,
owner VARCHAR2(30),
table_name VARCHAR2(30),
constraint VARCHAR2(30)
);

grant select, insert on except_table to public;


ALTER TABLE haa_node_entities ENABLE CONSTRAINT hne_hnde_fk
EXCEPTIONS INTO schema1. except_table;

ALTER TABLE table_data
ADD CONSTRAINT table_data_uk UNIQUE (event_date
, place_id
, industry_type
, customer_id)
EXCEPTIONS INTO except_table;

DELETE table_data
WHERE ROWID IN (SELECT row_id FROM except_table);
oracle- 21876 - 04.06.2009 : Zeljko Tomic Sokolac - best (0)

Velicina tabele u Oracle


SELECT a. table_name,
a. tablespace_name,
ROUND(SUM (b. bytes) / 1024 / 1024, 3) MB
FROM user_tables a,
user_extents b
WHERE a. table_name = b. segment_name
AND a. tablespace_name = b. tablespace_name
GROUP BY a. tablespace_name, a. table_name
ORDER BY MB DESC, table_name ASC
oracle- 17067 - 16.01.2008 : Zeljko Tomic - best (0)


SELECT ROUND ((SHARED_POOL. BYTES - free. BYTES) / (1024 * 1024), 2) mb_used,
ROUND (SHARED_POOL. BYTES / (1024 * 1024), 2) size_in_mb,
ROUND (free. BYTES / (1024 * 1024), 2) mb_avail,
ROUND (((SHARED_POOL. BYTES - free. BYTES) / SHARED_POOL. BYTES) * 100,
2
) percent_used
FROM (SELECT current_size BYTES
FROM v$sga_dynamic_components
WHERE component = 'shared pool') SHARED_POOL,
(SELECT BYTES
FROM v$sgastat
WHERE pool = 'shared pool' AND NAME = 'free memory') free
oracle- 11495 - 04.02.2007 : Zeljko Tomic Sokolac - best (0)

Toad 9 Debuger Setup


ALTER SYSTEM SET PLSQL_DEBUG = TRUE;

GRANT DEBUG CONNECT SESSION TO hr;

GRANT DEBUG ANY PROCEDURE TO hr;
oracle- 11149 - 09.01.2007 : Zeljko Tomic Sokolac - best (0)

plsql_warnings


alter session set plsql_warnings='disable:all';
oracle- 11102 - 30.12.2006 : Zeljko Tomic Sokolac - best (0)

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.
oracle- 10768 - 25.10.2006 : Zeljko Tomic Sokolac - best (0)


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
  • oracle- 10138 - 06.08.2006 : Zeljko Tomic Sokolac - best (0)

    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
    oracle- 10076 - 17.07.2006 : - best (0)

    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;
    oracle- 9958 - 29.06.2006 : Zeljko Tomic Sokolac - best (0)

    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
    oracle- 9223 - 25.04.2006 : - best (0)

    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;
    oracle- 9222 - 25.04.2006 : - best (0)

    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;
    oracle- 9067 - 24.04.2006 : Zeljko Tomic Sokolac - best (0)

    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;
    oracle- 9066 - 24.04.2006 : Zeljko Tomic Sokolac - best (0)


    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;
    oracle- 8880 - 22.04.2006 : Zeljko Tomic Sokolac - best (0)

    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;
    oracle- 2690 - 24.11.2005 : Zeljko Tomic Sokolac - best (0)

    Database Link


    CREATE DATABASE LINK db_link_name
    CONNECT TO database_user
    IDENTIFIED BY ""
    USING 'ConnectString'
    oracle- 2689 - 24.11.2005 : Zeljko Tomic Sokolac - best (0)

    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;
    oracle- 1881 - 06.07.2005 : Zeljko Tomic Sokolac - best (0)

    TIMESTAMP data type - Oracle 9i


    Convert DATE datatype to TIMESTAMP datatype

    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.

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

    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 :

    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:

    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.
    oracle- 1762 - 16.06.2005 : Zeljko Tomic Sokolac - best (0)

    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.
    oracle- 1761 - 16.06.2005 : Zeljko Tomic Sokolac - best (0)

    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.
    oracle- 1566 - 17.05.2005 : Zeljko Tomic Sokolac - best (0)


    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
  • oracle- 1558 - 16.05.2005 : Zeljko Tomic Sokolac - best (0)

    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;
    oracle- 1556 - 16.05.2005 : Zeljko Tomic Sokolac - best (0)

    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;
    oracle- 1515 - 09.05.2005 : Zeljko Tomic Sokolac - best (0)

    External Tables(2)


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

    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.

    SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
    WHERE OBJECT_NAME LIKE 'EMP_EXT';

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

    1 row selected.


    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:

    desc EMPLOYEES;

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

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


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

    8 rows created.


    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.
    oracle- 1514 - 09.05.2005 : Zeljko Tomic Sokolac - best (0)

    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

    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.
    oracle- 1513 - 09.05.2005 : Zeljko Tomic Sokolac - best (0)

    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.
    oracle- 1511 - 09.05.2005 : Zeljko Tomic Sokolac - best (0)


    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;
  • oracle- 1509 - 08.05.2005 : Zeljko Tomic Sokolac - best (0)


    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
    oracle- 1503 - 08.05.2005 : Zeljko Tomic Sokolac - best (0)


    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);
  • oracle- 1498 - 06.05.2005 : Zeljko Tomic Sokolac - best (0)


    System Development Life Cycle

    The following phases are:
  • Strategy and Analysis
  • Design
  • Build and Document
  • Transition
  • Production
  • oracle- 1497 - 06.05.2005 : Zeljko Tomic Sokolac - best (0)


    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;
    oracle- 1495 - 06.05.2005 : Zeljko Tomic Sokolac - best (0)


    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;
    oracle- 1494 - 06.05.2005 : Zeljko Tomic Sokolac - best (0)


    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;
  • oracle- 1492 - 05.05.2005 : Zeljko Tomic Sokolac - best (0)


    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);
    oracle- 1490 - 05.05.2005 : Zeljko Tomic Sokolac - best (0)

    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;
  • oracle- 1489 - 05.05.2005 : Zeljko Tomic Sokolac - best (0)


    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
  • oracle- 1488 - 05.05.2005 : Zeljko Tomic Sokolac - best (0)

    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.
  • oracle- 1487 - 05.05.2005 : Zeljko Tomic Sokolac - best (0)

    Oracle Supplied Packages(1)


    Oracle Supplied Packages

    Oracle supplied packages improve the functionality of the database.

    DBMS_ALERT
    Provides notification of database events

  • REGISTER - Lets a session register interest in an alert dbms_alert. register( IN VARCHAR2);
    See demo below.

  • 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.
  • oracle- 1486 - 05.05.2005 : Zeljko Tomic Sokolac - best (0)


    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
  • Idi na stranu - |1|2|