Error executing command from server

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
Sumit PrakashAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Sumit PrakashAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
johnsoneSenior Oracle DBACommented:
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
 
Sumit PrakashAuthor Commented:
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
 
Sumit PrakashAuthor Commented:
Johnson is best expert I ever worked with.
Thanks a lot :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.