A SQL script can contain one or more SQL statements or PL/SQL blocks, to do a logical unit of work. SQL scripts can be saved as files.
What you have: a relational table in Oracle.
What you want: data extracted from said relational table in JSON format.
How do you do it?
An approach for comparing two tables for differences in data, using a pure SQL solution:
In a recent project, a shadow table had to be compared periodically with its main source table to identify the differences between the two tables.
The nature of differences fell into one of these buckets:
Comparing two tables for differences (INS/UPD/DEL) needed a different solution from a simple MERGE SQL statement. Here an upsert was not to be executed, only the nature of differences to be identified.
Oracle partitioning features in 11G brought with it a beautiful new twist to range partitioning — the ability to create partitions on-the-fly.
Hello range interval partitioning.
I recently upgraded Oracle XE from 10G to 11G, and found that none of the PL/SQL code using UTL_HTTP was working after upgrade.
The code failed with the error:
declare * ERROR at line 1: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 47
Oracle 10G used to be happy as long as the user running network packages like UTL_HTTP had execute permission on the package. Oracle 11G and above are not so easy to please (and rightly so!) — they enforce extra security, which means you need more access control configuration to get this working.
You can call a web service from PL/SQL using the Oracle-supplied package UTL_HTTP. Here’s a demo of calling a public web service that returns latest city weather by ZIP.
A simple example of using UTL_FILE in PLSQL to create a file and write into it. Plus, the common ORA errors you encounter with the use of UTL_FILE, and how to resolve them.
How would you delete old data from a database schema so that it retains only the current year’s rows in all the tables in the schema, and the rest of the data is erased?
The last post showed a workaround for the error “ORA-02449: unique/primary keys in table referenced by foreign keys“, which blocks any attempt to drop an Oracle table if there are foreign keys from other tables referring to it.
The caveat: if the dropped table has to be recreated, the dropped foreign keys must be recreated as well.
How will you determine which foreign keys are to be recreated, and how will you quickly create them? Read on for an easy solution.
In the last post, we saw a neat way to implement auto-increment functionality in an Oracle table. The auto-incremented column gets its value populated in the background, without the issuer of the insert statement even getting to know about it.
BUT – what if the issuer of the insert statement does want to know about it?
You might want to use the current inserted row’s id, maybe for a further transaction in related tables, or maybe for tracing/logging purposes. The implementation is hidden, which means that you don’t know directly the value of the generated ID.
Oracle gives you a number of ways to know the name of the database you are connected to, from inside a SQL*Plus session. Here are three ways to find out your Oracle database name.
SQL> select name from V$database; NAME --------- XE
The above will work only if your login has access to V$DATABASE. This is generally accessible to DBA logins only. For non-DBA logins, you may need to grant SELECT on V$ views.
In case access to V$DATABASE cannot be granted to you, use one of the two publicly accessible methods below.