Showing posts with label Create. Show all posts
Showing posts with label Create. Show all posts

May 21, 2009

DB LINK

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services. Here example are given from Oracle database to another Oracle database .

CREATE DATABASE LINK "dblink_name"
CONNECT TO user_name
IDENTIFIED BY password
USING sid_name

In Some situation even though user_name,sid_name and password is correct database link created will result to a failure ;this is because Oracle cannot able to get the connection because of some reason ;in-order to meet this situation we want to specify more details for DBLink creation then this script cam be used.

CREATE DATABASE LINK "dblink_name"
CONNECT TO user_name
IDENTIFIED BY 'password'
USING '(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=host_name)(PORT=1521)))
(CONNECT_DATA=(SID=sid_name)))';

Errors

1)
By using DBLink DDL operations are not allowed .Such as Create ,Drop ,Truncate etc

Truncate table TABLENAME@DBLINK
ORA-02021: DDL operations are not allowed on a remote database


2)
If a Table which containing LOB datatypes are not able select using dblink

create table test_db ( fild_db clob,num number)

select * from test_db@convert;
ORA-22992: cannot use LOB locators selected from remote tables

Here only selection of datatypes other than LOB are allowed

select num from test_db@convert; --> This Works Fine.

3)

Before dropping a DBLink all transactions should be end else this error will occur.
ORA-02018: database link of same name has an open connection

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

May 11, 2009

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;
/

Apr 20, 2009

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).