Categories
ApEx Oracle PL/SQL

Getting Session Environment Parameters in Oracle

To display all environment variable values for a given oracle session, run the following code:

begin
FOR i IN 1..owa.num_cgi_vars LOOP
htp.p(owa.cgi_var_name(i)||' : '||owa.cgi_var_val(i));
END LOOP;
end;

The name of the environment variable and value will be displayed. To reference each variable individually use the following syntax:

OWA_UTIL.get_cgi_env(VARIABLE_NAME)

eg. OWA_UTIL.get_cgi_env(‘REQUEST_PROTOCOL’)

Here’s the list of all available variables:

PLSQL_GATEWAY
GATEWAY_IVERSION
SERVER_SOFTWARE
GATEWAY_INTERFACE
SERVER_PORT
SERVER_NAME
REQUEST_METHOD
PATH_INFO
SCRIPT_NAME
REMOTE_ADDR
SERVER_PROTOCOL
REQUEST_PROTOCOL
REMOTE_USER
HTTP_CONTENT_LENGTH
HTTP_CONTENT_TYPE
HTTP_USER_AGENT
HTTP_HOST
HTTP_ACCEPT
HTTP_ACCEPT_ENCODING
HTTP_ACCEPT_LANGUAGE
HTTP_REFERER
HTTP_ORACLE_ECID
WEB_AUTHENT_PREFIX
DAD_NAME
DOC_ACCESS_PATH
DOCUMENT_TABLE
PATH_ALIAS
REQUEST_CHARSET
REQUEST_IANA_CHARSET
SCRIPT_PREFIX
HTTP_COOKIE

Sample Output:

