Oracle: How to capture errors using "Log Errors Into" Clause

1) Create an error table to capture the errors from Insert, Update and Delete. To create this table,use Oracle's built in create_error_log function

Exec dbms_errlog.create_error_log("SOURCE_TABLE")

SOURCE_TABLE: Replace SOURCE_TABLE with name of your table. By default, error table will be prefixed by "ERR$_"

If you want to create a error table with a different name,

Exec dbms_errlog.create_error_log("SOURCE_TABLE","ERROR_TABLE_NAME")

By Default Error table will contain all columns from source table.
If your source_table contains long or blob columns, you need to skip them in the error table

Exec dbms_errlog.create_error_log("SOURCE_TABLE", skip_unsupported =>true)

Example: Insert into table_name ( col1,col2....) Values (Col1,Col2....)
log errors into err$_table_name
reject limit unlimited;

Update table_name
Set Col1 = ....
Where.....
log errors into err$_table_name
reject limit unlimited;

Delete table_name
Where.....
log errors into err$_table_name
reject limit unlimited;

If you don't use reject limit unlimited your transaction will fail and only 1 record will be inserted in the err$ table.

Oracle: How to capture errors using "Log Errors Into" Clause

1) Create an error table to capture the errors from Insert, Update and Delete. To create this table,use Oracle's built in create_error_log function

Exec dbms_errlog.create_error_log('SOURCE_TABLE')

SOURCE_TABLE: Replace SOURCE_TABLE with name of your table. By default, error table will be prefixed by "ERR$_"

If you want to create a error table with a different name,

Exec dbms_errlog.create_error_log("SOURCE_TABLE","ERROR_TABLE_NAME")

By Default Error table will contain all columns from source table.
If your source_table contains long or blob columns, you need to skip them in the error table

Exec dbms_errlog.create_error_log("SOURCE_TABLE", skip_unsupported =>true)

Example: Insert into table_name ( col1,col2....) Values (Col1,Col2....)
log errors into err$_table_name
reject limit unlimited;

Update table_name
Set Col1 = ....
Where.....
log errors into err$_table_name
reject limit unlimited;

Delete table_name
Where.....
log errors into err$_table_name
reject limit unlimited;

If you don't use reject limit unlimited your transaction will fail and only 1 record will be inserted in the err$ table.

Oracle: How to convert CLOB to BLOB

CREATE OR REPLACE Procedure Convert_Clob_To_Blob( clobvalue in clob , blobValue in out blob)
as
Position pls_integer := 1;
Temp_Buffer raw(32767);
Length_To_Copy int ;
clob_Length pls_integer := dbms_lob.getLength(clobValue);

begin

dbms_lob.open(BlobValue,dbms_lob.lob_readwrite);

--Length_To_Copy := least(1000,(clob_Length - Position) + 1);

While (Position <= clob_Length ) loop

Length_To_Copy := least(1000,(clob_Length - Position) + 1);

Temp_Buffer := utl_raw.cast_to_raw(dbms_lob.substr(clobValue,Length_To_Copy,Position));
If utl_raw.length(Temp_Buffer) > 0 then
dbms_lob.writeappend(BlobValue,utl_raw.length(Temp_Buffer),Temp_Buffer);
end if;

Position := Position + Length_To_Copy;

--Length_To_Copy := least(1000,(clob_Length - Position) + 1);


end loop;

dbms_lob.close(BlobValue);




end;
/

In order to test the above code look at this example.Lets assume you have a table called TABLE_NAME with a clob column called CLOB_COLUMN

Declare
b blob;
Begin
For cTemp in ( Select clob_column from table_name ) loop
Begin
b := null;

Insert into TABLE_NAME2(BLOB_COLUMN) values (empty_blob())
returning BLOB_COLUMN into b;
Convert_Clob_To_Blob (cTemp.clob_column,b);
end;
end loop;
Commit;
end;
/

Oracle: How to convert CLOB to BLOB

CREATE OR REPLACE Procedure Convert_Clob_To_Blob( clobvalue in clob , blobValue in out blob)
as
Position pls_integer := 1;
Temp_Buffer raw(32767);
Length_To_Copy int ;
clob_Length pls_integer := dbms_lob.getLength(clobValue);

