Posts

Showing posts from November, 2013

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;