Feed aggregator

REST Support clarifications

Anthony Shorten - Tue, 2017-05-23 19:10

In the Oracle Utilities Application Framework V4.3.0.3.0 release, the support for REST has been enabled for use as a complementary interface method adding to the SOAP support we already have in the product.

The REST support in the Oracle Utilities Application Framework was originally developed to support our new generation of the mobile connection platform we used for the Oracle Utilities Mobile Workforce Management platform and limited to that product initially. Subsequently, we have decided to open up the support for general use.

As the REST support was originally designed for its original purpose, the current release of REST is limited to specific aspects of that protocol but it is at a sufficient level to be used for general purpose functions. It is designed to be an alternative to SOAP integration for customers who want to a mixture of SOAP and REST in their integration architectures.

In the initial release, the REST support has been implemented as part of the online channel to take advantage of the Oracle WebLogic facilities and share the protocol and security setup of that channel. In a future release, we have plans to incorporate enhanced REST features in a separate channel dedicated to integration.

For more information about the REST platform support, including the limitations of this initial release, refer to the Web Services Best Practices whitepaper from My Oracle Support (Doc Id: 221475.1).

Log Buffer #511: A Carnival of the Vanities for DBAs

Pythian Group - Tue, 2017-05-23 09:50

This Log Buffer Edition covers Oracle, SQL Server, MySQL.

Oracle:

A Sneak Peek at Oracle’s Chatbot Cloud Service and 5 Key Factors Necessary for Bot ROI

Oracle JET Hybrid – NavDrawer Template Menu/Header Structure

Oracle Enterprise Linux 6.6 AMI Available on AWS

Datascape Podcast Episode 9 – What’s Up with Oracle These Days?

Sequential Asynchronous calls in Node.JS – using callbacks, async and ES6 Promises

SQL Server:

Fixing an SSRS Password Error while Changing Credentials

Azure DWH Part 8: Accessing Azure SQL Data Warehouse with C#

Personal Data, Privacy, and the GDPR

Performance Myths : Truncate Can’t Be Rolled Back

Troubleshooting CPU Performance on VMware

MySQL:

MySQL Shell: eye candy for a future release !

MySQL 8.0: It’s doxygen time for MTR

How to login in MariaDB with OS user without password

MySQL Enterprise backup : How to setup a slave for a standalone server with zero downtime?

Command Line Aficionados: Introducing s9s for ClusterControl

Categories: DBA Blogs

255 Again!

Jonathan Lewis - Tue, 2017-05-23 07:10

There’s so many things that can go wrong when you start using tables with more than 255 columns – here’s one I discovered partly because I was thinking about a client requirement, partly because I had a vague memory of a change in behaviour in 12c and Stefan Koehler pointed me to a blog note by Sayan Malakshinov when I asked the Oak Table if anyone remembered seeing the relevant note. Enough of the roundabout route, I’m going to start with a bit of code to create a table, stick a row in it, then update that row:

rem
rem     Script: wide_table_4.sql
rem     Author: Jonathan Lewis
rem     Dated:  May 2017
rem
rem     Last tested
rem             12.2.0.1
rem             12.1.0.2
rem             11.2.0,4
rem

set pagesize 0
set feedback off

spool temp.sql

prompt create table t1(

select
        'col' || to_char(rownum,'fm0000') || '  varchar2(10),'
from
        all_objects
where   rownum <= 320
;

prompt col0321 varchar2(10)
prompt )
prompt /

spool off

@temp

set pagesize 40
set feedback on

insert into t1 (col0010, col0280) values ('0010','0280');
commit;

update t1 set col0320 ='0320';
commit;

column file_no  new_value m_file_no
column block_no new_value m_block_no

select
        dbms_rowid.rowid_relative_fno(rowid)    file_no,
        dbms_rowid.rowid_block_number(rowid)    block_no,
        dbms_rowid.rowid_row_number(rowid)      row_no
from
        t1
;

alter system flush buffer_cache;
alter system dump datafile &m_file_no block &m_block_no;


So I’ve written one of those horrible scripts that write a script and then run it. The script creates a table with 320 columns and inserts a row that populates columns 10 and 280. That gets me two row pieces, one consisting of the 255 columns from columns 26 to 280 that goes in as row piece 0, the other consisting of the first 25 columns that goes in as row piece 1; the remaining 40 columns are not populated so Oracle “forgets” about them (“trailing nulls take no space”). The script then updates the row by setting column 320 to a non-null value.

For convenience I’ve then generated the file and block number (and row number, just to show its head piece went in as row 1 rather than row 0) of the row and done a symbolic block dump. The question is: what am I going to see in that block dump ?

Answer, and lots more, coming some time in the next 48 hours.


Oracle Utilities Unveils the Perfect Customer Platform for the Modern Utility

Oracle Press Releases - Tue, 2017-05-23 07:00
Press Release
Oracle Utilities Unveils the Perfect Customer Platform for the Modern Utility New customer to meter offering slashes tech costs by 25% and combines full power of customer information systems and meter data management systems

CS Week – Fort Worth, Texas—May 23, 2017

Oracle today unveiled Oracle Utilities Customer to Meter, a comprehensive meter-to-cash solution for today’s customer-first utility. Oracle Utilities Customer to Meter is the first offering to combine a market-leading customer information system (CIS) with a market-leading meter data management system (MDMS) into one solution with a single user interface.

