Is it possible to use Java
Dilip, January 28, 2002 - 11:48 am UTC
DB: 8.1.7.0.0 on UNIX
Would it be possible to use a Java stored procedure (including pl/sql wrapper) to get the value of an environment variable from within a PL/SQL block ?
I'm thinking along the lines of the Java code that Tom gave for running o/s commands from PL/SQL.
January 28, 2002 - 12:26 pm UTC
If you can write the Java to get it -- yes. However, the same exact caveat spelled out in my question:
..
The environment you set up is perhaps visible to your SQLPLUS running on YOUR
client however the plsql block is being executed by a dedicated server/shared
server running in a totally different process space, typically on a different
machine and DEFINITELY with a totally different environment.
....
would apply. The java is running on the SERVER - not on the client. Hence, it would have access to the SERVERS environment -- it would be a totally different environment.
User defined "serveroutput" like variable
Maverick, November 15, 2006 - 10:05 am UTC
Tom, I am trying to define a variable [like serveroutput] so that if it's set on, then my application is going to log errors in table , else it's not.
eg: "set Logoutput on"
If this is set ,then i can my procedures are going to log in Error Log table otherwise, they won't.
Can I create something like that? I am using Oracle 10g.
November 16, 2006 - 3:02 am UTC
create or replace package my_globals
as
g_log_stuff boolean default false;
end;
/
then your applications would:
begin my_globals.g_log_stuff := true; end;
and your log code would:
if ( my_globals.g_log_stuff )
then
log it
end if;
Serveroutput
Maverick, November 16, 2006 - 4:40 pm UTC
Thanks Tom. That was very helpful. But I am interested in knowing logic behind serveroutput,define etc..Variables.
Can you throw some light on that?
November 16, 2006 - 4:50 pm UTC
define is a sqlplus thing, sqlplus is a scripting/command line environment, it responds to "define"
nothing else does, define is just something sqlplus does, sort of like:
export X=something
echo $
in shell,
define X=something
select '&X' from dual;
in sqlplus.
set serverout on is shorthand for:
exec dbms_output.enable
it just sets a global variable in a package.
Shorthand??
Maverick, November 16, 2006 - 6:12 pm UTC
"
set serverout on is shorthand for:
exec dbms_output.enable
"
Tom, So, How can one create shorthands like this?
I am using package you mentioned above [and added couple of procedures ->Set and Reset] and I can call
exec mypkg.set , but How to create shorthand for that?
Just curious ..
November 17, 2006 - 3:14 am UTC
you would write your own sqlplus.
"set" is a sqlplus command, that is all.
Grabbing DOS Variable in SQLPlus
A reader, March 11, 2020 - 8:43 pm UTC
SQL> host echo define os_string=%OS% > dumb.sql
SQL> @dump
SQL> define os_string
DEFINE OS_STRING = "Windows_NT" (CHAR)
SQL>
March 12, 2020 - 10:39 am UTC
Nice! Thanks for sharing.
How to pass OS variables in .SQL file?
Maulik, March 10, 2021 - 9:03 am UTC
Hi can set below parameters in .sql file?
export NLS_LANG=AMERICAN_AMERICA.UTF8
export ORA_NCHAR_LITERAL_REPLACE=true
export LC_CTYPE="en_US.utf8"
March 10, 2021 - 9:11 am UTC
Those are environment variables, you don't pass them to your SQL file; you set them in the script that calls this file.
If you need to reference these values in the script for some reason, you need to pass them as parameters as shown in the original answer.