Have Any one used dbms_pipe.Help required.!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Have Any one used dbms_pipe.Help required.!!!

  1. #1
    Join Date
    Aug 2000
    Posts
    87
    Hai All,
    I am trying to Create a setup in which to send system commands.So i created packages for Daemon and the package is created successfully.while compiling this daemon.pc C program i am facing errors like DBMS_PIPE.PACK_MESSAGE must be declared etc...which i am not able to debug it.I here with sending the code of it. I am compiling this daemon.pc as proc sqlcheck=semantics daemon.pc.



    /************************************************************************
    File: daemon.sql

    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

    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;
    }


  2. #2
    Join Date
    Dec 1999
    Location
    Alpharetta, GA, US
    Posts
    192
    What schema you were using to compile this *.pc file.
    I think you have to run the catlog, catproc etc. scripts under this schema, befor you complie this *.pc

    I think this will help you


    Chan
    OCP7.3/8.0/8i/9i
    Sun Certified Sys. Admin

  3. #3
    Join Date
    Aug 2000
    Posts
    87
    Hello,
    Its under sys Schema.

    Thanks ,
    Vinod Kumar.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width