Leveraging a single shared technology stack, this new solution can reduce utility costs due to faster implementation times, fewer integration points, and greater operational efficiencies. Oracle Utilities Customer to Meter delivers all of the benefits of a complete meter solution and a powerful customer platform, enabling utilities to more immediately and efficiently extract value from advanced metering infrastructure data to improve customer experience. With this streamlined approach, utilities can more easily design customer-centric, personalized programs and services, and prepare for the continued growth of smart meter programs. This holistic solution lays the groundwork for an evolving utility that wants to roll out smart meters in the future, without a major IT project.

“Utilities that leverage data to deliver an improved customer experience and more personalized programs - such as tailored time-of-use billing, or targeted home energy management advice - and do this with a single, integrated solution that combines customer and meter data, will be well poised to take advantage of the continued growth of smart meters and a smarter, more customer-centric grid”, according to Roberta Bigliani, Vice President, IDC Energy Insights.

With rising customer expectations and expanding smart grids, utilities are turning to modern, comprehensive technologies that deliver world-class customer engagement and operational efficiencies. Oracle Utilities Customer to Meter provides the platform to respond to evolving market dynamics and quickly implement new business requirements that span metering, rate analysis, billing, collections and customer programs. For example, as electric utilities face increasing distributed generation they may test new rate structures to better manage demand. With Oracle Utilities Customer to Meter, months of customization can be reduced to hours of configuration and utilities can easily test and implement the changes necessary to evolve. Oracle Utilities Customer to Meter consolidates advanced usage and billing capabilities for all meters – from scalar to interval—so utilities can manage those meters and their data in one place and derive greater value from grid investments.

“Oracle Utilities continues to partner with utilities around the globe to solve the issue of increasing complexity in this rapidly transforming industry. Simplifying meter-to-cash processes is an important part of those partnerships. This new solution does exactly that: it allows utilities to get up and running in a matter of months with a complete meter-to-cash solution and allows them to leverage that complete solution to streamline business processes and easily stay ahead of rapidly evolving business drivers impacting how they serve their customers,” said Rodger Smith, senior vice president and general manager, Oracle Utilities. 

Oracle Utilities Customer to Meter is redefining the utility customer platform by enabling utilities to:

  • Implement a full meter-to-cash solution in a matter of months
  • Leverage one technology stack and reduce technology costs
  • Achieve service excellence in every customer interaction with a single, intuitive user interface
  • Deliver a powerful, streamlined customer experience across every channel
  • Expand smart meter programs seamlessly and derive more value from AMI data

Additional Resources
Contact Info
Valerie Beaudett
Oracle Corporation
+1 650.400.7833
valerie.beaudett@oracle.com
Christina McDonald
Burson-Marsteller
+1 212.614.4221
christina.mcdonald@bm.com
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Safe Harbor

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Valerie Beaudett

  • +1 650.400.7833

Christina McDonald

  • +1 212.614.4221

SCAP OVAL SQL57_TEST Example For Oracle E-Business Suite

Last week I posted a blog introducing SCAP and OVAL. Here is a quick follow-up with a link to a sql57_test example using the Oracle E-Business Suite - it will suffice for any Oracle database.

A great book to read first on SCAP titled ‘Security Automation Essentials’ for $15 on Amazon is a must read:  https://www.amazon.com/Security-Automation-Essentials-Streamlined-Communication/dp/0071772510. I would highly recommend this book to anyone interested in SCAP and much thanks to Witte, Cook, Kerr and Shaffer for writing it.

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

-Michael Miller, CISSP-ISSMP, CCSP, CCSK

References
 
SCAP OVAL, Oracle Database, Oracle E-Business Suite
Categories: APPS Blogs, Security Blogs

SQL Tuning Advisor against sql_id's in AWR

Syed Jaffar - Tue, 2017-05-23 04:23
We were in a situation very recently to run SQL Tuning Advisor against a bunch of SQL statements that appeared in the AWR's ADDM recommendations report. The initial effort to launch SQL Tuning Advisor against the SQL_ID couldn't go through as the SQL didn't exist in the shared pool.

Since the sql_id was present in the AWR report, thought of running the advisory against the AWR data, and found a very nice and precisely explained at the following blog:

http://www.redstk.com/running-sql-tuning-advisor-against-awr-data/


---- Example how to run SQL Tuning advisor against sql_id in AWR

variable stmt_task VARCHAR2(64);
SQL> exec :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK (begin_snap => 4118, end_snap => 4119, sql_id => 'caxcavmq6zkv9' , scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'sql_tuning_task01' );

SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sql_tuning_task01');

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_task01';

set long 50000
set longchunksize 500000
SET LINESIZE 150
Set pagesize 5000
 

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task01') FROM DUAL;


SQL> exec DBMS_SQLTUNE.drop_tuning_task(task_name =>'sql_tuning_task01');



References:
https://docs.oracle.com/database/121/ARPLS/d_sqltun.htm#ARPLS220
https://uhesse.com/2013/10/11/oracle-sql-tuning-advisor-on-the-command-line/



Happy reading/learning.

PL/SQL Collections Associate Array

Tom Kyte - Tue, 2017-05-23 03:46
<code>Hi, In the below example for Associative Array Indexed by String, set serveroutput on; DECLARE -- Associative array indexed by string: TYPE population IS TABLE OF NUMBER -- Associative array type INDEX BY VARCHAR2(64); -- indexed by str...
Categories: DBA Blogs

RMAN full back vs Incremental Backup

