APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Error message after PL/SQL page process

859740May 5 2011 — edited May 5 2011

Hi,

I've a function in a package which returns a BOOLEAN. I want to use it in a PL/SQL page process like this:

begin
if not my_package.my_function(my_variable) then
null; -- message
end if;
end;

I would like to use the result of my function to display one of the pre-defined messages: "Process success message" or the "Process Error Message".

To solve this, I think I need to assign a value to a global variable which tells APEX what the result of executing my "Process Source" was. Right?

At first I tried this with exceptions in the function, and an exception in de "process source". But after raising these exception the "process error message" also appears. But I would like to use (only) the "build-in"-messages..... Easier to translate...

Thanks.

thatJeffSmith-Oracle

/nolog - there's no connection, so how could module be set?

On connect, SQLcl is set as module for the session.

Kris Rice-Oracle

I'm confused why you run sqlplus at all anymore

Craig Elliott

For myself, I am using SQLcl, but sometimes I have to use SQL*Plus to connect using external authentication(RADIUS), since I have not figured out how to get SQLcl to work with RADIUS. Maybe once InstantClient 12 is released this will work?

Macbook ENV vars used:

SQLPATH=/oracle/common_sql

SQLCL=/oracle/instantclient_11_2/sqlcl/bin

TNS_ADMIN=/oracle/instantclient_11_2/network/admin

ORACLE_HOME=/oracle/instantclient_11_2

JAVA_HOME=/Library/Java/JavaVirtualMachines/jdk1.8.0_45.jdk/Contents/Home

In my $SQLPATH/login.sql file, I have:

--********************

-- COMMON CALLS

-- Add any SQL*Plus commands here that are to be executed when a

-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command

-- Used by Trusted Oracle

COLUMN ROWLABEL FORMAT A15

-- Used for the SHOW ERRORS command

COLUMN LINE/COL FORMAT A8

COLUMN ERROR FORMAT A65 WORD_WRAPPED

-- Used for the SHOW SGA command

COLUMN name_col_plus_show_sga FORMAT a24

COLUMN units_col_plus_show_sga FORMAT a15

-- Defaults for SHOW PARAMETERS

COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME

COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for SHOW RECYCLEBIN

COLUMN origname_plus_show_recyc FORMAT a16 HEADING 'ORIGINAL NAME'

COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'

COLUMN objtype_plus_show_recyc FORMAT a12 HEADING 'OBJECT TYPE'

COLUMN droptime_plus_show_recyc FORMAT a19 HEADING 'DROP TIME'

-- Defaults for SET AUTOTRACE EXPLAIN report

-- These column definitions are only used when SQL*Plus

-- is connected to Oracle 9.2 or earlier.

COLUMN id_plus_exp FORMAT 990 HEADING i

COLUMN parent_id_plus_exp FORMAT 990 HEADING p

COLUMN plan_plus_exp FORMAT a60

COLUMN object_node_plus_exp FORMAT a8

COLUMN other_tag_plus_exp FORMAT a29

COLUMN other_plus_exp FORMAT a44

-- Default for XQUERY

COLUMN result_plus_xquery HEADING 'Result Sequence'

--********************

--CUSTOM from here

column bytes format 999,999,999,999

column total_bytes format 999,999,999,999

col segment_name format a30

col table_name format a30

col segment_type format a20

col tablespace_name format a20

col file_name format a60

col db_link format a40

col host format a30

--********************

--SQL*Plus OR SQLcl determination from here

SET VERIFY OFF DEFINE ON ECHO OFF

COL SQLFILE FORMAT a200

COL SQLFILE NEW_VALUE sqllogin;

select decode (sys_context ('USERENV', 'MODULE'), 'SQLcl', 'sqlcl_login.sql', 'sqlplus_login.sql') sqlfile from dual

@/oracle/common_sql/&sqllogin

--********************

-- COMMON CALLS

-- spool file and prompts from here

COL SPOOLFILE FORMAT a200

COL SPOOLFILE NEW_VALUE SPOOL;