begin

dbms_lob.open(BlobValue,dbms_lob.lob_readwrite);

--Length_To_Copy := least(1000,(clob_Length - Position) + 1);

While (Position <= clob_Length ) loop

Length_To_Copy := least(1000,(clob_Length - Position) + 1);

Temp_Buffer := utl_raw.cast_to_raw(dbms_lob.substr(clobValue,Length_To_Copy,Position));
If utl_raw.length(Temp_Buffer) > 0 then
dbms_lob.writeappend(BlobValue,utl_raw.length(Temp_Buffer),Temp_Buffer);
end if;

Position := Position + Length_To_Copy;

--Length_To_Copy := least(1000,(clob_Length - Position) + 1);


end loop;

dbms_lob.close(BlobValue);




end;
/

In order to test the above code look at this example.Lets assume you have a table called TABLE_NAME with a clob column called CLOB_COLUMN

Declare
b blob;
Begin
For cTemp in ( Select clob_column from table_name ) loop
Begin
b := null;

Insert into TABLE_NAME2(BLOB_COLUMN) values (empty_blob())
returning BLOB_COLUMN into b;
Convert_Clob_To_Blob (cTemp.clob_column,b);
end;
end loop;
Commit;
end;
/

Oracle: How to retrieve DDL of any object

Select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual

Some key parameters for get_ddl

object_type - The type of object to be retrieved. ex: TABLE, TABLESPACE,INDEX etc
object_name - Name of the object.
object_schema- Schema containing the object. Defaults to the caller's schema.

ex: Select dbms_metadata.get_ddl ('TABLE','EMPLOYEE','SCOTT') from dual

Oracle: How to retrieve DDL of any object

Select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual

Some key parameters for get_ddl

object_type - The type of object to be retrieved. ex: TABLE, TABLESPACE,INDEX etc
object_name - Name of the object.
object_schema- Schema containing the object. Defaults to the caller's schema.

ex: Select dbms_metadata.get_ddl ('TABLE','EMPLOYEE','SCOTT') from dual

f

Toad: Debug menu grayed out (cannot debug any procedure)

Even if you have Toad XPert Edition or Toad with DBA Module, I sometime find the debug menu grayed out. Even If I create a new proceudre, It's still grayed out. If you get this issue, try the following and reconnect.

Issue Debug Connect Sesstion to your user as SYS or System.

Grant DEBUG CONNECT SESSION TO schema_owner

Replace Schema_Owner with your user name.

Log out and Log back in, Now Toad Debug menu will be available.

Another workaround is to give DBA Privs which is not recommended.

Toad: Debug menu grayed out (cannot debug any procedure)

Even if you have Toad XPert Edition or Toad with DBA Module, I sometime find the debug menu grayed out. Even If I create a new proceudre, It's still grayed out. If you get this issue, try the following and reconnect.

Issue Debug Connect Sesstion to your user as SYS or System.

Grant DEBUG CONNECT SESSION TO schema_owner

Replace Schema_Owner with your user name.

Log out and Log back in, Now Toad Debug menu will be available.

Another workaround is to give DBA Privs which is not recommended.

DC

Oracle: How to generate N sequential numbers

How to generate N sequential numbers using sql

select level from
dual
connect by level < N

Replace N by whatever number you want.

select level from dual connect by level <> 50

Oracle: How to generate N sequential numbers

How to generate N sequential numbers using sql

select level from
dual
connect by level < N

Replace N by whatever number you want.

select level from dual connect by level <> 50

LDAP: In a nutshell

LDAP Lightweight Directory Access Protocol (Based on X 500 Standard)


LDAP --> Protocol to retrieve data stored in information directory, which is heavily optimized for read performance.

LDAP --> Allows you to securely delegate read and modification authority based on your specific needs using ACIs (collectively, an ACL, or Access Control List).

LDAP --> Tree Structure

Top level node called base DN (Distinguished Name)
Base DN in one of the 3 formats (for foobar company in US)

