Over the past while I've been working a lot with text strings. Some of these have been short in length like tweets from Twitter, or longer pieces of text like product reviews. Plus others of various lengths.
In all these scenarios I have to break up the data into individual works or Tokens.
The examples given below illustrate how you can take a string and break it into its individual tokens. In addition to tokenising (more...)
In my previous blog post
, I took a look at the new APEX_JSON package that ships with Apex 5.0 and its capabilities for parsing JSON.
In this blog post, I am going to look at how the APEX_JSON package can be used to generate JSON from data in your database using PL/SQL.
There are multiple ways of creating JSON output using the APEX_JSON package. There are several overloaded write() procedures which can output (more...)
As I mentioned a year ago
, it looks like Apex 5.0 will include a new package called APEX_JSON for parsing and generating JSON. In this blog post, I will take a quick look at the parsing, and the next blog post will be about generating JSON output.
Since Apex 5.0 is still in Early Adopter (beta) mode and not yet released for download, we can run a query against the data dictionary (more...)
Many tools offer Public APIs, which expose some functionality to other tools. SQLTXPLAIN contains also some Public APIs. They are provided by package SQLTXADMIN.SQLT$E. I would say the most relevant one is XTRACT_SQL_PUT_FILES_IN_DIR. This blog post is about this Public API and how it can be used by other tools to execute a SQLT XTRACT from PL/SQL instead of SQL*Plus.
Imagine a tool that deals with SQL statements, and with the click of a button it invokes (more...)
Do you need to create PDF reports from PL/SQL?
There are a number of options:
- Anton Scheffer wrote a PL/SQL package called AS_PDF3 that covers all the basics: Different fonts (including TrueType), images, and tables.
- There's the commercial PL/PDF package that has more features, such as the ability to use existing PDF documents as templates for new documents, compression, encryption, and more. There's also an extra Toolkit component which allows you to extract pages from (more...)
I have been awfully quiet on my blog lately. I think that is because I have been busy with other things, like my garden and stuff like that. There are some ‘techie’ thing I have done in the meantime, though,
I have submitted a couple of abstracts for Tech14. Hope at least one of them gets selected. I really like presenting and if it is in a different country, that is just a plus. That (more...)
DBMS_OUTPUT.PUT_LINE is always a bit of a problem when you have strings longer than 80 characters in length, which occurs more frequently with Oracle Database 12c. An example of managing output occurs when you want to print a string with embedded line breaks. My solution is the following
I tested this on Oracle 11.2. If you update a table in SQL*Plus then update it again with a different value in a PL/SQL block, the second update replaces the first:
SQL> create table tab1 as
2 select 1 col1 from dual
SQL> update tab1 set col1 = 2
1 row updated.
firstname.lastname@example.org > create procedure p is begin null; end this_does_not_compile;
Warning: Procedure created with compilation errors.
email@example.com > show errors
Errors for PROCEDURE P:
1/32 PLS-00113: END identifier 'THIS_DOES_NOT_COMPILE' must match 'P'
at line 1, column 11
firstname.lastname@example.org > REM expexted
email@example.com > create function f return number is begin return null; end this_does_not_compile;
Warning: Function created with compilation errors.
firstname.lastname@example.org > (more...)
This is an idea for an enhancement to the PL/SQL syntax.
If I have the following declaration:
I can do this:
INSERT INTO mytable VALUES in_record;
I can also do this:
UPDATE mytable SET ROW = in_record WHERE ...;
I can do this, as long as I list each and every column, in the right order:
INSERT INTO mytable VALUES in_record
RETURNING cola, colb, colc INTO out_record;
But I (more...)
Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.
The regular install script for Logger looks something like the following (parts removed and table names are changed):
There is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only the EMP and DEPT tables to protect the suspects and maybe innocent.
The function defined is something like this:
In previous posts about caching mechanism of determinstic functions I wrote that cached results are kept only between fetch calls, but there is one exception from this rule: if all function parameters are literals, cached result will not be flushed every fetch call.
Little example with difference:
SQL> create or replace function f_deterministic(p varchar2)
2 return varchar2
7 return p;
SQL> set (more...)
For a project we are currently working on, we needed to generate, and send a Word 2010 document to the client. The document was generated by a great PL/SQL document generation tool called Doxxy, and was send to the client using the wpg_docload package. This is a standard Oracle pl/sql package that can be used to download files, BLOBs and BFILEs.
Before the download, we set the Content-type in the http header as follows :
Craig Shallahamer wrote excellent article “When is v$sesstat really updated?”.
And my today post just a little addition and correction about the difference of updating ‘Db time’ and ‘CPU used by this session’ statistics.
In this test I want to show that the statistics will be updated after every fetch call.
I have set arraysize=2, so sql*plus will fetch by 2 rows:
-- Result will be fetched by (more...)
PL/SQL is procedural language, that's very useful when you work with Oracle Database. As DBA, you might have to write PL/SQL to do some tasks. Developer on Oracle Database must know about PL/SQL.
This post I mention a book title - Oracle PL/SQL Programming
by Steven Feuerstein (@stevefeuerstein
), Bill Pribyl
. This book is a new edition that covers PL/SQL on Oracle Database 12c. Readers can use examples in a book for practice and get (more...)
I have reviewed the latest edition of Oracle PL/SQL Programming. This is not a book, at least for me, that I would read front to back. I use it mostly as a reference, but the great thing about this book is that you can also use this book to learn programming PL/SQL from scratch.
The book is nicely ordered into different parts, like Program structure and how to use SQL in PL/SQL (which by the (more...)