How to limit the amount of characters returned in Oracle query
If I understood what you are saying, SUBSTR
returns only a portion of some string, so that would be
select substr(your_string, 1, 32760)
from ...
How do I limit the number of rows returned by an Oracle query after ordering?
You can use a subquery for this like
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.
Update:
To limit the result with both lower and upper bounds things get a bit more bloated with
select * from
( select a.*, ROWNUM rnum from
( <your_query_goes_here, with order by> ) a
where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
(Copied from specified AskTom-article)
Update 2:
Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
See this answer for more examples. Thanks to Krumia for the hint.
Oracle SQL Developer limit number of character of datatype char(5)
Your regex ^[A-Z]{2}[0-9]$
only allows (exactly) three characters. But a column defined as char(5)
gets padded with spaces if you provide less than three, so in reality 'AA1'
will be stored as 'AA1 '
which violates your regex.
Use char(3)
instead.
Better yet, never use char
at all (you just experienced one of the reasons why), use varchar(5)
or varchar(3)
both will work with your regex.
2 or 4 uppercase letters plus 1 digit whose value is between 1 and 4
That's not what your regex does. [A-Z]{2}
is exactly two uppercase letters. If you want to allow patterns of different lengths there is no way you can use char(5)
unless you also include the spaces in the regex:
create table test
(
groupcode char(5) check(
regexp_like(groupcode, '(^[A-Z]{2}[0-9] $)|(^[A-Z]{4}[0-9]$)', 'c'))
);
The above regex allows two uppercase characters followed by on digit and two spaces or four uppercase characters followed by one digit.
LISTAGG 4000 Character Limit - Result of string concatenation is too long
The best solution I know is posted somewhere in the Internet... You could probably just google for it. It basically consist of few steps:
- Creating a collection type to store each text value to concatenate
create or replace type string_array_t as table of VARCHAR2(4000);
- Creating a PL/SQL function which takes
string_array_t
as parameter and returns concatenated text asCLOB
:
create or replace function
string_array2clob(
p_string_array string_array_t
,p_delimiter varchar2 default ','
) RETURN CLOB IS
v_string CLOB;
BEGIN
-- inside is a loop over p_string_array to concatenate all elements
--
-- below is just a draft because in real you should use a while loop
-- to handle sparse collection and you should put some checks to handle not initialized collection
-- and other important cases
-- furthermore it's better to create additional varchar2 variable as a buffer
-- and convert that buffer to clob when it's full for a better performance
for indx in p_string_array.first..p_string_array.last loop
v_string := v_string || to_clob(p_string_array(indx) || p_delimiter);
end loop;
RETURN substr(v_string, 1, nvl(length(v_string),0) - nvl(length(p_delimiter),0));
END string_array2clob;
/
- Aggregate query as usual but using
cast
andcollect
instead oflistagg
and at the end convert it toclob
with function from step above:
select t.name, string_array2clob(cast(collect(t.text order by t.line) as string_array_t ), p_delimiter => chr(10)) as text
from user_source t
group by t.name;
If your query is not just an example of concept and really you're trying to get a source of some object in database, then you should read about dbms_metadata.get_ddl
function. It's made for it.
Related Topics
Editing Record Issues in Access/SQL (Write Conflict)
Pros/Cons of Storing Serialized Hash VS. Key/Value Database Object in Activerecord
Why No Output When Plsql Anonymous Block Completes
Subtract Hours from the Now() Function
Number of Days Between Two Dates - Ansi SQL
Is There an Oracle Equivalent to SQL Server's Output Inserted.*
Adding Constraints That Check a Separate (Linked) Table for a Value
How to Build a Summary by Joining to a Single Table with SQL Server
Differencebetween Cube, Rollup and Groupby Operators
SQL Server Join Missing Null Values
Decode( ) Function in SQL Server
Best Way in MySQL or Rails to Get Avg Per Day Within a Specific Date Range
Does SQLite3 Have Prepared Statements in Node.Js
SQL Server Export to Excel with Openrowset
Returning Result Even for Elements in In List That Don't Exist in Table