5 Replies Latest reply: Apr 11, 2012 2:37 PM by WD RSS

    Ora DB 11g Express Communication capabilities

    WD
      Hello,

      I am working through, the dated, Oracle 10g Developer PL/SQL Programming book by Joan Casteel. I am trying to work through her examples for Oracle-supplied utilities and packages...specifically communications packages: UTL_HTTP Package, UTL_TCP Package, but it seems like these are not in Oracle 11g Express, is this true? And if so, are there equivalents that I should use to substitute? I looked through the included documentation that installed with Ora11g Express, but I cannot find any 'UTL_...' in the index. Also when I searched the Oracle Logo Database Express Edition Documentation online with UTL_HTTP or UTL_TCP it came back with 'no results found'. What documentation can I read that discusses this topic with the current version of Oracle DB?

      Thanks in advance,

      Warren
        • 1. Re: Ora DB 11g Express Communication capabilities
          P.Forstmann
          WD wrote:
          UTL_HTTP Package, UTL_TCP Package, but it seems like these are not in Oracle 11g Express, is this true?
          No:
          SQL> select owner, object_name, object_type, created
            2  from dba_objects
            3  where object_name in ('UTL_HTTP','UTL_TCP');
          
          OWNER                          OBJECT_NAME          OBJECT_TYPE         CREATED
          ------------------------------ -------------------- ------------------- --------
          SYS                            UTL_HTTP             PACKAGE BODY        27/08/11
          SYS                            UTL_TCP              PACKAGE BODY        27/08/11
          SYS                            UTL_HTTP             PACKAGE             27/08/11
          SYS                            UTL_TCP              PACKAGE             27/08/11
          PUBLIC                         UTL_HTTP             SYNONYM             27/08/11
          PUBLIC                         UTL_TCP              SYNONYM             27/08/11
          APEX_040000                    UTL_HTTP             SYNONYM             27/08/11
          
          7 rows selected.
          You need to be granted EXECUTE privilege on these packages to be able to use them.
          • 2. Re: Ora DB 11g Express Communication capabilities
            WD
            Thanks P. Forstmann,

            I ran the query and they are there. So, this is the procedure that was included with the .sql files for the book:
            SQL> CREATE OR REPLACE PROCEDURE read_http_sp
            2 (p_url VARCHAR2)
            3 IS
            4 lv_read_tbl utl_http.html_pieces;
            5 fh UTL_FILE.FILE_TYPE;
            6 BEGIN
            7 htp.print('<BASE HREF='||p_url||'>');
            8 lv_read_tbl := utl_http.request_pieces(p_url);
            9 fh := UTL_FILE.FOPEN('ORA_FILES','test_http.htm','w');
            10 FOR i IN 1..lv_read_tbl.COUNT LOOP
            11 UTL_FILE.PUT_LINE(fh, lv_read_tbl(i));
            12 END LOOP;
            13 UTL_FILE.FCLOSE(fh);
            14 END;
            15 /

            Warning: Procedure created with compilation errors.

            SQL> show errors
            Errors for PROCEDURE READ_HTTP_SP:

            LINE/COL ERROR
            -------- -----------------------------------------------------------------
            4/14 PL/SQL: Item ignored
            4/14 PLS-00201: identifier 'UTL_HTTP' must be declared
            5/5 PL/SQL: Item ignored
            5/5 PLS-00201: identifier 'UTL_FILE' must be declared
            8/2 PL/SQL: Statement ignored
            8/2 PLS-00320: the declaration of the type of this expression is
            incomplete or malformed

            9/2 PL/SQL: Statement ignored
            9/2 PLS-00320: the declaration of the type of this expression is
            incomplete or malformed

            LINE/COL ERROR
            -------- -----------------------------------------------------------------

            10/2 PL/SQL: Statement ignored
            10/14 PLS-00320: the declaration of the type of this expression is
            incomplete or malformed

            13/2 PL/SQL: Statement ignored
            13/18 PLS-00320: the declaration of the type of this expression is
            incomplete or malformed


            It compiles with a bunch of errors and it looks like UTL_HTTP and UTL_FILE need to be declared, but this makes no sense to me since they are Oracle-supplied. Also, I figure the author of the book knew how to write the procedure in the first place. I have never written a procedure like this so I am unfamiliar with a lot of the syntax. It does not absolutely have to run, I am just trying to understand from a learners stand point.

            Looking over the code...does the p_url parameter need to have a mode assigned 'IN', 'OUT', 'IN OUT'. That could be tripping it up at lines 8 and 9.

            Open to thoughts.

            -Warren
            • 3. Re: Ora DB 11g Express Communication capabilities
              P.Forstmann
              Try this with <user> = the Oracle account name that you are using to compile procedure read_http_sp:
              sqlplus / as sysdba
              grant execute on utl_http to <user>;
              grant execute on utl_file to <user>;
              • 4. Re: Ora DB 11g Express Communication capabilities
                WD
                I appreciate your persistence with this question...I am running Ora 11g Ex on my home computer so my user has dba privs as far as I know, but I logged on as SYSTEM and SYS, because I get errored out when I try the sqlplus / as sysdba...anyway, I granted the privs and this is what I get:

                SQL> grant execute on utl_http to spitspat;
                grant execute on utl_http to spitspat
                *
                ERROR at line 1:
                ORA-00942: table or view does not exist


                SQL> grant execute on utl_file to spitspat;
                grant execute on utl_file to spitspat
                *
                ERROR at line 1:
                ORA-00942: table or view does not exist

                Now under the same user I ran the query again and these are the results I get:

                SQL> run
                1 select owner, object_name, object_type, created
                2 from dba_objects
                3* where object_name in ('UTL_HTTP','UTL_TCP')
                rows will be truncated

                rows will be truncated


                OWNER OBJECT_NAME
                ------------------------------ ---------------------
                SYS UTL_TCP
                SYS UTL_HTTP
                SYS UTL_TCP
                SYS UTL_HTTP
                PUBLIC UTL_TCP
                PUBLIC UTL_HTTP
                APEX_040000 UTL_HTTP

                7 rows selected.

                So they are there, but for whatever reason I cannot get them to execute/function/compile properly...

                I was wondering if there was a step during install that I might have overlooked. Do you know of any specific elements during installing Ora DB 11g Express that might effect accessing specific utls?

                -Warren
                • 5. Re: Ora DB 11g Express Communication capabilities
                  orafad
                  Try the following:

                  (replace user test with your local user)
                  SQL> conn test
                  Enter password:
                  Connected.
                  SQL>
                  SQL> desc utl_http
                  ERROR:
                  ORA-04043: object "SYS"."UTL_HTTP" does not exist
                  
                  
                  SQL> conn / as sysdba
                  Connected.
                  SQL>
                  SQL> grant execute on sys.utl_http to test;
                  
                  Grant succeeded.
                  
                  SQL> conn test
                  Enter password:
                  Connected.
                  SQL> desc utl_http
                  PROCEDURE ADD_COOKIES
                   Argument Name                  Type
                   ------------------------------ -----------------
                   COOKIES                        TABLE OF RECORD
                   REQUEST_CONTEXT                BINARY_INTEGER
                  FUNCTION BEGIN_REQUEST RETURNS RECORD
                  ...