Solved

Run a report thru a pl/sql procedure from OS

Posted on 2002-07-05
2
1,303 Views
Last Modified: 2012-06-21
HI

I need to run a oracle report thru a pl/sql procedure from the OS(VMS). Can it be done?

If there is morethan one answer please let me know them.

Thanks in advance.
0
Comment
Question by:syamsundar
2 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 50 total points
ID: 7133567
You can "Host" out of PL/SQL to the UNIX or DOS prompt with dbms_pipe.  

The usage of dbms_pipe is explained below.  

Then use Reports Runtime syntax, as found in the help for r25run, r30run or
rwrun60 to run the report.  This syntax can be tested at the UNIX prompt.

Example:
r25run userid=id/password@database report=<report_name_and_path> destype=
printer

r30run userid=id/password@database report=<report_name_and_path> destype=
printer

rwrun60 userid=id/password@database report=<report_name_and_path> destype=
printer


Dynamic SQL and System Commands Using DBMS_PIPE


Two features which would be very nice in PL/SQL are the ability to execute a
system command (like HOST in SQL*Forms), and the capability to execute dynamic
SQL.  Neither of these features are available with PL/SQL 2.0.  The latter of
these, dynamic PL/SQL, is available (via the dbms_sql package) in PL/SQL
2.1, which is released with Oracle 7.1.  However, system commands are not yet
available with this release.  This bulletin presents an implementation of these
2 valuable features using the DBMS_PIPE package.  You must have both the Pro*C
precompiler and a C compiler installed for this to work.

Note that these examples are also documented in the Oracle8 Application
Developers Guide, although you may wish to use external callouts in Oracle 8
as these provide a clean interface between PL/SQL and C code.

Background
~~~~~~~~~~
In order to explain how this works, we need some background about the DBMS_PIPE
package.  This package is owned by SYS, and is created by the catproc.sql
script.  In order to run it, you will need execute privileges on it.  Contact
your DBA if this is not the case.  This package enables you to send messages
between sessions connected to the same database.  One of the sessions will be
your PL/SQL block, and the other will be the enclosed C program.

This bulletin includes two files, daemon.pc and daemon.sql:

daemon.pc is the source code for a C program, called a 'daemon'.  This is
because it needs to be running before it can receive messages.  However, it is
'sleeping' most of the time.  In this state, it is looping while waiting for a
message to be received over the pipe.  When a message is received, it will
'wake up' and process the message.  The term 'daemon' is common in discussions
about the UNIX operating system, and there are many UNIX daemons.  The
'sleeping' and 'waking up' are implemented by calls to
dbms_pipe.receive_message(), which will sleep until a message is received.

daemon.sql is the source code for a PL/SQL package.  This package has
procedures which use dbms_pipe to send and receive message to and from the
daemon.  One thing to note is that full handshaking is used.  This means that
the daemon will always send a message back to the package (except in the case
of the 'STOP' command).  This is valuable, since it allows us to make sure
that the daemon is running.

To set this up on your system, first extract daemon.pc and daemon.sql into
separate files on your system.  Then connect to the database with SQL*Plus,
and load the daemon.sql file with '@daemon'.  This will create the daemon
package.  You then need to compile daemon.pc into an executable.  How to do
this varies according to your operating system:  On UNIX systems, you can use
the 'proc.mk' makefile.  On VMS, you can use the 'lnproc' link script.  Note
that you will have to use the precompile option 'sqlcheck=semantics', since
the program contains embedded PL/SQL blocks.  You will also need to use the
'userid' precompile option, so that the precompiler will know how to connect
to the database.  For example, 'userid = scott/tiger'.  (Don't type the quotes,
they are only delimeters in this text.)  You can now use the daemon package to
execute system commands and (limited) dynamic SQL by using the functions
daemon.execute_system() and daemon.execute_sql().

Note that you can call the daemoon packaged procedures execute_system() and
execute_sql() from anonymous blocks in SQL*Plus, from an embedded PL/SQL
block in another precompiler program, or even a forms trigger.  This package
can only execute non-query SQL statements, with no bind variables.  Look at
the code for more details on these functions.

