Error handling in PLSQL – SQLERRM and SQLCODE

Error Handling / Exception Handling in Plsql

While named or anonymous plsql block execution, will fail because of some error in between of execution because of dml error. We can capture the error and can also handle them by adding "EXCEPTION" block inside the plsql block, which leads to the successful execution of plsql block.

declare
    v_var varchar2(1000);
    TABLE_MISSING EXCEPTION;
    PRAGMA EXCEPTION_INIT(TABLE_MISSING,-942);
begin
    execute immediate ' select count(*) from v$datafile' into v_var;
    dbms_output.put_line(v_var);
    EXCEPTION
        WHEN TABLE_MISSING THEN
        NULL;
end;
/

We can also print the error or can store them into the variable, and can use them for further investigation. For print the error we need to use SQLCODE and SQLERRM funtion

declare
    v_var varchar2(1000);
    TABLE_MISSING EXCEPTION;
    PRAGMA EXCEPTION_INIT(TABLE_MISSING,-942);
begin
    execute immediate ' select count(NAME) from v$datafile' into v_var;
    dbms_output.put_line(v_var);
    EXCEPTION
        WHEN TABLE_MISSING THEN
        DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || SQLERRM );
        raise;
end;
/

alter table move command

"alter table move" command


This command is generally used to move the segments of table from one tablespace to other tablespace, so
 how to use this command:

ALTER TABLE TABLE_NAME MOVE TABLESPCE tablespace_name;

after this command indexes are become invalid, in that way we need to rebuild the indexes and at same we can change the segment of indexes
how to:

(MOVE INDEX TO TABLESPACE (NOT DOMAIN INDEX / FULL TEXT INDEXES,.,,, IOT- TOP  AND LOB ARE THESE ARE THE CUISINES OF DOMAIN INDEX))

ALTER INDEX INDEX_NAME REBUID TABLESPACE TRY;

then need to MOVE LOB SEGMENT TO NEW TABLESPACE
the table_name , and column_name information can get from the user_lobs data dictionary table:

alter table table_name move lob(column_name) store as segment_name_unique ( tablespace try);

Domain index lob segment can moved by rebuild with replace parameters 

ALTER INDEX DOMAIN_INDEX_NAME REBUILD PARAMETERS('REPLACE LEXER HYPHEN_LEXER STORAGE MYSTORE');

you may also like 

for last command follow this post::

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Space Tuning / Reclaim space from tablespace / Freeup unused space

First of all identified most wasted space tables after this we will move the table into another tablespace. For this create a new tablespace or we can move table in a preexisting tablespace with the help of  "alter table move" command.

alter table table_name move tablespace tablespace_name;

after executing this command now we need to rebuild index (with or without tablespace clause)

alter index index_name rebuild tablespace tablespace_name;

*note table should not contain Full text index or domain index
If it contains then the scenerio is :

1. Collect create index script only for domain indexes 
2. Save it at some safe place
3. Drop domain index
4. Move table with "alter table move" command as above.
5. Rebuild indexes as above
6. And Rebuild domain indexes

If you forgot to drop domain index , No problem u might got some error as below:
ORA-02327: cannot create index on expression with datatype LOB
ORA-30967: operation directly on the Path Table is disallowed
 

SQL> alter table DR$BLB_01$I move tablespace try;
alter table DR$BLB_01$I move tablespace try
            *
ERROR at line 1:
ORA-30967: operation directly on the Path Table is disallowed


SQL> alter table DR$BLB_01$K move tablespace try;

Table altered.

SQL> alter table DR$BLB_01$N move tablespace try;

Table altered.

SQL> alter table DR$BLB_01$R move tablespace try;
alter table DR$BLB_01$R move tablespace try
            *
ERROR at line 1:
ORA-30967: operation directly on the Path Table is disallowed


SQL> alter table EMP move tablespace try;

Table altered.

and indexes
SQL> alter index SYS_IL0000074265C00002$$ rebuild tablespace try;
alter index SYS_IL0000074265C00002$$ rebuild tablespace try
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

solution to this

alter table table_name move lob(column_name_having_blob_clob_datatype) store as a tablespace tablespace_name;


Read also::
short and brief description on "alter table move"

Finding USED UNUSED INDEX IN ORACLE

Finding USED UNUSED INDEX IN ORACLE

In Oracle by default index tracking / monitoring is off. In order to find unused used index we need to set "INDEX MONITORING" on.

command:
alter index index_name monitoring usage;

data will save in table "v$object_usage"

select * from v$object_usage;

