Wednesday, June 25, 2014

Dynamic Cursor in mysql

One of the my Anonymous friend ask the question on my blog and the question is
"What if my cursor select query itself is dynamic? Does MySql support this functionality?"
First of all thanks to Anonymous friend to ask a question.

Yes it can support, its a little bit tricky.
First of all the problem is that we have to open a cursor for a query which is dynamic, variable will be pass at runtime.

We will open the cursor for view which is not exist at the time. In later step we will create view.
Then we will open cursor and later we can delete the view.
A prepare statement can be used to create a view with the dynamic SQL and the cursor can select from this statically-named view. There is almost no performance impact.

we have an example of using a view to pass the column name and table name into a cursor.


DELIMITER //
-- drop procedure if exists
DROP PROCEDURE IF EXISTS dynamic_Cursor//

CREATE PROCEDURE dynamic_Cursor(IN columnname varchar(255), tablename varchar(50))
BEGIN
DECLARE cursor_end CONDITION FOR SQLSTATE '02000';
DECLARE v_column_val VARCHAR(50);
DECLARE done INT DEFAULT 0;

-- declare the cursor for view which is not exist at that time
DECLARE cur_table CURSOR FOR SELECT * FROM emp_vw;
DECLARE CONTINUE HANDLER FOR cursor_end SET done = 1;

-- dynamic query will built the view
SET @query = CONCAT('CREATE VIEW emp_vw as select ', columnname, ' from ', tablename);
select @query;
PREPARE stmt from @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Open cursor
OPEN cur_table;
FETCH cur_table INTO v_column_val;
WHILE done = 0 DO
SELECT v_column_val;
FETCH cur_table INTO v_column_val;
END WHILE;
CLOSE cur_table;

DROP VIEW emp_vw;

END;
//

DELIMITER ;



call dynamic_Cursor('ename','emp');



continue part-2 of post
Create procedure, Cursor example , execute immediate

Tuesday, June 24, 2014

component "Excel Destination" (40) failed validation and returned error code 0xC020801C

component "Excel Destination" (40) failed validation and returned error code 0xC020801C

machine - 64 bit
windows - win7
Sqlserver - 2008

I got below error while execute task from data flow tab. I am trying to read data from raw file (for this i use component - Raw File Source) after some sort process i was trying to write the data on excel file. which fails with below error.

SSIS package "Package.dtsx" starting.
Information: 0x4004300A at create ExcelFilefrom Raw File, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC020801C at create ExcelFilefrom Raw File, Excel Destination [40]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at create ExcelFilefrom Raw File, SSIS.Pipeline: component "Excel Destination" (40) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at create ExcelFilefrom Raw File, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at create ExcelFilefrom Raw File: There were errors during task validation.
SSIS package "Package.dtsx" finished: Success.


where "create ExcelFilefrom Raw File" name of data flow.

Problem:-

If you have installed BIDS (Buisness intelligence Development Studio 64 bit package) on 64 bit machine, having connections that only support 32bit (like Excel, Access and old ODBC connections) you will get an error like above.

Solution:
The solution is to run your package in 32bit mode. This can be done within Visual Studio for debugging or within SQL Server Management Studio for scheduled packages.

This solution will also allow you to debug a Script Task, because that also doesn't work in 64bit mode.

step to be follow:
- open project
- right click on project name and select properties
- window will popup
- Look for "Debugging"
- Debug Options > "run64BitRuntime" set to "false"





After applying changes as above try to re-execute the dataflow task and successful outcome will be shown.
SSIS package "Package.dtsx" starting.

Information: 0x4004300A at create ExcelFilefrom Raw File, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80049304 at create ExcelFilefrom Raw File, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
Information: 0x40043006 at create ExcelFilefrom Raw File, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at create ExcelFilefrom Raw File, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at create ExcelFilefrom Raw File, SSIS.Pipeline: Execute phase is beginning.
Information: 0x40043008 at create ExcelFilefrom Raw File, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at create ExcelFilefrom Raw File, SSIS.Pipeline: "component "Excel Destination" (68)" wrote 538 rows.
Information: 0x40043009 at create ExcelFilefrom Raw File, SSIS.Pipeline: Cleanup phase is beginning.
SSIS package "Package.dtsx" finished: Success.

Wednesday, June 11, 2014

CHAR semantics and BYTE semantics

Earlier we are define the datatypes with byte size only (i.e. varchar2(10)), if we set datatype size varchar2(10) then it will store 10 characters for single byte character set. If we want to store the multibyte character of any other language then, in same column we are not able to store the 10 characters are multibyte in size.

From 9i oracle introduced NLS_LENGTH_SEMANTICS parameter. While installation oracle ask for character storage type "CHAR/BYTE".
We can check default character set with initialization parameter "NLS_LENGTH_SEMANTICS". The default length semantic is byte.