Remember that the daemon needs to be running first.  So you may want to run it
in the background, or in another window besides the SQL*Plus session from which
you call it.

NOTE: The daemon package also uses the dbms_output package to display the
results.  To get this to work, you need to have execute privileges on this
package as well.  It is also owned by SYS, and created by catproc.sql.  You
will also need to issue 'set serveroutput on' from the SQL> prompt in SQL*Plus
before running it.

See Appendix A of the 'ORACLE7 Server Application Developer's Guide' for more
information on the dbms_pipe and dbms_output packages.

/************************************************************************
File:          daemon.sql
Written by:    Scott Urman, Language Support
Last Modified: 11/7/94

This is the source code for the daemon package.  It sends messages to the
daemon listener via dbms_pipe.  The package has two functions and one
procedure:

execute_sql: passes the sql command given by the first argument to the daemon
             listener for execution.  The sql command must not be a query.
             Returns the sqlcode after execution of the command.
execute_system: passes the system command given by the first argument to the
             daemon listener for execution in the operating system.  Returns
             the result of the system command.
stop: causes the daemon to exit.  After this command, calls to execute_sql
             and execute_system will fail until the daemon is restarted.

This package sends the first message to the daemon over the pipe named
'daemon'.  As part of this message, the return pipe name is passed.  The name
of the return pipe is the value of dbms_pipe.unique_session_name.  This way,
each session will be listening on its own pipe, and so one session won't
receive the messsages meant for another.

************************************************************************/

create or replace package daemon as
  /* Executes a non-query sql statement or plsql block.  Arguments:
     command: the sql statement to execute
     timeout: (optional) number of seconds to wait to send or receive a
              message
     Returns the sqlcode after execution of the statement. */
  function execute_sql(command varchar2, timeout number default 10)
    return number;

  /* Executes a system (host) command.  Arguments:
     command: the command to execute
     timeout: (optional) number of seconds to wait to send or receive a
              message
     Returns the value passed to the operating system by the command. */
  function execute_system(command varchar2, timeout number default 10)
    return number;

  /* Tells the daemon listener to exit.  Arguments:
     timeout: (optional) number of seconds to wait to send the message. */
  procedure stop(timeout number default 10);
end daemon;
/

