Ugly formatting in SQL*Plus
Increase the linesize, e.g SET LINESIZE 32000 or use SET WRAP OFF (but this will truncate long values)
oracle10g
Pivoting rows into columns dynamically in Oracle
Oracle 11g provides a PIVOT operation that does what you want. Oracle 11g solution select * from (select id, k, v from _kv) pivot(max(v) for k in (‘name’, ‘age’, ‘gender’, ‘status’) (Note: I do not have a copy of 11g to test this on so I have not verified its functionality) I obtained this solution … Read more
ORA-01652 Unable to extend temp segment by in tablespace
I found the solution to this. There is a temporary tablespace called TEMP which is used internally by database for operations like distinct, joins,etc. Since my query(which has 4 joins) fetches almost 50 million records the TEMP tablespace does not have that much space to occupy all data. Hence the query fails even though my … Read more
Oracle “ORA-01008: not all variables bound” Error w/ Parameters
The ODP.Net provider from oracle uses bind by position as default. To change the behavior to bind by name. Set property BindByName to true. Than you can dismiss the double definition of parameters. using(OracleCommand cmd = con.CreateCommand()) { … cmd.BindByName = true; … }
Oracle Date TO_CHAR(‘Month DD, YYYY’) has extra spaces in it
Why are there extra spaces between my month and day? Why does’t it just put them next to each other? So your output will be aligned. If you don’t want padding use the format modifier FM: SELECT TO_CHAR (date_field, ‘fmMonth DD, YYYY’) FROM …; Reference: Format Model Modifiers
How to split array list into equal parts?
This should give you all your parts: int partitionSize = 1000; List<List<Integer>> partitions = new LinkedList<List<Integer>>(); for (int i = 0; i < originalList.size(); i += partitionSize) { partitions.add(originalList.subList(i, Math.min(i + partitionSize, originalList.size()))); }
Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
If your 200+ packages work as intended with “old fashioned” syntax, let it be. SQL will not start to perform better after migration to ANSI syntax – it’s just different syntax. All that being said, ANSI syntax is cleaner – you are not going to normal join if you forget (+) in some multi-column outer … Read more
What’s the difference between pls_integer and binary_integer?
Historical reasons. They used to be different before 10g: On 8i and 9i, PLS_INTEGER was noticeably faster than BINARY_INTEGER. When it comes to declaring and manipulating integers, Oracle offers lots of options, including: INTEGER – defined in the STANDARD package as a subtype of NUMBER, this datatype is implemented in a completely platform-independent fashion, which … Read more
Search for a particular string in Oracle clob column
Use dbms_lob.instr and dbms_lob.substr, just like regular InStr and SubstStr functions. Look at simple example: SQL> create table t_clob( 2 id number, 3 cl clob 4 ); Tabela zosta│a utworzona. SQL> insert into t_clob values ( 1, ‘ xxxx abcd xyz qwerty 354657 [] ‘ ); 1 wiersz zosta│ utworzony. SQL> declare 2 i number; … Read more
How do I clear oracle execution plan cache for benchmarking?
Peter gave you the answer to the question you asked. alter system flush shared_pool; That is the statement you would use to “delete prepared statements from the cache”. (Prepared statements aren’t the only objects flushed from the shared pool, the statement does more than that.) As I indicated in my earlier comment (on your question), … Read more