Shell Programming and Scripting

LOAD DATA
APPEND
INTO TABLE MK9210.EG
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
STUDY CHAR
,PATIENT CHAR
,CPEVENT CHAR
,NAMEG CHAR
,REFIDEG CHAR
,POSEG CHAR
,METHEG CHAR
,EGDAT CHAR
,EGTIM CHAR
,COMEG CHAR
,COM1EG CHAR
,COM2EG CHAR
,COM3EG CHAR
,COM4EG CHAR
,BYRLD INTEGER EXTERNAL
,SEXLD CHAR
,EGCLSIG CHAR
,CATEG CHAR
,TESTEG CHAR
,EVALEG CHAR
,CORESEG CHAR
,CSCDEG CHAR
,CORSNEG INTEGER EXTERNAL
,CORSUEG CHAR
,DATLDFL CHAR
,EGFND CHAR
,EGFNDTP CHAR
)

sqlplus apps/apps <<EOF
> set pagesize 5000 feedback off verify off heading off echo off serveroutput on
> spool output.dat
> select control_file from lshadmin.data_domain;
> exit
> EOF

LOAD DATA
APPEND
INTO TABLE MK9210.EG
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS

SQL> select C from C;
LOAD DATA
APPEND
INTO TABLE MK9210.EG
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
STUDY CHAR
,PATIENT CHAR
,CPEVENT CHAR
,NAMEG CHAR
,REFIDEG CHAR
,POSEG CHAR
,METHEG CHAR
,EGDAT CHAR
,EGTIM CHAR
,COMEG CHAR
,COM1EG CHAR
,COM2EG CHAR
,COM3EG CHAR
,COM4EG CHAR
,BYRLD INTEGER EXTERNAL
,SEXLD CHAR
,EGCLSIG CHAR
,CATEG CHAR
,TESTEG CHAR
,EVALEG CHAR
,CORESEG CHAR
,CSCDEG CHAR
,CORSNEG INTEGER EXTERNAL
,CORSUEG CHAR
,DATLDFL CHAR
,EGFND CHAR
,EGFNDTP CHAR
)

create table c (C varchar2(2555));

insert into c values('LOAD DATA
APPEND
INTO TABLE MK9210.EG
FIELDS TERMINATED BY ''|''
TRAILING NULLCOLS
(
STUDY CHAR
,PATIENT CHAR
,CPEVENT CHAR
,NAMEG CHAR
,REFIDEG CHAR
,POSEG CHAR
,METHEG CHAR
,EGDAT CHAR
,EGTIM CHAR
,COMEG CHAR
,COM1EG CHAR
,COM2EG CHAR
,COM3EG CHAR
,COM4EG CHAR
,BYRLD INTEGER EXTERNAL
,SEXLD CHAR
,EGCLSIG CHAR
,CATEG CHAR
,TESTEG CHAR
,EVALEG CHAR
,CORESEG CHAR
,CSCDEG CHAR
,CORSNEG INTEGER EXTERNAL
,CORSUEG CHAR
,DATLDFL CHAR
,EGFND CHAR
,EGFNDTP CHAR
)
');

sqlplus << EOSQL
${DB_LOGON}
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
set pagesize 5000 feedback off verify off heading off echo off serveroutput on
spool output.dat
select C from C;
exit
EOSQL

  sqlconn=`sqlplus ${sysuser}/${syspwd} <<-EOF 
          set longchunksize 200000 long 200000 pages 0
     SELECT CONTROL_FILE FROM LSHADMIN.DATA_DOMAIN WHERE STUDY_NAME = ${studyName}
     AND DOMAIN_NAME = ${tabname}
     EXIT
     EOF`

create table c (C clob);

var=`sqlplus -s ${DB_LOGON}<< EOSQL
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
set longchunksize 200000 long 200000 pages 0
set heading off
spool output.dat
select C from C;
exit
EOSQL`
echo $var

Makarand>./mak.sh
++++ Retrieved Passwords for Interfaces
LOAD DATA APPEND INTO TABLE MK9210.EG FIELDS TERMINATED BY '|' TRAILING NULLCOLS ( STUDY CHAR ,PATIENT CHAR ,CPEVENT CHAR ,NAMEG CHAR ,REFIDEG CHAR ,POSEG CHAR ,METHEG CHAR ,EGDAT CHAR ,EGTIM CHAR ,COMEG CHAR ,COM1EG CHAR ,COM2EG CHAR ,COM3EG CHAR ,COM4EG CHAR ,BYRLD INTEGER EXTERNAL ,SEXLD CHAR ,EGCLSIG CHAR ,CATEG CHAR ,TESTEG CHAR ,EVALEG CHAR ,CORESEG CHAR ,CSCDEG CHAR ,CORSNEG INTEGER EXTERNAL ,CORSUEG CHAR ,DATLDFL CHAR ,EGFND CHAR ,EGFNDTP CHAR )

mak=Makarand
var=`sqlplus -s ${DB_LOGON}<< EOSQL
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
set longchunksize 200000 long 200000 pages 0
set heading off
spool output.dat
select C from C where D='$mak';
exit
EOSQL`

sqlplus ${sysuser}/${syspwd} << EOF > ctl_file.ctl
     WHENEVER SQLERROR EXIT FAILURE;
           WHENEVER OSERROR EXIT FAILURE;
          set longchunksize 200000 long 200000 pages 0  head off feedback off echo off
      set heading off 
     SELECT CONTROL_FILE FROM LSHADMIN.DATA_DOMAIN WHERE STUDY_NAME = '${studyName}'
     AND DOMAIN_NAME = '${tabname}';
     exit
     EOF

SQL> SQL> SQL> SQL> SQL> 2 LOAD DATA
APPEND
INTO TABLE MK9210.EG
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
STUDY CHAR
,PATIENT CHAR
,CPEVENT CHAR
,NAMEG CHAR
,REFIDEG CHAR
,POSEG CHAR
,METHEG CHAR
,EGDAT CHAR
,EGTIM CHAR
,COMEG CHAR
,COM1EG CHAR
,COM2EG CHAR
,COM3EG CHAR
,COM4EG CHAR
,BYRLD INTEGER EXTERNAL
,SEXLD CHAR
,EGCLSIG CHAR
,CATEG CHAR
,TESTEG CHAR
,EVALEG CHAR
,CORESEG CHAR
,CSCDEG CHAR
,CORSNEG INTEGER EXTERNAL
,CORSUEG CHAR
,DATLDFL CHAR
,EGFND CHAR
,EGFNDTP CHAR
)

nohup sqlldr userid=${sysuser}/${syspwd} control=ctl_file.ctl data=$infile  direct=yes silent=all parallel=true &

SQL*Loader-100: Syntax error on command-line
SQL*Loader-350: Syntax error at line 2.
Expecting keyword LOAD, found "SQL".
SQL*Plus: Release 10.1.0.5.0 - Production on Mon May 12 16:48:45 2014

nohup sqlldr <username>/<password>@<oraclesid> control=ctl_file.ctl data=$infile  direct=yes silent=all parallel=true &

Makarand>cat c.ctl
LOAD DATA
APPEND
INTO TABLE C
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
C CHAR
)

Table C:
  2 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

nohup sqlldr <username>/<password>@<oraclesid> control=c.ctl data=data direct=yes silent=all parallel=true &