POUWIEL|COM

JeroenPouwiel

Checking for (in-)compatibility in charactersets

HOST echo %NLS_LANG% > Y:\temp\NLS_CHAR_SESSION.lst

DECLARE
  dbs_nls  varchar2(4000);
  ses_nls  varchar2(4000);
--
  file_contents VARCHAR2(32767) := '
@@Y:\temp\NLS_CHAR_SESSION.lst
';
--
BEGIN
--
  file_contents := TRIM(replace(replace(file_contents,CHR(13),''),CHR(10),' '));
  ses_nls := file_contents;
  SELECT a.value||'_'||b.value||'.'||c.value INTO dbs_nls
    FROM (select value from nls_database_parameters where parameter = 'NLS_LANGUAGE') a,
         (select value from nls_database_parameters where parameter = 'NLS_TERRITORY') b,
         (select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET') c;
--
  IF(NVL(dbs_nls,'X') != NVL(ses_nls,'Y')) THEN
    IF (UPPER(dbs_nls) = 'AMERICAN_AMERICA.WE8ISO8859P1' AND UPPER(ses_nls) = 'AMERICAN_AMERICA.WE8MSWIN1252') OR (UPPER(ses_nls) = 'AMERICAN_AMERICA.WE8ISO8859P1' AND UPPER(dbs_nls) = 'AMERICAN_AMERICA.WE8MSWIN1252')
      dbms_output.put_line('INFO: ');
      dbms_output.put_line('  CHARACTERSET WE8MSWIN1252 is a binary superset of WE8ISO8859P1')
      dbms_output.put_line('  DATABASE:: '||dbs_nls|| CHR(10) || '  SESSION :: '||ses_nls);
    ELSE
      dbms_output.put_line('##### WARNING!!! ##############################################');
      dbms_output.put_line('  The two CHARACTERSET settings are not equal !!!');
      dbms_output.put_line('  DATABASE:: '||dbs_nls|| CHR(10) || '  SESSION :: '||ses_nls);
    END IF;
  ELSE
    dbms_output.put_line('INFO: ');
    dbms_output.put_line('  The two CHARACTERSET settings are equal');
    dbms_output.put_line('  DATABASE:: '||dbs_nls|| CHR(10) || '  SESSION :: '||ses_nls);
  END IF;
--
END;
/

Some dabbling with PL/SQL

CREATE TABLE ballage2 ( X NUMBER(10), Y VARCHAR2(24 CHAR), Z DATE )
/

DECLARE
  lStart            number := 1 ;
  CODE_ARRAY        dbms_sql.number_table ;
  DESCRIPTION_ARRAY dbms_sql.varchar2_table ;
  COMPLETION_ARRAY  dbms_sql.date_table ;
BEGIN

    CODE_ARRAY(1) := 101 ;
    DESCRIPTION_ARRAY(1) := 'WRITING' ;
    COMPLETION_ARRAY(1) := TO_DATE('2017/02/27 14:00:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(2) := 102 ;
    DESCRIPTION_ARRAY(2) := 'PROCESSING' ;
    COMPLETION_ARRAY(2) := TO_DATE('2017/03/14 15:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(3) := 103 ;
    DESCRIPTION_ARRAY(3) := 'SEEDING' ;
    COMPLETION_ARRAY(3) := TO_DATE('2017/05/08 11:28:01', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(4) := 104 ;
    DESCRIPTION_ARRAY(4) := 'TRANSFORMING' ;
    COMPLETION_ARRAY(4) := TO_DATE('2018/07/07 17:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(5) := 105 ;
    DESCRIPTION_ARRAY(5) := 'BAWLING' ;
    COMPLETION_ARRAY(5) := '' ;
    LOOP
    BEGIN
        FORALL i IN lStart..CODE_ARRAY.COUNT
                INSERT INTO ballage2 VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;
        EXIT ;
    EXCEPTION WHEN OTHERS THEN
        lStart := lStart + sql%ROWCOUNT + 1 ;
    END ;
  END LOOP ;
END ;
/

CREATE OR REPLACE PACKAGE PNL_BALLAGE2 IS 
  type cg$row_type is record
    ( x                      ballage2.x%type
    , y                      ballage2.y%type
    , z                      ballage2.z%type
    ) ;
--------------------------------------------------------------------------------
  procedure ins( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure upd( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure del( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure lck( p_ballage2_old_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  function slct( p_x	    	in ballage2.x%type )
  return cg$row_type ;
--
  cg$tmp_rec cg$row_type ;
--
  type t_ballage2_tabtype is table of cg$row_type index by binary_integer ;
end PNL_BALLAGE2 ;
/

CREATE OR REPLACE PACKAGE BODY PNL_BALLAGE2 IS
  PROCEDURE ins( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    INSERT INTO ballage2
      ( x
      , y
      , z
      )
      VALUES
      (       p_ballage2_rec.x
      , NVL ( p_ballage2_rec.y , 'Initiële actie' )
      ,       TO_DATE(p_ballage2_rec.z, 'YYYY/MM/DD HH24:MI:SS') )
      ;
  --
  END ins ;
  --------------------------------------------------------------------------------
  PROCEDURE upd( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    UPDATE ballage2
    SET    y						= p_ballage2_rec.y
    ,      z            = p_ballage2_rec.z
    WHERE  x						= p_ballage2_rec.x
    ;
  --
  END upd ;
  --------------------------------------------------------------------------------
  PROCEDURE del( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    DELETE FROM ballage2
    WHERE  x						= p_ballage2_rec.x
    AND    y						= p_ballage2_rec.y
    AND    z						= p_ballage2_rec.z
    ;
  --
  END del ;
  --------------------------------------------------------------------------------
  PROCEDURE lck( p_ballage2_old_rec in cg$row_type )
  IS
  BEGIN
    SELECT x
    ,      y
    ,      z
    INTO   cg$tmp_rec.x
    ,      cg$tmp_rec.y
    ,      cg$tmp_rec.z
    FROM   ballage2
    WHERE  x						= p_ballage2_old_rec.x
    AND    y						= p_ballage2_old_rec.y
    AND    z						= p_ballage2_old_rec.z
    FOR UPDATE
    ;
  --
  END lck ;
  --------------------------------------------------------------------------------
  FUNCTION slct(p_x in ballage2.x%type)
  RETURN cg$row_type
  IS
    r_ballage2_rec cg$row_type DEFAULT NULL;
  BEGIN
    SELECT x
    ,      y
    ,      z
    INTO   r_ballage2_rec.x
    ,      r_ballage2_rec.y
    ,      r_ballage2_rec.z
    FROM   ballage2
    WHERE  x            = p_x
    ;
  --
    RETURN r_ballage2_rec ;
  --
  END slct;
END PNL_BALLAGE2;
/

DECLARE 
  my_data ballage2%rowtype;
BEGIN 
  my_data.x := 106; 
  my_data.y := 'JACKING';
  my_data.z := TO_DATE('2017/06/19 15:00:01', 'YYYY/MM/DD HH24:MI:SS');

  pnl_ballage2.ins( my_data ); 
END; 
/

DECLARE
  CURSOR my_data_cur
    IS
    SELECT x, y, z
      FROM ballage2
     WHERE x=102;

  my_data my_data_cur%ROWTYPE;
BEGIN
  OPEN my_data_cur;
  FETCH my_data_cur INTO my_data;
  my_data.y := 'altered via upd';
  CLOSE my_data_cur;
  commit;
  pnl_ballage2.upd( my_data );
END;
/

DECLARE
  my_data ballage2%ROWTYPE;
BEGIN
  SELECT *
   INTO my_data
   FROM ballage2
  WHERE x = 102;

  my_data.y := 'altered again via upd';

  pnl_ballage2.upd( my_data );
END;
/


DECLARE
  my_data ballage2%ROWTYPE;
BEGIN
  SELECT *
   INTO my_data
   FROM ballage2
  WHERE x = 103;

  pnl_ballage2.del( my_data );
END;
/

DECLARE
my_data ballage2%ROWTYPE;
p1      varchar2(16);
BEGIN
SELECT *
    INTO my_data
    FROM ballage2
   WHERE x = 102;

pnl_ballage2.lck( my_data );
my_data.y := 'altered using lck';
dbms_lock.sleep(20);
pnl_ballage2.upd( my_data );
commit;
END;
/


DECLARE
  v_ret ballage2%ROWTYPE;
BEGIN
  v_ret:=pnl_ballage2.slct(104);
  DBMS_OUTPUT.PUT_LINE(v_ret.x||', '||v_ret.y||', '||v_ret.z);
END;
/

PL/SQL thingy for selecting and creating insert PL/SQL thingy

Just like this, sure it can be extended with some checks and other nifty things..:

DECLARE
  counter     number  := 1 ;
  -- declare a varchar variable to contain the
  -- TO_CHAR string, as DATE type is actually 
  -- a number
  v_date2char varchar2(50) ;
  --
  CURSOR c_data_ballage
    IS
    SELECT code
    ,      description
    ,      TO_CHAR(completion, 'YYYY/MM/DD HH24:MI:SS') AS completion
      FROM ballage ;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000) ;
	
  DBMS_OUTPUT.PUT_LINE('DECLARE') ;
  DBMS_OUTPUT.PUT_LINE('  lStart            number := 1 ;') ;
  DBMS_OUTPUT.PUT_LINE('  CODE_ARRAY        dbms_sql.number_table ;') ;
  DBMS_OUTPUT.PUT_LINE('  DESCRIPTION_ARRAY dbms_sql.varchar2_table ;') ;
  DBMS_OUTPUT.PUT_LINE('  COMPLETION_ARRAY  dbms_sql.date_table ;') ;
  DBMS_OUTPUT.PUT_LINE('BEGIN') ;
  DBMS_OUTPUT.PUT_LINE(' ') ;
	
  FOR data_record IN c_data_ballage LOOP
    -- As moment of writing Oracle database considers NULL and empty string ('')
    -- as equal, as show in sample below:
    -- SQL> SELECT NVL(NULL,'This returns NULL') A,NVL('','This will too') B FROM DUAL;  
    -- A                 B  
    -- ----------------- -------------
    -- This returns NULL This will too
		
    IF data_record.completion IS NOT NULL THEN
      v_date2char := 'TO_DATE('''||data_record.completion||''', '''||'YYYY/MM/DD HH24:MI:SS'||''')' ;  
    ELSE
      v_date2char := ''''||data_record.completion||'''' ;
    END IF ;
		
    DBMS_OUTPUT.PUT_LINE('    CODE_ARRAY('||counter||') := '  || data_record.code ||' ;') ;
    DBMS_OUTPUT.PUT_LINE('    DESCRIPTION_ARRAY('||counter||') := '''|| data_record.description ||''' ;') ;
    DBMS_OUTPUT.PUT_LINE('    COMPLETION_ARRAY('||counter||') := '|| v_date2char ||' ;') ;
    counter     := counter + 1 ;
    v_date2char := NULL ;
  END LOOP ;
	
  DBMS_OUTPUT.PUT_LINE('    LOOP') ;
  DBMS_OUTPUT.PUT_LINE('    BEGIN') ;
  DBMS_OUTPUT.PUT_LINE('    	FORALL i IN lStart..CODE_ARRAY.COUNT') ;
  DBMS_OUTPUT.PUT_LINE('    		INSERT INTO ballage VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;') ;
  DBMS_OUTPUT.PUT_LINE('    	EXIT ;') ;
  DBMS_OUTPUT.PUT_LINE('    EXCEPTION WHEN OTHERS THEN') ;
  DBMS_OUTPUT.PUT_LINE('    	lStart := lStart + sql%ROWCOUNT + 1 ;') ;
  DBMS_OUTPUT.PUT_LINE('    END ;') ;
  DBMS_OUTPUT.PUT_LINE('  END LOOP ;') ;
  DBMS_OUTPUT.PUT_LINE('END ;') ;
  DBMS_OUTPUT.PUT_LINE('/') ;
END ;
/

This will generate the following output (for my “ballage” table that is):

DECLARE
  lStart            number := 1 ;
  CODE_ARRAY        dbms_sql.number_table ;
  DESCRIPTION_ARRAY dbms_sql.varchar2_table ;
  COMPLETION_ARRAY  dbms_sql.date_table ;
BEGIN

    CODE_ARRAY(1) := 101 ;
    DESCRIPTION_ARRAY(1) := 'WRITING' ;
    COMPLETION_ARRAY(1) := TO_DATE('2017/02/27 14:00:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(2) := 102 ;
    DESCRIPTION_ARRAY(2) := 'PROCESSING' ;
    COMPLETION_ARRAY(2) := TO_DATE('2017/03/14 15:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(3) := 103 ;
    DESCRIPTION_ARRAY(3) := 'SEEDING' ;
    COMPLETION_ARRAY(3) := TO_DATE('2017/05/08 11:28:01', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(4) := 104 ;
    DESCRIPTION_ARRAY(4) := 'TRANSFORMING' ;
    COMPLETION_ARRAY(4) := TO_DATE('2018/07/07 17:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(5) := 105 ;
    DESCRIPTION_ARRAY(5) := 'BAWLING' ;
    COMPLETION_ARRAY(5) := '' ;
    LOOP
    BEGIN
        FORALL i IN lStart..CODE_ARRAY.COUNT
                INSERT INTO ballage2 VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;
        EXIT ;
    EXCEPTION WHEN OTHERS THEN
        lStart := lStart + sql%ROWCOUNT + 1 ;
    END ;
  END LOOP ;
END ;
/

Auto-generate your tablespace create statements, so you don’t have to…

DECLARE
  var_mod number;
  CURSOR c1 IS
    SELECT tablespace_name, ROUND(SUM(bytes)/(1024*1024),0) AS mb
      FROM dba_data_files
     WHERE tablespace_name IN (SELECT DISTINCT tablespace_name
                                 FROM dba_segments
                                WHERE owner IN ('<schema_name>','<schema_name>','<schema_name>','<schema_name>'))
    GROUP BY tablespace_name
    ORDER BY 1;
BEGIN
   FOR tblspc IN c1
   LOOP
     var_mod := FLOOR(tblspc.mb/9216);
     IF tblspc.mb <= 9216 THEN
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE '||tblspc.mb||'M AUTOEXTEND ON MAXSIZE 9G;');
     ELSIF REMAINDER(tblspc.mb, 9216) = 0 THEN
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       FOR i IN 1..var_mod LOOP
         var_mod := var_mod - 1;
         DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE '||tblspc.tablespace_name||' ADD DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       END LOOP;
     ELSE
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       FOR i IN 1..var_mod LOOP
         var_mod := var_mod - 1;
         DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE '||tblspc.tablespace_name||' ADD DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       END LOOP;
     END IF;
     var_mod :=0;
   END LOOP;
END;
/

This piece of code will gather all used tablespaces by given schema's and output create statements for those tablespaces broken down into separate files of max. 9G.
Use at your own leisure and discretion... As always: No support and no responsibility

Export using DBMS_DATAPUMP and via DB_Link

Fill/alter in the required parameters and Ctrl-C/Ctrl-V:

DECLARE
  h1   number;
BEGIN
  h1 := dbms_datapump.open(operation => 'IMPORT', job_mode => 'SCHEMA', job_name => '<JOB_NAME__MAKE_THIS_UPPERCASE!!!>', version => 'COMPATIBLE', remote_link=>'<DB_LINK_NAME>');
  dbms_datapump.set_parallel(handle => h1, degree => 1);
  dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
  dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''<SCHEMA_NAME>'')');
  dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'NOT IN(''<EXCLUDED_TABLE01>'',''<EXCLUDED_TABLE02>'',''<EXCLUDED_TABLE03>'')', object_type => 'TABLE');
  dbms_datapump.set_parameter(handle => h1, name => 'FLASHBACK_TIME', value => 'TO_TIMESTAMP (to_char(sysdate,''dd.mm.yyyy HH24:MI:SS''),''dd.mm.yyyy HH24:MI:SS'')');
  dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
  dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
  dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
END;
/

Pretty basic procedure

For the life of me, I can never seem to remember this stuff… Read the rest of this entry »

Categories