H.Tonguç YILMAZ Blog

problem) 

create table test as
select sysdate dcol, user vcol1, ‘ ‘ vcol2, ‘ ‘ vcol3, 0 ncol1, 5.5 ncol2
from dual

describe test

dcol date
vcol1 varchar2(30)
vcol2 char(1)
vcol3 char(8)
ncol1 number
ncol2 number
solution 1)

create table test as
select cast( null as varchar2(10) ) a,
cast( null as date ) b,
cast( null as number(5,2) ) c
from dual;

describe test

a varchar2(10)
b date
c number(5,2)

solution 2)

create table test as  
select sysdate today_date, to_char(sysdate, ‘dd-mm-yyyy’) char_date    
from dual;

describe test

today_date date
char_date varchar2(10)

(1) create a html page indicating that the access is restricted (i.e. access_denied.html)

(2) upload this page using the WebDav feature into Oracle XE
(a) Use the Internet Explorer for that: File > Open > http://localhost:8080/ (check open as webfolder)
(b) copy the file access_denied.html to your root folder
(c) make sure you can call it via http//localhost:8080/access_denied.html

(3) run the following code when connecting to XE as user SYSTEM:
CREATE OR REPLACE PROCEDURE SYSTEM.sec_authorize IS
BEGIN
   –
   — procedure called when procedure is invoked by the DAD / DBMS_EPG
   –
   IF OWA_UTIL.get_cgi_env (‘REMOTE_ADDR’) IN (‘192.168.0.4′, ‘192.168.0.5′) THEN
      NULL;
   ELSE
      OWA_UTIL.redirect_url (‘/access_denied.html’);
   END IF;
END;
/
GRANT EXECUTE ON SYSTEM.sec_authorize TO anonymous;
 
BEGIN
  DBMS_EPG.set_dad_attribute (
    dad_name   => ‘APEX’,
    attr_name  => ‘before-procedure’,
    attr_value => ‘SYSTEM.SEC_AUTHORIZE’);
END;
/

Update the values for REMOTE_ADDRESS to match your system.

The problem is that in a UNION, Oracle finds all the qualifying rows and then “deduplicates” them;

SQL> select * from dual
2  union
3  select * from dual;

D
-
X

SQL> select * from dual
2  union ALL
3  select * from dual;

D
-
X
X

SQL> create table test_table(pid number, name varchar2(200), sal number)
2  /

Table created.

SQL> insert into test_table values(1, ‘Scott’, 5000)
2  /

1 row created.

SQL> insert into test_table values(2, ‘Tiger’, 5000)
2  /

1 row created.

SQL>
SQL> insert into test_table values(1, ‘Scott’, 5000)
2  /

1 row created.

SQL> insert into test_table values(2, ‘Tiger’, 5000)
2  /

1 row created.

SQL>
SQL> create table test_table_1 as
2  select * from test_table where 1=2
3  /

Table created.

SQL> select * from test_table_1
2  /

no rows selected

SQL>
SQL> select pid, name, sal from test_table_1
2  union
3  select pid, name, sal from test_table
4  /

PID NAME                                    SAL
———- ——————————– ———-
1 Scott                                  5000
2 Tiger                                  5000

SQL> select pid, name, sal from test_table_1
2  union all
3  select pid, name, sal from test_table
4  /

PID NAME                                    SAL
———- ——————————– ———-
1 Scott                                  5000
2 Tiger                                  5000
1 Scott                                  5000
2 Tiger                                  5000

The bottom line: If you can use UNION ALL, by all means use it over UNION to avoid a costly deduplication step—a step that is probably not even necessary most of the time.

http://www.oracle.com/technology/oramag/oracle/04-nov/o64asktom.html

http://www.oracle.com/siebel/index.html

http://siebeloracle.com/forums/

http://www.siebelfans.com/

http://siebel.ittoolbox.com/ 

https://ebusiness.siebel.com/supportweb/ (will need a username/password from your client, or your company)

