is not an identifier error

Shell Programming and Scripting
1

Hi gurus,

I am trying to execute a shell script which connects to the oracle db and get the values from a table and then it exports the values on UNIX. For this, I am creating a temp file which stores the values returned from the select query and then executes this file. Below is the code which does this operation.

SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET TIMING OFF
SET TERMOUT OFF
SET LINES 160
SPOOL ${TmpFile}
    SELECT 'export DBName='||SUBSTR(source_table_name,1,INSTR(source_table_name,'.' )-1  )|| ';' FROM  DW_JOB_STREAMS WHERE JOB_STREAM_ID = '${JobStreamId}'
    UNION SELECT 'export src_tbl_name='||SUBSTR(source_table_name,INSTR(source_table_name,'.' )+1  )|| ';' FROM  DW_JOB_STREAMS WHERE JOB_STREAM_ID = '${JobStreamId}'
    UNION SELECT 'export LastExtractDate=' ||''''|| TO_CHAR(LAST_EXTRACT_DATE, 'MM/DD/YYYY HH12:MI:SS' )  ||''''|| ';' FROM  DW_JOB_STREAMS WHERE JOB_STREAM_ID = '${JobStreamId}'
    UNION SELECT 'export Access_User='||LOWER(ACCESS_USER)|| ';' FROM  DW_JOB_STREAMS WHERE JOB_STREAM_ID = '${JobStreamId}';
SPOOL OFF
exit
EOF`
       RC=$?
           if [ $RC -ne 0 ] ; then
               echo "$0: ERROR! while retrieving the Last Extract Date for JOB STREAM ID ${JobStreamId}."
              exit 1
          fi
       . ${TmpFile}

I am able to see the values correctly in TmpFile. But when it executes in UNIX, I am getting the below error only for a particular value which is LastExtractDate. The value in the db table for this column is 15/06/2010 12:31:00 PM. But when i execute the script, I get the below error:

export: 12:31:00';: is not an identifier

It is not recognizing the date part before the timestamp.

Can someone shed some light on this.

Thanks,

2

Hi.

At the very least, you will have to wrap quotes around the "export DBName=" part.

i.e.

...
SELECT 'export DBName="'||SUBSTR(source_table_name,1,INSTR(source_table_name,'.' )-1  )|| '";' FROM  DW_JOB_STREAMS WHERE JOB_STREAM_ID = '${JobStreamId}'

...
3

It still fails with the same error.

export: 12:31:00'";: is not an identifier

4

You haven't posted your complete script, which makes analysing it somewhat tricky.

i.e. where's the line that contains "sqlplus"; what is TmpFile, and where does it come from, etc.

Please post all that you have (including the update you made with the quotes). Thanks.

5 
#!/bin/ksh
WORKFLOW_NAME=$1
# Setup Informatica environment variables for this server
. /apps/informatica/shared/scripts/${HOSTNAME}_infa_setup.ksh
JobStreamId=${WORKFLOW_NAME}
set ORACLE_HOME=/oracle/product/10.2/bin
export ORACLE_HOME
export TmpFile=$INFMT_ADMIN_DIR/DATA/$FOLDER_NAME/ctrl/$WORKFLOW_NAME.$$
LOGON=`cat $INFMT_ADMIN_DIR/ctrl/logon_orcl_control_schema.txt`
`sqlplus -s $LOGON << EOF
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET TIMING OFF
SET TERMOUT OFF
SET LINES 160
SPOOL ${TmpFile}
    SELECT 'export DBName='||SUBSTR(source_table_name,1,INSTR(source_table_name,'.' )-1  )|| ';' FROM  DW_JOB_STREAMS WHERE JOB_STREAM_ID = '${JobStreamId}'
    UNION SELECT 'export src_tbl_name='||SUBSTR(source_table_name,INSTR(source_table_name,'.' )+1  )|| ';' FROM  DW_JOB_STREAMS WHERE JOB_STREAM_ID = '${JobStreamId}'
    UNION SELECT 'export LastExtractDate="' ||''''|| TO_CHAR(LAST_EXTRACT_DATE, 'DD/MM/YYYY HH12:MI:SS' )  ||''''|| '";' FROM  DW_JOB_STREAMS WHERE JOB_STREAM_ID = '${JobStreamId}'
    UNION SELECT 'export Access_User='||LOWER(ACCESS_USER)|| ';' FROM  DW_JOB_STREAMS WHERE JOB_STREAM_ID = '${JobStreamId}';