This data dictionary table is independently store the schema data. To access this data dictionary table a schema/user does not need any extra grant or privileges.

Nanoseconds / Millisecond in Mysql

MySQL 5.6.4 milliseconds (fractional seconds) are supported by the newly introduced type TIMESTAMP(fsp), where fsp stands for fractional seconds precision. fsp ranges from 0 to 6 with 0 indicating there's no fractional part.

Mycurrent mysql version is 5.5.0024
after upgrade it to 5.6.0013, now i able save millisecond in database.

create table tymdate( column1 datetime(6));
insert into tymdate values ('2013-10-19 11:12:59.045673');

note*- Application users / Developers Please don't forget to update mysql j connector.

RowID Equivalent in SQLSERVER

Physical location of a row in SQL Server

Introduction

In Oracle, each row can be identified by the ROWID column. It is a pseudo column. This column contains the information about the address of row saved in datafile.

ROWNUM is quite different thing, if we compare it to ROWID, rownum to particular row will not remain fix, it gets change over any dml operation done on a table.

ROWID equivalent in sqlserver, we have  

SQLSERVER 2008 - %%physloc%%
SQLSERVER 2005 - %%lockres%%


QUERY USING PHYSLOC

select %%physloc%% from table_name;

To decode the value of above column, we can use below function
sys.fn_PhysLocFormatter



select %%physloc%% , sys.fn_physlocformatter(%%physloc%%) from table_name;

 

Result will be as below:

0xA901000001000000  (1:425:0)

How to read above format ?
row with ID = ???? is located in the file 1 on page 425 and in slot 0.

using this we can identify the actual data file of the row, using the view sys.database_files.

Some Queries

select * from table_name where %%physloc%% = 0xA901000001000000

this will return row

SQL Server Delete Cascade

src - http://randomconsultant.blogspot.in/2008/10/sql-server-cascade-delete.html

Delete cascade , recursive delete sqlserver 

CREATE Procedure spDeleteRows
/* 
Recursive row delete procedure. 

It deletes all rows in the table specified that conform to the criteria selected, 
while also deleting any child/grandchild records and so on.  This is designed to do the 
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys 
table to find any child tables, then deletes the soon-to-be orphan records from them using 
recursive calls to this procedure. Once all child records are gone, the rows are deleted 
from the selected table.   It is designed at this time to be run at the command line. It could 
also be used in code, but the printed output will not be available.
*/
 (
 @cTableName varchar(50), /* name of the table where rows are to be deleted */
 @cCriteria nvarchar(1000), /* criteria used to delete the rows required */
 @iRowsAffected int OUTPUT /* number of records affected by the delete */
 )
As
set nocount on
declare  @cTab varchar(255), /* name of the child table */
 @cCol varchar(255), /* name of the linking field on the child table */
 @cRefTab varchar(255), /* name of the parent table */
 @cRefCol varchar(255), /* name of the linking field in the parent table */
 @cFKName varchar(255), /* name of the foreign key */
 @cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
 @cChildCriteria nvarchar(1000), /* criteria to be used to delete 
                                           records from the child table */
 @iChildRows int /* number of rows deleted from the child table */

/* declare the cursor containing the foreign key constraint information */
DECLARE cFKey CURSOR LOCAL FOR 
SELECT SO1.name AS Tab, 
       SC1.name AS Col, 
       SO2.name AS RefTab, 
       SC2.name AS RefCol, 
       FO.name AS FKName
FROM dbo.sysforeignkeys FK  
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id 
                              AND FK.fkey = SC1.colid 
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id 
                              AND FK.rkey = SC2.colid 
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id 
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id 
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
     BEGIN
 /* build the criteria to delete rows from the child table. As it uses the 
           criteria passed to this procedure, it gets progressively larger with 
           recursive calls */
 SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + 
                              @cRefTab +'] WHERE ' + @cCriteria + ')'
 print 'Deleting records from table ' + @cTab
 /* call this procedure to delete the child rows */
 EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
 FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
     END
Close cFKey
DeAllocate cFKey
/* finally delete the rows from this table and display the rows affected  */
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName

In the above scenario, we were trying to perform the following :

    DELETE FROM X WHERE field1 = '234'

Using this procedure, we would use the following command:

    exec spDeleteRows 'X', 'field1 = ''234''', 0

Hashbytes sqlserver

HASHBYTES(), as the name implies, is a function or algorithm that generates a hash from some input. If you're not familiar with what a hash is, here's a good working definition for a hash function:
hash function - A hash function takes in data and returns back a fixed length block of bits such that any change to the data should result in a different block.