Tom Kyte - Tue, 2017-05-23 03:46
Hii AskTOM Team. I also refer following two website for my problem. https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmcncpt.htm#BRADV109 https://www.pythian.com/blog/rman-full-backup-level-0-incrmental/ But I have confusion abou...
Categories: DBA Blogs

Named NOT NULL Constraint

Tom Kyte - Tue, 2017-05-23 03:46
How can I name a "not null" constraint that sets the NULLABLE flag in DBA/USER_TAB_COLUMNS? If a column is nullable and I add a check constraint with a name that meets our company standards, the column still shows as NULLABLE. If I alter the tab...
Categories: DBA Blogs

Webcast: "Enterprise Manager 13c Cloud Control for Managing Oracle E-Business Suite"

Steven Chan - Tue, 2017-05-23 02:00

Oracle University has a wealth of free recorded webcasts for Oracle E-Business Suite.  Here's a free webcast that covers our E-Business Suite plug-in for Oracle Enterprise Manager:

Application Management Suite delivers capabilities to facilitate management of Oracle E-Business Suite environments running in the Oracle Cloud and on-premises using a single pane of glass. Angelo Rosado, Senior Principal Product Manager, shares key new features provided in the latest release of Application Management Suite for Oracle E-Business Suite available with EM 13c. Application Management Suite features that will be covered include deploying patches and customizations across all environments; comparing configurations between instances; provisioning a new instance to the Oracle Cloud; migrating an existing instance to the cloud; enforcing compliance standards; and automated cloning. This material was presented at Oracle OpenWorld 2016. 

Related Articles

Categories: APPS Blogs

More Resources for Elasticsearch with PeopleSoft

PeopleSoft Technology Blog - Mon, 2017-05-22 18:22

There are some new resources available to help with your deployment of Elasticsearch with PeopleSoft.  First, there is a new paper that provides guidance on working with Elasticsearch clusters.  This paper describes setting up clusters during installation, adding and removing nodes, administration of the cluster, starting and stopping clusters and nodes, and managing large indexes.  The Cluster paper is available on the Elasticsearch Home Page on MyOracleSupport.  This paper applies to the PeopleTools release 8.55.11 or later patches.  When PeopleTools 8.56 is released, this cluster information will be included in the 8.56 PeopleBooks.

Two new Spotlight Series videos on Elasticsearch have also been posted.  They are part of a series that began with the Elasticsearch Strategies video, which provides an overview of the process of deploying Elasticsearch with PeopleSoft.  These latest videos cover two important topics:

  • Deploying Elasticsearch--Provides greater detail on the deployment process, particularly for customers that haven't deployed Oracle's Secure Enterprise Search and are not familiar with the PeopleSoft Search Framework.
  • Moving from SES to Elasticsearch--Provides detail on deploying Elasticsearch for customers that have deployed Secure Enterprise Search and the the PeopleSoft Search Framework.

Keep your eye on the Search Concepts Page on peoplesoftinfo.com for updates to Search technology with PeopleSoft.

Webcast: "Faster and Better: Oracle E-Business Suite Desktop Integration Enhancements"

Steven Chan - Mon, 2017-05-22 17:35

Oracle University has a wealth of free recorded webcasts for Oracle E-Business Suite.  If you're looking for an update on the latest enhancements to Web ADI in EBS 12.2, see:

Senthilkumar Ramalingam, Group Manager Product Development, discusses the simplified user experience and the latest OOXML standards support in Oracle Web Applications Desktop Integrator and Oracle Report Manager. The session includes new features in Release 12.2 and other design changes that result in vastly improved performance and spreadsheet experience. In addition, it offers information on how you can use Desktop Integration Framework to build your own custom desktop integrations between Oracle E-Business Suite and Microsoft Excel for enhanced end user productivity for mass upload/download of spreadsheet data. This material was presented at Oracle OpenWorld 2016. 

 
Categories: APPS Blogs

Dude, Where’s My File ? Finding External Table Files in the midst of (another) General Election

The Anti-Kyte - Mon, 2017-05-22 16:11

It’s early summer in the UK, which means it must be time for an epoch defining vote of some kind. No, I’m not talking about Britain’s Got Talent.
Having promised that there wouldn’t be another General Election until 2020, our political classes have now decided that they can’t go any longer without asking us what we think. Again.
Try as I might, it may not be possible to prevent the ear-worm phrases from the current campaign slipping into this post.
What I want to look at is how you can persuade Oracle to tell you the location on disk of any files associated with a given external table.
Specifically, I’ll be covering :

  • getting the name of the Database Server
  • finding the fully qualified path of the datafile the external table is pointing to
  • finding other files associated with the table, such as logfiles

In the course of this, we’ll be challenging the orthodoxy of Western Capitalism “If You Can Do It In SQL…” with the principle of DRY ( Don’t Repeat Yourself).
Hopefully I’ll be able to come up with a solution that is “Strong and Stable” and yet at the same time “Works For The Many, Not the Few”…

The Application

For the most part, I’ve written this code against Oracle 11g Express Edition. However, there are two versions of the final script, one of which is specifically for 12c. I’ll let you know which is which when we get there.

I have an external table which I use to load data from a csv file.

Initially, our application’s external table looks like this :

create table plebiscites_xt
(
    vote_year number(4),
    vote_name varchar2(100)
)
    organization external
    (
        type oracle_loader
        default directory my_files
        access parameters
        (
            records delimited by newline
            badfile 'plebiscites.bad'
            logfile 'plebiscites.log'
            skip 1
            fields terminated by ','
            (
                vote_year integer external(4),
                vote_name char(100)
            )
        )
            location('plebiscites.csv')
    )
