package project1;It wasn't pretty, but it seemed to work.
import java.util.Date;
public class Class1
{
public static void main(String[] args)
{
Class1 class1 = new Class1();
Date d = new Date();
long l = d.getTime() + 1000000000;
String s = String.valueOf( l );
for ( int i = 0; i < l; i++ )
{
//some sort of MOD "wait" here, then check for the file
}
}
}
![]() |
Labels: development, failed, ideas, life, work
CREATE TABLE t_childThis table has been around for quite some time. You decide that you would like to constrain the values in the SOON_TO_BE_PARENT_ID column. First, here's the data that exists:
(
child_id NUMBER(10)
CONSTRAINT pk_childid PRIMARY KEY,
soon_to_be_parent_id NUMBER(10)
);
INSERT INTO t_child
( child_id,
soon_to_be_parent_id )
SELECT
rownum,
TRUNC( dbms_random.value( -9999, -1 ) )
FROM dual
CONNECT BY LEVEL <= 10;
CJUSTICE@TESTING>SELECT * FROM t_child;Now I'll create a table that will contain the values I want to constraint SOON_TO_BE_PARENT_ID to, call it a lookup or reference table.
CHILD_ID SOON_TO_BE_PARENT_ID
---------- --------------------
1 -5560
2 -1822
3 -2499
4 -7039
5 -8718
6 -1019
7 -9997
8 -9553
9 -4477
10 -1458
CREATE TABLE t_parentI'll populate it with some data:
(
parent_id NUMBER(10)
CONSTRAINT pk_parentid PRIMARY KEY
);
INSERT INTO t_parent( parent_id )Now I'll add the constraint that references the PARENT_ID column of T_PARENT
SELECT rownum
FROM dual
CONNECT BY LEVEL <= 10;
CJUSTICE@TESTING>SELECT * FROM T_PARENT;
PARENT_ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
ALTER TABLE t_childand rename the column to PARENT_ID:
ADD CONSTRAINT fk_parentid
FOREIGN KEY ( soon_to_be_parent_id )
REFERENCES t_parent( parent_id )
ENABLE
NOVALIDATE;
ALTER TABLE t_child RENAME COLUMN soon_to_be_parent_id TO parent_id;What will this do? I should no longer be able to enter a value into T_CHILD.PARENT_ID that does not exist in T_PARENT, but it will ignore anything that already exists.
INSERT INTO t_childPerfect! Now I'll add a value that does exist in T_PARENT.
( child_id,
parent_id )
VALUES
( 11,
11 );
INSERT INTO t_child
*
ERROR at line 1:
ORA-02291: integrity constraint (CJUSTICE.FK_PARENTID) violated - parent key not found
INSERT INTO t_childWin!
( child_id,
parent_id )
VALUES
( 11,
10 );
1 row created.
"...if you simply read the Concepts Guide...and retain just 10%..., you’ll already know 90% more than most people do"
Labels: constraints, documentation
dbms_application_info.set_session_longopsg_index and g_slno are global variables in the package. l_table is a PL/SQL TABLE OF VARCHAR2.
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBER DATA',
sofar => 0,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBER DATA' );
SELECTNow you too can sit for hours and watch your job move incrementally forward!
username,
sid,
serial#,
TO_CHAR( start_time, 'MM/DD/YYYY HH24:MI:SS' ) start_ti,
time_remaining rem,
elapsed_seconds ela,
ROUND( ( sofar / REPLACE( totalwork, 0, 1 ) ) * 100, 2 ) per,
sofar,
totalwork work,
message,
target_desc
FROM v$session_longops
WHERE start_time >= SYSDATE - 1
ORDER BY start_time DESC
PROCEDURE get_member_data
IS
l_exists INTEGER;
TYPE table_of_lobs IS TABLE OF VARCHAR2(3);
l_table TABLE_OF_LOBS := TABLE_OF_LOBS( 'COM', 'ORG' );
l_count INTEGER := 0;
BEGIN
--check to see if there is enrollment data, if not, move on
SELECT COUNT(*)
INTO l_exists
FROM members
WHERE rownum < 2;
IF l_exists = 1 THEN--data exists, truncate and reload
g_index := dbms_application_info.set_session_longops_nohint;
EXECUTE IMMEDIATE 'TRUNCATE TABLE member_stg';
g_audit_key := p_audit.begin_load
( p_targettable => 'MEMBER_STG',
p_loadsource => 'MEMBER_SOURCE',
p_loadstatus => 'PRE',
p_loadprogram => 'GET_MEMBER_DATA',
p_commenttext => 'INSERT' );
dbms_application_info.set_session_longops
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBERS',
sofar => 0,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBERS' );
FOR i IN 1..l_table.COUNT LOOP
l_count := l_count + 1;
INSERT INTO member_stg
SELECT *
FROM members;
g_total_rows_affected := g_total_rows_affected + sql%rowcount;
COMMIT;
dbms_application_info.set_session_longops
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBERS',
sofar => l_count,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBERS' );
END LOOP;
p_audit.end_load
( p_auditkey => g_audit_key,
p_loadstatus => 'SUC',
p_rowsuccess => g_total_rows_affected );
gather_table_stats
( p_tablename => 'MEMBER_STG',
p_schemaname => 'MYHOME' );
dbms_application_info.set_session_longops
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBERS',
sofar => l_count + 1,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBERS' );
END IF;
EXCEPTION
WHEN others THEN
p_audit.failed_load
( p_auditkey => g_audit_key,
p_comments => SQLCODE || ' ' || SQLERRM );
RAISE;
END get_member_data;
This section describes the rules that can be applied to table columns to enforce different types of data integrity.Reading on past the brief section to the Constraint States I found this nugget:
Null rule: A null rule is a rule defined on a single column that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column.
Unique column values: A unique value rule defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns).
Primary key values: A primary key value rule defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key.
Referential integrity rules: A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).
Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:
* Restrict: Disallows the update or deletion of referenced data.
* Set to null: When referenced data is updated or deleted, all associated dependent data is set to NULL.
* Set to default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
* Cascade: When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.
* No action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred. (Oracle Database uses No Action as its default action.)
Complex integrity checking: A user-defined rule for a column (or set of columns) that allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns).
ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.This is a great tool for legacy systems. You have data in the column(s) that you can't really do anything with, but you want to insure that all future data that goes in that particular column(s) matches the parent key.
Labels: database, documentation, oracle
|
|
|
|