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.
{ 5 comments }
Exceptions
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.
{ 22 comments }
“ORA-00904: invalid identifier” is a familiar error to Oracle developers. It occurs, for example, when you refer to a table column that does not exist.
What’s worse than an error that shows up when you don’t expect it? An error that does NOT show up when you totally expect it.
Here’s a puzzle for you to solve. You’re given two tables – EMPL and DEPT – in which column EMP_DEPT_ID of table EMPL references DEPT_ID of table DEPT.
SQL> desc dept Name Null? Type ----------------- -------- ------------ DEPT_ID NUMBER(2) DEPT_NAME VARCHAR2(6) SQL> desc empl Name Null? Type ----------------- -------- ------------ EMP_ID NUMBER(2) EMP_NAME VARCHAR2(6) EMP_DEPT_ID NUMBER(2)
Note that the foreign key column names in the two tables are not identical. The column is called DEPT_ID in table DEPT, EMP_DEPT_ID in table EMPL.
{ 4 comments }
Consider a PL/SQL call to a function which returns a user-defined type (UDT) – say, a SQL Type object. This works well as long as the called function resides in the same database as the calling PL/SQL, but if the called function is in a remote database, the PL/SQL call via DBLink end ups with the following error:
Error: PLS-00453: remote operations not permitted on object tables or user-defined type columns
This has often led to issues discovered too late for developers, who successfully tested function calls over multiple schemas simulating remote calls, but when the function databases were separated in later stages of testing, the PL/SQL call no longer worked.
{ 1 comment }
If you have got Oracle Total Recall enabled on a table, and now want to truncate or drop the table, you are in for a problem.
SQL> truncate table customer;
truncate table customer
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL> drop table customer;
drop table customer
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
Clearly enough, Oracle does not allow you to do so because it has flashback archive enabled.
SQL> -- Check history tracking SQL> select flashback_archive_name 2 from dba_flashback_archive_tables 3 where table_name = 'CUSTOMER'; FLASHBACK_ARCHIVE_NA -------------------- TOTALRECALL_FA
How do you get around this problem?
{ 0 comments }
We’ve seen the easy working of MULTISET operations on nested tables of simple types (i.e. collections of text, numbers). And we’ve seen the problem with extending MULTISET operations to nested tables of complex types (i.e. those based on OBJECTs with multiple attributes).
PL/SQL code when run with MULTISET operations on complex collections throws up the error:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_<operation type>'
There is a workaround though, which lets us use MULTISET operations successfully with complex types. Here’s how.
{ 2 comments }
Sometimes you want to drop and recreate an Oracle table, and are in a fix because when you issue the DROP command Oracle responds with ORA-02449:
SQL> drop table agreement;
drop table agreement
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
Disabling constraints has no effect on ORA-02449, the only resolution is to drop constraints. Which presents another dilemma: the error does not specify which foreign keys are causing this problem, and from which tables.
Luckily, there is a quick workaround to ORA-02449, provided you are ready to heed the risks of the approach along with it.
{ 2 comments }
The SQL WITH clause is similar in concept to a function definition in procedural code. In a function, we factor the common code, put it all together at one place and call it as many times as needed in the main program. That’s precisely how we use the WITH clause in SQL – factor out the common subquery, put it all together at one place and call it as many times as needed in the main query.
BUT there is a difference.
{ 2 comments }
CASE is a smarter rewrite for IF-THEN-ELSE, we said. It is for sure, but there is a difference in the way the ELSE part of it is handled.
Compare the code units below, one using CASE WHEN the other using IF-ELSIF. Both are identical in logic – two defined conditions, no ELSE path.
{ 0 comments }
Look at this small piece of code which inserts a row into the EMP table.
[click to continue…]
{ 6 comments }