create or replace package body daemon as

  function execute_system(command varchar2, timeout number default 10)
    return number is

    s number;
    result varchar2(20);
    command_code number;
    pipe_name varchar2(30);
  begin

    /* Use uniqe_session_name to generate a unique name for the return pipe.
       We include this as part of the inital message to the daemon, and it is
       send along the pipe named 'daemon'.  */
    pipe_name := dbms_pipe.unique_session_name;

    /* Send the 'SYSTEM' command to the daemon. */
    dbms_pipe.pack_message('SYSTEM');
    dbms_pipe.pack_message(pipe_name);
    dbms_pipe.pack_message(command);
    s := dbms_pipe.send_message('daemon', timeout);
    if s <> 0 then
      raise_application_error(-20010,
        'Execute_system: Error while sending.  Status = ' || s);
    end if;

    /* Check for the handshake message.  Note that we are now listening on
       the pipe which is unique to this session. */
    s := dbms_pipe.receive_message(pipe_name, timeout);
    if s <> 0 then
      raise_application_error(-20011,
        'Execute_system: Error while receiving.  Status = ' || s);
    end if;

    /* Get the operating system result code, and display it using
       dbms_output.put_line(). */
    dbms_pipe.unpack_message(result);
    if result <> 'done' then
      raise_application_error(-20012,
        'Execute_system: Done not received.');
    end if;

    dbms_pipe.unpack_message(command_code);
    dbms_output.put_line('System command executed.  result = ' ||
                         command_code);
    return command_code;
  end execute_system;


  function execute_sql(command varchar2, timeout number default 10)
    return number is

    s number;
    result varchar2(20);
    command_code number;
    pipe_name varchar2(30);
  begin


    /* Use uniqe_session_name to generate a unique name for the return pipe.
       We include this as part of the inital message to the daemon, and it is
       send along the pipe named 'daemon'.  */
    pipe_name := dbms_pipe.unique_session_name;

    /* Send the 'SQL' command to the daemon. */
    dbms_pipe.pack_message('SQL');
    dbms_pipe.pack_message(pipe_name);
    dbms_pipe.pack_message(command);
    s := dbms_pipe.send_message('daemon', timeout);
    if s <> 0 then
      raise_application_error(-20020,
        'Execute_sql: Error while sending.  Status = ' || s);
    end if;

    /* Check for the handshake message.  Note that we are now listening on
       the pipe which is unique to this session. */
    s := dbms_pipe.receive_message(pipe_name, timeout);
    if s <> 0 then
      raise_application_error(-20021,
        'Execute_sql: Error while receiving.  Status = ' || s);
    end if;

    /* Get the result code from the SQL statement, and display it using
       dbms_output.put_line(). */
    dbms_pipe.unpack_message(result);
    if result <> 'done' then
      raise_application_error(-20022,
        'Execute_sql: Done not received.');
    end if;

    dbms_pipe.unpack_message(command_code);
    dbms_output.put_line('SQL command executed.  sqlcode = ' || command_code);
    return command_code;
  end execute_sql;


  procedure stop(timeout number default 10) is
    s number;
  begin

    /* Send the 'STOP' command to the daemon. */
    dbms_pipe.pack_message('STOP');
    s := dbms_pipe.send_message('daemon', timeout);
    if s <> 0 then
      raise_application_error(-20030,
        'Stop: Error while sending.  Status = ' || s);
    end if;
  end stop;

end daemon;
/



/************************************************************************
File:          daemon.pc
Written by:    Scott Urman, Language Support
Last Modified: 11/7/94

This is the source code for the daemon listener to implement dynamic sql
and system commands from plsql.  The program accepts three
'daemon commands':

STOP:   causes the daemon to disconnect from Oracle and exit.
SYSTEM: causes the daemon to execute the next item on the pipe as a
        operating system command.
SQL:    causes the daemon to execute the next item on the pipe as an
        sql statement.  Also returns the sqlcode resulting from the
        statement.

The daemon commands are received over the pipe named 'daemon'.  As part of the
first message sent along this pipe, the name of the pipe to use for the return
session is passed.

************************************************************************/
#include <stdio.h>
#include <string.h>
EXEC SQL include sqlca;

EXEC SQL begin declare section;
  char *uid = "scott/tiger";  /* User/password to connect to Oracle */
  int status;                 /* Return value for dbms_pipe.send_message
                                 and dbms_pipe.receive_message */
  varchar command[20];        /* Daemon command to execute */
  varchar value[2000];        /* Value (SQL statement or system command)
                                 associated with previous daemon command */
  varchar return_name[30];    /* Name of the pipe on which to send the
                                 results */
EXEC SQL end declare section;

/* This is the error handler for connecting to Oracle.  If we failed on the
   connection attempt, we need to exit the program. */
void connect_error() {

  char msg_buffer[512];
  int msg_length;
  int buffer_size = 512;

  EXEC SQL whenever sqlerror continue;
  sqlglm(msg_buffer, &buffer_size, &msg_length);
  printf("Daemon error while connecting:\n");
  printf("%.*s\n", msg_length, msg_buffer);
  printf("Daemon quitting.\n");
  exit(1);
  }


/* This is the general error handler. Note that we don't exit the program in
   this case.  We just print the error and continue.  This is because any
   errors probably will not affect future operations, and we should keep the
   daemon running.  This of course depends on the error, and you may want to
   change this behavior. */
void sql_error() {

  char msg_buffer[512];
  int msg_length;
  int buffer_size = 512;

  EXEC SQL whenever sqlerror continue;
  sqlglm(msg_buffer, &buffer_size, &msg_length);
  printf("Daemon error while executing:\n");
  printf("%.*s\n", msg_length, msg_buffer);
  printf("Daemon continuing.\n");
  }

