Posts

Showing posts from 2012

PL/SQL Finding Duplicate Values in a Table

select column_name, count(column_name)
from table_name
group by column_name
having count(column_name)>1;

PL/SQL Unique Constraints

create table supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  constraint u_supplier_id unique(supplier_id)
);
end;


create table supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  constraint u_supplier unique(supplier_id, supplier_name)
);
end;


declare
  v_supplier_name supplier.contact_name%type;
begin
  select user
  into v_supplier_name
  from dual;
  insert into supplier
  (
    supplier_id,
    supplier_name,
    contact_name
  )
  values
  (
    seq_supplier_id.nextval,
    v_supplier_name,
    'Test'
  );
end;

select * from supplier;

PL/SQL Using Sequence in Insert Command

declare
  v_last_name employees.last_name%type;
  v_hire_date employees.hire_date%type;
begin
  select user, sysdate
  into v_last_name, v_hire_date
  from dual;
    insert into employees
    (
      employee_id,
      first_name,
      last_name,
      email,
      hire_date,
      job_id
    )
    values
    (
      seq_employee_id.nextval,
      'Test',
      v_last_name,
      'test@xyz.com',
      v_hire_date,
      'IT_PROG'
    );
end;


select * from employees where job_id like '%IT%';
insert into employees
(
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  job_id
)
values
(
  seq_employee_id.nextVal,
  'Nurhak',
  'KAYA',
  'nurhak.kaya@xyz.com',
  sysdate,
  'IT_PROG'
);

Using Apache log4net in C#/.NET

Image
Here are the steps; Download the latest version of log4net library from Apache web site and add reference of log4net.dll in to your project.Go to your project's AssemblyInfo.cs file and add below line at the end of it.                       [assembly: log4net.Config.XmlConfigurator(Watch = true)]Go to your project's Web.config file and add these in it, just below <configuration> tag.<configSections>     <sectionname="log4net"type="log4net.Config.Log4NetConfigurationSectionHandler,log4net, Version=1.2.10.0, Culture=neutral, PublicKeyToken=1b44e1d426115821" />   </configSections>
  <!-- Log4net Logging Setup -->   <log4net>     <appendername="FileAppender"type="log4net.Appender.FileAppender,log4net">       <filevalue="c:\\mylogfile.txt" />       <appendToFilevalue="true" />       <lockingModeltype="log4net.Appender.FileAppender+MinimalLock" />       <layout…

PL/SQL Working with Columns

Drop & Modify Constraints

alter table table_name drop constraint PK_XXX_XXX_ID;
alter table table_name modify constraint FK_XXX_XXX_XXX_ID enable;
alter table table_name modify constraint FK_XXX_XXX_YYY_ID enable;
alter table table_name modify constraint FK_XXX_XXX_ZZZ_ID enable;
--validation disabled
alter table table_name modify constraint CHK_XXX_XXX_TTT_NAME enable novalidate;

Add Constraints

ALTER TABLE table_name
add CONSTRAINT FK_XXX_XXX_XXX_ID
   FOREIGN KEY (column_name)
   REFERENCES reference_table (ID);

Rename Table

alter table table_name
rename to new_table_name;


Rename Columns

alter table table_name rename column column_name_x to new_column_name_x;
alter table table_name rename column column_name_y to new_column_name_y;
alter table table_name rename column column_name_z to new_column_name_z;

Add Columns

alter table
   table_name
