Feed aggregator

AskTom's print_table - SQLCL version

Kris Rice - Fri, 2016-06-10 15:03
There was an idea logged over on community.oracle.com to add AskTom style print_table to sqlcl. Here's how easy it is now that there's scripting in sqlcl.  This is a simple js file that loops over the rows and columns and prints them.  When using the executeReturnListofList the first row is the name of the columns for reference.  With that data there it's quite simple to customize any output.

SQLCL - Blob loading from a file

Kris Rice - Fri, 2016-06-10 15:03
There is a ton of ways having new scripting in sqlcl can help.  One example is a simpler way to load a local file into a BLOB in a column. Prior to this the only options I know of would be to write custom code or move the files to the server and use utl_file or xdb or some combination of things like this. Enter SQLCL and javascript method #1. Here's a simple script which loads a local file

SQLcl - Oct 13th Edition

Kris Rice - Fri, 2016-06-10 15:03
Another build of SQLCL is out.  This one has lots of great things in it.  There's fix in there to work with Cygwin for the unlucky people that are still on windows. Now the new stuff:  SCRIPT This new command can radically change things you can do in the tool.  The script command runs a script that can be any language that is JSR-223 enabled.  The best list I can find is here: https://

SQLcl - Oct 5th Edition

Kris Rice - Fri, 2016-06-10 15:03
It was time for another push of the new sql command line , sqlcl.  Here's the changes in the latest build.  There are a number of bugs fixed relating to connecting to idle databases, alias feedback, and picking up the $ORACLE_HOME's jdbc driver when available.  There is only a couple functional changes. First is that now the jdbc connect string itself can be specified.  This opens up a number

SQLCL - More secure, now with REST !

Kris Rice - Fri, 2016-06-10 15:03
A new SQLCL build was just posted go grab it and kick the tires.  There are well over 100 bug fixes in there so it's better than ever.  Also there's some new things. More Secure    Imagine you have an api that is called and a password or something is used in the parameters.  We use exec MY_API(...)  and it works just fine.  However consider if someone with access to v$sql they just got

SQLcl , yet again

Kris Rice - Fri, 2016-06-10 15:03
By the Numbers There's a new SQLcl out for download.  In case, there are too many to keep track of the build numbers are quite easy to tell if you have the latest.  The build posted today is  sqlcl-4.2.0.15.167.0827-no-jre.zip Here's what we are doing 4.2.0 <-- doesn't matter at all 15     <- year 167   <- day in julian 0827 <- time the build was done So yes, this build was done today at 8am

Repeating another SQLCL EA release

Kris Rice - Fri, 2016-06-10 15:03
    It's been a month and a lot of bugs have been fixed in that time and new features added. That adds up to time for another EA for our new sqlcl.  The third sqlcl EA is out now and can be found here on the SQL Developer 4.1 download page.     Not sure what this sqlcl thing is ? Check here. LOAD:  Making CSV loading to the db very easy Here's a very quick example of the possibilities.

3 new things about sdsql

Kris Rice - Fri, 2016-06-10 15:03
New Name ! The first is a new name this EA it's named sqlcl for sql command line.  However, the binary to start it up is simply sql.  Nothing is easier when you need to run some sql than typing 'sql' and hitting enter. #./sql klrice/klrice@//localhost/orcl SQLcl: Release 4.1.0 Beta on Fri Jan 30 12:53:05 2015 Copyright (c) 1982, 2015, Oracle. All rights reserved. Connected to: Oracle

SDSQL - Output Formats

Kris Rice - Fri, 2016-06-10 15:03
   In SQL Developer, Jeff has shown in many tips & tricks talks and here on his blog how to get data from any select into a few formats.  The most common I would say is csv.   Nothing here has changed. Well, almost.  What will be new is the html format is getting a facelift and a json format is being added neither are in the version of sdsql or sql developer that are on otn yet     As expected

SDSQL - Pre and Post Commands