o="company name",c=Country
ex: o="foobar,Inc", c=US
(not preferred now a days,as no place to put website name)

o="website of the company"
ex: o=foobar.com (most common as straight forward)

dc=compnay, dc = com
ex: dc=foobar,dc=com
(used nowadays, Microsoft Acive Directory uses this format)

Here o=>organisation, c=>country,dc=>domain components


Under root base DN, Create nodes that logically seperate your data.
For Historic reasons (X500),These logical components are set up as OU,CN
OU=>Origanizational Unit
CN=>Common Name

All entires in LDAP have a unique DN and this DN is composed of 2 parts, RDN(Relative) and lcoation within the ldap direcotry where the record resides.
RDN is the portion of your DN that is not related to the directory tree structure.

Most entries stored in ldap will have a name, they are stored as CN

Most objects stored in LDAP will use CN value as RDN

So, If I want to store recipie for Oatmeal raisin

ex:
dc=foobar, dc=com
- ou=recipie
-- cn=oatmeal raisin

So, the full DN for the LDAP record is (Remeber it read it backwards) cn=oatmeal raisin,ou=recipie,dc=foobar,dc=com

Typically to store employee logins either CN is used store their name or UID is used to store the login

ex: dc=abclondon,dc=com
-ou=london
--cn="ramesh manni" or uid="rmanni" ----> name-based or login-based

LDAP stores all information for a given record as key-value pairs
ex:
dn: cn=Oatmeal Deluxe, ou=recipes, dc=foobar, dc=com
cn: Instant Oatmeal Deluxe
recipeCuisine: breakfast
recipeIngredient: 1 packet instant oatmeal
recipeIngredient: 1 cup water
recipeIngredient: 1 pinch salt
recipeIngredient: 1 tsp brown sugar
recipeIngredient: 1/4 apple, any type

Searches against these attribute/value pairs are case insensitive excpet for passwords

Great site to discuss Audio/Video devices

Oracle: Regular Expression: Examples

How to split strings like "ABC Company (XXX)" into "ABC Company", "XXX"


Select col1, regexp_substr(col1,'[^(]*') Comp_Name,
ltrim(regexp_substr(col1,'\([^)]*'),'(') comp_code
from ( Select 'ABC Company (XXX)' col1 from dual ).

Script does 3 things

1) For name, It takes everything till it hits '('
2) For Code, it takes everything from '(' [you need to escape open paranthesis symbol], till it finds the closing parenthesis ')'.
3) Ltrim is to remove the Open parenthesis in the code.

Oracle: Regular Expression: Examples

How to split strings like "ABC Company (XXX)" into "ABC Company", "XXX"


Select col1, regexp_substr(col1,'[^(]*') Comp_Name,
ltrim(regexp_substr(col1,'\([^)]*'),'(') comp_code
from ( Select 'ABC Company (XXX)' col1 from dual ).

Script does 3 things

1) For name, It takes everything till it hits '('
2) For Code, it takes everything from '(' [you need to escape open paranthesis symbol], till it finds the closing parenthesis ')'.
3) Ltrim is to remove the Open parenthesis in the code.

Regular Expression: How to split comma seperated values using sql

Lets create a table first and insert few comma seperated rows.

1) create table tab1 (owner number, cars varchar2(200));

2) insert into tab1 (
select 1, 'Ford,Toyota,Nissan' from dual union all
select 2, 'Lexus,Mercedes,BMW,Infiniti' from dual union all
select 3, 'Ferrari' from dual union all
select 4, 'Porsche,Lotus,Lamborghini,Maserati,Aston Martin' from dual union all
select 5, 'Maybach,Bentley' from dual);

3) select owner, cars str from tab1;

Now here is the sql to split.

select owner, car
from ( select owner, regexp_substr(str, '[^,]+', 1, level) car,
level lv, lag(level, 1, 0) over (partition by owner order by level) lg
from ( select owner, cars str from tab1 )
connect by regexp_substr(str, '[^,]+', 1, level) is not null
)
where lv != lg;

Replace"," in [^,] with your delimiter