Solved

Error executing command from server

Posted on 2016-11-20
6
23 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
  • 3
  • 3
6 Comments
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:johnsone
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Sumit Prakash
Comment Utility
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 34

Accepted Solution

by:
johnsone earned 500 total points
Comment Utility
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
Comment Utility
Johnson is best expert I ever worked with.
Thanks a lot :-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now