SQL> show parameter nls_length_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE

Semantics
Two type CHAR semantics and BYTE semantics

There are three different ways, we can declare CHAR/VARCHAR2 data type.
Let us take VARCHAR2(10) as an example here.

1. VARCHAR2(10)
2. VARCHAR2(10 BYTE)
3. VARCHAR2(10 CHAR)

BYTE SEMANTICS treat strings as a sequence of bytes. This is the default for character datatypes.
VARCHAR2(10 BYTE) in this oracle store only 10 byte of data, it depends upon the size of char. But if database store multibyte char it will store only 5 char.

CHAR SEMANTICS treat strings as a sequence of characters.
VARCHAR2(10 CHAR) will store 10 char at all whether they are single byte or mutlibyte character.

example:
If you want to fill five chinese char and five english words together, then In byte semantics, this would require (5*3 bytes) + (1*5 bytes) = 20 bytes; in character semantics, the column would require 10 characters.

VARCHAR2(20 BYTE) use byte semantics.
VARCHAR2(10 CHAR) use character semantics.

WHICH IS HELPFUL AT WHAT PLACE
Character semantics are useful for defining varying-width multibyte strings; it reduces the complexity when defining the actual length requirements for data storage.



Tuesday, June 10, 2014

SQLNET.AUTHENTICATION_SERVICES

SQLNET.AUTHENTICATION_SERVICES

Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services.
If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.

Default value is null
None
none for no authentication methods. A valid username and password can be used to access the database.


ALL
all for all authentication methods


NTS
SQLNET.AUTHENTICATION_SERVICES= (NTS) It allows Windows users to be authenticated using Windows NT native security.

To connect sys user as "/ as sysdba", we need to add current machine user in "ora_dba" group. by doing with we can connect sys user with supplying the password.

Connection error ORA-01031: insufficient privileges

I just turn on the computer and when trying to connect with oracle with sys user with command "/ as sysdba " it throws error "ORA-01031: insufficient privileges"



What can be problem :
1. Machine user is not associated with the "ora_dba" group.
2. Authentication setting in sqlnet.ora file.

This problem is arise after i recover the database crashed because of control file lost. However this doesn't matter the problem can be face any time.

Resolve the problem:
1. try to login with sys user as /as sysdba

C:\Users\gurjeet>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 10 08:15:35 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


2. First of all we will check the whether the machine user associated with "ora_dba" group or not.
for this (on windows)
move to 
start - control panel - administrative tools(1) - computer management(2) - groups(3) - ora_dba(4) [numbers are counted from below image]




3. After double click on "ora_dba" we can see no machine user is associated with ora_dba group.
So here we need to add the current machine user in this group , For this click on "ADD" button.



4. Type machine user name after click on "check Names" button


5. Apply ok.
  

6. Open cmd and type sqlplus / as sysdba



If still not able to connect with the sys user using command / as sysdba 
It might be problem that you are using wrong  authentication metthod in sqlnet.ora file.

To change the authentication method locate the file "sqlnet.ora" , which will  reside in "$oracle_home/NETWORK/ADMIN.sqlnet,ora" and change the authentication mode from NONE to NTS.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

Related series of post 
authentication method 

SQLNET.AUTHENTICATION_SERVICES

Thursday, June 5, 2014

Copy / clone oracle database

Lets we have current database name ZA1

preprerequisite-
1. We need parameter file from old database or you can write your own
 create pfile='d:\ZA2.ora' from spfile;

2. Get the datafiles and logfiles path information, we can get the path of control file from pfile.
select name from v$datafile;
select member from v$logfile;

3. Take the backup of control file to trace
alter database backup controlfile to trace;

4. Need cold database backup
shu immediate;
Now copy all datafiles and log file to specific folder


-- Clone the database - cold cloning
1. Create new folder structure (for control file, data files), for easily understand you can get the required folder structure from *.ora file and backup of control fil.
2. Even more , change the path in *.ora file and backup of control file

3. Open cmd

set oracle_sid=ZA2

oradim -new -sid ZA2 -intpwd ZA2 -startmode m -pfile D:\ZA2.ora
(make sure that u have place the "ZA2.ora" file at path, it will contain the contents of ZA1.ora file having some related changes of database name and folder path.)

You might got this error
DIM-00014: Cannot open the Windows NT Service Control Manager.
solution open cmd with "run as administrator"

4. Open database at nomount state
C:\Windows\system32> sqlplus sys/ZA2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 9 15:35:48 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.


SQL> startup nomount pfile='D:\ZA2.ora'

5.  At this stage we need to create control file after startup database at nomount state
   connect with sysdba user (before creation of control file and reuse of datafile , copy all the datafiles and redo log file)