create table test (id number not null,
name  varchar2(10) not null, source_id number);

–
– the idea is to have uniqueness between name and source_id,
– but only in cases where source_id is not null
–
insert into test values(1,’name1′,1);
insert into test values(2,’name2′,2);
insert into test values(3,’name1′,”);
insert into test values(4,’name1′,”);
commit;

create unique index fbi_test_source_id on test(
case when source_id is not null then SOURCE_ID end,
case when source_id is not null then NAME      end );

insert into test values(1,’name1′,1);

ORA-00001: unique constraint (ABI.FBI_TEST_SOURCE_ID) violated

insert into test values(3,’name1′,”);

1 row inserted

insert into test values(3,’name1′,2);

1 row inserted

commit

Commit complete

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:39951226964354

Steve Adams, a very bright guy who knows a lot about Oracle, has been known to say, “If a schema has no IOTs or clusters, that is a good indication that no thought has been given to the matter of optimizing data access.”

let the database enforce integrity ->

First of all – while applications are important, it is all about data. Your application will not be the last application to use this data. If it is, the data probably isn’t that important. Time after time I see data repurposed, reused – more so then application logic ever is. The data is the foundation here, it will outlive most application interfaces.

Virtually every system for some reason uses an inappropriate datatype ->

It seems that common sense would dictate that you use strings to store strings and numbers for numbers and dates for dates but I see people putting dates in numbers or strings (and wondering why it doesn’t sort properly!). I see numbers hidden inside of strings. I see people using varchar2(4000) for all text fields “just in case”.

• You lost the edit of the data.
• You will increase your storage needs.
• You decrease data integrity
• You negatively affect performance because you will confuse the optimizer.

Top Browsers and Operating Systems

BROWSERS

OPERATING SYSTEMS


Internet Explorer:
.0%

Windows:
11.4%


Firefox:
11.4%

Linux:
.0%


Netscape:
.0%

Macintosh:
.0%


Other:
.0%

Other:
.0%



Statement processed.

0.58 seconds

The dbms_job.submit procedure accepts three in parameters:

1. The name of the job to submit
2. The start time for the job
3. The interval to execute the job

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;

– Submit job to begin at 0600 and run every hour
dbms_job.submit(   :jobno, ’statspack.snap;’,    trunc(sysdate)+6/24,    ‘trunc(SYSDATE+1/24,”HH”)’,    TRUE,    :instno);

– Submit job to begin at 0900 and run 12 hours later
dbms_job.submit(   :jobno,    ’statspack.snap;’,    trunc(sysdate+1)+9/24,    ‘trunc(SYSDATE+12/24,”HH”)’,    TRUE,    :instno);

– Submit job to begin at 0600 and run every 10 minutes
dbms_job.submit(   :jobno,    ’statspack.snap;’,   trunc(sysdate+1/144,’MI’),   ‘trunc(sysdate+1/144,”MI”)’,    TRUE,    :instno);

– Submit job to begin at 0600 and run every hour, Monday – Friday
dbms_job.submit(   :jobno,    ’statspack.snap;’,    trunc(sysdate+1)+6/24,   ‘trunc(     least(       next_day(SYSDATE,”MONDAY”),        next_day(SYSDATE,”TUESDAY”),        next_day(SYSDATE,”WEDNESDAY”),        next_day(SYSDATE,”THURSDAY”),        next_day(SYSDATE,”FRIDAY”)      )    +1/24,”HH”)’,    TRUE,    :instno);

commit;
end;
/

SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

ALTER TABLE locations ADD (uid_col RAW(32));

UPDATE locations SET uid_col = SYS_GUID();

SELECT location_id, uid_col FROM locations;

LOCATION_ID UID_COL
———– —————————————-
1000 7CD5B7769DF75CEFE034080020825436
1100 7CD5B7769DF85CEFE034080020825436
1200 7CD5B7769DF95CEFE034080020825436
1300 7CD5B7769DFA5CEFE034080020825436
. . .