JOBS /// IsNumeric

Job will execute in a current schema only ;It will not switch between two schemas .

------------------------------
IsNumeric in ORACLE
----------------

select ISNUM from (
select LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) ISNUM from table_name ) where ISNUM is null

create or replace function isnumeric (param in char) return boolean as
dummy varchar2(100);
begin
dummy:=to_char(to_number(param));
return(true);
exception
when others then
return (false);
end;
/

Hidden Table

Create table " " (EMPNO NUMBER(3));

Creates a table with 4 spaces as its name.
When you select the list of tables from all_tables, such tables are not visible.

To access such a table use
Select * From " ";

(Remember you need to give exactly the same number of spaces as you gave while creating the table).

This feature can be used to store information that you feel is confidential for you.

For Example :

You might create tables of this kind

2 spaces + "MYTAB" + 1 space + "FILE" + 3 spaces

When you create such a table only you know how to access it (Unless someone is intelligent than you to guess it).

Export Acess to Oracle

This topic deals with the exporting the table and its data from Access to Oracle database . For this first you need is to create a datasource from Start>Programs>Your Oracle Home program>Configuration and Migration Tools path (or from Start>Settings>Control Panel>Administrative Tools you will see a utility named'Data Sources (ODBC)'), you will see a utility named 'Microsoft ODBC Administrator'. Launch that utility to begin the process.



Click on the Add button to add a new data source.



If your connection test was successful, you will see the following:


Then Open the Access file that u want to Export to Oracle .Then at a time only one table in an Access file can be exported to Oracle .After opening Access(.mdb) file select a table say(ie: TEST) and right click the TABLE in OBJECT and select EXPORT









In the status bar of Access file at this time instead of READY status it will show EXPORT

after completing this without error go and check in your oracle database the access table and data will be exported to oracle

Table Creation/// NOT NULL (check constraint)

Everything (Table Creation including Primary ,Foreign key ,Check constraint and giving user defined CONSTRAINT NAME for NOT NULL CONSTRAINT) in a single Script ;without using alter command for predefined constraints till design time


CREATE TABLE   STUDENTS_MAST(
N_STUDID NUMBER(5),CONSTRAINT PK_STUDID  PRIMARY KEY(N_STUDID))


CREATE TABLE STUDENT_TEAM (
N_STDTMID NUMBER(10) ,CONSTRAINT PK_STUDENT_TEAM PRIMARY KEY(N_STUDTMID),
N_STUDID NUMBER(10),CONSTRAINT FK_STUD_TEAM_EMPID FOREIGN KEY(N_STUDID) REFERENCES STUDENTS_MAST(N_STUDID),
C_TLFLAG CHAR(1) ,CONSTRAINT CK_STUD_TEAM_TLFLAG CHECK (C_TLFLAG IN ('Y','N')) ,
D_STDATE DATE CONSTRAINT NN_STUD_TEAM_STDATE NOT NULL,
D_ENDDATE DATE CONSTRAINT NN_STUD_TEAM_ENDATE NOT NULL,
D_ENTRYDAT DATE DEFAULT SYSDATE )


--------------

CREATE TABLE STUDENT_TEAM (N_STDTMID NUMBER(10) CONSTRAINT NN_STUDENT_STATUS_TMID NOT NULL)


ALTER TABLE STUDENT_STATUS ADD ( CONSTRAINT NN_ST_STATUS_TMID CHECK (N_STDTMID IS NOT NULL))


Both Scripts will restrict not null values but error code will be different first script results


ORA-01407: cannot update to NULL

ORA-01400: cannot insert NULL into N_STDTMID

second script results


Another difference is the null(nullable) field will be true when null is checked by first script but it will not be for second second script
-----------
During check constraint creation if already exists some records in the particular table which violates the new check constraint.This error will be displayed
ORA-02293: cannot validate (##########) - check constraint violated

-----------------

Oracle Wokspace 4

dbms_wm.gotodate

begin
dbms_wm.gotoworkspace('B_focus_1');
end;

insert into test values (6,'Wayanad','YS','n',null,sysdate);

select * from test order by one ;

begin
dbms_wm.gotodate( (sysdate-5)) ;
end;

select * from test order by one ;

begin
dbms_wm.gotoworkspace('B_focus_1');
end;

select * from test order by one ;

Freezeworkspace

begin
dbms_wm.freezeworkspace('B_focus_1', 'READ_ONLY');
end;

insert into test values (6,'Wayanad','YS','n',null,sysdate);


This Error will be throwed when we try any DML operations (here to insert data) into a readonly workspace.

ORA-20123: workspace 'B_focus_1' is currently frozen in READ_ONLY mode


Export And Import Implications
The following implications are a result of version-enabling tables:

* Imports of version-enabled tables can only be performed if the target database has Workspace Manager installed and no workspaces defined other than LIVE.
* Only full database exports are supported with version-enabled databases.
* The IGNORE=Y parameter must be set for imports of version enabled databases.
* Imports of version-enabled databases cannot use the FROMUSER and TOUSER functioanlity.

For More about workspaces Refer  here