Nurhak Kaya's Software Development Blog

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

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

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

create table  schema_name.table_name_ copy as select * from  schema_name.table_name_ orj;

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

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

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;

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

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/

declare v_counter number:=0; begin   select seq_table_id.nextval   into v_counter   from dual;     dbms_output.put_line(v_counter); end;

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

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;

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;

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_i

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_

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;

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;

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

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;

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;

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;

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());              }          }      } }

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;

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;

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

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;

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(

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;

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

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!

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;

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;

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

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

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