Kris Rice - Fri, 2016-06-10 15:03
  This is in the process of evolving but should be enough for anyone to give it a whirl.  The idea is that for any given statement you may want to run something before and possible after.   Here's a trivial example.  I'm issuing 2 statements 1) a drop table and 2) creating a table of select * from v$mystat before each command.  Then after I'm doing sql that shows me the changes from that point

Aliases with sdsql and simpler CTAS

Kris Rice - Fri, 2016-06-10 15:03
First, we just put up a new build of sdsql.  Go get it or the last thing here will not work.   SQL is a great and verbose language so there's many ways to shorten what we have to type.  As simple as a view or saving a script to call later with @/path/to/sessions.sql  SDSQL is taking it a step further and we added aliases to the tool.  Almost as if right on queue, John asked if we could add them.

DIY SQCL Commands

Kris Rice - Fri, 2016-06-10 15:03
As mentioned once or twice or 100 times, sqlcl exposes javascript scripting with nashorn to make things very scriptable.  To learn more on Nashorn itself there's a lot of great write ups such as http://www.oracle.com/technetwork/articles/java/jf14-nashorn-2126515.html  So far, the scripting examples have been along the lines of conditional or looping of existing sqlcl commands. Here's an

SQLCL Monitoring itself with Longops

Kris Rice - Fri, 2016-06-10 15:03
Longops is a great way to monitor things that take some time to do work.  There's an easy example of using longops on oracle-base here.  I borrowed the script from there and put it into a file named my_slow_thing.sql.  Now here's a nice example of what's possible with sqlcl. The easy way to using this is add it to your login.sql which is what I did. Of course the script could run at anytime

Assertions in a future Oracle release

Jeff Moss - Fri, 2016-06-10 14:34

I just found this link on OTN to vote for including assertions in a future release of the Oracle database.

A great idea – please vote for it.

One of the most important votes this month…well, I do live in England! Smile

Create Temporary Tables in Oracle

Learn DB Concepts with me... - Fri, 2016-06-10 13:33

Global Temporary Tables in Oracle

Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running multiple DML operations. For example, consider the following:

A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.

During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.

This statement creates a temporary table that is transaction specific:

NOTE : Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

*********************************************************************************
HERE is an example to create a global temporary table with on commit DELETE ROWS :
*********************************************************************************

sql>  CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;
      
      
    
sql>  insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');

1 row inserted.


sql> select * from ADMIN_WORK_AREA;

commit;

Commit complete.

sql> select * from ADMIN_WORK_AREA;


NOTE: records in this temp table will be deleted upon commit. This is equivalent to truncating table on commit.

*********************************************************************************
HERE is an example to create a global temporary table with on commit PRESERVE ROWS :
*********************************************************************************


sql>  CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT PRESERVE ROWS;
      
            
sql>  insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');

1 row inserted.

1 row inserted.

sql>  select * from ADMIN_WORK_AREA;

commit;

Commit complete.

sql>  select * from ADMIN_WORK_AREA;


NOW exit the session and login back and select the table.

sql>  select * from ADMIN_WORK_AREA;

table is empty

NOTE: records (rows) in this temp table will be deleted upon session exit only, as long as you are using same session you can see these rows. 
This is equivalent to truncating table on session exit.

Categories: DBA Blogs

Inserting Data into table with DML Error Logging (catching errors whiles inserting data into table)

Learn DB Concepts with me... - Fri, 2016-06-10 10:29


Inserting Data with DML Error Logging:
When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.

DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements.


--------------------------------------------------------
--  DDL for Table ATEST1
--------------------------------------------------------


  CREATE TABLE "ATOORPU"."ATEST1"
   (    "ID" NUMBER constraint ATEST1_PK PRIMARY KEY,
    "TDATE" DATE,
    "AMOUNT" VARCHAR2(20 BYTE),
    "ORD_NO" NUMBER
   ) ;

