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.
2 Comments | Filed in Oracle | Tags: sql, twitter, yql
