Posts

Showing posts from 2013

PL/SQL Inserting Big Data Using Refcursor and Fuction

--Inserting big data using refcursor and function

declare
  cursor c_beyan
  is
  select *
  from ilc_beyan
  order by id;

    v_KENTLI_ID           ilc_beyan.kentli_id%TYPE;
    v_ISLEM_ADRESLERI_ID  ilc_beyan.islem_adresleri_id%TYPE;
    v_HESAP_ID            ilc_beyan.hesap_id%TYPE;
    v_DONEM_YILI          ilc_beyan.donem_yili%TYPE;
    v_DONEM_AYI           ilc_beyan.donem_ayi%TYPE;
    v_BIRIM_ADET          ilc_beyan.birim_adet%TYPE;
    v_ACIKLAMA            ilc_beyan.aciklama%TYPE;
    v_BEYAN_TUTARI        ilc_beyan.beyan_tutari%TYPE;
    v_KAPANIS_ID          ilc_beyan.kapanis_id%TYPE;
    v_counter number      :=0;

begin
    for r_beyan in c_beyan loop
        v_KENTLI_ID           :=r_beyan.kentli_id;
        v_ISLEM_ADRESLERI_ID  :=r_beyan.islem_adresleri_id;
        v_HESAP_ID            :=r_beyan.hesap_id;
        v_DONEM_YILI          :=r_beyan.donem_yili;
        v_DONEM_AYI           :=r_beyan.donem_ayi;
        v_BIRIM_ADET          :=r_beyan.birim_adet;
        …

PL/SQL Exists Condition

select *
from table_x x
where exists (select *
              from table_y y
              where x.id = y.x_id);

PL/SQL Finding all of the Constraints

select *
from  user_constraints
where   R_CONSTRAINT_NAME LIKE '%GLR%'
order by r_constraint_name;

PL/SQL Copying One Table with all Constraints and Data

create table schema_name.table_name_copy
as
select * from schema_name.table_name_orj;

PL/SQL Inserting Top N Records From One Table To Another

declare
  cursor c_glr
  is
      select *
      from ( select * from schema_name_x.table_name_x order by id desc) table_x
      where rownum <=6
      order by rownum;  

  v_id               schema_name_x.table_name_x.id%TYPE;
  v_aciklama     schema_name_x.table_name_x.aciklama%TYPE;
  v_kisa_adi      schema_name_x.table_name_x.kisa_adi%TYPE;
  v_user_id       schema_name_x.table_name_x.user_id%TYPE;
  v_user_date   schema_name_x.table_name_x.user_date%TYPE;