SPOOL OFF
exit
EOF`
       RC=$?
           if [ $RC -ne 0 ] ; then
               echo "$0: ERROR! while retrieving the Last Extract Date for JOB STREAM ID ${JobStreamId}."
              exit 1
          fi
       . ${TmpFile}

---------- Post updated at 02:22 PM ---------- Previous update was at 02:21 PM ----------

Scott,

Thanks for the quick response. I have added the (") quotation marks in the script as suggested by you for one of the columns.

6

Hi.

I think you need to apply the quotes to all of them in turn, not just one.

As an example:

$ cat Test1
eval `sqlplus -s scott/tiger << !
  set feedback off
  set heading off
  select 'export XX="' || sysdate || '";' from dual
  union
  select 'export YY="' || 123 || '";' from dual;
!`

echo $XX
echo $YY

$ ./Test1
15-JUN-10
123
7

Hey Scott,

Problem is with only the LastExtractDate variable but not others. However I have put the quotations for all the variables and tried. It still did not work. What else might be the problem?

Thanks,
Sam

8

What's with all those single quotes?

Try removing the bits in red.

UNION SELECT 'export LastExtractDate="' ||''''|| TO_CHAR(LAST_EXTRACT_DATE, 'DD/MM/YYYY HH12:MI:SS' )  ||''''|| '";' FROM  DW_JOB_STREAMS WHERE JOB_STREAM_ID = '${JobStreamId}'

Also remove the backticks ` around the sqlplus to see what output you would get.

`sqlplus -s $LOGON << EOF
....
EOF`
9

Hey Scott,

Your script works fine for the scenario you mentioned in above script. But in my case, I have a date column with timestamp attached to it. so there is a space between the date value and time part which is causing the issue here. the value will be like this. 15/06/2010 12:30:30 PM

10

Yes, hence the need for the quotes.

Not working:

$ cat Test1
eval `sqlplus -s scott/tiger << !
  set feedback off
  set heading off
  select 'export XX="' || sysdate || '";' from dual
  union
  select 'export YY=' || 123 || ' ' || 456 || ';' from dual;
!`

echo $XX
echo $YY

$ ./Test1
./Test1[1]: export: 456: is not an identifier

Working:

$ cat Test1
eval `sqlplus -s scott/tiger << !
  set feedback off
  set heading off
  select 'export XX="' || sysdate || '";' from dual
  union
  select 'export YY="' || 123 || ' ' || 456 || '";' from dual;
!`

echo $XX
echo $YY

$ ./Test1
15-JUN-10
123 456

This is always tricky as I don't have your data or data structure, but based on your expression that doesn't work (which does work, when changed to....)

$ cat Test2
eval `sqlplus -s scott/tiger << !
  set feedback off
  set heading off
  SELECT 'export LastExtractDate="' ||''''|| TO_CHAR(SYSDATE, 'DD/MM/YYYY HH12:MI:SS' )  ||''''|| '";' FROM  DUAL WHERE 1 = '1';
!`

echo $LastExtractDate

$ ./Test2
'15/06/2010 08:45:43'

Therefore I suggest you take each individual select, and test it in isolation, as from my point of view there's no issue.

11

Hey Scott,

Thanks much for providing the support. Now it works for me. But one thing I want to know is will I be able to create a spool file from the output of the select statement? I need to do this as I want to use the spool file in multiple places with in the script.

Thanks,
Sam

12

Hi.

Great :smiley:

Yes, I think it should be OK...

$ cat Test1
eval `sqlplus -s scott/tiger << !
  set feedback off
  set heading off
  spool OUT.SPOOL
  select 'export XX="' || sysdate || '";' from dual
  union
  select 'export YY="' || 123 || ' ' || 456 || '";' from dual;
!`