reject limit unlimited
/

I’ve created the table in the MIKE schema.

The file that we’re currently loading – plebiscites.csv contains the following :

year,vote_name
2014,Scottish Independence Referendum
2015,UK General Election
2016,EU Referendum
2017,UK General Election

For the purposes of this exercise, I’ll assume that the file is uploaded frequently ( say once per day). I’ll also assume that there’s some ETL process that loads the data from the external table into a more permanent table elsewhere in the database.

As is the nature of this sort of ETL, there are times when it doesn’t quite work as planned.
This is when, equipped with just Read-Only access to production, you will need to diagnose the problem.

In these circumstances, just how do you locate any files that are associated with the external table?
Furthermore, how do you do this without having to create any database objects of your own ?

Finding the server that the files are on

There are a couple of ways to do this.
You could simply look in V$INSTANCE…

select host_name 
from v$instance
/

Alternatively…

select sys_context('userenv', 'server_host')
from dual
/

…will do the same job.
Either way, you should now have the name of the server that your database is running on and, therefore, the server from which the file in question will be visible.
Now to find the location of the data file itself…

Finding the datafile

In keeping with the current standard of public discourse, we’re going to answer the question “How do you find an External Table’s current Location File when not connected as the table owner” by answering a slightly different question ( i.e. as above but as the table owner)…

Our search is simplified somewhat by the fact that the location of any external table is held in the _EXTERNAL_LOCATIONS dictionary views :

select directory_owner, directory_name, location
from user_external_locations
where table_name = 'PLEBISCITES_XT'
/

With this information, we can establish the full path of the file by running…

select dir.directory_path||'/'||uel.location as xt_file
from user_external_locations uel
inner join all_directories dir
    on dir.owner = uel.directory_owner
    and dir.directory_name = uel.directory_name
where uel.table_name = 'PLEBISCITES_XT'
/

…which results in…

XT_FILE                                                                        
--------------------------------------------------------------------------------
/u01/app/oracle/my_files/plebiscites.csv                                        

This is all rather neat and simple. Unfortunately, our scenario of having to investigate an issue with the load is likely to take place in circumstances that render all of this of limited use, at best.

Remember, the scenario here is that we’re investigating an issue with the load on a production system. Therefore, it’s quite likely that we are connected as a user other than the application owner.
In my case, I’m connected as a user with CREATE SESSION and the LOOK_BUT_DONT_TOUCH role, which is created as follows :

create role look_but_dont_touch
/

grant select any dictionary to look_but_dont_touch
/

grant select_catalog_role to look_but_dont_touch
/

As well as the table’s data file, we’re going to want to look at any logfiles, badfiles and discardfiles associated with the table.

Finding other External Table files

At this point it’s worth taking a look at how we can find these additional files. Once again, we have two options.
First of all, we can simply check the table definition using DBMS_METADATA…

set long 5000
set pages 100
select dbms_metadata.get_ddl('TABLE', 'PLEBISCITES_XT', 'MIKE')
from dual
/

…alternatively, we can use the _EXTERNAL_TABLES to home in on the ACCESS_PARAMTERS defined for the table…

set long 5000
select access_parameters
from dba_external_tables
where owner = 'MIKE'
and table_name = 'PLEBISCITES_XT'
/

For our table as it’s currently defined, this query returns :

records delimited by newline
            badfile 'plebiscites.bad'
            logfile 'plebiscites.log'
            skip 1
            fields terminated by ','
            (
                vote_year integer external(4),
                vote_name char(100)
            )

In either case, we end up with a CLOB that we need to search to find the information we need.
To do this programatically, you may be tempted to follow the time-honoured approach of “If you can do it in SQL, do it in SQL”…

with exttab as
(
    select dir.directory_path,  
        regexp_replace( ext.access_parameters, '[[:space:]]') as access_parameters
    from dba_external_tables ext
    inner join dba_directories dir
        on dir.owner = ext.default_directory_owner
        and dir.directory_name = ext.default_directory_name
    where ext.owner = 'MIKE' 
    and ext.table_name = 'PLEBISCITES_XT'
)
select directory_path||'/'||
    case when instr(access_parameters, 'logfile',1,1) > 0 then
        substr
        ( 
            access_parameters, 
            instr(access_parameters, 'logfile') +8, -- substring start position
            instr(access_parameters, chr(39), instr(access_parameters, 'logfile') +8, 1) - (instr(access_parameters, 'logfile') +8) -- substr number of characters
        ) 
        else to_clob('Filename not specified')    
    end as log_file_name,
    directory_path||'/'||
    case when instr(access_parameters, 'badfile',1,1) > 0 then
        substr
        ( 
            access_parameters, 
            instr(access_parameters, 'badfile') +8, -- substring start position
            instr(access_parameters, chr(39), instr(access_parameters, 'badfile') +8, 1) - (instr(access_parameters, 'badfile') +8) -- substr number of characters
        ) 
        else to_clob('Filename not specified')    
    end as bad_file_name,
    directory_path||'/'||
    case when instr(access_parameters, 'discardfile',1,1) > 0 then    
        substr
        ( 
            access_parameters, 
            instr(access_parameters, 'discardfile') +12, -- substring start position
            instr(access_parameters, chr(39), instr(access_parameters, 'discardfile') +12, 1) - (instr(access_parameters, 'discardfile') +12) -- substr number of characters
        ) 
        else to_clob('Filename not specified')    
    end as discard_file_name    