begin
  for r_glr in c_glr loop
      v_id             := r_glr.id;
      v_aciklama  := r_glr.aciklama;
      v_kisa_adi   := r_glr.kisa_adi;
      v_user_id    := r_glr.user_id;
      v_user_date := r_glr.user_date;

      insert into schema_name_y.table_name_y
      values
      (
        v_id,
        1,
        null,
        1,
        nls_upper(v_kisa_adi,'nls_sort=xturkish'),
        nls_upper(v_kisa_adi,'nls_sort=xturkish'),
        1,
        v_aciklama,
        'tarife',
        v_user_id,
  …

PL/SQL Retrieving Top N Records From a Query

select *
from ( select * from xyz_table_name order by id desc) x_table
where rownum <=10
order by rownum;

PL/SQL Working with Database Objects

create or replace
type obj_xyz_table is
       object (
                id                number       ,
                name               varchar2(50) ,
                surname            varchar2(50)
                -- here can come more properties according to the needs of your project
             );

***************

create or replace
type tab_xyz_table_name is table of obj_xyz_table;

How to specify proxy credentials in web.config

If you need to configure a website to access a webservice etc. which is deployed on another machine vie a proxy, you just need to update your web projects web.config file as shown below:

<configuration>   ...   <!--<system.net>     <defaultProxy useDefaultCredentials="true">       <proxy proxyaddress="http://proxyIPAdress:portNumber" usesystemdefault="True"/>     </defaultProxy>   </system.net>-->   ...
</configuration>
That's all.  Happy coding :)

Creating Setup Files with InstallShield Limited Edition for Visual Studio 2012

This is a nice article that explains how to create a basic setup file using Installshield Limited in Visual Studio 2012 Professional.
http://jasonklee.com/2013/07/installshield-limited-in-visual-studio-2012-professional/

PL/SQL Select Next Value of a Sequence

declare
v_counter number:=0;
begin
  select seq_table_id.nextval
  into v_counter
  from dual;

  dbms_output.put_line(v_counter);
end;

PL/SQL Find & Delete Duplicate Records

SELECT
select *
  from
    (
      select d.*
      , count(*) over
      (partition by adi) cnt
      from ort_ilce_kodlari d
    )
where cnt > 1;

DELETE
delete from ort_ilce_kodlari a
       where rowid >
        (
          select min(rowid)
          from ort_ilce_kodlari b
          where a.adi = b.adi
        );

PL/SQL Insert Big Data and Commit

declare
  v_counter number:=0;
begin
  for j in 1..1000000 loop
      insert into test_table
      (
        id,
        kurum_id,
        tur_id,
        adi,
        aktif,
        kayit_kullanici_id,
        kayit_tarihi
      )
      values
      (
        SEQ_TEST_TABLE_ID.nextval,
        1,
        1,
        'TEST_DATA_'|| j,
        1,
        1,
        sysdate
      );
      v_counter:=v_counter+1;
      if mod(v_counter, 10000 ) = 0 then    -- Commit every 10000 records
        commit;
        dbms_output.put_line(v_counter||'-> committed');
      end if;
    end loop;
end;

PL/SQL Updating a Table by using Cursor

declare
  cursor veteriner_cur
  is
  select id
  from mzb_veteriner_bilgileri
  order by id;
begin
  for c1
  in veteriner_cur
  loop
  dbms_output.put_line(
    c1.id
  );
  update mzb_veteriner_bilgileri
  set gorev_baslangic_tarihi=sysdate
  where id=c1.id;
  end loop;
end;

PL/SQL Create View with Outer Join

create or replace force view vew_mzb_hayvan_bilgileri
(
  id,
  kulak_kupe_no,
  kesilme_tarihi,
  turu,
  saglik_rapor_no,
  gelis_sekli,
  gelis_tarihi,
  yetistiren,
  kestiren,
  ante_mortem_id,
  post_mortem_id,
  kesim_id
)
as
select
  hayvan.id,
  hayvan.kulak_kupe_no,
  hayvan.kesilme_tarihi,
  tur.adi turu,
  kabul.saglik_rapor_no,
  kabul.gelis_sekli,
  kabul.gelis_tarihi,
  yetistiren.adi_soyadi yetistiren,
  kestiren.adi_soyadi kestiren,
  antehayvan.ante_mortem_id,
  posthayvan.post_mortem_id,
  kesimdetay.kesim_id
from  mzb_hayvan_bilgileri hayvan,
      mzb_hayvan_turleri tur,
      mzb_hayvan_kabul_bilgileri kabul,
      vew_ort_kentli yetistiren,
      vew_ort_kentli kestiren,
      mzb_ante_mortem_hay_bilgileri antehayvan,
      mzb_post_mortem_hay_bilgileri posthayvan,
      mzb_kesim_bilgileri_detay kesimdetay,
      mzb_kesim_detay_hay_bilgileri kesimdetayhayvan
where hayvan.hayvan_tur_id=tur.id and
      hayvan.hayvan_kabul_id=kabul.id and
      kabul.yetistirici…

PL/SQL Create View with Inner Join

CREATE OR REPLACE FORCE VIEW SCHEMA_NAME.VEW_MZB_KESIM_BILGILERI_DETAY
(
   ID,
   KESIM_ID,
   KULAK_KUPE_NO_BILGILERI,
   YETISTIRICI_ADI_SOYADI,
   AGIRLIK,
   DETAY_HAYVAN_KAYIT_ID,
   KARKAS_IMHA,
   KARACIGER_IMHA,
)
AS
     SELECT ana.ID,
            KESIM_ID,
            KULAK_KUPE_NO_BILGILERI,
            YETISTIRICI_ADI_SOYADI,
            AGIRLIK,
            detay.id "DETAY_HAYVAN_KAYIT_ID",
            CONCAT (
               CONCAT (KARKAS_IMHA, ' '),
               DECODE (KARKAS_IMHA,
                       NULL, NULL,
                       DECODE (KARKAS_IMHA_BIRIMI,  1, 'kg',  2, 'gr')))
               AS KARKAS_IMHA,
            CONCAT (
               CONCAT (KARACIGER_IMHA, ' '),
               DECODE (KARACIGER_IMHA,
                       NULL, NULL,
                       DECODE (KARACIGER_IMHA_BIRIMI,  1, 'kg',  2, 'gr')))
               AS KARACIGER_IMHA,
       FROM mzb_kesim_bilgileri_detay ana, mz…

PL/SQL Decode

CREATE OR REPLACE FORCE VIEW VEW_TABLE_NAME ("ID", "COLUMN_X")
AS
  SELECT "ID",
    concat(concat(COLUMN_X,    ' '),
    decode(COLUMN_X,    null, null, decode(COLUMN_Y,    1, 'kg', 2, 'gr'))) AS COLUMN_X,
FROM TABLE_NAME;

PL/SQL Check Constraint & Unique Index

Add Check Constraint
alter table table_name
add constraint chk_table_name_column_name
check (column_name=nls_upper(adi,'NLS_SORT=XTURKISH')) enable;

Create Unique Index
create unique index "schema_name"."ind_table_name_column_name" on "schema_name"."table_name"
  (
    "column_name"
  );

Drop Index
drop index ind_table_name_column_name;

PL/SQL Working with Functions

--package

create or replace
package pkg_xyz is

  function fnc_varsayilan_cevap_kontrol (p_soru_id in number) return number;

end pkg_mzb;


--package body
create or replace
package body         pkg_xyz is

function fnc_varsayilan_cevap_kontrol (p_soru_id number) return number is

    varsayilanKontrol       number := 0;

    begin

        select count (varsayilan)
          into varsayilanKontrol
          from mzb_muayene_soru_cevaplari
         where soru_id = p_soru_id
         and varsayilan=1;

        return varsayilanKontrol;

        exception
            when No_Data_Found
            then
            return null ;

    end ;

end;

PL/SQL Working with Cursors

SELECT-INTOSimple Select
declare
  l_last_name employees.last_name%type;
begin
  select last_name
    into l_last_name
    from employees
    where employee_id=138;

    dbms_output.put_line(l_last_name);
end;

Join

declare
  l_last_name employees.last_name%type;
  l_department_name departments.department_name%type;
begin
  select last_name, department_name
  into l_last_name, l_department_name
  from employees e, departments d
  where e.department_id=d.department_id
  and e.employee_id=138;

  dbms_output.put_line(
        l_last_name||
        ' is working in '||
        l_department_name ||
        ' department.');
end;

Cursor FOR Loop


declare
  cursor test_cur
  is
    select * from employees
    where department_id=50;
begin
  for c1
  in test_cur
  loop
    dbms_output.put_line(
      c1.first_name||
      ' '||
      c1.last_name
    );
  end loop;
end;


PL/SQL Functions

function fnc_my_function(p_id number) return number is
    v_counter       number := 0;

    begin

        select count(k.varsayilan)
        into v_count
        from test_table k
        where id=p_id;

        return v_counter;

        exception
            when no_data_found
            then
            return null ;

    end ;

-- Test the function


select package_name.fnc_my_function(65243)
from dual;

PL/SQL Working with Date Value

select * from table_name where id=181
and column_date=to_date('28.02.2013 10:39:15', 'dd.mm.yyyy hh24:mi:ss');

select to_char(current_date, 'DD-MM-YYYY HH:MI:SS') from dual;


Working with "Paths" in C#

using System;
using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks;
namespace ProTestService {     classProgram     {          staticvoid Main(string[] args)          {              string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);              path = Path.Combine(path.Replace("file:\\", ""), "test.txt");              using (StreamWriter newTask = newStreamWriter(path, true))              {                   newTask.WriteLine("ProTestService calisma zamani--> " +DateTime.Now.ToString());              }          }      } }

