Skip to main content


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          :=

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     ;   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             :=;       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,         &

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 >   ...   <!-- <>     <defaultProxy useDefaultCredentials="true">       <proxy proxyaddress="http://proxyIPAdress:portNumber" usesystemdefault="True"/>     </defaultProxy>   </> -->   ... </ configuration > That's all.  Happy coding :)

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(   );   update mzb_veteriner_bilgileri   set gorev_baslangic_tarihi=sysdate   where;   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.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 and       hayvan.hayvan_kabul_i

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_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_

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-INTO Simple 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;

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 {     class Program     {          static void 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 = new StreamWriter (path, true ))              {                   newTask.WriteLine( "ProTestService calisma zamani--> "  + DateTime .Now.ToString());              }          }      } }

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(

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',   '[email protected]',   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',   '[email protected]',   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',   '[email protected]',   sysdate,   'IT_PROG' ); savepoint point3; --ROLLBACK!! select * from employees where email='[email protected]'; --Data has been found! rollback to savepoint point2; --'Rollback to savepoint point2' co

Happy Birthday Blog!

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 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 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