Feed aggregator

bulk update

Tom Kyte - Fri, 2017-03-17 18:46
Hi Tom, I have table called customers with data below SQL> select * from customers; ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- --------...
Categories: DBA Blogs

Unsuccessful addition of column with NOT NULL constraint ends with creation hidden columns SYS_C[...] on Oracle12c

Tom Kyte - Fri, 2017-03-17 18:46
Hello! Recently I've encountered a problem with adding new column to existing, not empty table. New column had a NOT NULL constraint, so ALTER TABLE command failed with ORA-01758 (that's clear). But having looked at DBA/USER_TAB_COLS dictionary ...
Categories: DBA Blogs

SQL Query to split rows based on dates

Tom Kyte - Fri, 2017-03-17 18:46
Could you please help me with a SQL that would generate the desired output? <code> create table user_tmp (vname varchar(15) , external_pgm varchar(1), eff_date date, exp_date date); insert into user_tmp values('JOHN','A', '01-JAN-1...
Categories: DBA Blogs

How can we identify a session in GV_$SESSION with DBMS_SESSION.UNIQUE_SESSION_ID ?

Tom Kyte - Fri, 2017-03-17 18:46
Actually (one node RAC) I can identify a session in V_$SESSION with AUDSID = "sys_context('USERENV', 'SESSIONID')". We are switching to multiple node RAC and I have to use GV_$SESSION and DBMS_SESSION.UNIQUE_SESSION_ID to get a unique id across al...
Categories: DBA Blogs

Convert BLOB file to readable format

Tom Kyte - Fri, 2017-03-17 18:46
Hi, There is one table which has data type as BLOB for one of the columns. We need to open that file and see the context. Please suggest, how can we convert that file and make it in readable format? Thanks.
Categories: DBA Blogs

java.sql.SQLRecoverableException: ORA-01034: ORACLE not available

Tom Kyte - Fri, 2017-03-17 18:46
I try to make connection to oracle db on Linux from java application. T got the following error. What is the cause and hot to resolve it? java.sql.SQLRecoverableException: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not ex...
Categories: DBA Blogs

Welcome to the new Fishbowl Solutions Blog

Out with the old and in with the new.  Welcome to the new home of the Fishbowl Solutions blog! Please enjoy upgraded functionality and integration with our website.  Check back often for new and exciting posts form our talented staff.  If you want automatic updates click the subscribe link to the right and be notified whenever a new post appears.

 

 

 

 

 

 

 

The post Welcome to the new Fishbowl Solutions Blog appeared first on Fishbowl Solutions.

Categories: Fusion Middleware, Other

Oracle BPM: Loops and Gateway Struggles

Jan Kettenis - Fri, 2017-03-17 11:40
If there is one issue that I see people often struggle with, then it is the use of loops in combination with gateways. The following discusses a few cases.

The following picture shows several loops in combination with a Parallel gateway, of which some are valid and some not. The same holds for the Inclusive gateway.

To understand why some loops are valid and other not, you have to realize that at the beginning of a Parallel or Inclusive gateway as many tokens are generated as there are parallel flows that run between the start and end of the gateway. To the BPM engine this translates to 1 or more threads that are instantiated.

No such restrictions are there for an exclusive gateway, because then there is only one token (thread) active at any time.

So in BPMN the following flows are not valid:
  • From "crossover?", because you are going to another thread that may already have passed the point that the flow goes to. However, JDeveloper does not prevent you from doing so.
  • From "loop back inside to beginning", because at the beginning of the gateway new threads would have to be instantiated for flows of which some threads may already run. JDeveloper should fail validation of such a construct.
  • From "loop back inside from outside", because you would then have to go back to a thread already ended in the merge. JDeveloper should fail validation of such a construct.

The flows that are valid in BPMN are:
  • From "loop back inside", as you loop back within the same thread.
  • From "loop back outside to beginning" as you are re-instantiating a new set of threads for which the previous set already ended.

In case the latter does not work apply patch 23230734.

EBS Support Implications for Discoverer 11gR1 in June 2017

Steven Chan - Fri, 2017-03-17 10:57

What happens to Discoverer support in June 2017?

The Oracle Lifetime Support Policy: Oracle Fusion Middleware Products document states:

  • Premier Support for Discoverer 11gR1 ended on June 30, 2014. 
  • Extended Support for Discoverer 11gR1 ends on June 30, 2017. 

No new patches for Discoverer 11gR1 or its E-Business Suite (EBS) Discoverer-based content will be created after June 30, 2017.  EBS customers will continue to have access to existing released patches and other published resources.

Which EBS releases are affected?

E-Business Suite 12.1 and 12.2 included workbooks, business areas, and folders built for Discoverer 11gR1.  Both EBS 12.1 and 12.2 are affected by this.

What should EBS users use for analytics now?

This document was published in March 2014:

That Note recommends that Discoverer users migrate to Oracle Business Intelligence Enterprise Edition (OBIEE), Oracle Business Intelligence for Applications (OBIA), or Oracle Endeca Information Discovery.

Are there automated tools for migrating from Discoverer to other Oracle analytics tools?

No, there are no automated tools for migrating Discoverer content to OBIEE, OBIA, or Oracle Endeca Information Discovery.

Can EBS customers request new patches after June 2017?

No, Oracle will not produce new patches or documentation for Discoverer, EBS content for Discoverer, or Discoverer certifications with EBS 12.1 or 12.2 after June 30, 2017. 

Can EBS customers access existing Discoverer-related resources after June 2017?

Yes, EBS customers will still be able to download existing Discoverer patches.  For example, Discoverer 11.1.1.7 was certified in June 2013 and is certified for EBS 12.1 and 12.2.  Customers will continue to be able to download Discoverer 11.1.1.7 and Discoverer-related documentation for EBS environments (Note 1380591.1 for EBS 12.2, Note 1074326.1 for EBS 12.1).

Can EBS customers continue to use Discoverer after June 2017?

Yes, but Oracle's ability to assist with questions will be increasingly-limited as environments with Discoverer are retired.  Customers should minimize changes to their Discoverer-related infrastructure with the goal of keeping Discoverer environments stable: e.g. limiting changes that might affect load, hardware infrastructure, or business processes.

Will Discoverer work with new desktop client updates after June 2017?

This is unknown.  No new certifications for Discoverer will be performed after June 2017.  Desktop client updates such as new JRE releases, new Windows updates, and new browsers may have unpredictable effects on Discoverer.  Oracle will not issue new compatibility patches for these types of issues after June 2017.


Categories: APPS Blogs

Integrigy COLLABORATE 17 Sessions - Presentations on Oracle Database, Oracle E-Business Suite, and PeopleSoft Security

Integrigy is presenting nine papers this year at COLLABORATE 17 (https://collaborate.oaug.org/). The COLLABORATE 17 conference is a joint conference for the Oracle Applications User Group (OAUG), Independent Oracle Users Group (IOUG), and Quest International Users Group.

Here is our schedule. If you have questions or would like to meet with us while at COLLABORTE 17, please conact us at info@integrigy.com.

Sunday Apr 02, 2017

1:45 PM - 2:45 PM

Oracle E-Business Suite 12.2 Security Enhancements

https://app.attendcollaborate.com/event/member?item_id=5621519

Banyan E

Speaker: Stephen Kost

1:45 PM - 2:45 PM

How to Control and Secure Your DBAs and Developers in Oracle E- Business Suite

https://app.attendcollaborate.com/event/member?item_id=5740411

South Seas F

Speaker: Michael Miller

Monday Apr 03, 2017

9:45 AM - 10:45 AM

The Thrifty DBA Does Database Security

https://app.attendcollaborate.com/event/member?item_id=5660960

Jasmine D

Speaker: Stephen Kost

1:00 PM - 4:30 PM

Integrigy team available for meetings and discussions Contacts us at info@integrigy.com to arrange

 

 

Tuesday Apr 04, 2017

9:45 AM - 10:45 AM

Solving Application Security Challenges with Database Vault

https://app.attendcollaborate.com/event/member?item_id=5660961

Jasmine D

Speaker: Stephen Kost

1:00 PM - 4:30 PM

Integrigy team available for meetings and discussions Contacts us at info@integrigy.com to arrange

 

 

Wednesday Apr 05, 2017

9:45 AM - 10:45 AM

When You Can't Apply Database Security Patches

https://app.attendcollaborate.com/event/member?item_id=5660962

Jasmine D

Speaker: Stephen Kost

11:00 AM - 12:00 PM

Common Mistakes When Deploying Oracle E-Business Suite to the Internet

https://app.attendcollaborate.com/event/member?item_id=5621520

South Seas B

Speaker: Stephen Kost

1:30 PM - 2:30 PM

Securing Oracle 12c Multitenant Pluggable Databases

https://app.attendcollaborate.com/event/member?item_id=5660950

Palm A

 

Speaker: Michael Miller

2:45 PM - 3:45 PM

How to Control and Secure Your DBAs and Developers in PeopleSoft

https://app.attendcollaborate.com/event/member?item_id=5617942

Ballroom  J

Speaker: Michael Miller

Thursday Apr 06, 2017

8:30 AM - 9:30 AM

Oracle E-Business Suite Mobile and Web Services Security

https://app.attendcollaborate.com/event/member?item_id=5621407

South Seas B

Speaker: Michael Miller

 

You can download a complete listing of Integrigy's sessions at Integrigy COLLABORATE 17 Sessions.

Oracle Database, Oracle E-Business Suite, Oracle PeopleSoft
Categories: APPS Blogs, Security Blogs

PeopleSoft Security

This is a quick summary of Integrigy’s latest research on PeopleSoft. Was sending this to a client and decided it was a good posting:

Guide to PeopleSoft Logging and Auditing

How to Control and Secure PeopleSoft DBAs and Developers

PeopleSoft Database Security

PeopleSoft Database Secure Baseline Configuration

PeopleSoft Security Quick Reference

If you have any questions, please contact us at info@integrigy.com

 

 
 
Oracle PeopleSoft, Whitepaper
Categories: APPS Blogs, Security Blogs

Deploying Oracle E-Business Suite 12.2 REST Web Services

This is the forth posting in a blog series summarizing the new Oracle E-Business Suite 12.2 Mobile and web services functionality and recommendations for securing them.

Physically deploying REST services with 12.2 is straightforward. REST is an architectural style and not a protocol and is best used to support lightweight and “chatty” interfaces such as Mobile applications.  With 12.2, REST Web Application Description Language (WADL) interface definition files are generated within the E-Business Suite's WebLogic server and run through the OAFM Application. The OAFM application created with the installation of the Oracle E-Business Suite.

If you have any questions, please contact us at info@integrigy.com

-Michael Miller, CISSP-ISSMP, CCSP, CCSK

References
 

 

     
     
     
     
     
     
    Web Services, DMZ/External, Oracle E-Business Suite
    Categories: APPS Blogs, Security Blogs

    Links for 2017-03-16 [del.icio.us]

    Categories: DBA Blogs

    Two identical queries with same parameter values have different execution plans ..

    Tom Kyte - Fri, 2017-03-17 00:26
    Hello and thanks for your time. We noticed an odd behavior of Oracle 12.1 query plan selection for the same query. The query generated from .Net Entity Framework has an inefficient query plan than if we run the same query in SQL Developer. When we...
    Categories: DBA Blogs

    Can I set the basis for SYSDATE within a session?

    Tom Kyte - Fri, 2017-03-17 00:26
    When writing code that deals with time, it would be very useful to be able to "set" the starting point for SYSDATE within a session. For example, suppose I want to select one set of data if the query runs in March and a different set if it runs in...
    Categories: DBA Blogs

    Update a nested column with a database tool like Oracle sql developer

    Tom Kyte - Fri, 2017-03-17 00:26
    Hello , i have another question about nested tables. When i created the table projects with the column project_name and categories. categories is the nested column. Can i easy update the column categories (viz : i write direct in the column usin...
    Categories: DBA Blogs

    Accessing Nested Tables Elements

    Tom Kyte - Fri, 2017-03-17 00:26
    Hello , i want to have multiple value per cell and i used nested tables. After creating my table projects with a the nested column categories, i insert some rows. but when i do select * from tables , i have the error unsupported data type by the ...
    Categories: DBA Blogs

    Purpose of clauses in SQL*Loader control file

    Tom Kyte - Fri, 2017-03-17 00:26
    Hi, I am Learning Data Loading With Sql Loader.i written some basic control files(with out using Clauses) then data loaded well. but i did not understand the clauses like NULLIF, CHAR, DEFAULTIF etc. So please tell me why we use this Clauses in Co...
    Categories: DBA Blogs

    Converting quarter number to dates of months of this quarter

    Tom Kyte - Fri, 2017-03-17 00:26
    Hi Tom! I have a table containing a column with a QUARTER of the year (1, 2, 3, 4). I'd like to multiply each row into another table while substituting the quarter column with two columns - containing the DATE of the FIRST and the LAST day of each...
    Categories: DBA Blogs

    ORA-54002 when trying to create Virtual Column using REGEXP_REPLACE on Oracle 12cR2

    Jeff Moss - Thu, 2017-03-16 17:50

    I encountered an issue today trying to create a table in an Oracle 12cR2 database, the DDL for which, I extracted from an Oracle 11gR2 database. The error returned when trying to create the table was:

    ORA-54002: only pure functions can be specified in a virtual column expression

    The definition of the table included a Virtual Column which used a REGEXP_REPLACE call to derive a value from another column on the table.

    Here is a simplified test case illustrating the scenario (Thanks Tim for the REGEXP_REPLACE example code):

    select * from v$version
    /
    create table test_ora54002_12c(
     col1 VARCHAR2(20 CHAR) NOT NULL
     ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
    )
    /
    drop table test_ora54002_12c purge
    /

    Running this on 11gR2 gives:

    SQL> select * from v$version
     2 /
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE 11.2.0.4.0 Production
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production
    
    5 rows selected.
    
    Elapsed: 00:00:00.40
    SQL> create table test_ora54002_12c(
     2 col1 VARCHAR2(20 CHAR) NOT NULL
     3 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
     4 )
     5 /
    
    Table created.
    
    Elapsed: 00:00:00.24
    SQL> drop table test_ora54002_12c purge
     2 /
    
    Table dropped.

    Running this on 12cR2 gives:

    SQL> select * from v$version
    /
     2
    BANNER CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
    PL/SQL Release 12.2.0.1.0 - Production 0
    CORE 12.2.0.1.0 Production 0
    TNS for Linux: Version 12.2.0.1.0 - Production 0
    NLSRTL Version 12.2.0.1.0 - Production 0
    
    SQL> create table test_ora54002_12c(
     col1 VARCHAR2(20 CHAR) NOT NULL
     ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
    )
    /
     2 3 4 5 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
     *
    ERROR at line 3:
    ORA-54002: only pure functions can be specified in a virtual column expression
    
    
    SQL> drop table test_ora54002_12c purge
    /
     2 drop table test_ora54002_12c purge
     *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    As you can see, 12cR2 gives the ORA-54002 error.

    Looking on MOS, highlights this article, which suggests that you shouldn’t have been able to do this in 11gR2, i.e. it was a bug and that 12cR2 has fixed this bug and thus you can no longer create such a virtual column (the article refers to functional index and check constraint use cases as well).

    In my case, I was able to rewrite the virtual column to use simple string functions such as SUBSTR, TRANSLATE and INSTR to achieve what I wanted and the virtual column was allowed to be created with these – problem solved – a shame really as the REGEXP_REPLACE approach was far neater.

    Pages

    Subscribe to Oracle FAQ aggregator