Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Invoking PL/SQL through the embedded gateway (11.2.0.3)
Invoking PL/SQL through the embedded gateway [message #582570] Fri, 19 April 2013 08:45 Go to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I'm trying to run pl/sql through the embedded pl/sql gateway. I think that this should work:
exec dbms_xdb.sethttpport(8181)
exec dbms_epg.create_dad('SCOTT_DAD','/scott/*')
exec dbms_epg.authorize_dad('SCOTT_DAD','SCOTT')

create or replace procedure hello_world as begin
htp.print('Hello world');
end;
/
and this URL
http://127.0.0.1:8181/scott/hello_world
should then generate a login prompt and run the procedure. But nothing comes back to the browser, it hangs waiting for a reply. I'm sure the DAD is configured OK, this is the result of the status check script:
orcl>
orcl> @?/rdbms/admin/epgstat
+--------------------------------------+
| XDB protocol ports:                  |
|  XDB is listening for the protocol   |
|  when the protocol port is non-zero. |
+--------------------------------------+

HTTP Port FTP Port
--------- --------
     8181        0

1 row selected.

+---------------------------+
| DAD virtual-path mappings |
+---------------------------+

Virtual Path                     DAD Name
-------------------------------- --------------------------------
/apex/*                          APEX
/scott/*                         SCOTT_DAD

2 rows selected.

+----------------+
| DAD attributes |
+----------------+

DAD Name     DAD Param                DAD Value
------------ ------------------------ -----------------------------
APEX         database-username        ANONYMOUS
             default-page             apex
             document-table-name      wwv_flow_file_objects$
             request-validation-funct wwv_flow_epg_include_modules.
             ion

             document-procedure       wwv_flow_file_mgr.process_dow
             nls-language             american_america.al32utf8
             document-path            docs

7 rows selected.

+---------------------------------------------------+
| DAD authorization:                                |
|  To use static authentication of a user in a DAD, |
|  the DAD must be authorized for the user.         |
+---------------------------------------------------+

DAD Name                         User Name
-------------------------------- --------------------------------
SCOTT_DAD                        SCOTT

1 row selected.

+----------------------------+
| DAD authentication schemes |
+----------------------------+

DAD Name             User Name                        Auth Scheme
-------------------- -------------------------------- -------------
APEX                 ANONYMOUS                        Anonymous
SCOTT_DAD                                             Dynamic

2 rows selected.

+--------------------------------------------------------+
| ANONYMOUS user status:                                 |
|  To use static or anonymous authentication in any DAD, |
|  the ANONYMOUS account must be unlocked.               |
+--------------------------------------------------------+

Database User   Status
--------------- --------------------
ANONYMOUS       EXPIRED & LOCKED

1 row selected.

+------------------------------------------------------------------
| ANONYMOUS access to XDB repository:
|  To allow public access to XDB repository without authentication,
|  ANONYMOUS access to the repository must be allowed.
+------------------------------------------------------------------

Allow repository anonymous access?
----------------------------------
false

1 row selected.

orcl>
and I'm definitely hitting the listener and the dispatcher, you can see there is a current session here:
c:\users\john\home>lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.2.0.3.0 - Production on 19-APR-2013 14:30:15

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jwdell.bplc.co.za)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:19 refused:0 current:1 max:1022 state:ready
         DISPATCHER <machine: JWDELL, pid: 4796>
         (ADDRESS=(PROTOCOL=tcp)(HOST=jwdell.bplc.co.za)(PORT=55625))
The command completed successfully

c:\users\john\home>
any ideas why I get nothing coming back to the browser? I've tried Firefox, IE, and wget. They all waiting for a response. It is the same if I pass the credentials in the URL,
http://scott:tiger@127.0.0.1:8181/scott/hello_world
Have I missed out a step, perhaps?

Thank you for any insight.

Re: Invoking PL/SQL through the embedded gateway [message #582583 is a reply to message #582570] Fri, 19 April 2013 10:27 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Problem solved (or at least, identified). The dispatcher trace was full of messages such as this,
*** 2013-04-19 16:17:14.505
  NS Primary Error: TNS-12535: TNS:operation timed out
  NS Secondary Error: TNS-12606: TNS: Application timeout occurred
kmduicxd: 0F050020, kmduiflg: 41, circuit: 00000000
  (circuit) not initialized.
Failed to get Client Address:
  NS Primary Error: TNS-12533: TNS:illegal ADDRESS parameters
  NS Secondary Error: TNS-12560: TNS:protocol adapter error
  NT Generic Error: TNS-00503: Illegal ADDRESS parameters
there was nothing wrong with any address parameters, but I did have a mismatch between name resolution in my DNS and in my hosts file, and it seems that was the problem.
Previous Topic: Conditional Button Display
Next Topic: The embedded pl/sql gateway versus Apex listener deployed to Glassfish
Goto Forum:
  


Current Time: Fri Mar 29 05:36:58 CDT 2024