POUWIEL|COM

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

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

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

ascii dumps and converts to multilangual hexes.
US7ASCII is outdated, it slowely drives me insane.
Those are a few of my least favorite things…

Here we are, struggling again. *sigh…

The skinny:
Read through Oracle’s NLS_LANG faq. Again. Don’t argue, just do it. Yes, Again!
Check your codepage, I’ll assume your on Windows otherwise you wouldn’t be in this mess.
For arguments sake, let’s assume it’s 850.

Microsoft Windows [Version 6.0.6002]
Copyright (c) 2006 Microsoft Corporation.  All rights reserved.

c:\Windows>chcp
Active code page: 850

Set your NLS_LANG to: american_america.we8pc850

Start SQLPlus.

Note to the weary:
When working remotely via Citrix, it’s at all good possible you’ll check your codepage, determine it’s that 850, set your NLS_LANG to american_america.we8pc850, run your script that’s located on a mounted share and still have garbage.
That.. that I can’t help you with.. Still need to figure that one out. I suppose it’s due to the fact that the scripts are compiled on another system, placed on the share and both environments may have different codepages than 850. Maybe another time, eh!?

Update ::
According to RedGrittyBrick on superuser.com, it seems that Notepad uses ANSI as another word for WE8MSWIN1252. Set your NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 and be golden again…

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

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘<user>’, ‘<table_name>’, estimate_percent => NULL, cascade => TRUE);

Instead of estimating the statistics using a given percentage, this will hit all rows.

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

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

Quick reminder:

$> svrmgrl 
sql> connect internal

source:

USE_SHARED_SOCKET
You can set parameter USE_SHARED_SOCKET to true to enable use of shared sockets. If this parameter is set to true, the network listener passes the socket descriptor for client connections to the database thread. As a result, the client does not need to establish a new connection to the database thread and database connection time improves. Also, all database connections share the port number used by the network listener, which can be useful if you are setting up third-party proxy servers.
This parameter only works in dedicated server mode in a TCP/IP environment.
If this parameter is set, you cannot use the 9.0 listener to spawn Oracle7 release 7.x databases.
To spawn a dedicated server for an Oracle database not associated with the same Oracle home as the listener and have shared socket enabled, you must also set parameter USE_SHARED_SOCKET for both Oracle homes.

USE_SHARED_SOCKET could be a performance bottleneck if you have more connections to the database. The load on using the same socket on Windows has been very weak with respect to performance.
So the answer would depend on how many simultaneous connections will hit the listener at a point of time.
I don’t have any benchmark values for this ( there was never a need for this ).
But we decided against the use of this with just 50 connections.