Getting PL/SQL: ORA-00942: table or view does not exist

[tekpr00]

Hello All,

I am getting the following error when I run the PL/SQL embedded in a shell script.
SELECT ts# into v_ts_id FROM v where name = v_rec.tablespace_name;
*
ERROR at line 30:
ORA-06550: line 30, column 30:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 30, column 1:
PL/SQL: SQL Statement ignored

However, when I ran the script directly having logged in as sqlplus / as sysdba:
@sample.sql

It produces the expected result:
SQL> @sample.sql


Summary
========
1) Allocated Space: 21565 MB (21.06 GB)
2) Used Space: 17850.93 MB (17.43 GB)
3) Used Space Percentage: 82.78 %
-------
-------
-------
-------

Here is the sql statement embedded in shell script:

#!/bin/ksh
#
# Script Name:  GrowthRate.sh
#
# ------------------------------------------------------------------------------
# Author : tekproo
# Date   : January 18, 2017
# Purpose: To calculate the Current Capacity & Future Growth rate of the databses
#
# Parameters: none
#
# ------------------------------------------------------------------------------
# History:
# January 18, 2017 - tekpr00 : Created
#
#
# ------------------------------------------------------------------------------
#
LASTFRIDAY=$(date --date='last Friday' +"%m%d%Y")
mv gname.csv gname_WEEKENDING_$LASTFRIDAY.log

ALL_DATABASES=`cat /etc/oratab|grep -v "^#"|cut -f1 -d: -s`
for DB in $ALL_DATABASES
do
   unset  TWO_TASK
   export ORACLE_SID=$DB
   export ORACLE_HOME=`grep "^${DB}:" /etc/oratab|cut -d: -f2 -s`
   export PATH=$ORACLE_HOME/bin:$PATH
   echo "---> For Week Ending: `date +%m%d%Y` Database: $ORACLE_SID">>gname.csv
    sqlplus  -s "/ as sysdba" <<-EOF>>gname.csv
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
not_in_awr EXCEPTION;
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_count number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
v_db_begin_size number := 0;
v_db_end_size number := 0;
v_db_begin_allocated_space number := 0;
v_db_end_allocated_space number := 0;
v_db_growth number := 0;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';
BEGIN
FOR v_rec in v_cur
LOOP
BEGIN
v_ts_begin_allocated_space := 0;
v_ts_end_allocated_space := 0;
v_ts_begin_size := 0;
v_ts_end_size := 0;
SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
select count(*) into v_count from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
IF v_count=0 THEN
RAISE not_in_awr;
END IF;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
IF UPPER(v_rec.tablespace_name)='SYSTEM' THEN
v_numdays := v_end_snap_date - v_begin_snap_date;
END IF;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_db_begin_allocated_space := v_db_begin_allocated_space + v_ts_begin_allocated_space;
v_db_end_allocated_space := v_db_end_allocated_space + v_ts_end_allocated_space;
v_db_begin_size := v_db_begin_size + v_ts_begin_size;
v_db_end_size := v_db_end_size + v_ts_end_size;
v_db_growth := v_db_end_size - v_db_begin_size;