from exttab    
/

…which returns…

Hmmm, it’s possible that a slightly more pragmatic approach is in order here…

set serveroutput on size unlimited
declare
    function get_file
    ( 
        i_owner in dba_external_tables.owner%type,
        i_table in dba_external_tables.table_name%type, 
        i_ftype in varchar2
    )
        return varchar2
    is
        separator constant varchar2(1) := '/';
        dir_path dba_directories.directory_path%type;
        access_params dba_external_tables.access_parameters%type;
        start_pos pls_integer := 0;
        end_pos pls_integer := 0;
    begin
        select dir.directory_path, lower(regexp_replace(ext.access_parameters, '[[:space:]]'))
        into dir_path, access_params
        from dba_external_tables ext
        inner join dba_directories dir
            on dir.owner = ext.default_directory_owner
            and dir.directory_name = ext.default_directory_name
        where ext.owner = upper(i_owner) 
        and ext.table_name = upper(i_table);

        start_pos := instr( access_params, i_ftype||chr(39),1,1) + length(i_ftype||chr(39));
        if start_pos - length(i_ftype||chr(39)) = 0 then
            return 'Filename Not Specified';
        end if;    
        end_pos := instr(access_params, chr(39), start_pos, 1);
        return dir_path||separator||substr(access_params, start_pos, end_pos - start_pos);
    end get_file;

begin
    dbms_output.put_line('LOGFILE '||get_file('MIKE', 'PLEBISCITES_XT', 'logfile'));
    dbms_output.put_line('BADFILE '||get_file('MIKE', 'PLEBISCITES_XT','badfile'));
    dbms_output.put_line('DISCARDFILE '||get_file('MIKE', 'PLEBISCITES_XT','discardfile'));
end;
/

Yes, it’s PL/SQL. No, I don’t think I’ll be getting a visit from the Database Police as this is a rather more DRY method of doing pretty much the same thing…

LOGFILE /u01/app/oracle/my_files/plebiscites.log
BADFILE /u01/app/oracle/my_files/plebiscites.bad
DISCARDFILE Filename Not Specified


PL/SQL procedure successfully completed.

As we’re about to find out, this solution also falls short of being a panacea…

Separate Directory Definitions

What happens when the directories that the files are created in are different from each other ?
Let’s re-define our table :

drop table plebiscites_xt
/

create table plebiscites_xt
(
    vote_year number(4),
    vote_name varchar2(100)
)
    organization external
    (
        type oracle_loader
        default directory my_files
        access parameters
        (
            records delimited by newline
            badfile 'plebiscites.bad'
            logfile my_files_logs:'plebiscites.log'
            discardfile my_files_discards:'plebiscites.disc'
            skip 1
            fields terminated by ','
            (
                vote_year integer external(4),
                vote_name char(100)
            )
        )
            location('plebiscites.csv')
    )
reject limit unlimited
/

You’ll notice here that we’ve added a discard file specification. More pertinently, the directory location for both the discard file and the log file are now specified.
Therefore, our solution needs some tweaking to ensure that it is fit for the many. In fact, while we’re at it, we may as well add the location file in as well….

set serveroutput on size unlimited
declare
    separator constant varchar2(1) := chr(47); -- '/'

    loc_dir_path dba_directories.directory_path%type;
    loc_file user_external_locations.location%type;
    
    function get_file( i_table user_external_tables.table_name%type, i_ftype in varchar2)
        return varchar2
    is
        squote constant varchar2(1) := chr(39); -- " ' "
        colon constant varchar2(1) := chr(58); -- ':'
        
        dir_path dba_directories.directory_path%type;
        access_params dba_external_tables.access_parameters%type;
        
        filedef_start pls_integer := 0;
        filedef_end pls_integer := 0;
        filedef_str clob;
        
        dir_defined boolean;
        
        dir_start pls_integer := 0;
        dir_end pls_integer := 0;
        
        dir_name dba_directories.directory_name%type;
        
        fname_start pls_integer := 0;
        fname_end pls_integer := 0;
        
        fname varchar2(4000);
        
    begin
        select dir.directory_path, lower(regexp_replace(ext.access_parameters, '[[:space:]]'))
        into dir_path, access_params
        from dba_external_tables ext
        inner join dba_directories dir
            on dir.owner = ext.default_directory_owner
            and dir.directory_name = ext.default_directory_name
        where ext.table_name = upper(i_table);
        
        filedef_start := instr(access_params, i_ftype, 1,1); 
        
        if filedef_start = 0 then
            return 'Filename Not Specified';
        end if;
        filedef_end := instr(access_params, squote, filedef_start, 2) + 1;
        filedef_str := substr(access_params, filedef_start, filedef_end - filedef_start);

        dir_defined := instr( filedef_str, colon, 1, 1) > 0;
        if dir_defined then 

            dir_start := length(i_ftype) + 1; 
            dir_end := instr( filedef_str, colon, 1, 1);
            dir_name := substr(filedef_str, dir_start, dir_end - dir_start);
            begin
                select directory_path
                into dir_path
                from dba_directories
                where directory_name = upper(dir_name);
            exception when no_data_found then
                return 'The directory object specified for this file does not exist';
            end;    
        end if;    
        
        fname_start := instr(filedef_str, squote, 1, 1) + 1; 
        fname_end := instr(filedef_str, squote, 1, 2);
        fname := substr( filedef_str, fname_start, fname_end - fname_start);
        return dir_path||separator||fname;
    end get_file;

