Please Request a Reasonable Conversion
Here is something you need to be aware of when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements in PL/SQL.
Let’s execute this simple anonymous PL/SQL block:
eddie@db11gr2> DECLARE
2 l_string VARCHAR2 (4000);
3 l_rc sys_refcursor;
4 BEGIN
5 l_string := RPAD (' ', 4000);
6 OPEN l_rc FOR 'select :1 from dual' USING l_string;
7 EXECUTE immediate 'select :1 from user_objects where rownum = 1'
8 INTO l_string USING l_string;
9 END;
10 /
PL/SQL procedure successfully completed.
The EXECUTE IMMEDIATE and OPEN FOR statements are used with the USING clause. USING supplies a bind argument for the SQL string. In this example the value of the bind argument is a 4,000 character string.
Now, let’s pass a string greater than 4,000 characters to the OPEN FOR statement:
eddie@db11gr2> DECLARE
2 l_string VARCHAR2 (4001);
3 l_rc sys_refcursor;
4 BEGIN
5 l_string := RPAD (' ', 4001);
6 OPEN l_rc FOR 'select :1 from dual' USING l_string;
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 6
Oops, got an error. Let’s also try the EXECUTE IMMEDIATE statement with a string greater than 4,000 characters:
eddie@db11gr2> DECLARE
2 l_string VARCHAR2 (4001);
3 BEGIN
4 l_string := RPAD (' ', 4001);
5 EXECUTE immediate 'select :1 from user_objects where rownum = 1'
6 INTO l_string USING l_string;
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 5
Got the same error: “ORA-01460: unimplemented or unreasonable conversion requested”.
How about if we pass a NULL:
eddie@db11gr2> DECLARE
2 l_rc sys_refcursor;
3 BEGIN
4 OPEN l_rc FOR 'select :1 from dual' USING NULL;
5 END;
6 /
OPEN l_rc FOR 'select :1 from dual' USING NULL;
*
ERROR at line 4:
ORA-06550: line 4, column 45:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 4, column 3:
In this case we get “PLS-00457: expressions have to be of SQL types”.
Well, there is a restriction on what values you can bind: When binding values to dynamic SQL, only SQL datatypes are supported. You can bind strings, numbers, dates, collections, LOBs, XML documents… However, you cannot bind values having a PL/SQL specific datatype such as Booleans, associative arrays and varchar2 values greater than 4000.
Make sure you keep the above restriction in mind when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements.
Source and resources:
- EXECUTE IMMEDIATE Statement
- Unsupported Data Type in Native Dynamic SQL
- OPEN FOR Statement
- Discussions about the ORA-01460 error
See How Easily You Can Post to Twitter From an Oracle DB
Posting to Twitter from inside an Oracle database is not something new (OraTweet, ORA_Tweet). However, what if you could post to Twitter by simply executing a SELECT statement without using any custom PL/SQL code?
Try the following in your SQL*Plus. It will ask you for your Twitter username and password and the status message that you want to post:
eddie@db11gr2> set define !
eddie@db11gr2> set verify off
eddie@db11gr2> set sqlterminator off
eddie@db11gr2> SELECT HTTPURITYPE(
2 UriFactory.escapeUri(
3 'http://query.yahooapis.com/v1/public/yql?q=
4 USE "http://awads.net/yql/twitter.xml" as tw_t;
5 INSERT INTO tw_t (status, username,password)
6 VALUES("!status","!!username","!!password")
7 &format=json
8 &env=store://datatables.org/alltableswithkeys&format=xml&callback=cbfunc')).getclob()
9 json_response
10 FROM dual
11 /
Enter value for status: Tweeting from SQL*Plus.
Enter value for username: sqlyql
Enter value for password: sqlyqlpw
JSON_RESPONSE
--------------------------------------------------------------------------------
cbfunc({"query":{"count":"1","created":"2010-05-02T02:02:14Z","lang":"en-US","re
sults":{"status":{"created_at":"Sun May 02 02:02:14 +0000 2010","id":"1322057475
5","text":"Tweeting from SQL*Plus.","source":"<a href=\"http://apiwiki.twitter.c
om/\" rel=\"nofollow\">API</a>","truncated":"false","in_reply_to_status_id":null
,"in_reply_to_user_id":null,"favorited":"false","in_reply_to_screen_name":null,"
user":{"id":"139255376","name":"sqlyql","screen_name":"sqlyql","location":null,"
description":null,"profile_image_url":"http://s.twimg.com/a/1272578449/images/de
fault_profile_1_normal.png","url":null,"protected":"false","followers_count":"1"
,"profile_background_color":"9ae4e8","profile_text_color":"000000","profile_link
_color":"0000ff","profile_sidebar_fill_color":"e0ff92","profile_sidebar_border_c
olor":"87bc44","friends_count":"0","created_at":"Sun May 02 01:59:28 +0000 2010"
,"favourites_count":"0","utc_offset":null,"time_zone":null,"profile_background_i
mage_url":"http://s.twimg.com/a/1272578449/images/themes/theme1/bg.png","profile
_background_tile":"false","notifications":"false","geo_enabled":"false","verifie
d":"false","following":"false","statuses_count":"1","lang":"en","contributors_en
abled":"false"},"geo":null,"coordinates":null,"place":null,"contributors":null}}
}});
Elapsed: 00:00:01.92
The above is made possible using HTTPURITYPE and YQL, the Yahoo! Query Language. Awesome combination!
Note that on June 30, 2010 Twitter will be shutting off basic authentication on the Twitter API. After that date, the above will stop working unless the YQL query is modified to use OAuth.
Are you on Twitter? Follow me at eddieawad and the rest of the Oracle Tweeple at eddieawad/oracle.
Filed in Oracle with 2 Comments | Tags: sql, twitter, yqlAre You Soft on Your Deletes?
Frustrated with soft deletes in SQL, Reeditor writes:
In the past, I’ve added a deleted_at (datetime) column and appended “WHERE deleted_at IS NULL” to every query involving that table. But it’s a total pain in the ass, and it’s complicated as shit: there’s always a few queries that are missed, a few developers that forgot or didn’t know they had to check that the record wasn’t deleted, denormalized counts are hard to keep in sync, etc. etc. It always turns into an embarrassing fiasco rather than something you add once and then don’t have to worry about.
Good discussion followed which included a couple of interesting links: What is the best way to implement soft deletion? and The trouble with soft delete.
So, if there is a business need to retain deleted records, do you go soft or hard?
Filed in Oracle with Comments Off | Tags: sqlSQLSaturday Open for Registration
On the weekend of May 22, 2010, the Portland Development community is coming together in a way never experienced before.
Using the University of Portland campus, SQLSaturday, Portland Code Camp, and Portland Bar Camp are combining and coordinating efforts to bring 800-1000 regional technology professionals together for the opportunity to immerse themselves in seminars, presentations, group exploration, and networking. Participants will be able to engage in their preferred technology, as well as to 'sample' other options.
Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.
>> Click here to register.
Most sessions will be one hour and fifteen minutes (1:15), and a few 'deep dive' sessions will be longer (2.5 hours). The 'deep dive' sessions will be scheduled for 7:00 PM (after the snacks and refreshments.) Also, throughout the day, there may be opportunities for 30 minute 'SQL snack' sessions.
>> Click here to submit your presentation abstract.
I encourage all Oracle SQL and PL/SQL enthusiasts living in the Pacific Northwest to present in and/or attend this not-to-be-missed event.
SQL Injection Prevention Cheat Sheet
This article provides a set of simple techniques for preventing SQL Injection vulnerabilities. These techniques can be used with practically any kind of programming language with any type of database. There are other types of databases, like XML databases, which can have similar problems (e.g., XPath and XQuery injection) and these techniques can be used to protect them as well.More…
Oracle Database Limits You May Not Know About
The following is a list of database limits which are divided into four categories in addition to the PL/SQL compiler limits. It is an aggregation of 5 separate web pages from the Oracle Database 11g Release 2 documentation library. I put them all here on one page for convenience.
Datatype Limits
| Datatypes | Limit | Comments |
|---|---|---|
BFILE |
Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments | The maximum number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow. |
BLOB |
Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) |
The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000). |
CHAR |
Maximum size: 2000 bytes | None |
CHAR VARYING |
Maximum size: 4000 bytes | None |
CLOB |
Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) |
The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000). |
| Literals (characters or numbers in SQL or PL/SQL – more) | Maximum size: 4000 characters | None |
LONG |
Maximum size: 2 GB – 1 | Only one LONG column is allowed per table. |
NCHAR |
Maximum size: 2000 bytes | None |
NCHAR VARYING |
Maximum size: 4000 bytes | None |
NCLOB |
Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) |
The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000). |
NUMBER |
999…(38 9′s) x10125 maximum value -999…(38 9′s) x10125 minimum value | Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa) |
| Precision | 38 significant digits | None |
RAW |
Maximum size: 2000 bytes | None |
VARCHAR |
Maximum size: 4000 bytes | None |
VARCHAR2 |
Maximum size: 4000 bytes | None |
Logical Database Limits
| Item | Type of Limit | Limit Value |
|---|---|---|
GROUP BY clause |
Maximum length | The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block. |
| Indexes | Maximum per table | Unlimited |
| Indexes | Total size of indexed column | 75% of the database block size minus some overhead |
| Columns | Per table | 1000 columns maximum |
| Columns | Per index (or clustered index) | 32 columns maximum |
| Columns | Per bitmapped index | 30 columns maximum |
| Constraints | Maximum per column | Unlimited |
| Subqueries | Maximum levels of subqueries in a SQL statement | Unlimited in the FROM clause of the top-level query
255 subqueries in the WHERE clause |
| Partitions | Maximum length of linear partitioning key | 4 KB – overhead |
| Partitions | Maximum number of columns in partition key | 16 columns |
| Partitions | Maximum number of partitions allowed per table or index | 1024K – 1 |
| Rows | Maximum number per table | Unlimited |
| Stored Packages | Maximum size | PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code. |
| Trigger Cascade Limit | Maximum value | Operating system-dependent, typically 32 |
| Users and Roles | Maximum | 2,147,483,638 |
| Tables | Maximum per clustered table | 32 tables |
| Tables | Maximum per database | Unlimited |
Physical Database Limits
| Item | Type of Limit | Limit Value |
|---|---|---|
| Database Block Size | Minimum | 2048 bytes; must be a multiple of operating system physical block size |
| Database Block Size | Maximum | Operating system dependent; never more than 32 KB |
| Database Blocks | Minimum in initial extent of a segment | 2 blocks |
| Database Blocks | Maximum per datafile | Platform dependent; typically 222 – 1 blocks |
| Controlfiles | Number of control files | 1 minimum; 2 or more (on separate devices) strongly recommended |
| Controlfiles | Size of a control file | Dependent on operating system and database creation options; maximum of 20,000 x (database block size) |
| Database files | Maximum per tablespace | Operating system dependent; usually 1022 |
| Database files | Maximum per database | 65533
May be less on some operating systems
Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance |
| Database extents (more) | Maximum per dictionary managed tablespace | 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier) |
| Database extents | Maximum per locally managed (uniform) tablespace | 2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier) |
| Database file size | Maximum | Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks |
MAXEXTENTS |
Default value | Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter |
MAXEXTENTS |
Maximum | Unlimited |
| Redo Log Files | Maximum number of logfiles | Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement
Control file can be resized to allow more entries; ultimately an operating system limit |
| Redo Log Files | Maximum number of logfiles per group | Unlimited |
| Redo Log File Size | Minimum size | 4 MB |
| Redo Log File Size | Maximum Size | Operating system limit; typically 2 GB |
| Tablespaces | Maximum number per database | 64 K
Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file |
| Bigfile Tablespaces | Number of blocks | A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks. |
| Smallfile (traditional) Tablespaces | Number of blocks | A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks. |
| External Tables file | Maximum size | Dependent on the operating system.An external table can be composed of multiple files. |
Process and Runtime Limits
| Item | Type of Limit | Limit Value |
|---|---|---|
| Instances per database | Maximum number of cluster database instances per database | Operating system-dependent |
| Locks | Row-level | Unlimited |
| Locks | Distributed Lock Manager | Operating system dependent |
| SGA size | Maximum value | Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems |
| Advanced Queuing Processes | Maximum per instance | 10 |
| Job Queue Processes | Maximum per instance | 1000 |
| I/O Slave Processes | Maximum per background process (DBWR, LGWR, etc.) | 15 |
| I/O Slave Processes | Maximum per Backup session | 15 |
| Sessions | Maximum per instance | 32 KB; limited by the PROCESSES and SESSIONS initialization parameters |
| Global Cache Service Processes | Maximum per instance | 10 |
| Shared Servers | Maximum per instance | Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance |
| Dispatchers | Maximum per instance | Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance |
| Parallel Execution Slaves | Maximum per instance | Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance |
| Backup Sessions | Maximum per instance | Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance |
PL/SQL Compiler Limits
| Item | Limit |
|---|---|
| bind variables passed to a program unit | 32768 |
| exception handlers in a program unit | 65536 |
| fields in a record | 65536 |
| levels of block nesting | 255 |
| levels of record nesting | 32 |
| levels of subquery nesting | 254 |
| levels of label nesting | 98 |
| levels of nested collections | no predefined limit |
magnitude of a PLS_INTEGER or BINARY_INTEGERvalue |
-2147483648..2147483647 |
| number of formal parameters in an explicit cursor, function, or procedure | 65536 |
| objects referenced by a program unit | 65536 |
precision of a FLOAT value (binary digits) |
126 |
precision of a NUMBER value (decimal digits) |
38 |
precision of a REAL value (binary digits) |
63 |
| size of an identifier (characters) | 30 |
| size of a string literal (bytes) | 32767 |
size of a CHAR value (bytes) |
32767 |
size of a LONG value (bytes) |
32760 |
size of a LONG RAW value (bytes) |
32760 |
size of a RAW value (bytes) |
32767 |
size of a VARCHAR2 value (bytes) |
32767 |
size of an NCHAR value (bytes) |
32767 |
size of an NVARCHAR2 value (bytes) |
32767 |
size of a BFILE value (bytes) |
4G * value of DB_BLOCK_SIZE parameter |
size of a BLOB value (bytes) |
4G * value of DB_BLOCK_SIZE parameter |
size of a CLOB value (bytes) |
4G * value of DB_BLOCK_SIZE parameter |
size of an NCLOB value (bytes) |
4G * value of DB_BLOCK_SIZE parameter |
Sources and Resources:
- Datatype Limits
- PL/SQL Program Limits
- Process and Runtime Limits
- Logical Database Limits
- Physical Database Limits
Yahoo Launches SQL Interface to Twitter
The new YQL set of tables for Twitter enables any developer to use simple SQL-like queries to retrieve and post Twitter data. For simple user queries, getting a user’s twitter profile data is as simple as something like “SELECT * FROM twitter.status WHERE id=’8036408424?;“.More…
Excel – Charting the Results of Oracle Analytic Functions
This is a somewhat complicated example that builds a couple of sample tables, uses a SQL statement with the Oracle analytic function LEAD submitted through ADO in an Excel macro, and then presents the information on an Excel worksheet. When the user clicks one of three buttons on the Excel worksheet, an Excel macro executes that then build charts using disconnected row sources – a disconnected ADO recordset is used to sort the data categories before pushing that data into the charts that are built on the fly.More…
36K “Oracle lang:sql” Scripts
In case you're bored, here are 36,000+ Oracle SQL scripts available for your reading pleasure through Google code search.
Filed in Google, Oracle with 2 Comments | Tags: search, sqlRewriting SQL Queries for Performance in 9 Minutes (Video)
Here is another video from Stephane Faroult about how you can improve query performance:
Filed in Oracle with 3 Comments | Tags: database, performance, sql, video
