Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Error executing command from server

Posted on 2016-11-20
6
Medium Priority
?
86 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 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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

580 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