begin
    -- Get the current file that the XT is pointing to 
    select dir.directory_path, ext.location
        into loc_dir_path, loc_file 
    from dba_external_locations ext
    inner join dba_directories dir
        on dir.owner = ext.directory_owner
        and dir.directory_name = ext.directory_name
        and ext.table_name = 'PLEBISCITES_XT';
        
    dbms_output.put_line('LOCATION '||loc_dir_path||separator||loc_file);    
    dbms_output.put_line('LOGFILE '||get_file('PLEBISCITES_XT', 'logfile'));
    dbms_output.put_line('BADFILE '||get_file('PLEBISCITES_XT','badfile'));
    dbms_output.put_line('DISCARDFILE '||get_file('PLEBISCITES_XT','discardfile'));
    dbms_output.put_line('PREPROCESSOR '||get_file('plebiscites_xt', 'preprocessor'));
end;
/

Run this and we get :

LOCATION /u01/app/oracle/my_files/plebiscites.csv
LOGFILE /u01/app/oracle/my_files/logs/plebiscites.log
BADFILE /u01/app/oracle/my_files/plebiscites.bad
DISCARDFILE /u01/app/oracle/my_files/discards/plebiscites.disc
PREPROCESSOR Filename Not Specified


PL/SQL procedure successfully completed.

Having made such a big thing of preferring the DRY principle to the “Do it in SQL” doctrine, I feel it’s only fair to point out that the new features of the WITH clause in 12c does tend to blur the line between SQL and PL/SQL somewhat…

set lines 130
column ftype format a20
column file_path format a60
with function get_file( i_table in dba_external_tables.table_name%type, i_ftype in varchar2)
    return varchar2
    is
    
        separator constant varchar2(1) := chr(47); -- '/'
        squote constant varchar2(1) := chr(39); -- " ' "
        colon constant varchar2(1) := chr(58); -- ':'
        
        dir_path dba_directories.directory_path%type;
        access_params dba_external_tables.access_parameters%type;
        
        filedef_start pls_integer := 0;
        filedef_end pls_integer := 0;
        filedef_str clob;
        
        dir_defined boolean;
        
        dir_start pls_integer := 0;
        dir_end pls_integer := 0;
        
        dir_name dba_directories.directory_name%type;
        
        fname_start pls_integer := 0;
        fname_end pls_integer := 0;
        
        fname varchar2(4000);
        
    begin
        select dir.directory_path, lower(regexp_replace(ext.access_parameters, '[[:space:]]'))
        into dir_path, access_params
        from dba_external_tables ext
        inner join dba_directories dir
            on dir.owner = ext.default_directory_owner
            and dir.directory_name = ext.default_directory_name
        where ext.table_name = upper(i_table);
        
        filedef_start := instr(access_params, i_ftype, 1,1); 
        
        if filedef_start = 0 then
            return 'Filename Not Specified';
        end if;
        filedef_end := instr(access_params, squote, filedef_start, 2) + 1;
        filedef_str := substr(access_params, filedef_start, filedef_end - filedef_start);

        dir_defined := instr( filedef_str, colon, 1, 1) > 0;
        if dir_defined then 

            dir_start := length(i_ftype) + 1; 
            dir_end := instr( filedef_str, colon, 1, 1);
            dir_name := substr(filedef_str, dir_start, dir_end - dir_start);
            begin
                select directory_path
                into dir_path
                from dba_directories
                where directory_name = upper(dir_name);
            exception when no_data_found then
                return 'The directory object specified for this file does not exist';
            end;    
        end if;    
        
        fname_start := instr(filedef_str, squote, 1, 1) + 1; 
        fname_end := instr(filedef_str, squote, 1, 2);
        fname := substr( filedef_str, fname_start, fname_end - fname_start);
        return dir_path||separator||fname;
    end get_file;

select 'LOCATION ' as ftype, dir.directory_path||sys_context('userenv', 'platform_slash')||ext.location as file_path
from user_external_locations ext
inner join dba_directories dir
    on dir.owner = ext.directory_owner
    and dir.directory_name = ext.directory_name
    and ext.table_name = 'PLEBISCITES_XT'
union select 'LOGFILE', get_file('plebiscites_xt', 'logfile') from dual
union select 'BADFILE', get_file('plebiscites_xt', 'badfile') from dual
union select 'DISCARDFILE', get_file('plebiscites_xt', 'discardfile') from dual
union select 'PREPROCESSOR', get_file('plebiscites_xt', 'preprocessor') from dual
/

Hopefully that’s something to think about in between the Party Election Broadcasts.


Filed under: Oracle, PL/SQL, SQL Tagged: 'server_host'), 12c, 12c pl/sql function in with clause, dba_external_locations, dba_external_tables, don't repeat yourself, external table badfile, external table discardfile, external table logfile, external tables, sys_context('userenv'

Legacy users get ORA-01017 in 12.2

Laurent Schneider - Mon, 2017-05-22 09:50

The default case insensitive string disappeared in 12cR2, let’s call it the 10G string in this post, but it was the same since Oracle 7 at least. It was introduced in V5 or V6 to replace clear-text passwords.

What’s happening then with my ultra-old-accounts?

You could well set a new password (or the same password again) to each account to be migrated in 11g/12cR1 before moving to 12cR2.

If nobody knows the password and nobody can change it because it is hardcoded in the application and neither easy to read (hidden / obfuscated /encrypted) nor to change, then, you are in TROUBLE ! This is documented in Note 2075401.1

