Solved

Error executing command from server

Posted on 2016-11-20
6
63 Views
Last Modified: 2016-11-23
HI All, I am new to DB ,I am executing a command from server to fetch some data from DB. Error I am getting is ERROR at line 1: ORA-04044: procedure, function, package, or type is not allowed here

Here is the query and error:

-bash-3.2$ more SP1.sh
ORACLE_HOME=/data/eocecm/oracle/product/11.2.0/client_1/; export ORACLE_HOME
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH


sqlplus -s "omsepc/feb2015@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=eocecmdbprd1.kpno.be)(Port=1511))(CONNECT_DATA=(SID=OMSEPCPRD1)))"<< EOF
set colsep "," 
set embedded on 
set heading on 
set feedback off 
    set echo off 
    set verify off 
    set pagesize 10000 
    set trimspool on 
    set linesize 30000 

spool /data/Sumit/CURSOR/Sumit1.log
select * from v$SYSSTAT;
spool off; 
EOF


-bash-3.2$ ./SP1.sh
select * from v
              *
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
0
Comment
Question by:Sumit Prakash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 41894858
Your issue is shell scripting and not database.  A $ in shell terms indicates a variable name.  You need to escape it so the shell doesn't interpret the $.

ORACLE_HOME=/data/eocecm/oracle/product/11.2.0/client_1/; export ORACLE_HOME
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH


sqlplus -s "omsepc/feb2015@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=eocecmdbprd1.kpno.be)(Port=1511))(CONNECT_DATA=(SID=OMSEPCPRD1)))"<< EOF
set colsep "," 
set embedded on 
set heading on 
set feedback off 
    set echo off 
    set verify off 
    set pagesize 10000 
    set trimspool on 
    set linesize 30000 

spool /data/Sumit/CURSOR/Sumit1.log
select * from v\$SYSSTAT;
spool off; 
EOF

Open in new window

Also, I would think you you have a tnsnames.ora entry for the database connection so you don't need the full string like that.  Even if you don't, you should look at the easy connect string.
1
 

Author Comment

by:Sumit Prakash
ID: 41894869
Hello JohnSone,

Thanks for your quick help. It is working now.

Can you please help me I am getting below output of thta command :-

-------------- ---------------- --------------- ----------------------------------------------------------------
        25 OMSEPC                               1165      60989 000000069E764A60       1165      60989    3251585 00000006A04DE610         85 OMSEPC
              0 2147483644                                   INACTIVE DEDICATED         85 OMSEPC                         omsprd2                        1234
          eocecmpeprd2                                                          62383 unknown                        JDBC Thin Client                                 US
ER       00                            0                                                      00000006B197D568      3561406637 85y5a8za4dh5d                 0 20-NOV-16
     18417111                                                                                     JDBC Thin Client                                  2546894660
                                  0                                                                               9984072            -1              0               0
           0              14 18-NOV-16            0 NO  NONE          NONE       NO  OTHER_GROUPS                     DISABLED ENABLED  ENABLED                       0
                                                                 NO HOLDER                                      NO HOLDER
        55151        345 SQL*Net message from client                                      driver id                                                        1952673792 00
00000074637000 #bytes                                                                    1 0000000000000001
              0 00                  2723168908           6 Idle                                                                      0               1 WAITING
            729328                   -1                          0 SYS$USERS                                                        DISABLED FALSE FALSE FIRST EXEC
           100 00000006A04DE610              86
        25 OMSEPC                               1402      40737 00000006B27BFC38       1402      40737    3251589 000000069C58D6A8         85 OMSEPC
              0 2147483644                                   INACTIVE DEDICATED         85 OMSEPC                         omsprd2                        1234
          eocecmpeprd2                                                          62388 unknown                        JDBC Thin Client                                 US
ER       00                  


How can i get the out pur in proper format. Also I am redirecting the output in .csv file now.

But i want a proper format in my file.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41894931
Your sample output doesn't match your script.  The script is selecting from V$SYSSTAT, but that output looks like V$SESSION.  So, since the script and the output don't match, I don't know what the problem is.

When I run the script that I posted, I get output with commas between the fields.  You will always see blank spaces because SQL*Plus pads the fields so they are all the same length.  The only way that I know how to avoid that is with concatenation.

SELECT statistic#
       || ','
       || name
       || ','
       || class
       || ','
       || value
       || ','
       || stat_id
       || ','
       || con_id
FROM   v$sysstat;
1
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:Sumit Prakash
ID: 41894944
I am sorry I used some other command as I was not getting the error after you suggested. So the main script is below:-

-bash-3.2$ more SP1.sh
ORACLE_HOME=/data/eocecm/oracle/product/11.2.0/client_1/; export ORACLE_HOME
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH


sqlplus -s "omsepc/feb2015@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=eocecmdbprd1.kpno.be)(Port=1511))(CONNECT_DATA=(SID=OMSEPCPRD1)))"<< EOF
clear screen;
SET serveroutput ON size 1000000;
SET SERVEROUTPUT ON FORMAT WRAPPED;
SET ECHO OFF;
SET verify OFF;
SET linesi 500;
set trims on;
set linesize 4000;
set feed off;
clear screen; 

spool /data/Sumit/CURSOR/Sumit1.csv
select a1.value, s1.username, s1.sid, s1.serial#,s1.*
from v\$sesstat a1, v\$statname b1, v\$session s1
where a1.statistic# = b1.statistic#  and s1.sid=a1.sid
and b1.name = 'opened cursors current'
and  value >22
order by value desc;
spool off; 


Now please help me how to get a proper output
EOF
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 41894970
These commands are not needed:

clear screen;
SET serveroutput ON size 1000000;
SET SERVEROUTPUT ON FORMAT WRAPPED;

If you look at the first script you posted, you had this command:

set colsep ","

You need to add that back to get commas between the columns.  If that is not what you want, use what I showed you to create concatenated fields.  You have everything you need, just put it together.
1
 

Author Closing Comment

by:Sumit Prakash
ID: 41899729
Johnson is best expert I ever worked with.
Thanks a lot :-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question