--------------------------------------------------------
INSERT SOME VALUES
--------------------------------------------------------


Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (1,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (2,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (3,to_date('01-MAR-16','DD-MON-RR'),null,100);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (4,to_date('01-MAR-16','DD-MON-RR'),'100',200);

--------------------------------------------------------
CREATE ERROR LOG TABLE USING THE DBMS PACKAGE :
--------------------------------------------------------


EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('ATEST1', 'ERR_ATEST1');   -- ATEST1 source table and ERR_ATEST1 error log table


Error Logging Restrictions and Caveats
  • Oracle Database logs the following errors during DML operations:
  • Column values that are too large
  • Constraint violations (NOT NULL, unique, referential, and check constraints)
  • Errors raised during trigger execution
  • Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
  • Partition mapping errors

Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

--------------------------------------------------------
-- This will generate some insert errors
--------------------------------------------------------


INSERT INTO ATEST1
  SELECT ID+3,TDATE,AMOUNT,ORD_NO
  FROM ATEST1
  WHERE id > 1
  LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


Note:

If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.

--------------------------------------------------------
-- This will generate some update errors
--------------------------------------------------------


update ATEST1 set ID=3 where ID>5 LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


--------------------------------------------------------
LETS CHECK THE ERROR MESSAGES RECORDED:
--------------------------------------------------------


select * from ERR_ATEST1;
Categories: DBA Blogs

“Unstructured Data” – No such thing!

Jeff Moss - Fri, 2016-06-10 06:58

I keep hearing this term lately and I dislike it.

There is no such thing as Unstructured Data. All data has structure. If it didn’t have structure we wouldn’t be able to use it.

What about free text? Well, that’s just a single column value (stored in a CLOB in Oracle, for example) and the free text is, more often than not, on a row with other columns, such as identifiers and timestamps, i.e. yet more structure.

I think what people mean when they use this “marketing foam”TM term is “data that we have not yet defined the structure for”, but in order to use it at some later stage, the structure will need to be defined – that definition process doesn’t actually give the data structure in and of itself, it simply defines what that structure is, in order to be able to use it.

Interestingly, the Wikipedia article for Unstructured Data calls out the imprecise nature of the term:

The term is imprecise for several reasons:

  1. Structure, while not formally defined, can still be implied.
  2. Data with some form of structure may still be characterized as unstructured if its structure is not helpful for the processing task at hand.
  3. Unstructured information might have some structure (semi-structured) or even be highly structured but in ways that are unanticipated or unannounced.

In other words, it does have structure, but maybe we’ve not written it down, or the structure isn’t helpful to processing or is structured in ways we were not expecting – so what?…it’s still structured!

All of the above seem to me to support the view that all data does indeed have structure.

Enter your bets on Euro2016Challenge.eu now

Dimitri Gielis - Fri, 2016-06-10 05:55
Looks like I forgot to put on my blog also this year we created a bet site for the European Cup Soccer. Thanks to the people who reminded me to put this post on my blog :)

It all started in 2006 when I first created a site to promote Oracle Application Express (APEX). The site allowed to bet on the games of the World Cup. At that time everybody was using Excel files internally to put the scores together, enter the bets of the people... so I thought why not build it in APEX :) Oh the betting is for fun and honour ... so no money involved!

Since then every two years we have updated the site and enabled it again. Today almost 3000 people are playing with us. We changed a few times from url; first it was called DG Tournament, than the World Cup Challenge and this year it's the Euro 2016 Challenge.

So if you didn't put your bets in, there're a few hours left ... happy betting and that the best may win!


This year we (Belgium) have a chance to come far in the tournament, go go go Belgium! :)

Categories: Development

My Glamorous Life : What’s the point?

Tim Hall - Fri, 2016-06-10 04:19

Last night I put out a video of my trip home.

I’ve written about the My Glamorous Life series of videos before (here). A couple of people commented recently that “nothing happens in them” and they are “quite boring”. That’s the point really.

Temporary Post Used For Theme Detection (4a0f3a36-664d-47d9-93c7-24f2096e004d – 3bfe001a-32de-4114-a6b4-4005b770f6d7)

Jeff Moss - Fri, 2016-06-10 02:18

This is a temporary post that was not deleted. Please delete this manually. (31074fcb-66af-42a8-a620-ab6d94cd18a0 – 3bfe001a-32de-4114-a6b4-4005b770f6d7)

Pages

Subscribe to Oracle FAQ aggregator