Results 1 to 5 of 5

Thread: Dbms_pipe?

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Dbms_pipe?

    I was trying to play with some code which let's you run OS commands(How to run a unix host command from oracle plsql package?), but I'm not able to compile this sample code:

    Code:
    SQL> CREATE OR REPLACE FUNCTION forbesc.host_command( cmd IN VARCHAR2 )
      2    RETURN INTEGER IS
      3      STATUS   NUMBER;
      4      errormsg VARCHAR2(80);
      5      pipe_name VARCHAR2(30);
      6  BEGIN
      7      pipe_name := 'HOST_PIPE';
      8      dbms_pipe.pack_message( cmd );
      9      STATUS := dbms_pipe.send_message(pipe_name);
     10      RETURN STATUS;
     11  END;
     12  /
    
    Warning: Function created with compilation errors.
    
    SQL> show errors
    Errors for FUNCTION FORBESC.HOST_COMMAND:
    
    LINE/COL ERROR
    -------- ---------------------------------------------------------------
    8/5      PL/SQL: Statement ignored
    8/5      PLS-00201: identifier 'DBMS_PIPE' must be declared
    9/5      PL/SQL: Statement ignored
    9/15     PLS-00201: identifier 'DBMS_PIPE' must be declared

    But the DBMS_PIPE package is there:

    Code:
    SQL> desc dbms_pipe
    FUNCTION CREATE_PIPE RETURNS NUMBER(38)
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     PIPENAME                       VARCHAR2                IN
     MAXPIPESIZE                    NUMBER(38)              IN     DEFAULT
     PRIVATE                        BOOLEAN                 IN     DEFAULT
    FUNCTION NEXT_ITEM_TYPE RETURNS NUMBER(38)
    PROCEDURE PACK_MESSAGE
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     ITEM                           VARCHAR2                IN
    ...
    What am I missing? I am in the DBA role ...

    Thanks,
    --=Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What am I missing?
    OWNER


    SYS.DBMS_PIPE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    There's a public synonym for the package (which is why I think the DESC DBMS_PIPE) worked:

    Code:
    SQL> CREATE OR REPLACE FUNCTION forbesc.host_command( cmd IN VARCHAR2 )
      2    RETURN INTEGER IS
      3      STATUS   NUMBER;
      4      errormsg VARCHAR2(80);
      5      pipe_name VARCHAR2(30);
      6  BEGIN
      7      pipe_name := 'HOST_PIPE';
      8      sys.dbms_pipe.pack_message( cmd );
      9      STATUS := sys.dbms_pipe.send_message(pipe_name);
     10      RETURN STATUS;
     11  END;
     12  /
    
    Warning: Function created with compilation errors.
    
    SQL> show errors
    Errors for FUNCTION FORBESC.HOST_COMMAND:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    8/5      PL/SQL: Statement ignored
    8/5      PLS-00201: identifier 'SYS.DBMS_PIPE' must be declared
    9/5      PL/SQL: Statement ignored
    9/15     PLS-00201: identifier 'SYS.DBMS_PIPE' must be declared
    SQL>
    And like I mentioned, my account is in the DBA group, so I don't think it's a privileges issue.
    --=cf

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    privileges acquired via ROLE do NOT apply within named PL/SQL code.

    GRANT EXECUTE ON SYS.DBMS_PIPE TO <your_user>;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    That comes back to burn me about once a year.

    Thanks,
    --=Chuck

Posting Permissions

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