Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, KK.

Asked: August 04, 2000 - 11:54 pm UTC

Last updated: October 14, 2008 - 5:55 pm UTC

Version: 4.0.8

Viewed 10K+ times! This question is

You Asked

Can I use the procedure init_cgi_env in the package OWA?

and Tom said...

Yes you can -- I do it sometimes when I want to run a web procedure from within SQLPLus and it requires some environment variables to be set...

Note that if you call this -- you must supply the environnemt -- anything you supply will overwrite the existing environment.

Here is an example:

declare
nm owa.vc_arr;
vl owa.vc_arr;
begin
nm(1) := 'REMOTE_ADDR';
vl(1) := '1.2.3.4';
owa.init_cgi_env( nm.count, nm, vl );
end;
/

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Now the mystery is solved...

Andrew, March 05, 2002 - 7:38 pm UTC

Tom 

Thanks for this most useful tip. Now I've solved the mystery of the ORA-06502. I wish I understood why NULL or an exception like "No cgi_env environment detected" wasn't implemented when owa_util.get_cgi_env was coded.

SQL> set serveroutput on
SQL> begin
  2  DBMS_OUTPUT.PUT_LINE ( owa_util.get_cgi_env( 'REMOTE_ADDR' )); 
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 315
ORA-06512: at line 2


SQL> 
SQL> declare
  2         nm  owa.vc_arr;
  3         vl  owa.vc_arr;
  4  begin
  5         nm(1) := 'REMOTE_ADDR';
  6         vl(1) := '1.2.3.4';
  7         owa.init_cgi_env( nm.count, nm, vl );
  8      DBMS_OUTPUT.PUT_LINE ( owa_util.get_cgi_env( 'REMOTE_ADDR' )); 
  9  end;
 10  /
1.2.3.4

PL/SQL procedure successfully completed.
 

Nice

F.Maer, January 01, 2004 - 12:57 pm UTC

Dear Tom,
Well and wish the same from you.Regarding documentation for
OWA_* PACKAGES,In OTN nothing exists as valid documents.The
Links are spurious and misleading and take lots of time to output a page.They never lead to any of the OWA_* Packages.Could you please look into this and provide some valid links?
Waiting for your reply.
Bye!

Tom Kyte
January 01, 2004 - 1:16 pm UTC

spurious?
misleading?

not sure I'm following what you mean there, what is spurious or misleading?

</code> http://docs.oracle.com/docs/cd/A97329_03/mix.902/q20102/toc.htm

specifically:

Oracle9i Application Server PL/SQL Web Toolkit Reference
http://docs.oracle.com/docs/cd/A97329_03/web.902/a90101/toc.htm

Oracle9i Application Server mod_plsql User's Guide
http://docs.oracle.com/docs/cd/A97329_03/web.902/a90855/toc.htm <code>

Proper way to detect running from web

Jack Wells, January 01, 2004 - 4:05 pm UTC

Tom,

What is the proper way to have a PL/SQL procedure detect that it is running from the web (i.e. being processed with mod_plsql) vs. being run from SQL*Plus, Forms, Reports, etc.?

I can trap for the 6502 error but it doesn't feel right to have production code depend on an erroneous error like this. Do you have a more elegant way to do the following?

DECLARE
FUNCTION called_from_web
RETURN BOOLEAN
IS
lv VARCHAR2 (100);
BEGIN
lv := owa_util.get_cgi_env ('server_name');
RETURN TRUE;
EXCEPTION
WHEN VALUE_ERROR THEN
RETURN FALSE;
END called_from_web;
BEGIN
IF called_from_web THEN
htp.p ('This procedure is being called from the web');
ELSE
DBMS_OUTPUT.put_line ('This procedure is not being called from the web');
END IF;
END;
/

This procedure is not being called from the web

PL/SQL procedure successfully completed.


Tom Kyte
January 01, 2004 - 5:32 pm UTC

that is what exceptions are all about?

begin
select x into y from t where a = b;
exception
when no_data_found then y := null;
end;


you'd have no worries with that right? same difference here. Your method is not 100% (there is no method that is really) since I can call owa_util.init myself and trick you into thinking I'm on the web (in fact, for testing, i often do just that)

Nice

Ram, January 02, 2004 - 12:08 am UTC

Dear Sir,
1)What does "owa" stand for?
2)Do you have a simple example for owa_util.calendarprint?
How to use this procedure?
Thanks in advance.
Regards!

Tom Kyte
January 02, 2004 - 9:19 am UTC

1) Oracle Web Agent

2) 
http://docs.oracle.com/docs/cd/A97329_03/web.902/a90101/psutil.htm#1005729

 
ops$tkyte@ORA9IR2> exec owa_util.calendarprint( 'select created, username, null from all_users order by 1' );
 
PL/SQL procedure successfully completed.


put that in a page and see what it does (makes a nice little calendar with the name of the user placed on their creation date) 

Proper way to...

Jack Wells, January 02, 2004 - 9:47 pm UTC

Tom,

<quote>you'd have no worries with that right?</quote>

Well, I guess the only worry I have is that the OWA_UTIL package might get updated in a future release and have a named exception or something put in which avoids the "value error" that is currently happening.

I'm trying to create VPD policy/predicate procedure that sets the context for the session. If the procedure is called from the web, I get the web environment and set the context based on the website that called it. I want this same procedure to work if called from SQL*Plus, Forms, etc. so I need to test from where it was called from first (to avoid calling the owa_util.get_cgi_env function).

Is my previous example (trapping the value error with an exception) "the best way" to accomplish this?


Tom Kyte
January 03, 2004 - 9:10 am UTC

as stated however -- very very very trivial to trick this out.

declare
nm owa.vc_arr;
vl owa.vc_arr;
begin
nm(1) := 'WEB_AUTHENT_PREFIX';
vl(1) := 'WEB$';
owa.init_cgi_env( nm.count, nm, vl );
end;
/


your forms developers or someone in sqlplus can set the environment to anything they feel like setting it to. sorry -- but you'll have to come up with plan B if you want this to be "secure" (which apparently you do since you are using VPD and all).....

OWA_UTIL.CALENDARPRINT Working

Dick Goulet, October 13, 2008 - 4:58 pm UTC

Tom,

I think the original question still was not answered. OWA_UTIL.Calendarprint just sends back a "pl/sql procedure completed". Where did the output go to so that one can send it to a browser?
Tom Kyte
October 14, 2008 - 5:55 pm UTC

it just uses htp.p? Just like anything else "mod_plsql" would. htp.p 'prints' to an array in memory and mod_plsql retrieves this array and sends it over tcp/ip using http to the browser.


the original question was not about calendarprint, so I'm very confused...

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library