PLSQL_GATEWAY : WebDb
GATEWAY_IVERSION : 3
SERVER_SOFTWARE : Oracle-Application-Server-10g/10.1.2.0.0 Oracle-HTTP-Server
GATEWAY_INTERFACE : CGI/1.1
SERVER_PORT : 7780
SERVER_NAME : dummyserver
REQUEST_METHOD : POST
PATH_INFO : /wwv_flow.show
SCRIPT_NAME : /pls/dmt
REMOTE_ADDR : 148.132.152.59
SERVER_PROTOCOL : HTTP/1.1
REQUEST_PROTOCOL : HTTP
REMOTE_USER : APEX_PUBLIC_USER
HTTP_CONTENT_LENGTH : 416
HTTP_CONTENT_TYPE : application/x-www-form-urlencoded
HTTP_USER_AGENT : Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)
HTTP_HOST : dummyserver.com.au:7780
HTTP_ACCEPT : */*
HTTP_ACCEPT_ENCODING : gzip, deflate
HTTP_ACCEPT_LANGUAGE : en-au
HTTP_REFERER : http://dummyserver.com.au:7780/pls/dmt/f?p=4500:1003:4479870342920497::NO:::
HTTP_ORACLE_ECID : 1297402712:144.135.172.44:22243:0:21186,0
WEB_AUTHENT_PREFIX :
DAD_NAME : dmt
DOC_ACCESS_PATH : docs
DOCUMENT_TABLE : wwv_flow_file_objects$
PATH_ALIAS :
REQUEST_CHARSET : UTF8
REQUEST_IANA_CHARSET : UTF-8
SCRIPT_PREFIX : /pls
HTTP_COOKIE : ISCOOKIE=true; LOGIN_USERNAME_COOKIE=U123456; LOGIN_PASSWORD_COOKIE=; LOGIN_REMEMBER_ME_COOKIE=; r1=%23ALL; r2=%23ALL; r3=%23ALL; propHide=4301%2C%23ALL; WWV_FLOW_USER2=47770A7FD83D3E0A; ORACLE_PLATFORM_REMEMBER_UN=U123456:dmt; __ppFullPath=di-di; s_nr=1296902947468; s_evar18=%5B%5B%27referral%27%2C%271296902515781%27%5D%5D; SearchSettings=50,0,0,1,1,1,1,1; HTMLDB_IDLE_SESSION=11-FEB-2011 10:22:00; WWV_CUSTOM-F_955908081711180_100=739B959ED77BAFE2

Categories
ApEx Oracle

Oracle ApEx Request That Will Submit Requested Page Though Link

There is a very useful Oracle ApEx REQUEST constant called BRANCH_TO_PAGE_ACCEPT. If included in a URL, the request will process the page as if it was submitted. Now think about this for a minute because this is a useful tool that can enhance your application dramatically. I will provide an example to make my point. Traditionally clicking on a URL in ApEx will simply load the page requested. Loading a page means that the “After Submit Processes” region in ApEx will never be triggered. As a consequence most programmers will plug their process in in “Before Header” or “After Header” point in their page process restricted via some execution condition. This is highly inefficient because it means that the process will be evaluated every time the page is accessed.

To improve this process, add a BRANCH_TO_PAGE_ACCEPT Request in your Column Link. This modification will submit the page requested which will then allow you to place the process in the “After Submit” region. Execution of the block will be more targetted making you application more efficient.
BRANCH_TO_PAGE_ACCEPT
The request looks something like this:

f?p=&APP_ID.:23:&SESSION.:BRANCH_TO_PAGE_ACCEPT:&DEBUG.::P0_REQ_ID:#ID#

Happy Coding!

Categories
BLOB Load File Into BLOB Oracle PL/SQL

Loading a File Into a BLOB Object in Oracle

Loading a file into a BLOB object in Oracle is easy. All we need to do is use the DBMS_LOB package to load the file into a temporary LOB object. This is demonstrated in the following example.

This example assumes that you have a directory object set up where the files are stored. In this example the following object is used:

CREATE DIRECTORY FILEUPLOADS AS '/home_new/dmt/public_html/fileuploads';

Use the following function to load a file based on the filename parameter and return the BLOB reference for processing:


CREATE OR REPLACE FUNCTION loadBlobFromFile(p_file_name VARCHAR2) RETURN BLOB AS
  dest_loc  BLOB := empty_blob();
  src_loc   BFILE := BFILENAME('FILEUPLOADS', p_file_name);
BEGIN
  -- Open source binary file from OS
  DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
  
  -- Create temporary LOB object
  DBMS_LOB.CREATETEMPORARY(
        lob_loc => dest_loc
      , cache   => true
      , dur     => dbms_lob.session
  );
    
  -- Open temporary lob
  DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
  
  -- Load binary file into temporary LOB
  DBMS_LOB.LOADFROMFILE(
        dest_lob => dest_loc
      , src_lob  => src_loc
      , amount   => DBMS_LOB.getLength(src_loc));
  
  -- Close lob objects
  DBMS_LOB.CLOSE(dest_loc);
  DBMS_LOB.CLOSE(src_loc);
  
  -- Return temporary LOB object
  RETURN dest_loc;
END loadBlobFromFile;
/

Easy as that.

Categories
CHAR Datatype Oracle VARCHAR2

Oracle VARCHAR2(1) vs CHAR(1)?

Well, minimum size for both Oracle datatypes is 1 byte. So in terms of storage, they’ll both consume either null or 1 byte in storage space. So, when declaring a character datatype with a length of 1, it doesn’t really matter which one you use. The rules change when the length is greater than 1 though.

This is because CHAR is a fixed character data, so values held in this datatype is always RPAD’ed with blanks. Storing character data as VARCHAR2 will save space on, not just the character field, but any indexes that reference it.

As a personal preference, I always use VARCHAR2. The most important practice though is to be consistent. For example, if you use CHAR for a 1 byte character datatype and VARCHAR2 for everything else, then stick with it throughout your whole database.

Categories
ApEx Bug Oracle

“Typo” in Oracle ApEx Manage Services Page

Could a developer in Oracle please fix the “typo” in the Manage Services page in Oracle Application Express. I’ve been looking at the error for some time now and was hoping that it will be fixed on the latest ApEx version. Well, I’m looking at version 3.2 and it’s still there. I’m sick of looking at it now so can someone in Oracle please fix it.

The error can be found under Home -> Manage Services. Under the Workspace region, there’s an item that spells “Request Service Terminiation”… I think it should say “Request Service Termination”.

Request Service Terminiation

If I am wrong, I apologize. If I’m right, please fix it because I hold such high regard for Oracle and I don’t want to see them reduced to “human” status by appearing to make such human-like mistakes.

Thank you Oracle.

Categories
ApEx ASP Oracle

Convert Oracle ApEx 3.1 Package Export to 3.0

Here’s a useful ASP page which I wrote which will convert an Oracle Application Express 3.1 package export (or page export) to 3.0 automatically! Just in case you developed an application in 3.1, but need to install it in a 3.0 environment.

http://www.web-backlinks.com/convert-oracle-apex3.1-package-to-3.0.asp

Enjoy!