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