add
   (
    USER_ID NUMBER(18,0) DEFAULT 1 NOT NULL ENABLE,
    ENTRY_DATE DATE DEFAULT sysdate NOT NULL ENABLE,
    UPDATE_USER_ID NUMBER(18,…

PL/SQL Insert Data Using Max Function

declare
  v_max_id number;
begin
  select max(region_id)
  into v_max_id
  from regions;
  dbms_output.put_line(v_max_id);
  insert into regions
  (region_id,region_name)
  values(v_max_id+1,'Australia');
end;

PL/SQL Oracle Dual Table

select * from locations
end;

declare
  v_city locations.city%type;
begin
  select 'Roma'
  into v_city
  from dual;

  dbms_output.put_line('Selected city is; '||v_city);
end;


declare
  v_city locations.city%type;
begin
  select 'Roma'
  into v_city
  from dual;
  update locations
  set city=v_city
  where postal_code='6823';
end;


declare
  v_postal_code locations.postal_code%type;
begin
  select '6823'
  into v_postal_code
  from dual;
  dbms_output.put_line(v_postal_code);
  update locations
  set city='Romaxx'
  where postal_code=v_postal_code;
end;

PL/SQL Anchored Datatypes

declare
  v_first_name employees.first_name%type;
  v_last_name employees.last_name%type;
begin
  select first_name, last_name
  into v_first_name, v_last_name
  from employees
  where employee_id=100;

  dbms_output.put_line('First name :'||v_first_name||' Last name : '||v_last_name);
  exception
    when no_data_found then
      dbms_output.put_line('No such employee');
end;



declare
  v_first_name employees.first_name%type;
  v_last_name employees.last_name%type;
  v_phone_number employees.phone_number%type;
  v_salary employees.salary%type;
begin
  select first_name, last_name, phone_number, salary
  into v_first_name, v_last_name, v_phone_number, v_salary
  from employees
  where employee_id=101;

  dbms_output.put_line('First name '||v_first_name||' Last name '||v_last_name||' Phone number '||v_phone_number||' Salary '||v_salary);
  exception
    when no_data_found then
      dbms_output.put_line('No such employee');

PL/SQL Working with 'dbms_output.put_line' statement

declare
  v_day varchar2(20);
begin
  v_day:= to_char(sysdate, 'Day');
  dbms_output.put_line('Today is '||v_day);
end;


declare
  v_day varchar2(20);
begin
  v_day:= to_char(sysdate, 'Day, HH24:MI');
  dbms_output.put_line('Today is '||v_day);
end;

PL/SQL Substitution Variables 2

set serveroutput on
declare
  v_num number:=&sv_num;
  v_result number;
begin
  v_result:= power(v_num, 2);
  dbms_output.put_line('The value of v_result is: '||v_result);
end;


declare
  v_radius number:=&v_radius;
  v_area number;
begin
  v_area:= power(v_radius,2)*3.14;
  dbms_output.put_line('The area of the circle is '||v_area);
end;


declare
  v_kenar1 number:=&v_kenar1;
  v_kenar2 number:=&v_kenar2;
  v_cevre number;
  v_alan number;
begin
  v_cevre:=v_kenar1+v_kenar2;
  v_alan:=v_kenar1*v_kenar2;
  dbms_output.put_line('Cevre: '||v_cevre||' Alan: '||v_alan);
end;

PL/SQL Substitution Variables

declare
  v_employee_id number:=&sv_employee_id;
  v_first_name varchar2(35);
  v_last_name varchar2(35);
begin
  select first_name, last_name
  into v_first_name, v_last_name
  from employees
  where employee_id=v_employee_id;

  dbms_output.put_line('Employee name: '||v_first_name||' '||v_last_name);
  exception
    when no_data_found then
      dbms_output.put_line('There is no such employee');
end;

PL/SQL Select Statement

declare
  v_first_name varchar2(35);
  v_last_name varchar2(35);
begin
  select first_name, last_name
  into v_first_name, v_last_name
  from employees
  where employee_id=100;

  dbms_output.put_line('Employee name: '||v_first_name||' '||v_last_name);
  exception
    when no_data_found then
      dbms_output.put_line('There is no employee with '||' employee id 100');
end;

Solution for Visual Studio 2012 & TFS Problem: "The solution was offline during its previous session and will remain offline."

Image
Here are the steps to solve this problem;

Go to registry editor.Go to Computer\HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\11.0\TeamFoundation\Instances\xxx\Collections\XXXModify Offline DWORD value to 0.Start your Visual Studio Project again and select OK button to connect to TFS server.





Memcached Caching

Image
Tools
MemCacheD Manager v1.0.3.0 as Memcached Server Enyim Memcached Client as Memcached .Net Client Library (Add these references to your project: Enyim.Caching.dll, log4net.dll, MemcachedProviders.dll)Your Client ProgramMemCacheD Manager
Run the setup file and configure a Memcached Server as shown below:

Client Program
Create a new .Net project and add these settings to web.config file as shown below:<configSections> <sectionname="cacheProvider"type="MemcachedProviders.Cache.CacheProviderSection,MemcachedProviders"allowDefinition="MachineToApplication"restartOnExternalChanges="true"/> <sectionGroupname="enyim.com"> <sectionname="memcached"type="Enyim.Caching.Configuration.MemcachedClientSection, Enyim.Caching"/> </sectionGroup> <sectionname="log4net"type="log4net.Config.Log4NetConfigurationSectionHandler,log4net"/> </configSections>

<enyim.com> <memcached&…