SSIS: Connect to Oracle - Cannot retrieve the column code page info from the OLE DB Provider

SSIS: Connect to Oracle - Cannot retrieve the column code page info from the OLE DB Provider




I have tried to access data in Oracle from SSIS via OLE DB and the component reported the following warnings:

"Cannot retrieve the column code page info from the OLE DB Provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used."

The fix is simple. Set AlwaysUseDefaultCodePage=TRUE on the offending OLE DB Source component and it works fine.

Read More

How to extract Month, Year value from date using Extract Function in Oracle

Extract Function in Oracle

The extract function extracts a value from a date. You can only extract YEAR, MONTH, and DAY from a DATE



Syntax:

  EXTRACT (
{ YEAR
MONTH
DAY
HOUR
MINUTE
SECOND }
{ TIMEZONE_HOUR
TIMEZONE_MINUTE }
{ TIMEZONE_REGION
TIMEZONE_ABBR }
FROM { date_value
interval_value } )


• Works in - Oracle 11g, Oracle 10g, Oracle 9i


Sample:



select extract(YEAR FROM sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)

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

2013

1 row selected.





select extract(MONTH FROM sysdate) from dual;


EXTRACT(MONTHFROMSYSDATE)

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

5

1 row selected.



select extract(DAY FROM sysdate) from dual;



EXTRACT(DAYFROMSYSDATE)

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

14

1 row selected.

Read More

Get month value from Date in SQL server

Get month value from Date in SQL server:

Sample:


select month(getdate()) as month_num, datename(month,getdate()) as month_name


month_num                 month_name


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

5                          May





select month(getdate()) as month_num , datename(month,'1-jan-2013') as month_name

month_num                    month_name


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

5                            January

Read More

Error message 402: The data types ntext and varchar are incompatible in the equal to operator.

where clause with ntext in SQL server:

I have a column of type ntext in SQL server how can I evaluate it in a WHERE clause.


Structure of the Table:

sp_help Priorities


Coulumn_name           Type

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

PriorityId                      int

ProjectId                      int

Priority                         nvarchar

Description                   ntext



Sample :

select * from Priorities

where Description = 'High'



The error message for the above Query:

Msg 402, Level 16, State 1, Line 2

The data types ntext and varchar are incompatible in the equal to operator.



Solution:

select * from Priorities
where cast(Description as nvarchar(max)) = 'High'

Read More

INSERT Statement

INSERT Statement

The SQL INSERT statement allows you to insert a single record or multiple records into a table.

Syntax:

INSERT INTO table

(column-1, column-2, ... column-n)

VALUES

(value-1, value-2, ... value-n);



INSERT Statement - Using VALUES keyword example

Sample:

insert into customer (customer_id,customer_name)
values (101,'CustName');



SQL INSERT Statement - Using sub-selects

Sample:

insert into customer (customer_id,customer_name)
select cust_id,first_name from customer_temp;

Read More

CREATE TABLE Statement

The SQL CREATE TABLE statement allows you to create and define a table. Each column must have a data type. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.




The syntax for the SQL CREATE TABLE statement is:

CREATE TABLE table_name

(
column1 datatype null/not null,
column2 datatype null/not null,
...
);



For Example



CREATE TABLE customer
(
customer_id number(10) not null,
customer_name varchar2(50) null,
address varchar2(50),
city varchar2(50),
state varchar2(25),
zip_code varchar2(10)
);

Read More

SQL Statement Types

SQL Statement Types:

1. DDL – Data Definition Language.

2. DML – Data Manipulation Language.

3. DCL – Data Control languages



DDL Statements:

• Create – it used create objects like Tables,Views and etc…

• Alter – It used to alter the Structure of the Obejects like Tables, Views and etc…

• Truncate – It removes all the records in table.

• Grant – Provides / Assigns Privileges

DML Statements:

• Select – Retrieve data from tables.

• Insert – It helps to insert data in to table.

• Update – It helps to Modify the Existing data in table.

• Delete – It helps to Remove data from table.



DCL Statements:

• Commit – It will ends the current Transaction & making the changes to permanent.

• Savepoint – It will identify a point or Num of transaction in Set of Transactions to which we can later roll back.

• Rollback- It will undo / remove all the changes made by Current transaction.

Read More

SQL Introduction

SQL Introduction

SQL stands for Structured Query Language and it is generally referred to as SEQUEL. SQL was developed by IBM in the 1970s.

The American National standard Institute (ANSI) published first SQL stands in 1986 and released updates in 1992 & 1999, Known as SQL 92 and SQL99.

SQL is Simple & powerful language to Create, Access and Manipulate data and object structure in the data base.

The Below Topics will help us to learn SQL.

• SQL Statement Types
• CREATE TABLE Statement
• INSERT Statement
• How to extract Month, Year value from date using Extract Function in Oracle

To Learn More Concepts:

• Oracle SQL Blogs
• Oracle DBA
• Oracle Tuning
• Oracle Insight

Read More