PL/SQL Insert into one table from another by using sequence

insert into table_1
(
  col_1, col_2, col_3
)
select  seq_my_sequence.nextval,
        k.col_2,
        k.col_3,
        from my_schema.table_2 k;

PL/SQL Synonym and Grant

grant select on table_name to schema_name;

grant select on seq_name to schema_name;

grant insert on table_name to schema_name;

grant update on table_name to schema_name;

grant execute on schema_name_x.pkg_package_name to schema_name_y;

------

create or replace synonym schema_name_2.table_name for schema_name_1.table_name;

drop sysnonym schema_name_2.table_name;


PL/SQL Using In&In Reverse Options in the Loop

begin
  for v_counter in 0..10 loop
      if mod(v_counter,2)=0 then
            dbms_output.put_line('v_counter='||v_counter);
          end if;
    end loop;
    dbms_output.put_line('Done...');
end;

begin
  for v_counter in reverse 0..10 loop
      if mod(v_counter,2)=1 then
            dbms_output.put_line('v_counter='||v_counter);
          end if;
    end loop;
    dbms_output.put_line('Done..');
end;


*******outputs********
v_counter=0
v_counter=2
v_counter=4
v_counter=6
v_counter=8
v_counter=10
Done...

v_counter=9
v_counter=7
v_counter=5
v_counter=3
v_counter=1
Done..

PL/SQL Use Loop with "Exit When" Condition

declare
  v_student_id students.section_id%type:=5;
  v_surname students.student_surname%type;
begin
  loop
        v_student_id:=v_student_id+1;
        insert into students
        (
          student_id,
          student_surname
        )
        values
        (
          v_student_id,
          'Test xyz'
        );
        exit when v_student_id=10;
      end loop;
      dbms_output.put_line('Done...');
end;

PL/SQL Use the Case Statement