main() {

  EXEC SQL whenever sqlerror do connect_error();
  EXEC SQL connect :uid;
  printf("Daemon connected.\n");

  EXEC SQL whenever sqlerror do sql_error();
  printf("Daemon waiting...\n");
  while (1) {
    /* Wait for a message to be received, using pipe daemon. */
    EXEC SQL EXECUTE
      begin
        :status := dbms_pipe.receive_message('daemon');
        if :status = 0 then
          dbms_pipe.unpack_message(:command);
        end if;
      end;
    END-EXEC;

    if (status == 0) {
      /* At this point, we have successfully received a message.  Now we
         need to determine which daemon command to execute. */
      command.arr[command.len] = '\0';
      if (!strcmp((char *)command.arr, "STOP")) {
        /* STOP command received.  Simply exit the program. */
        printf("Daemon exiting.\n");
        break;
        }

      else if (!strcmp((char *)command.arr, "SYSTEM")) {
        /* SYSTEM command received.  Unpack the next 2 values.  These will be
           the name of the return pipe, and the command to pass to the
           operating system.  */
        EXEC SQL EXECUTE
          begin
            dbms_pipe.unpack_message(:return_name);
            dbms_pipe.unpack_message(:value);
          end;
        END-EXEC;
        value.arr[value.len] = '\0';
        printf("Will execute system command '%s'\n", value.arr);

        /* Execute the command. */
        status = system(value.arr);

        /* Send a message back to indicate that the command has been
           executed.  Also send the result of the system command.
           Use the pipe passed in from the first message for this. */

        EXEC SQL EXECUTE
          begin
            dbms_pipe.pack_message('done');
            dbms_pipe.pack_message(:status);
            :status := dbms_pipe.send_message(:return_name);
          end;
        END-EXEC;

        if (status) {
          printf("Daemon error while responding to system command.");
          printf("  status: %d\n", status);
          }
        }

      else if (!strcmp((char *)command.arr, "SQL")) {
        /* SQL command received.  Unpack the next 2 values.  These will be
           the name of the return pipe, and the SQL command to execute. */
        EXEC SQL EXECUTE
          begin
            dbms_pipe.unpack_message(:return_name);
            dbms_pipe.unpack_message(:value);
          end;
        END-EXEC;
        value.arr[value.len] = '\0';
        printf("Will execute sql command '%s'\n", value.arr);

        /* Execute the command.  Note that we don't want to go to the error
           handler if there is a problem - we just pass the code back. */
        EXEC SQL whenever sqlerror continue;
        EXEC SQL EXECUTE IMMEDIATE :value;
        status = sqlca.sqlcode;

        /* Reset the error handler, and send a message back to indicate
           that the command has been executed.  Also send the sqlcode.
           Use the pipe passed in from the first message for this. */

        EXEC SQL whenever sqlerror do sql_error();
        EXEC SQL EXECUTE
          begin
            dbms_pipe.pack_message('done');
            dbms_pipe.pack_message(:status);
            :status := dbms_pipe.send_message(:return_name);
          end;
        END-EXEC;

        if (status) {
          printf("Daemon error while responding to sql command.");
          printf("  status: %d\n", status);
          }
        }

      else {
        /* Invalid daemon command received. */
        printf("Daemon error: invalid command '%s' received.\n", command.arr);
        }
      }
    else {
      /* We get here if an error was received while the daemon was waiting.
         If the status = 1, this is a timeout and is probably not a problem.
         However, the default timeout for the receive_message function is
         1000 days, so unless the daemon is kept running for over 3 years
         without receiving a signal, you won't time out. */
      printf("Daemon error while waiting for signal.");
      printf("  status = %d\n", status);
      }
  }

  EXEC SQL commit work release;
}


0
 

Author Comment

by:syamsundar
ID: 8000607
thank you schwertner
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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 how to recover a database from a user managed backup

746 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

11 Experts available now in Live!

Get 1:1 Help Now