Skip to Main Content

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.

Export to Excel

user10991018Oct 27 2021

we upgraded our apex version from Application Express 4.0.0.00.46
to Application Express 20.2.0.00.20
i have a button in my apex page which is used to export data to excel when clicked.
Action - Redirect to URL
Target - Server name -
http://<servername>:<portnumber>/apex/f?p=&APP_ID.:1:&SESSION.:CSV:
i am getting error and not redirecting to csv file.
this code was working in Apex Application Express 4.0.0.00.46

Comments

/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

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

...  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

Post Details

Added on Oct 27 2021
2 comments
107 views