First disclaimer : it is a good thing to achieve a better security. SHA1 and SHA2 are a lot better than the oldstyle-longly-hacked-unsalted-case-insensitive-homemade-algorythm. SHA3 has been published in 2015 and it not used in Oracle 12cR2 yet. SHA2 is a bit older (2001) but still recommended. SHA1 is oldish (1995) and no-longer-recommended, collision has been detected. Read more on wikipedia or crypto101

SHA-1 was a really huge improvement when introduced in 11gR1. The old self-made algorythm has been a torture for Oracle Security team. It has been published on Internet. Extremly powerfull password cracker can find your “not-too-long” password in notime. In 11g, Oracle removed the 10g String from the DBA_USERS view. I wrote about this here. It remained on the base table, USER$ until 12cR2. Now Oracle completly removed it by default in 12cR2. 10 years after SHA1 was introduced in 11gR1.

Still. You are the dba. You want to migrate your database not to chase passwords.

You could edit your sqlnet.ora to allow 10g strings.


SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

This works


SQL> sho parameter sec_case_sensitive_logon
NAME                      VALUE
------------------------- -----
sec_case_sensitive_logon  FALSE

SQL> CREATE USER "U" IDENTIFIED BY 
     VALUES 'DC6F2B33D359A95B';
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> conn u/abcdefg@pdb01
Connected.
SQL> conn u/AbCdEfG@pdb01
Connected.

If you have SQLNET.ALLOWED_LOGON_VERSION_SERVER=11, then you could keep the same setting of sec_case_sensitive_logon as in 11g. I recommmend the default (true).

But, that’s it ? Wellllllll… not sure.

In 12.1

 
SQL> select dbms_metadata.get_ddl('USER','U') from dual
DBMS_METADATA.GET_DDL('USER','U')
----------------------------------
   CREATE USER "U" IDENTIFIED BY 
     VALUES 'DC6F2B33D359A95B'

Let’s try in 12.2


SQL> select dbms_metadata.get_ddl('USER','U') from dual
DBMS_METADATA.GET_DDL('USER','U')
------------------------------------
CREATE USER "U" IDENTIFIED BY VALUES 
  'S:0000000000000000000000000000000
00000000000000000000000000000'

While this is a perfectly working syntax, and IDENTIFIED BY VALUES is not supported *. So if create that user, then, obviously, the 10G string is lost.

Well, unsupported feature then? Hmm, yes. You should never have used identified by values.

Ok, so if I refresh my Test database with Production data, how can I save test passwords? You can’t. At least not in a supported way by using identified by values.

You could something like :


