ORA-00904: invalid identifier


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers ORA-00904: invalid identifier
# 1  
Old 03-20-2015
ORA-00904: invalid identifier

I've been trying to debug this for 3 hours now. TAB is a table where it contains all the table names. If I try to run it without the filter 'where' it runs fine and outputs the number of rows. But when I put a filter it shows that error. I want to be able to know if the table exists, if it doesn't, create it. But I'm stuck here..

Code:
#bin/ksh

OUTPUT=$(sqlplus -s ABC31/ABC31@ALPHA01 << EOF
        set echo off feed off heading off tab off pagesize 0 trimspool on linesize 500
        SELECT COUNT(*) from tab where tname = 'SAMPLE_TABLE';
exit;
EOF)

echo $OUTPUT

ORA-00904: "SAMPLE_TABLE": invalid identifier

Moderator's Comments:
Mod Comment Use code tags, thanks.

Last edited by zaxxon; 03-20-2015 at 05:26 AM..
# 2  
Old 03-20-2015
If I understand correctly, I think you want to store the output in a variable.

Use like below:
Code:
vSQLResult=`sqlplus -s DBUID/DBPASS@SCHEMA <<EOF
set pages 0
set head off
set feed off
<<Your DB queries goes here>>
exit
EOF`

Then echo/print the variable.
# 3  
Old 03-20-2015
Quote:
Originally Posted by gmatcat
... I want to be able to know if the table exists, if it doesn't, create it. ...
#bin/ksh

OUTPUT=$(sqlplus -s ABC31/ABC31@ALPHA01 << EOF
set echo off feed off heading off tab off pagesize 0 trimspool on linesize 500
SELECT COUNT(*) from tab where tname = 'SAMPLE_TABLE';
exit;
EOF)


echo $OUTPUT

ORA-00904: "SAMPLE_TABLE": invalid identifier
Code:
$
$
$ cat -n create_table_if_needed.sh
     1  #!/usr/bin/ksh
     2  OUTPUT=$(sqlplus -s test/test@test << EOF
     3  set echo off feed off heading off tab off pagesize 0 trimspool on linesize 500 time off timing off
     4  declare
     5      v_message  varchar2(40) := 'Table did not exist and was created.';
     6  begin
     7      select 'Table exists.'
     8        into v_message
     9        from tab
    10       where tname = 'SAMPLE_TABLE' ;
    11       dbms_output.put_line(v_message);
    12  exception
    13      when no_data_found then
    14          execute immediate 'create table sample_table ( x number, y varchar2(1) )';
    15          dbms_output.put_line(v_message);
    16  end;
    17  /
    18  exit;
    19  EOF)
    20
    21  echo $OUTPUT
    22
$
$ ./create_table_if_needed.sh
Table did not exist and was created.
$
$ ./create_table_if_needed.sh
Table exists.
$
$ ./create_table_if_needed.sh
Table exists.
$
$

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Red Hat

Ora-27603:ora-27626:

Hi, User claim that job is running slow from their end. I DBA found in database the below errors in alert log file. ORA-27603: Cell storage I/O error, I/O failed on disk o/192.168.10.3/RECO_DM01_CD_01_drm01 at offset 13335789568 for data length 1048576 ORA-27626: Exadata error: 2201 (IO... (2 Replies)
Discussion started by: Maddy123
2 Replies

2. UNIX for Dummies Questions & Answers

Sql invalid identifier ORA-00904

Hello I am trying to use the following inside a shell script : S_TAB=$(sqlplus -s / as sysdba <<EOF set feedback off linesize 132 pages 0 echo off verify off heading off select created from v\$database; select 'hurrah' from dual; EOF) Spews out lots of rubbish... (2 Replies)
Discussion started by: d_b
2 Replies

3. AIX

Bison -pap_expr_yy invalid character:% unexpected "identifier" while running make for Apache2.4.3 64

The Follwing packages are installed on my AIX 6.1 box gcc-4.7.2-1 gcc-c++-4.7.2-1 gcc-cpp-4.7.2-1 gcc-gfortran-4.7.2-1 libgcc-4.7.2-1 libgomp-4.7.2-1 libstdc++-4.7.2-1 libstdc++-devel-4.7.2-1 gmp-5.0.5-1 libmpc-1.0.1-2 libmpc-devel-1.0.1-2 libmpcdec-1.2.6-1 libmpcdec-devel-1.2.6-1... (0 Replies)
Discussion started by: Ashish Gupta
0 Replies

4. UNIX for Advanced & Expert Users

grep all ORA errors except one ORA error

Hi - I am trying to grep all "ORA" errors in a log files.I have to grep all ORA errors except one error for example ORA-01653.How can exclude that error in "grep" command? In following "grep" command I want to exclude "ORA-01653" error grep -i ORA alert.log >>/tmp/ora_errors.txt ... (7 Replies)
Discussion started by: Mansoor8810
7 Replies

5. 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

6. Solaris

maxuprc and maxusers - ORA-27300, ORA-27301, ORA-27302

Hi all, Am intermittently getting the following errors on one of my databases. Errors in file /oracle/HRD/saptrace/background/hrd_psp0_13943.trc: ORA-27300: OS system dependent operation:fork failed with status: 12 ORA-27301: OS failure message: Not enough space ORA-27302:... (1 Reply)
Discussion started by: newbie_01
1 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

Problem with shell script...ORA-00904:invalid identifier

Guys, Please suggest me what's wrong with this script? #!/usr/bin/sh ############################################################################## # Author : Bhagat Singh # # # Date : Nov 13,2006 #... (12 Replies)
Discussion started by: bhagat.singh-j
12 Replies
Login or Register to Ask a Question