create table students
(
  student_id number not null,
  student_name varchar2(50),
  student_surname varchar2(50) not null,
  section_id number,
  final_grade number,
  letter_grade char(1),
  constraint students_pk primary key(student_id)
);

insert into students
values
(
  1,
  'Nurhak',
  'Kaya',
  89,
  85,
  'D'
);

declare
  v_student_id number:=1;
  v_section_id number:=89;
  v_final_grade number;
  v_letter_grade char(1);
begin
  select final_grade
  into v_final_grade
  from students
  where student_id=v_student_id
  and section_id=v_section_id;

  case
      when v_final_grade>=90 then v_letter_grade:='A';
      when v_final_grade>=80 then v_letter_grade:='B';
      when v_final_grade>=70 then v_letter_grade:='C';
      when v_final_grade>=60 then v_letter_grade:='D';
      else v_letter_grade:='F';
    end case;
    --check result
    dbms_output.put_line('Exam result is: '||v_letter_grade);
e…

PL/SQL Multiple Loop Transactions

declare
  v_counter number;
begin
  v_counter := 0;
  for i in 1..20 loop
      v_counter:=v_counter+1;
      dbms_output.put_line(i || '. value is: '||v_counter);
      if v_counter=10 then
            commit;
            v_counter:=0;
          end if;
    end loop;
end;

PL/SQL Use of Rollback and Savepoint

insert into employees
(
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  job_id
)
values
(
  seq_employee_id.nextval,
  'Nurhak',
  'Kaya',
  'test1@gmail.com',
  sysdate,
  'IT_PROG'
);
savepoint point1;
insert into employees
(
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  job_id
)
values
(
  seq_employee_id.nextval,
  'Nurhak',
  'Kaya',
  'test2@gmail.com',
  sysdate,
  'IT_PROG'
);
savepoint point2;
insert into employees
(
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  job_id
)
values
(
  seq_employee_id.nextval,
  'Nurhak3',
  'Kaya',
  'test3@gmail.com',
  sysdate,
  'IT_PROG'
);
savepoint point3;

--ROLLBACK!!


select * from employees
where email='test3@gmail.com'; --Data has been found!

rollback to savepoint point2; --'Rollback to savepoint point2' command has undone the last
                          --inserted …

Happy Birthday Blog!

Image
It's been a year since I have started to post my first blog and today it's my blog's birthday!
That has been an amazing experience to be a blog writer! I've learnt a lot by blogging and that feels great to have followers from all over the world!
Thank you to everyone who's been with me for this past year! You're always welcome to follow my blog in the coming years!

PL/SQL Concat & Create Concat View

select concat(concat(first_name, ' '), last_name)
as "Name_Surname"
from employees
where employee_id = 152;


create or replace force view vew_emp_full_name
as
select concat(concat(first_name, ' '), last_name)
as "Name_Surname",
manager_id+department_id
as "Total"
from employees;

PL/SQL Create&Replace&Alter View

create or replace force view "xyz"."vew_test_table_name"
as
select * from test_table_name;

alter view "xyz"."vew_test_table_name"
add constraint pk_vew_test_table_name_id primary key (id) disable;

PL/SQL Update one table from another

select * from personel;

create table personel_yeni
(
 PERSONEL_ID   NUMBER(10,0) NOT NULL ENABLE,
 PERSONEL_NAME VARCHAR2(20 BYTE),
 OFFICE_ID     NUMBER(10,0)
);

select * from personel_yeni;

insert into personel_yeni
values
(
  19,
  'Some data',
  9
);

update personel_yeni y set
  personel_name=
  (
    select personel_name
    from personel e
    where e.personel_id=y.personel_id
  ),
  office_id=
  (
    select office_id
    from personel e
    where e.personel_id=y.personel_id
  );

PL/SQL Triggers that listen to changes of a certain table

select * from user_triggers where table_name='my_table_name';
select * from all_triggers where table_name='my_other_table_name';

PL/SQL One-to-one Relationship

create table Office
(
  office_id numeric(10) not null,
  building_name varchar2(20),
  constraint u_office unique(building_name),
  constraint office_pk primary key(office_id)
);

create table Personel
(
  personel_id number(10) not null,
  personel_name varchar2(20),
  office_id number(10),
  constraint u_personel unique(office_id), --with this constraint each personel will have just one office id.
  constraint personel_pk primary key(personel_id)
);


--Here in the blow code block, as you can see in personel_detail table, personel_id is a PK and also it is a FK that references personel table.
--In this condition we can't insert any personel data in to personel detail table which does not have any reference in personel table. For example if there is a personel with personel_id=1 then we can insert one data with this same personel_id-which is 1- into personel_detail table but we can't do the same thing with the personel_id=2, because there is no personel with personel_id=2.

cre…