Selecting a value of column through sqlplus

Shell Programming and Scripting
1

Hi All,

The value of a column in my DB table is:

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
)

when i select this column from sqlplus in unix:

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

the only thing that gets printed is:

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

Can anyone help with why is it truncating the value

2

i tried to reproduce your problem but getting whole value

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
)

table

create table c (C varchar2(2555));

value

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

mak.sh

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
3

The table column is of type CLOB..

I had to add this:
set longchunksize 200000 long 200000 pages 0
Now its working for me..

Now I want to get this clob value to a shell variable , ao am using

  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`

when I do echo $sqlconn the value is null..
can we fetch clob into shell variable?

4

again it is working for me
table structure changed to clob

create table c (C clob);

mak.sh

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

output

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 )

check your query ...specialy where clause .. did variables setting correctly ..

try to pass variables like below in red

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`
1 Like
5 
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

the ctl_file.ctl created is

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
)

hence,

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

is throwing error as:

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

How to avoid this error?

6

use

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

use database user_name , password & sid as mention in red

c.ctl

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

c.log

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.

command

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