Get the create control file script from trace file and change the Database name & files path, also change 'REUSE' needs to be changed to 'SET'.

 CREATE CONTROLFILE SET DATABASE "ZA2" RESETLOGS force LOGGING noARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 8
     MAXLOGHISTORY 1
     LOGFILE
     GROUP 1 'D:\app\gurjeet\oradata\ZA2\REDO01.LOG' SIZE 50M,
     GROUP 2 'D:\app\gurjeet\oradata\ZA2\REDO02.LOG' SIZE 50M,
    GROUP 3 'D:\app\gurjeet\oradata\ZA2\REDO03.LOG' SIZE 50M
    DATAFILE
    'D:\app\gurjeet\oradata\ZA2\SYSTEM01.DBF',
    'D:\app\gurjeet\oradata\ZA2\SYSAUX01.DBF',
    'D:\app\gurjeet\oradata\ZA2\UNDOTBS01.DBF',
    'D:\app\gurjeet\oradata\ZA2\USERS01.DBF'
    CHARACTER SET AL32UTF8
/

6. Now Database went into mount state after creation of control file.

7. Open the database
 Alter database open resetlogs;



Related series of posts
ORA-01503: ORA-01161: ORA-01110:
backup controlfile to trace / generate script of control file
Recover database if it is in between crashed


for any modification / updation / suggestion mail at gurjeetkamboj@gmail.com or comment.


ORA-01503: ORA-01161: ORA-01110:


CREATE CONTROLFILE REUSE DATABASE "ZA2" RESETLOGS force logging noARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 1
  LOGFILE
  GROUP 1 'E:\app\gurjeet\oradata\ZA2\REDO01.LOG' SIZE 50M,
  GROUP 2 'E:\app\gurjeet\oradata\ZA2\REDO02.LOG' SIZE 50M,
  GROUP 3 'E:\app\gurjeet\oradata\ZA2\REDO03.LOG' SIZE 50M
  DATAFILE
  'E:\app\gurjeet\oradata\ZA2\SYSTEM01.DBF',
  'E:\app\gurjeet\oradata\ZA2\SYSAUX01.DBF',
  'E:\app\gurjeet\oradata\ZA2\UNDOTBS01.DBF',
  'E:\app\gurjeet\oradata\ZA2\USERS01.DBF'
CHARACTER SET AL32UTF8
SQL> /
CREATE CONTROLFILE REUSE DATABASE "ZA2" RESETLOGS force logging
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name ZA1 in file header does not match given name of ZA2
ORA-01110: data file 1: 'E:\app\gurjeet\oradata\ZA2\SYSTEM01.DBF'

When it can occur:

1. when we try to create control file while creating new database using old database datafiles and logfiles.
2. Create new control file when old control file is corrupt or lost.

Problem:-
1. It can be because of the db name in "CREATE CONTROLFILE REUSE DATABASE "ZA2" " ie ZA2 is not match with the db name store in datafile and log files.

Solution:-
1. Use appropriate database name in create control file statement.
2. If you want to change the database name (want to clone the database) use "SET" instead of "REUSE" in create control file script.
    i.e. CREATE CONTROLFILE SET DATABASE "ZA2"

   


-from oracle docs
SET DATABASE Clause
Use SET DATABASE to change the name of the database. The name of a database can be as long as eight bytes.



Tuesday, June 3, 2014

backup controlfile to trace / generate script of control file

Some time we need to recreate the control file in database so for this we should have the backup of control file.

Why we need to recreate the control file:
- All copies of our control file is lost or corrupted in database
- cloning of database
- recovering database using old datafiles, redologfiles
- we need to change the parameter value of database

how to have the backup of control file in form of script:
1. First of all you should know the path of alert log file in your database.

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      d:\app\gurjeet\diag\rdbms\orcl
                                                 \orcl\trace

                                               
2. We need to execute below command, it will generate the script of "create control file"
SQL> alter database backup controlfile to trace;

Database altered.


3. Move to above directory, path return by parameter "user_dump_dest", and look for alert file named as "alter_dbname".
open the file and look for the content like below

The create control file script will looks like below.

Create controlfile reuse set database "orcl"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'D:\app\gurjeet\oradata\orcl\SYSTEM01.DBF',
'D:\app\gurjeet\oradata\orcl\SYSAUX01.DBF',
'D:\app\gurjeet\oradata\orcl\UNDOTBS01.DBF',
'D:\app\gurjeet\oradata\orcl\USERS01.DBF'
LOGFILE GROUP 1 ('D:\app\gurjeet\oradata\orcl\redo01.log') SIZE 51200K,
GROUP 2 ('D:\app\gurjeet\oradata\orcl\redo02.log') SIZE 51200K,
GROUP 3 ('D:\app\gurjeet\oradata\orcl\redo03.log') SIZE 51200K RESETLOGS
web stats