Host command NOT getting executed in Hot backup script. - High Level Programming

user__user3110 · #1 07-14-2008

Hi All,
i have problem in the following Sql Script.
problem is : HOST (!) command is not getting executed.pls de-bugg this HoTBackup Script......
Most urgent...!
Thank You very Much !!

Code:

SQL> set serveroutput on
SQL> set trimspool on
SQL> set line 500
SQL> set head off
SQL> set feed off
SQL>
SQL> spool c:\test\backup.txt
SQL>
SQL> declare
  2    copy_cmnd constant varchar2(30) := 'copy';       -- Use "ocopy" for NT
  3    copy_dest constant varchar2(30) := 'c:\test\'; -- C:\BACKUP\ for NT
  4
  5    dbname  varchar2(30);
  6    logmode varchar2(30);
  7  begin
  8    select name, log_mode
  9    into   dbname, logmode
 10    from   sys.v_$database;
 11
 12    if logmode <> 'ARCHIVELOG' then
 13       raise_application_error(-20000,
 14                       'ERROR: Database must be in ARCHIVELOG mode!!!');
 15       return;
 16    end if;
 17
 18    dbms_output.put_line('spool backup.'||dbname||'.'||
 19                         to_char(sysdate, 'ddMonyy')||'.log');
 20
 21    -- Loop through tablespaces
 22    for c1 in (select tablespace_name ts
 23               from   sys.dba_tablespaces)
 24    loop
 25      dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');
 26      -- Loop through tablespaces' data files
 27      for c2 in (select file_name fil from   sys.dba_data_files where  tablespace_name = c1.ts)
 28      loop
 29        dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);
 30      end loop;
 31
 32      dbms_output.put_line('alter tablespace '||c1.ts||' end backup;');
 33    end loop;
 34
 35    -- Backup controlfile and switch logfiles
 36    dbms_output.put_line('alter database backup controlfile to trace;');
 37    dbms_output.put_line('alter database backup controlfile to '||''''|| copy_dest||'control.'||dbnam
 38    dbms_output.put_line('alter system switch logfile;');
 39    dbms_output.put_line('spool off');
 40  end;
 41  /
spool backup.ORA9I.14Jul08.log
alter tablespace SYSTEM begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\SYSTEM01.DBF c:\test\
alter tablespace SYSTEM end backup;
alter tablespace UNDOTBS1 begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\UNDOTBS01.DBF c:\test\
alter tablespace UNDOTBS1 end backup;
alter tablespace TEMP begin backup;
alter tablespace TEMP end backup;
alter tablespace CWMLITE begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\CWMLITE01.DBF c:\test\
alter tablespace CWMLITE end backup;
alter tablespace DRSYS begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\DRSYS01.DBF c:\test\
alter tablespace DRSYS end backup;
alter tablespace EXAMPLE begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\EXAMPLE01.DBF c:\test\
alter tablespace EXAMPLE end backup;
alter tablespace INDX begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\INDX01.DBF c:\test\
alter tablespace INDX end backup;
alter tablespace ODM begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\ODM01.DBF c:\test\
alter tablespace ODM end backup;
alter tablespace TOOLS begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\TOOLS01.DBF c:\test\
alter tablespace TOOLS end backup;
alter tablespace USERS begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\USERS01.DBF c:\test\
alter tablespace USERS end backup;
alter tablespace XDB begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\XDB01.DBF c:\test\
alter tablespace XDB end backup;
alter tablespace TTS begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\TTS.DBF c:\test\
alter tablespace TTS end backup;
alter tablespace TESTING begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\TESTING.DBF c:\test\
alter tablespace TESTING end backup;
alter database backup controlfile to trace;
alter database backup controlfile to 'c:\test\control.ORA9I.14Jul081739';
alter system switch logfile;
spool off
SQL>
SQL> spool off
SQL>
SQL> set head on
SQL> set feed on
SQL> set serveroutput off
SQL>

AS you can see,it's the Host command is being printed instead of getting executed.
Plsssssssss Help.

Thanks

joeyg · #2 07-14-2008

Why the following?

Code:

29        dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);

If I am reading this right, by putting the '!' at the beginning of the line it will not be executed.

jim mcnamara · #3 07-14-2008

dbms writes to stdout. Adding a ! in front of it has no reall effect.

If your requirement is to call OS commands from inside PL/SQL read this:
http://www.idevelopment.info/data/Or..._1.shtml<br />

user__user3110 · #4 07-14-2008

Hi,
yes your exectly right.. im trying to execute OS command inside pl/sql.
and therez the error occurs..i dnt know how to do it.
The link u have given is not working for me.

Thanks
Zaheer

jim mcnamara · #5 07-17-2008

Sorry about the link -use dbms_pipes
from orafaq.com - a site you should visit.

Quote:

There is no direct way to execute operating system commands from PL/SQL. PL/SQL doesn't have a "host" command, as with SQL*Plus, that allows users to call OS commands. Nevertheless, the following workarounds can be used:

Database Pipes

Write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.

Code:

CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 )
  RETURN INTEGER IS
    status   NUMBER;
    errormsg VARCHAR2(80);
    pipe_name VARCHAR2(30);
BEGIN
  pipe_name := 'HOST_PIPE';
  dbms_pipe.pack_message( cmd );
  status := dbms_pipe.send_message(pipe_name);
  RETURN status;
END;
/

Quote:

External Procedure Listeners:

From Oracle 8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.

Using Java

See example at http://www.orafaq.com/scripts/plsql/oscmd.txt

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Help Required: Command to find IP address and command executed of a user	loggedout	Security	2	08-06-2008 05:12 PM
Verifying if the shell command executed or not?	eamani_sun	Shell Programming and Scripting	3	05-13-2008 12:18 AM
Any way to know wt command has been executed from last 2-3 days ?	manoja422002	UNIX for Advanced & Expert Users	3	03-26-2008 07:22 AM
perl - why is the shell script executed before the print command?	mjays	Shell Programming and Scripting	3	09-21-2007 02:49 AM
problem executed shell command from PL/SQL	alodvg	Shell Programming and Scripting	2	08-25-2006 11:58 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode