Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

is not an identifier error

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting is not an identifier error
# 8  
Old 06-15-2010
What's with all those single quotes?

Try removing the bits in red.
Code:
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.

Code:
`sqlplus -s $LOGON << EOF
....
EOF`

# 9  
Old 06-15-2010
Quote:
Originally Posted by scottn
Hi.

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

As an example:

Code:
$ 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

Code:
$ ./Test1
15-JUN-10
123

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  
Old 06-15-2010
Yes, hence the need for the quotes.

Not working:
Code:
$ 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:
Code:
$ 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....)
Code:
$ 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.
Last edited by Scott; 06-15-2010 at 05:47 PM..
# 11  
Old 06-15-2010
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  
Old 06-15-2010
Hi.

Great Smilie

Yes, I think it should be OK...
Code:
$ 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:

Code:
$ 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";

Last edited by Scott; 06-15-2010 at 06:33 PM.. Reason: Cut and paste error
# 13  
Old 06-15-2010
Bug
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.

Code:
#!/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  
Old 06-15-2010
Smilie

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

Code:
$ 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.

Code:
$ 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!!!

Code:
$ ./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

Code:
. ${TmpFile}

way back.

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

Code:
$ 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.
Last edited by Scott; 06-15-2010 at 08:21 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Compilation Error--Undeclared Identifier

Hello, I am trying to install BBFTP software on my Mac (OS X), and am running into some compilation errors. Here is the code, the specific errors are listed after: #include <dirent.h> #include <errno.h> #include <fnmatch.h> #include <netinet/in.h> #include <syslog.h> #include <sys/stat.h>... (1 Reply)
Discussion started by: Tyler_92
1 Replies

2. Solaris

Is not a identifier error

Hi I am trying to execute a script. I have solaris 10, After i login i will toggle to BASH. The script tried to set a variable called CARBON_HOME. Even i tried to set it manually. But still it is saying the following error.:wall: " CARBON_HOME=/usr/wso2/wso2esb-4.0.0: is not an... (5 Replies)
Discussion started by: gnanagurus
5 Replies

3. Shell Programming and Scripting

error while extracting a line from a file based on identifier

here is the content of input file CREATE TABLE `bla bla bla` ( `allianceSiteId` int(11) DEFAULT NULL, `trunkGroupsId` int(11) DEFAULT NULL, `lastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY `allianceSiteId`... (4 Replies)
Discussion started by: vivek d r
4 Replies

4. Shell Programming and Scripting

Identifier issue

Hi, We are in the process of migrating our servers from Solaris to AIX. During our testing phase, while we are testing the scripts(reccnt.int), we are getting the following error during execution: /reccnt.int: a:fname:b:dte:tme:eqind:norecs:cntr:c:d:e:f: is not an identifier It is... (6 Replies)
Discussion started by: pyaranoid
6 Replies

5. UNIX for Advanced & Expert Users

ORACLE_SID= ...: is not an identifier

Hello All, On My solaris 5.10 SPARC, i am always having problems setting my Oracle variables. even if it exists in the .profile file. below is an example $ export ORACLE_SID=test ORACLE_SID=test: is not an identifier even that the echo is returning the variable $ echo $ORACLE_SID... (3 Replies)
Discussion started by: beayni33
3 Replies

6. Shell Programming and Scripting

is not an identifier

Hi Guys... I am using the following codes in my script: SID_L=`cat /var/opt/oracle/oratab|grep -v "^#"|cut -f1 -d: -s` SID_VAR=$SID_L for SID_RUN in $SID_VAR do ORACLE_HOME=`grep ^$SID_RUN /var/opt/oracle/oratab | \ awk -F: '{print $2}'` ;export ORACLE_HOME export... (2 Replies)
Discussion started by: Phuti
2 Replies

7. Shell Programming and Scripting

not an identifier

Hi I have already gone through this topic on this forum, but still i am getting same problem. I am using solaris 10. my login shell is /usr/bash i have got a script as below /home/gyan> cat 3.cm #!/usr/bin/ksh export PROG_NAME=rpaa001 if i run this script as below , it works fine... (3 Replies)
Discussion started by: gyanibaba
3 Replies

8. Solaris

-sh: is not an identifier

Hi , I am getting the following message when log into my unix account in sun solaris (version5.9)server. -sh: ORACLE_HOME=/apps/oracle/product/10.2.0/client_1: is not an identifier The ORACLE_HOME is set in .profile file. Another thing is that SID is also set inside .profile like... (4 Replies)
Discussion started by: megh
4 Replies

9. Shell Programming and Scripting

': not a valid identifier

I am trying to write a bash script. I am able to do simple things like pass arguments, assign variables and echo the results. However, when I try to declare and array or anything a little more complicated I get ': not a valid identifier Here is my code so far: #!/bin/bash echo start t... (7 Replies)
Discussion started by: script123
7 Replies

10. Shell Programming and Scripting

Getting is not an identifier error

Hi all, I am getting this error while setting CLASSPTH using a script my script is #!bin/ksh export CLASSPATH=$CLASSPATH:<some path>:<some path> If i do this thing on shell prompt individually it is working fine. but while using in shell script it is giving error. better if you... (12 Replies)
Discussion started by: swat
12 Replies
Login or Register to Ask a Question