$ ./Test1

$ cat OUT.SPOOL
export XX="15-JUN-10";                                                          
export YY="123 456";

Alternatively you could just redirect the output:

$ cat Test1
sqlplus -s scott/tiger << ! > OUT.sh
  set feedback off
  set heading off
  select 'export XX="' || sysdate || '";' from dual
  union
  select 'export YY="' || 123 || ' ' || 456 || '";' from dual;
!

$ ./Test1

$ cat OUT.sh
export XX="15-JUN-10";
export YY="123 456";
13

sorry to bug you again and again. The script creates the spool file. But it is not executing the spool file. My requirement is to run the spool file once it is created, so that it will export the variables in spool file. I need these exported variables as I am using them in multiple places with in the script.

Below is the code that I have changed as per your suggestion.

#!/bin/ksh
export TmpFile=/apps/sam/scripts/listen.$$
eval `sqlplus -s scott/tiger@orcl << !
  set feedback off
  set heading off
  SPOOL ${TmpFile}
  SELECT 'export LastExtractDate="' ||''''|| TO_CHAR(SYSDATE, 'DD/MM/YYYY HH12:MI:SS' ) ||''''|| '";' FROM DW_JOB_STREAMS WHERE JOB_STREAM_ID = 'wf_Table_To_File'
  UNION
  SELECT 'export DBName="' ||''''|| SUBSTR(source_table_name,1,INSTR(source_table_name,'.' )-1  ) ||''''|| '";' FROM DW_JOB_STREAMS WHERE JOB_STREAM_ID = 'wf_Table_To_File'
  UNION
  SELECT 'export src_tbl_name="' ||''''|| SUBSTR(source_table_name,INSTR(source_table_name,'.' )+1  ) ||''''|| '";' FROM DW_JOB_STREAMS WHERE JOB_STREAM_ID = 'wf_Table_To_File'
  UNION
  SELECT 'export Access_User="' ||''''|| LOWER(ACCESS_USER) ||''''|| '";' FROM DW_JOB_STREAMS WHERE JOB_STREAM_ID = 'wf_Table_To_File';
!`
  chmod ugo+rwx $TmpFile
  . ${TmpFile}
echo $LastExtractDate
echo $DBName
echo $src_tbl_name
echo $Access_User

Thanks in advance

14

:slight_smile:

Going back to a previous post, pick one, let's say #10:

$ cat Test1
eval `sqlplus -s scott/tiger << !
  set feedback off
  set heading off
  select 'export XX="' || sysdate || '";' from dual
  union
  select 'export YY="' || 123 || ' ' || 456 || '";' from dual;
!`

echo $XX
echo $YY

$ ./Test1
15-JUN-10
123 456

If you are planning to export some variables for use in the current script, then you don't need to spool anything. The variables are already set after running the SQL, and you can use them anywhere in your script.

$ cat Test1
SetSomeVars() {
eval `sqlplus -s scott/tiger << !
  set feedback off
  set heading off
  select 'export XX="' || sysdate || '";' from dual
  union
  select 'export YY="' || 123 || ' ' || 456 || '";' from dual;
!`
}

SetSomeVars

echo "Variables set after calling function..."
echo $XX
echo $YY

(I didn't quite test this yet, 'cos I shutdown my Oracle server!! - will "fire it up" again and test!!)

---------- Post updated at 01:07 AM ---------- Previous update was at 01:05 AM ----------

I LOVE VMFUSION!!!

$ ./Test1
Variables set after calling function...
15-JUN-10
123 456

---------- Post updated at 01:12 AM ---------- Previous update was at 01:07 AM ----------

I asked you about

. ${TmpFile}

way back.

If your "spool file" was stored in $Tmpfile, then that's exactly how you would load it.

$ cat Test1
sqlplus blah << !
  ....
  spool $TmpFile
  select ......
  union ....
!

. $TmpFile

If you are going to use the spool approach, you don't need to use backticks, eval and all that when running your SQL.

Pick one approach, as right now you are kind of using two possibly conflicting approaches to reach your goal.