HASHBYTES() is actually a function which provides access to several hashing algorithms. In the earlier versions, it supports MD2, MD4, MD5, SHA, SHA1 algorithms and these algorithms are limited up to 20 bytes only.
In SQL Server 2012, we have an enhancement in this function and now it supports SHA2_256, SHA2_512 algorithms that can generate 32 and 64 bytes hash codes for the respective input.
SQL Server 2005 and up have the following protocols (how you specify them in HASHBYTES is in parentheses):

    MD 2     (MD2)
    MD 4     (MD4)
    MD 5     (MD5)
    SHA-0   (SHA)
    SHA-1   (SHA1)

SQL Server 2012 introduces these additional hashing algorithms:
    SHA-2 256 bits AKA SHA-256 (SHA2_256)
    SHA-2 512 bits AKA SHA-512 (SHA2_512)

Hashing Algorithms You Should Probably Avoid
Since a hash function will return a fixed length block, obviously, there are a finite set of possibilities for the output. Therefore, there's bound to be different inputs that will result in the same output. We call these situations collisions. What is of concern is if a collision can be engineered fairly quickly/cheaply. This is referred to as a collision attack. If a hashing algorithm is susceptible to this type of attack or other attacks with reasonable resources that either (a) allow you to create an identical hash with different input or (b) figure out the input from the hash, then those algorithms should be avoided. As of the writing of this tip, the following algorithms fall into that list:
    MD 2 - developed in 1989 by Ronald Rivest, it generates a 128-bit hash value. It is susceptible to several attacks and is no longer considered cryptopgraphically secure.
    MD 4 - Also developed by Rivest (1990), it generates a 128-bit hash value like MD 2. And like MD 2, it is susceptible to a couple of attacks and is no longer considered cryptographically secure.
    MD 5 - Developed by Rivest in 1992, it also generates a 128-bit hash value. While we see it used often to "digitally fingerprint" files and documents, it is also considered "broken" and no longer cryptographically secure.
    SHA-0 - SHA-0 was withdrawn shortly after being made public due to a "significant" flaw. It generates a 160-bit hash value. It was replaced by SHA-1.
Hashing Algorithms Which Are Good for Now
If you're not on SQL Server 2012, SHA-1 is the best choice to use.

The SHA-2 algorithm comes in several block sizes, of which SQL Server 2012 implements two:
    SHA-2 256 bit block size (called SHA-256)
    SHA-2 512 bit block size (called SHA-512)


Some simple examples
 :