END;
END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_db_end_allocated_space||' MB'||' ('||round(v_db_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_db_end_size||' MB'||' ('||round(v_db_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||Around(v_db_end_size/v_db_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_db_begin_allocated_space||' MB'||' ('||round(v_db_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_db_end_allocated_space||' MB'||' ('||round(v_db_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_db_begin_size||' MB'||' ('||round(v_db_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_db_end_size||' MB'||' ('||round(v_db_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_db_growth||' MB'||' ('||round(v_db_growth/1024,2)||' GB)');
IF (v_db_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for the Database');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_db_growth/v_numdays,2)||' MB'||' ('||round((v_db_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_db_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_db_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_db_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_db_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_db_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_db_growth/v_numdays)*90)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\');
END IF;
EXCEPTION
WHEN NOT_IN_AWR THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('====================================================================================================================');
DBMS_OUTPUT.PUT_LINE('!!! ONE OR MORE TABLESPACES USAGE INFORMATION NOT FOUND IN AWR !!!');
DBMS_OUTPUT.PUT_LINE('Execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT, or wait for next AWR snapshot capture before executing this script');
DBMS_OUTPUT.PUT_LINE('====================================================================================================================');
END;
/
exit;
EOF
done
set content_type=text/csv  mutt -e set content_type=text/csv | mailx -s "Database Storage Growth Report" tekpr00@mail.com < gname.csv

 

[stefanhei]

Hi,
in your here-document v$tablespace is evaluated as a shell variable. I do not have a box with korn shell at hand, but in bash you can turn of variable subsitution in here-docs by quoting the token that is used as delimiter:

#!/bin/bash
...
sqlplus -s "/ as sysdba" <<-"EOF" >>gname.csv
set serverout on 
...
EOF
...

 

[tekpr00]

Even after changing:

#!/bin/bash

It still errors out:
SELECT ts# into v_ts_id FROM v where name = v_rec.tablespace_name;
*
ERROR at line 30:
ORA-06550: line 30, column 30:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 30, column 1:
PL/SQL: SQL Statement ignored

I believe you it has something to the with the shell too because the SQL statement works perfectly.

 

[SamBones]

Yeah, stefanhei is correct. The shell is parsing the here-document before it goes to sqlplus. This is normal shell behavior. That lets you use shell variables in your here-doc.

You can change it to this and it should work...

SELECT ts# into v_ts_id FROM v[highlight #FCE94F]\$[/highlight]tablespace where name = v_rec.tablespace_name;

 

[stefanhei]

My suggestion was not to use bash, but a mechanism that disables variable substitution in your here-doc.
After reading up a ksh manual online I found out that quoting the delimiter turns off parsing with this shell too.
So either use SamBones suggestion, which should be the most portable one (but in my eyes makes the here-doc harder to read) or quote the token that starts the here-doc:

sqlplus -s "/ as sysdba" <<-[highlight #FCE94F]"[/highlight]EOF[highlight #FCE94F]"[/highlight] >>gname.csv

 

[tekpr00]

THanks stefanhei and SamBones:

Even when I changed the line to sqlplus -s "/ as sysdba" <<-"EOF" >>gname.csv.
I am still getting:
---> For Week Ending: 02032017 Database:mydbase

PL/SQL procedure successfully completed.

However, when I ran only the script from inside the database, I get expected result as follows:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @sample.sql;

Summary
========
1) Allocated Space: 21585 MB (21.08 GB)
2) Used Space: 17978.19 MB (17.56 GB)
3) Used Space Percentage: 83.29 %
History
========
1) Allocated Space on 26-JAN-17: 21555 MB (21.05 GB)
2) Current Allocated Space on 03-FEB-17: 21585 MB (21.08 GB)
3) Used Space on 26-JAN-17: 17910.75 MB (17.49 GB)
4) Current Used Space on 03-FEB-17: 17978.19 MB (17.56 GB)
5) Total growth during last 8 days between 26-JAN-17 and 03-FEB-17: 67.44 MB (.07 GB)
6) Per day growth during last 8 days: 8.43 MB (.01 GB)
Expected Growth
===============
1) Expected growth for next 15 days: 126.45 MB (.12 GB)
2) Expected growth for next 30 days: 252.9 MB (.25 GB)
3) Expected growth for next 45 days: 379.35 MB (.37 GB)

/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/
PL/SQL procedure successfully completed.

 

[tekpr00]

It seems the only line directed to gname.csv is:
PL/SQL procedure successfully completed

I have tried commenting out the follwing:
--set serverout off
--set verify off


still gettiing:
---> For Week Ending: 02032017 Database:mydbase
PL/SQL procedure successfully completed.

 

[Beilstwh]

What happened when you escaped the $ character like sambones suggested and for DBMS_OUTPUT to work you have to set serveroutput on

Bill
Lead Application Developer
New York State, USA

 

[tekpr00]

Thanks everyone for helping.
More Blessing to you all.