COL SYSTIMESTAMP FORMAT a38

SELECT '/oracle/spoolfiles/'||USER||'_'||GLOBAL_NAME||'_'||TO_CHAR(SYSDATE,'DD_MON_YYYY_HH24MISS')||'.lst' spoolfile FROM GLOBAL_NAME;

spool &spool

PROMPT ---*************************-------------------

PROMPT -- Note: SQLBLANKLINES is ON

PROMPT -- Note: ECHO is ON

PROMPT -- Note: DEFINE is OFF

PROMPT ---*************************-------------------

COL "TAKE NOTE" FORMAT a80

Select 'Scripts will run on ' || GLOBAL_NAME || ' as ' || USER "TAKE NOTE" FROM GLOBAL_NAME;

SELECT SYSTIMESTAMP "DATE/TIME" FROM DUAL;

PROMPT -----------------------------------------------

SET VERIFY ON DEFINE OFF ECHO ON

---

In my sqlcl_login.sql OR sqlplus_login.sql files, I have the SET commands that are specific to that application.

thatJeffSmith-Oracle

Ahhh, you're on a Mac...This should work on Windows at least.

Brad_the_Dazed

Maybe I'm resurrecting the dead, but SYS_CONTEXT('USERENV','MODULE') doesn't seem to get set for SQLcl initially ... only after set appinfo on

$ $HOME/sqlcl/bin/sql

SQLcl: Release 17.3.0 Production on Thu Oct 19 11:16:48 2017

... user and password connection info ...

SQL> select sys_context ('USERENV', 'MODULE') from dual;

SYS_CONTEXT('USERENV','MODULE')

---------------------------------------------------------------------------------------------------------------------------

java@myhostname (TNS V1-V3)

SQL> show java

Java Detail

-----------

java.home= /usr/java/jdk1.8.0_92/jre

java.vendor= Oracle Corporation

java.vendor.url= http://java.oracle.com/

java.version= 1.8.0_92

---------------------------------------------------------------------------------------------------------------------------

os.arch= amd64

os.name= Linux

SQL> set appinfo on

SQL> select sys_context ('USERENV', 'MODULE') from dual;

SYS_CONTEXT('USERENV','MODULE')

---------------------------------------------------------------------------------------------------------------------------

SQLcl

(oh, while researching, another site copied this forum ...

SQLcl - login.sql - dedect if login.sql is called from SQLcL or from SQL*Plus - SQLcl )

Gary Graham-Oracle

This works on Windows...

C:\Tools\sqlcl\r17_3_1\sqlcl\bin>sql scott/tiger@xe
SQLcl: Release 17.3.0 Production on Thu Oct 19 11:31:51 2017
Copyright (c) 1982, 2017, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 17.3.0.256.1818
SQL> select sys_context('USERENV','MODULE') from dual;
SYS_CONTEXT('USERENV','MODULE')
--------------------------------------------------------------------------------
SQLcl
SQL>

And...

SQL> show appinfo
appinfo is OFF and set to "SQLcl"
SQL>

Brad_the_Dazed

Such sweet o/s love ... or lack thereof ... the unix version needs to be burped ... :-)

SQL> show appinfo

appinfo is OFF and set to "SQLcl"

SQL> select sys_context('USERENV','MODULE') from dual;

SYS_CONTEXT('USERENV','MODULE')

----------------------------------------------------------------------------------------------------------------------------------------------

java@oquin2 (TNS V1-V3)

SQL> set appinfo on

SQL> set appinfo off

SQL> select sys_context('USERENV','MODULE') from dual;

SYS_CONTEXT('USERENV','MODULE')

----------------------------------------------------------------------------------------------------------------------------------------------

SQLcl

SQL> show version

Oracle SQLDeveloper Command-Line (SQLcl) version: 17.3.0.256.1818

1 - 7

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 2 2011

Added on May 5 2011

#apex-discussions, #exception, #page, #pl, #process, #sql

5 comments

5,561 views

APEX

Error message after PL/SQL page process

Comments

Post Details