SQL> select 'alter user "'||name||
       '" identified by values '''||
       password||''';' txt
     from user$, v$instance 
     where version > '12.2' 
     and spare4 is null  
     and type# = 1
     and regexp_like(PASSWORD,'[A-F0-9]{16}');

txt
-----------------------------------
alter user "U" identified by values
   'DC6F2B33D359A95B';

This may work. In 12.2.0.1. Maybe not in 13. Maybe not in 12.2.0.1.0PSU July. It’s not supported. If it does not work, it is NOT-A-BUG.

The SHA1 was introduced 10 years ago in Oracle 11gR1. If you have not changed your password in ten years, and you don’t know how many employees and ex-employees know this password, and it is case-insensitive, and its “pseudo-hashing-algorythm” has been hacked for maybe two decades, yet, I can only warmly recommend to change those accounts passwords !

Again: so if I refresh my Test database with Production data, how can I save test passwords ?
If I were you I would design a better system for login. For human users, use global users and an Identity solution, like Oracle Universal directory. For technical account, build yourself a tool that generate a random password, and update the user and credentials, something like


select
substr(
REGEXP_REPLACE(
UTL_RAW.cast_to_varchar2(
SYS.DBMS_CRYPTO.RANDOMBYTES (1024)
) ,'[^!#-~]'
),
1,
20
) PW
from dual;
PW
--------------------
%K0w(^%UN.B82Yjjfu{?

And use it to reset your technical user and to configure your application credentials.

* Note 554605.1: the 'IDENTIFIED BY VALUES' clause on a CREATE/ALTER USER statement is not officially documented, and is intended purely for internal

Legacy users get ORA-01017 in 12.2

Laurent Schneider - Mon, 2017-05-22 09:50

The default case insensitive string disappeared in 12cR2, let’s call it the 10G string in this post, but it was the same since Oracle 7 at least. It was introduced in V5 or V6 to replace clear-text passwords.

What’s happening then with my ultra-old-accounts?

You could well set a new password (or the same password again) to each account to be migrated to 11g passwords before moving to 12cR2.

If nobody knows the password and nobody can change it because it is hardcoded in the application and neither easy to read (hidden / obfuscated /encrypted) nor to change, then, you are in TROUBLE ! This is documented in Note 2075401.1

First disclaimer : it is a good thing to achieve a better security. SHA1 and SHA2 are a lot better than the oldstyle-longly-hacked-unsalted-case-insensitive-homemade-algorythm. SHA3 has been published in 2015 and it not used in Oracle 12cR2 yet. SHA2 is a bit older (2001) but still recommended. SHA1 is oldish (1995) and no-longer-recommended, collision has been detected. Read more on wikipedia or crypto101

SHA-1 was a really huge improvement when introduced in 11gR1. The old self-made algorythm has been a torture for Oracle Security team. It has been published on Internet. Extremly powerfull password cracker can find your “not-too-long” password in notime. In 11g, Oracle removed the 10g String from the DBA_USERS view. I wrote about this here. It remained on the base table, USER$ until 12cR2. Now Oracle completly removed it by default in 12cR2. 10 years after SHA1 was introduced in 11gR1.

Still. You are the dba. You want to migrate your database not to chase passwords.

You could edit your sqlnet.ora to allow 10g strings.


SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

This works


SQL> sho parameter sec_case_sensitive_logon
NAME                      VALUE
------------------------- -----
sec_case_sensitive_logon  FALSE

SQL> CREATE USER "U" IDENTIFIED BY 
     VALUES 'DC6F2B33D359A95B';
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> conn u/abcdefg@pdb01
Connected.
SQL> conn u/AbCdEfG@pdb01
Connected.

If you have SQLNET.ALLOWED_LOGON_VERSION_SERVER=11, then you could keep the same setting of sec_case_sensitive_logon as in 11g. I recommmend the default (true).

But, that’s it ? Wellllllll… not sure.

In 12.1

 
SQL> select dbms_metadata.get_ddl('USER','U') from dual
DBMS_METADATA.GET_DDL('USER','U')
----------------------------------
   CREATE USER "U" IDENTIFIED BY 
     VALUES 'DC6F2B33D359A95B'

Let’s try in 12.2


SQL> select dbms_metadata.get_ddl('USER','U') from dual
DBMS_METADATA.GET_DDL('USER','U')
------------------------------------
CREATE USER "U" IDENTIFIED BY VALUES 
  'S:0000000000000000000000000000000
00000000000000000000000000000'

While this is a perfectly working syntax, and IDENTIFIED BY VALUES is not supported *. So if create that user, then, obviously, the 10G string is lost.

Well, unsupported feature then? Hmm, yes. You should never have used identified by values.

Ok, so if I refresh my Test database with Production data, how can I save test passwords? You can’t. At least not in a supported way by using identified by values.

You could something like :


SQL> select 'alter user "'||name||
       '" identified by values '''||
       password||''';' txt
     from user$, v$instance 
     where version > '12.2' 
     and spare4 is null  
     and regexp_like(PASSWORD,'[A-F0-9]{16}');

txt
-----------------------------------
alter user "U" identified by values
   'DC6F2B33D359A95B';

This may work. In 12.2.0.1. Maybe not in 13. Maybe not in 12.2.0.1.0PSU July. It’s not supported. If it does not work, it is NOT-A-BUG.

The SHA1 was introduced 10 years ago in Oracle 11gR1. If you have not changed your password in ten years, and you don’t know how many employees and ex-employees know this password, and it is case-insensitive, and its “pseudo-hashing-algorythm” has been hacked for maybe two decades, yet, I can only warmly recommend to change those accounts passwords !

Again: so if I refresh my Test database with Production data, how can I save test passwords ?
If I were you I would design a better system for login. For human users, use global users and an Identity solution, like Oracle Universal directory. For technical account, build yourself a tool that generate a random password, and update the user and credentials, something like


select
substr(
REGEXP_REPLACE(
UTL_RAW.cast_to_varchar2(
SYS.DBMS_CRYPTO.RANDOMBYTES (1024)
) ,'[^!#-~]'
),
1,
20
) PW
from dual;
PW
--------------------
%K0w(^%UN.B82Yjjfu{?

And use it to reset your technical user and to configure your application credentials.

* Note 554605.1: the 'IDENTIFIED BY VALUES' clause on a CREATE/ALTER USER statement is not officially documented, and is intended purely for internal

System trigger doesn't save some information

Tom Kyte - Mon, 2017-05-22 09:26
Hello everyone. I have a problem with a simple trigger system. Here's my code <code>CREATE TABLE COLLEGATO( ID_SESSIONE VARCHAR(30), NOME_UTENTE VARCHAR(30), IDENTIFICAZIONE VARCHAR(30), INDIRIZZO_IP VARCHAR(30), HOST_MACC VARCHAR(40), ...
Categories: DBA Blogs

How to spool each record of a sql query into different files

Tom Kyte - Mon, 2017-05-22 09:26
I have a requirement where i need to spool reach record into a different file Eg. Select * from Test_Table Output :- ABC XYZ QWE ASD .... and so on So need to generate individual files which will have the output saved File1 : A...
Categories: DBA Blogs

Accessing HTTPS link from the plsql block

Tom Kyte - Mon, 2017-05-22 09:26
Hello There, I hope you're doing well. I'm trying to access a HTTPS link but I'm facing some errors DECLARE req UTL_HTTP.REQ; resp UTL_HTTP.RESP; name VARCHAR2(256); value VARCHAR2(1024); BEGIN UTL_HTTP.SET_PROXY('proxy...
Categories: DBA Blogs

Data migration from AIX 6.1 / 11.2.0.3 to CentOS 6.9 / 11.2.0.3

Tom Kyte - Mon, 2017-05-22 09:26
Hello, I am planning to migrate DB data from ORACLE 11gR2 on AIX 6.1 to ORACLE 11gR2 on CentOS 6.9. Many gurus told me using ASM and RMAN but my site is not using both of them. By the different endian format, I think I need to use transporta...
Categories: DBA Blogs

resoution for ORA-01476: divisor is equal to zero

Tom Kyte - Mon, 2017-05-22 09:26
Hi Sir, could you please provide a solution for the below error.ora 01476 divisor is equal to zero. Table structure:emp empno| curr_bal| prev_bal |prev_to_prev_bal 1 | 46 |47 |49 2 |18 |19...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator