INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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:

CODE --> 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 

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

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:

CODE --> bash

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

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

(OP)
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.

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

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

CODE

SELECT ts# into v_ts_id FROM v\$tablespace where name = v_rec.tablespace_name; 




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

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:

CODE -->

sqlplus -s "/ as sysdba" <<-"EOF" >>gname.csv 

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

(OP)
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.

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

(OP)
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.

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

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

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

(OP)
Thanks everyone for helping.
More Blessing to you all.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close