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.

sys_context not working from APEX application / environment

731104Jan 24 2012 — edited Jan 25 2012
I am struggling to retrieve a user-defined context value using sys_context(...) from APEX application / environment

I have created a global context to be used as a storage mechanism for log severity to be recorded.
 CREATE OR REPLACE CONTEXT PA_LOGGER_CTX USING PA_LOGGER ACCESSED GLOBALLY;
It is seeded through a function pa_logger.set_severity(1) within the package pa_logger through (excerpt):
  dbms_session.set_context(c_ctx_context_label, c_ctx_severity_label, TO_CHAR(p_severity_in));

  with:
  c_ctx_context_label     CONSTANT NVARCHAR2(16) := 'PA_LOGGER_CTX';
  c_ctx_severity_label    CONSTANT NVARCHAR2(8) := 'SEVERITY';
  p_severity_in   IN  PLS_INTEGER
using the following from sqlplus, other DB-tools, works as expected:
-- setting it
BEGIN
  pa_logger.set_severity(2);
END;
-- displaying it
SELECT sys_context('PA_LOGGER_CTX', 'SEVERITY') FROM DUAL;
returns 2

This works from different sessions, different users ... no problem.

Using it from within an APEX (accessed via embedded gateway/XE11.2 or OHS/11.2.0.2.0 ) application or even from the "SQL Commands" from the APEX Workspace returns a NULL value for :
SELECT sys_context('PA_LOGGER_CTX', 'SEVERITY') FROM DUAL;
And
   SELECT value  FROM GLOBAL_CONTEXT WHERE NAMESPACE='PA_LOGGER_CTX' and ATTRIBUTE = 'SEVERITY';
returns the right value, which obviously defies the purpose of using the CONTEXT.
It is as well possible to set the CONTEXT to a different value, which is picked up outside APEX, but even so using sys_context does not work, always returning NULL

As a note aside: using sys_context with a standard CONTEXT, e.g. SELECT sys_context('USERENV', 'NLS_DATE_FORMAT') from DUAL works without any problems.


I guess I have a conceptual break somewhere. Does this have to do with the APEX user used, wrapping within the APEX environment, the way the context is defined ....

Any help is appreciated.

Many thanks,
- Thomas

Comments

Anindya Gayen

Can pls anyone help me with the above problem.

Thanks.

_Phil

I think you need to synchronize your file.

The first time you run the interface, ODI creates the tables based on your XSD and populates them with the data.  If you change the data in the XML file, then you need to synchronize the file for the changes to be picked up.  See B.4.9 SYNCHRONIZE in the XML driver documentation:

Oracle Data Integrator Driver for XML Reference

Anindya Gayen

Hi,

Thanks for the reply.

I have got the solution.

Synchronization is not needed over here.

If we configure the Data Server in the following manner, any update or delete in CSV file will reflect in the target.

jdbc:snps:complexfile?f=C:\cstdb\cstdb.csv&d=C:\cstdb\cstdb.xsd&re=Root&ro=true&dod=yes


Here dod means drop on disc. It automatically drops the schema when we close the JDBC connection.

_Phil

Glad you got sorted.  Yep, DOD will do the trick too.  I personally prefer to maintain the tables post-load and use synchronize, especially if it's a reusable process and I expect the files I'm loading to be in a consistent format.

But as always, whatever works best for your process.

TriTechWorks

Here's what you can do to handle CSV files using HSQL.

Say the CSV file contains order data. Each order record contains data about a particular order like order number, product code, quantity, ordered by, date etc. Now lets take a hypothetical requirement which says, the user needs to know how many orders were ordered per product.


Option 1]  At the basic level, read the order file, parse each line and write the logic to get count of orders per product.

Option 2] Load this CSV file into a database like MySQL and write database queries to get the orders per product.

So where does this leave us? Have we run out of options? I am sure we would have tried & used the above two options . But I wanted a different approach. Following questions were lingering in my mind:

1] Why can’t I write SQL queries against the CSV file itself. After all it’s like any RDBMS table.

2] Why should I load the CSV file into some database before I query it?

3] Why can’t I create a database table & attach the CSV file to it?

Continue Reading Here: Handling CSV Files

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 22 2012
Added on Jan 24 2012
4 comments
3,064 views