DECLARE @String varchar(7);
Set @String ='j4info'

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD2' AS [Algorithm type]
, HASHBYTES('MD2', @String) as [HashBytes]
, LEN(HASHBYTES('MD2', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD4' AS [Algorithm type]
, HASHBYTES('MD4', @String) as [HashBytes]
, LEN(HASHBYTES('MD4', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD5' AS [Algorithm type]
, HASHBYTES('MD5', @String) as [HashBytes]
, LEN(HASHBYTES('MD5', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'SHA' AS [Algorithm type]
, HASHBYTES('SHA', @String) as [HashBytes]
, LEN(HASHBYTES('SHA', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'SHA1' AS [Algorithm type]
, HASHBYTES('SHA1', @String) as [HashBytes]
, LEN(HASHBYTES('SHA1', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2012 and above
SELECT 'SHA2_256' AS [Algorithm type]
, HASHBYTES('SHA2_256', @String) as [HashBytes]
, LEN(HASHBYTES('SHA2_256', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2012 and above
SELECT 'SHA2_512' AS [Algorithm type]
, HASHBYTES('SHA2_512', @String) as [HashBytes]
, LEN(HASHBYTES('SHA2_512', @String)) as [Length in Bytes]
GO
Note : Executions of above script in earlier version of SQL Server, it will return NULL value for SHA2_256 & SHA2_512.

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Grant required role/Previliges to user:
GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser;

Create storage preferences and print joins (lexer - basic_lexer)

begin
    ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
    ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k) lob (data) store as (disable storage in row cache)');
    ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE','tablespace LOB_INDEX storage (initial 32k) compress 2');
    ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
    ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
    ctx_ddl.set_attribute ( 'mylex', 'index_themes', 'NO');
    ctx_ddl.set_attribute ( 'mylex', 'index_text', 'YES'); 
end;
/
CREATE TABLE my_docs (
    id    NUMBER(10)     NOT NULL,
    name  VARCHAR2(200)  NOT NULL,
    doc   BLOB           NOT NULL
);

create index domain_idx on my_docs(doc) indextype is ctxsys.context parameters( 'lexer mylex storage mystore');
Now index data will store in tablespace "LOB_INDEX"


YOU may also like :-
basic-lexer printjoins domain indexes

Lexer -> Basic Lexer -> Printjoins in DOMAIN index

lexer  Web definitions

Fancy term for a tokener..

Lexer Types

Use the lexer preference to specify the language of the text to be indexed. To create a lexer preference, you must use one of the following lexer types:

Type     Description
BASIC_LEXER                     Lexer for extracting tokens from text in languages, such as English and most western European languages that use white space delimited words.
MULTI_LEXER                    Lexer for indexing tables containing documents of different languages
CHINESE_VGRAM_LEXER     Lexer for extracting tokens from Chinese text.
CHINESE_LEXER                 Lexer for extracting tokens from Chinese text.
JAPANESE_VGRAM_LEXER   Lexer for extracting tokens from Japanese text.
JAPANESE_LEXER               Lexer for extracting tokens from Japanese text.
KOREAN_MORPH_LEXER     Lexer for extracting tokens from Korean text.
USER_LEXER                      Lexer you create to index a particular language.
WORLD_LEXER                   Lexer for indexing tables containing documents of different languages; autodetects languages in a document


BASIC_LEXER

Use the BASIC_LEXER type to identify tokens for creating Text indexes for English and all other
supported whitespace-delimited languages.

The BASIC_LEXER also enables base-letter conversion, composite word indexing, case-sensitive indexing
 and alternate spelling for whitespace-delimited languages that have extended character sets.

In English and French, you can use the BASIC_LEXER to enable theme indexing.

Note:
Any processing the lexer does to tokens before indexing (for example, removal of characters,
 and base-letter conversion) are also performed on query terms at query time. This ensures
 that the query terms match the form of the tokens in the Text index.

BASIC_LEXER supports any database character set.

BASIC_LEXER attribute printjoins:
    Specify the non alphanumeric characters that, when they appear anywhere in a word (beginning, middle, or end), are processed as alphanumeric and included with the token in the Text index. This includes printjoins that occur consecutively.

    For example, if the hyphen '-' and underscore '_' characters are defined as printjoins, terms such as pseudo-intellectual and _file_ are stored in the Text index as pseudo-intellectual and _file_.
    
BASIC_LEXER ExampleThe following example sets printjoin characters and disables theme indexing with the BASIC_LEXER:

begin
ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
ctx_ddl.set_attribute ( 'mylex', 'index_themes', 'NO');
ctx_ddl.set_attribute ( 'mylex', 'index_text', 'YES');
end;

To create the index with no theme indexing and with printjoins characters set as described, issue the following statement:

create index myindex on mytable ( docs ) indextype is ctxsys.context parameters ( 'LEXER mylex' );

domain index is marked LOADING/FAILED/UNUSABLE

domain index is marked LOADING/FAILED/UNUSABLE

Replicate the error
CREATE TABLE my_docs (
   id    NUMBER(10)     NOT NULL,
   name  VARCHAR2(200)  NOT NULL,
   doc   BLOB           NOT NULL
 );


 ALTER TABLE my_docs ADD (
   CONSTRAINT my_docs_pk PRIMARY KEY (id)
 );


 CREATE SEQUENCE my_docs_seq;


 CREATE OR REPLACE DIRECTORY documents AS 'C:\work';


 CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name  IN  my_docs.name%TYPE) AS
   v_bfile      BFILE;
   v_blob       BLOB;
 BEGIN
   INSERT INTO my_docs (id, name, doc)
   VALUES (my_docs_seq.NEXTVAL, p_file_name, empty_blob())
   RETURN doc INTO v_blob;

   v_bfile := BFILENAME('DOCUMENTS', p_file_name);
   Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
   Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
   Dbms_Lob.Fileclose(v_bfile);

   COMMIT;
 END;
 /



create index idx_domain on my_docs(doc) indextype is ctxsys.context parameters ('sync (on commit)');

Now Rebuild the index "idx_domain" to replicate the error , on same time i execute below command and it throws error:

SQL>  EXEC load_file_to_my_docs('try.pdf');
BEGIN load_file_to_my_docs('try.pdf'); END;

*
ERROR at line 1:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
ORA-06512: at "G.LOAD_FILE_TO_MY_DOCS", line 5
ORA-06512: at line 1

Solution::
Rebuilds Domain index/ full text index as below:::
 
ALTER INDEX your_index REBUILD ONLINE PARAMETERS ('REPLACE LEXER your_lexer');