A. owa_util.showpage to display HTML output in SQL*Plus
1. Make sure PL/SQL Web Toolkit is installed in the database.
SQL> select owa_util.get_version from dual;
GET_VERSION
---------------------------------
10.1.2.1.9
SQL> exec owa_util.showpage;
BEGIN owa_util.showpage; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 354
ORA-06512: at "SYS.HTP", line 665
ORA-06512: at "SYS.HTP", line 758
ORA-06512: at "SYS.HTP", line 1165
ORA-06512: at "SYS.OWA_UTIL", line 345
ORA-06512: at line 1
2. After variable initialization, it shall work
SQL> DECLARE
name_arr OWA.VC_ARR;
value_arr OWA.VC_ARR;
BEGIN
OWA.INIT_CGI_ENV(0, NAME_ARR, VALUE_ARR);
END;
/
PL/SQL procedure successfully completed.
SQL> declare
begin
htp.init;
htp.htmlOpen;
htp.headOpen;
htp.title('Hello ');
htp.headClose;
htp.bodyOpen;
htp.print('Hello World');
htp.bodyClose;
htp.htmlClose;
end;
/
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec owa_util.showpage;
Content-type: text/html
Content-length: 79
<html>
<head>
<title>Hello </title>
</head>
<body>
Hello World
</body>
</html>
PL/SQL procedure successfully completed.
B. Display P/L SQL package in browsers
1. Configure Apache dads.conf file on the server (e.g. for Grid Control) by adding below lines to it (Reference: How to Obtain the SSO Username for a PL/SQL Application Doc ID: 269688.1). For EBS, the DAD file is at $IAS_ORACLE_HOME/Apache/modplsql/conf (See part C below).
<Location /pls/dba>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername dbadmin
PlsqlDatabasePassword PSWD
PlsqlDatabaseConnectString dbServerName:1522:dbname
PlsqlAuthenticationMode Basic
PlsqlDefaultPage dbadmin.home
PlsqlDocumentTablename dbadmin.wwdoc_document
PlsqlDocumentPath docs
PlsqlDocumentProcedure dbadmin.wwdoc_process.process_download
</Location>
$ sqlplus system/PWD@dbname
SQL> create user dbadmin identified by PSWD
default tablespace users
temporary tablespace temp
quota unlimited on users;
SQL> grant resource to dbadmin;
SQL> grant create session to dbadmin;
$ sqlplus dbadmin/PSWD@dbname
SQL> CREATE or REPLACE procedure helloworld is BEGIN
owa.num_cgi_vars := 0;
htp.init;
htp.htmlOpen;
htp.headOpen;
htp.title('Hello ');
htp.headClose;
htp.bodyOpen;
htp.print('Hello World');
htp.bodyClose;
htp.htmlClose;
end;
/
2. Test the simple page:
http://gridcontrolserver:7777/pls/dba/dbadmin.helloworld
Notes: if password PSWD is not in the line " PlsqlDatabasePassword " of the dads.conf file, the page will ask for a login password.
3. For more complex package, use synonym to hide the schema name.
Assume a more complicated package is created in the database
SQL> CREATE OR REPLACE PACKAGE MSSQL AS
procedure home (modify varchar2 DEFAULT null);
... ... ...
SQL> show user
USER is "SYSTEM"
SQL> create role custom_pack NOT IDENTIFIED;
SQL> grant custom_pack to John;
SQL> grant custom_pack to Ray;
-- create synonym
SQL> create public synonym mssql for dbateam.mssql;
Synonym created.
SQL> conn dbateam/PSWD@dbname
Connected.
SQL> grant execute on mssql to custom_pack;
Now, http://gridcontrolserver:7777/pls/dba/mssql.home
works with John and Ray's database credentials.
C. Run modplsql with Oracle EBS 11i & R12.1 Apache (Notes: mod_plsql is not supported in R12. See Doc ID 726711.1)
1. add one line (with real path) to $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/oracle_apache.conf
include $IAS_ORACLE_HOME/Apache/modplsql/conf/plsql.conf
Notes on EBS R12.2.10 in 2021: I did try below lines in R12.2.10, even MOD_PLSQL is not supported in R12 by default.
a). $ cd $FMW_HOME/webtier/instances/EBS_web_OHS1/config/OHS/EBS_web
and add one line to file oracle_apache.conf
include ${ORACLE_INSTANCE}/config/${COMPONENT_TYPE}/${COMPONENT_NAME}/plsql.conf
b). Copy plsql.conf to here:
$ cp -p $ORACLE_HOME/Apache/modplsql/conf/plsql.conf .
It shall have lines like
# Configure Oracle HTTP Server to load mod_plsql
LoadModule plsql_module $ORACLE_HOME/Apache/modplsql/bin/modplsql.so
c). The problem is now "adapcctl.sh start" will not start Apache service and give status 204. Log $FMW_HOME/webtier/instances/EBS_web_OHS1/diagnostics/logs/OHS/EBS_web/console~OHS~1.log has error:
Cannot load $ORACLE_HOME/Apache/modplsql/bin/modplsql.so into server: $ORACLE_HOME/Apache/modplsql/bin/modplsql.so: wrong ELF class: ELFCLASS32
See Doc ID 1492343.1 (Error occurs because the software has been compiled using 32 Bit Java and you are using 64 Bit Java in your environment) for Wrong ELF Class ELFCLASS32. R12.2 file system has only one modplsql.so file, and it is identical to the one in R12.1 (which may be copied over to R12.2 by the upgrade process). Seems it does not work with 64Bit JDK. The question for a workaround becomes: does modplsql.so have a version for 64Bit Java?
2. add lines to $IAS_ORACLE_HOME/Apache/modplsql/conf/dads.conf
<location /pls/EBSTRAIN>
SetHandler pls_handler
Order allow,deny
Allow from All
AllowOverride None
PlsqlDatabaseUsername apps
PlsqlDatabasePassword appsPWD
plsqlDatabaseConnectString dbServerName.domain.com:1526:dbname
PlsqlAuthenticationMode Basic
PlsqlDefaultPage fnd_web.ping
</Location>
3. bounce Apache by adapcctl.sh! Then, below EBS built-in page shall work (assume 4453 is the ssl listener port).
https://EBSsiteName.domain.com:4453/pls/EBSTRAIN/fnd_web.ping
NOTES: To troubleshoot / trace mod plsql errors (specially HTTP-403 or 403 Forbidden error), turn "PlsqlLogEnable On" in file $IAS_ORACLE_HOME/Apache/modplsql/conf/plsql.conf.
4. test a custom page
$ sqlplus apps/appsPWD
SQL> show user
USER is "APPS"
SQL> CREATE or REPLACE procedure jy_helloworld is
begin
owa.num_cgi_vars := 0;
htp.init;
htp.htmlOpen;
htp.headOpen;
htp.title('Hello ');
htp.headClose;
htp.bodyOpen;
htp.print('Hello World');
htp.bodyClose;
htp.htmlClose;
end;
/
https://EBSsiteName.domain.com:4453/pls/EBSTRAIN/jy_helloworld
shall work and display "Hello World"
In addition, $FND_TOP/admin/template/httpd_conf_1013.tmp, which is used to generate $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf, can define a custom URL for an OS directory path.
5. To register your own PLSQL procedures or packages, you can go to System Admin Responsibility
Security > "Web PL/SQL" (it will also add a row to table fnd_enabled_plsql).
(Not sure exactly what this does)
NOTES: To troubleshoot / trace mod plsql errors (specially HTTP-403 or 403 Forbidden error), turn "PlsqlLogEnable On" in file $IAS_ORACLE_HOME/Apache/modplsql/conf/plsql.conf.
4. test a custom page
$ sqlplus apps/appsPWD
SQL> show user
USER is "APPS"
SQL> CREATE or REPLACE procedure jy_helloworld is
begin
owa.num_cgi_vars := 0;
htp.init;
htp.htmlOpen;
htp.headOpen;
htp.title('Hello ');
htp.headClose;
htp.bodyOpen;
htp.print('Hello World');
htp.bodyClose;
htp.htmlClose;
end;
/
https://EBSsiteName.domain.com:4453/pls/EBSTRAIN/jy_helloworld
shall work and display "Hello World"
In addition, $FND_TOP/admin/template/httpd_conf_1013.tmp, which is used to generate $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf, can define a custom URL for an OS directory path.
5. To register your own PLSQL procedures or packages, you can go to System Admin Responsibility
Security > "Web PL/SQL" (it will also add a row to table fnd_enabled_plsql).
(Not sure exactly what this does)
No comments:
Post a Comment