It may come as a surprise but Documentum SELECT DQL statement has a pagination feature. This largely overlooked capability is implemented with the RETURN_RANGE hint and works quite well.
While it is not such a big deal to programmatically implement a missing feature, it is generally more efficient to use its built-in implementation if it is available. In this article, I’ll show how to paginate through a result set both using a coded implementation and using the RETURN_RANGE hint.
Firstly, we need data we can browse through. It is not hard to generate random strings of characters or numbers but we want them to be plausible, i.e. a fake IP v4 address is not just a 4-group suite of numbers but each number must also be less than 256 and each group is dot-separated; a job title or a company name must look like a real job or company and, at the very least, must be pronounceable. This is not an absolute must but it not only makes using them a more enjoyable experience but sometimes a correct format is expected and necessary for the data to be processed correctly.
Many sources of fake data are available on-line, e.g. this site sample-csv-files has several types of data delivered as easy-to-parse csv files (e.g customers, organizations,people, etc.). For programmers, the Faker python package, offers a very rich spectrum of fake data and we will use it too.
Since we work with Documentum, a document management system, using fake tabular data for attributes is not the only factor to consider; document contents in various formats such as pdf, Office, Autodesk or simple text are also to be considered sometimes. Here however, in the context of pagination through metadata, contents are not relevant and we don´t need fake data for them. We will only create content-less documents with fake data as their attributes.
RETURN_RANGE syntax
The OpenText DQL Reference Guide, Appendix A.13, documents that hint in a rather sketchy manner:
RETURN_RANGE starting_row ending_row [optimize_top_row] 'sorting_clause'
The hint specifies which rows are returned by a query sorted by the returned values of specified properties. This hint is provided as a general way to paginate the results of a query.starting_row specifies the starting row number to return from the qualified rows. It starts at 1.ending_row specifies the ending row number to return from the qualified rows.optimize_top_rows provides the top rows for optimization and is optional.sorting_clause specifies the attribute and its sorting sequence used to determine the range. It defines the sequence of the qualified results. It has the format:
'attribute_name [ASC|DESC] [,attribute_name [ASC|DESC]...]'
It uses ascending by default and is compulsory. Note the mandatory enclosing single quotes.
Thus, starting_row and ending_row define a sliding windows through a result set and by varying their values n .. n + nb_rows – 1, it is possible to navigate page-wise through a result set. The sequence:
1 .. nb_rowsnb_rows + 1 .. 2* nb_rows
2 * nb_rows + 1 .. 3 * nb_rows
…n * nb_rows + 1 .. (n + 1) * nb_rows
with n = 0, 1, 2 …
determines the 0-based, nb_rows-high page n to reach.
Before we show examples of use, we need the aforementioned fake data. We will generate documents with their metadata filled either by the Faker module or from the website. The following python program populate.py, emits api statements to create content-less documents with content type organization defined as organization(Organization Id,Name,Website,Country,Description,Founded,Industry,Number of employees) as described in organizations samples:
#!/usr/bin/python3
# cec@dbi-services, January 2024;
# Purpose: generate Documentum api statement to populate a repository with content-less documents with fake metadata;
# The generated script can later be fed to iapi for execution;
# Usage:
# ./populate.py [--csv-url|-c] [--nb-docs|-n]
# where:
# the optional --csv-url option tells the program to get its csv-formatted data from the given url. By default, the Faker python module is used to internally generate the data;
# Examples:
# ./populate.py > ./populate.lst
# scp ./populate.lst dmadmin@dmtest:/tmp/.
# ssh dmadmin@dmtest 'bash -l -c "/home/dmadmin/documentum/product/current/bin/iapi dmtest -Udmadmin -Pxxx -R/tmp/populate.lst"'
# or populate the docbase in 1 step:
# ./populate.py | ssh dmadmin@dmtest 'bash -l -c "/home/dmadmin/documentum/product/current/bin/iapi dmtest -Udmadmin -Pxxx"'
# or just copy the program onto the CS or a CS-client machine, and work directly from there:
# ./populate.py | /home/dmadmin/documentum/product/current/bin/iapi dmtest -Udmadmin -Pxxx
#
WORKING_CABINET = 'PAGINATE'
DEFAULT_NB_DOCS = 500
# as the Faker module does not provide values for them, the global constant INDUSTRIES has been manually populated with data from the site as shown below:
# curl -s https://media.githubusercontent.com/media/datablist/sample-csv-files/main/files/organizations/organizations-10000.csv | gawk -v FS="," '{if (!match($8, /^[0-9]+$/)) industries[$8]++}END{for (i in industries) print i | "sort" }' | gawk 'BEGIN{printf "INDUSTRIES = ["} {printf("%s%s", NR > 1 ? ", " : "", "\"" $0 "\"")}END{print "]"}'
INDUSTRIES = ["Accounting", "Airlines / Aviation", "Alternative Dispute Resolution", "Alternative Medicine", "Animation", "Apparel / Fashion", "Architecture / Planning", "Arts / Crafts", "Automotive", "Aviation / Aerospace", "Banking / Mortgage", "Biotechnology / Greentech", "Broadcast Media", "Building Materials", "Business Supplies / Equipment", "Capital Markets / Hedge Fund / Private Equity", "Chemicals", "Civic / Social Organization", "Civil Engineering", "Commercial Real Estate", "Computer Games", "Computer Hardware", "Computer Networking", "Computer / Network Security", "Computer Software / Engineering", "Construction", "Consumer Electronics", "Consumer Goods", "Consumer Services", "Cosmetics", "Dairy", "Defense / Space", "Design", "Education Management", "E - Learning", "Electrical / Electronic Manufacturing", "Entertainment / Movie Production", "Environmental Services", "Events Services", "Executive Office", "Facilities Services", "Farming", "Financial Services", "Fine Art", "Fishery", "Food / Beverages", "Food Production", "Fundraising", "Furniture", "Gambling / Casinos", "Glass / Ceramics / Concrete", "Government Administration", "Government Relations", "Graphic Design / Web Design", "Health / Fitness", "Higher Education / Acadamia", "Hospital / Health Care", "Hospitality", "Human Resources / HR", "Import / Export", "Individual / Family Services", "Industrial Automation", "Industry", "Information Services", "Information Technology / IT", "Insurance", "International Affairs", "International Trade / Development", "Internet", "Investment Banking / Venture", "Investment Management / Hedge Fund / Private Equity", "Judiciary", "Law Enforcement", "Law Practice / Law Firms", "Legal Services", "Legislative Office", "Leisure / Travel", "Library", "Logistics / Procurement", "Luxury Goods / Jewelry", "Machinery", "Management Consulting", "Maritime", "Marketing / Advertising / Sales", "Market Research", "Mechanical or Industrial Engineering", "Media Production", "Medical Equipment", "Medical Practice", "Mental Health Care", "Military Industry", "Mining / Metals", "Motion Pictures / Film", "Museums / Institutions", "Music", "Nanotechnology", "Newspapers / Journalism", "Non - Profit / Volunteering", "Oil / Energy / Solar / Greentech", "Online Publishing", "Other Industry", "Outsourcing / Offshoring", "Package / Freight Delivery", "Packaging / Containers", "Paper / Forest Products", "Performing Arts", "Pharmaceuticals", "Philanthropy", "Photography", "Plastics", "Political Organization", "Primary / Secondary Education", "Printing", "Professional Training", "Program Development", "Public Relations / PR", "Public Safety", "Publishing Industry", "Railroad Manufacture", "Ranching", "Real Estate / Mortgage", "Recreational Facilities / Services", "Religious Institutions", "Renewables / Environment", "Research Industry", "Restaurants", "Retail Industry", "Security / Investigations", "Semiconductors", "Shipbuilding", "Sporting Goods", "Sports", "Staffing / Recruiting", "Supermarkets", "Telecommunications", "Textiles", "Think Tanks", "Tobacco", "Translation / Localization", "Transportation", "Utilities", "Venture Capital / VC", "Veterinary", "Warehousing", "Wholesale", "Wine / Spirits", "Wireless", "Writing / Editing"]
def parse_cmd_line():
import argparse
import textwrap
parser = argparse.ArgumentParser(formatter_class = argparse.RawDescriptionHelpFormatter,
description = textwrap.dedent("""\
This script generates Documentum API statements to create content-less documents with fake metadata.
The generated script can later be fed to iapi for execution.
"""))
parser.add_argument("--csv-url", "-c",
type = str,
dest = "csv_url",
required = False,
default = "",
help = "It tells the program to get its csv-formatted data from the given url. By default, the Faker python module is used to internally generate the data.")
parser.add_argument("--nb-docs", "-n",
type = int,
dest = "nb_docs",
required = False,
default = DEFAULT_NB_DOCS,
help = "Number of content-less fake documents to create. It defaults to 100.")
args = parser.parse_args()
return args.csv_url, args.nb_docs
def setup_test(cabinet_name):
print(f'''
set,c,apiconfig,dfc.date_format
d-M-yyyy
create,c,dm_cabinet
set,c,l,object_name
{cabinet_name}
save,c,l
reset,c,l
?,c,create type organization (doc_index integer, organization_id character(15), organization_name character(255), website character(255), country character(100), description character(100), founded date, industry character(100), number_of_employees integer) with supertype dm_document publish
''')
def to_api(cabinet_name, org):
print(f'''
create,c,organization
set,c,l,subject
Fake document for testing pagination in a result set through the RETURN_RANGE hint;
set,c,l,object_name
{org["organization_name"]}
set,c,l,doc_index
{org["doc_index"]}
set,c,l,organization_id
{org["organization_id"]}
set,c,l,organization_name
{org["organization_name"]}
set,c,l,website
{org["website"]}
set,c,l,country
{org["country"]}
set,c,l,description
{org["description"]}
set,c,l,founded
{org["founded"]}
set,c,l,industry
{org["industry"]}
set,c,l,number_of_employees
{org["number_of_employees"]}
link,c,l,/{cabinet_name}
save,c,l
''')
def generate_fake_organizations(cabinet_name, nb_docs = DEFAULT_NB_DOCS):
from faker import Faker
import random
fake = Faker()
for i in range(nb_docs):
to_api(cabinet_name,
entry := {
"doc_index": i + 1, # let's make it 1-based;
"organization_id": fake.pystr(min_chars = 15, max_chars = 15),
"organization_name": fake.company(),
"website": fake.url(schemes = ["https"]),
"country": fake.country(),
"description": fake.text(max_nb_chars = 100).replace('\n', ' '),
"founded": fake.date_of_birth(minimum_age=1, maximum_age=50).strftime("%d-%m-%Y"),
"industry": random.sample(INDUSTRIES, 1)[0],
"number_of_employees": round(random.randint(10, 50000 + 1), -1)
}
)
def generate_organizations_from_csv(cabinet_name, csv_url, nb_docs):
import requests
data = [l.split(",") for l in requests.get(csv_url).text.split("\r\n")[1:]]
if not data:
print(f'no data found from {gcsv_url}, exiting ...')
exit(0)
doc_index = 0
from itertools import cycle
for _, d in zip(range(nb_docs), cycle(data)):
if len(d) < 9:
continue
to_api(cabinet_name,
entry := {
"doc_index": doc_index + 1, # let's make it 1-based;
"organization_id": d[1],
"organization_name": d[2],
"website": d[3],
"country": d[4],
"description": d[5],
"founded": d[6],
"industry": d[7],
"number_of_employees": d[8]
}
)
doc_index += 1
#------------------------------
# main;
if __name__ == '__main__':
gcsv_url, gnb_docs = parse_cmd_line()
setup_test(WORKING_CABINET)
if gcsv_url:
generate_organizations_from_csv(WORKING_CABINET, gcsv_url, gnb_docs)
else:
generate_fake_organizations(WORKING_CABINET, gnb_docs)
# ready to test pagination using the RETURN_RANGE hint;
# clean up statements;
print(f'''
#?,c,delete organization objects
#?,c,delete dm_cabinet object where object_name = '{WORKING_CABINET}'
#?,c,drop type organization
''')
The generated api statements can then be submitted to the iapi interpreter for execution, either on the fly:
./populate.py | /home/dmadmin/documentum/product/current/bin/iapi dmtest -Udmadmin -Pxxx
or in 2 steps:
./populate.py > ./populate.api
/home/dmadmin/documentum/product/current/bin/iapi dmtest -Udmadmin -Pxxx -R./populate.api
or anything in between if the interpreter is installed on a remote machine (see the program’s header).
Here is the program’s usage:
./paginate.py --help
usage: paginate.py [-h] [--csv-url CSV_URL] [--nb-docs NB_DOCS]
This script generates Documentum API statements to create content-less documents with fake metadata.
The generated script can later be fed to iapi for execution.
options:
-h, --help show this help message and exit
--csv-url CSV_URL, -c CSV_URL
It tells the program to get its csv-formatted data from the given url. By default, the Faker python module is used to internally generate the data.
--nb-docs NB_DOCS, -n NB_DOCS
Number of content-less fake documents to create. It defaults to 100.
So, if Faker is preferred, invoke it with no csv url, like this:
./paginate.py --nb-docs=200
If the csv data from the web site are preferred, specify the csv url like this:
./paginate.py --nb-docs=200 --csv-url=https://media.githubusercontent.com/media/datablist/sample-csv-files/main/files/organizations/organizations-100.csv
The available data will be cycled until the specified number of documents is reached.
Here are the first few statements emitted by the program:
set,c,apiconfig,dfc.date_format
d-M-yyyy
create,c,dm_cabinet
set,c,l,object_name
PAGINATE
save,c,l
reset,c,l
?,c,create type organization (doc_index integer, organization_id character(15), organization_name character(255), website character(255), country character(100), description character(100), founded date, industry character(100), number_of_employees integer) with supertype dm_document publish
create,c,organization
set,c,l,subject
Fake document for testing pagination in a result set through the RETURN_RANGE hint;
set,c,l,object_name
Jones and Sons
set,c,l,doc_index
0
set,c,l,organization_id
LdxOVYaDjNMbtGE
set,c,l,organization_name
Jones and Sons
set,c,l,website
https://www.norton.com/
set,c,l,country
Gabon
set,c,l,description
Bit item loss maybe center can. Story expert budget these kitchen along.
set,c,l,founded
18-01-1992
set,c,l,industry
Wine / Spirits
set,c,l,number_of_employees
47550
link,c,l,/PAGINATE
save,c,l
create,c,organization
set,c,l,subject
Fake document for testing pagination in a result set through the RETURN_RANGE hint;
set,c,l,object_name
Bauer Ltd
set,c,l,doc_index
1
set,c,l,organization_id
HJwnJzuXxxmEqDI
set,c,l,organization_name
Bauer Ltd
set,c,l,website
https://kent.net/
set,c,l,country
Heard Island and McDonald Islands
set,c,l,description
Read second six out close shoulder card see. Beyond state back that common fine bank.
set,c,l,founded
18-03-2010
set,c,l,industry
Construction
set,c,l,number_of_employees
43340
link,c,l,/PAGINATE
save,c,l
...
# clean up;
#?,c,delete organization objects
#?,c,delete dm_cabinet object where object_name = 'PAGINATE'
#?,c,drop type organization
After a suitable date format has been set in the session, a cabinet /PAGINATE to contain the fake documents and the doctype organization are created, followed by the creation of the specified number of documents. The attribute doc_index contains the index number of the document in the range 0 .. nb_docs – 1, and subject is set to a constant text explaining the purpose of the document; this makes it easy to identify the test documents (not really necessary here since we only work with one doctype, organization; let’s say it is another faker data, a constant one). The generated API script ends with some clean up statements to rollback the test; they are commented in order to be used later.
Now that we have the documents in the repository, let’s see how they look like as follows:
select count(*) from organization
count(*)
----------------------
200
(1 row affected)
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization order by doc_index
doc_index subject object_name organization_id organization_name website country description founded industry number_of_employees
------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ------------------------- ---------------------------------------------------------------------------------------------------- -------------------
1 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Jones and Sons LdxOVYaDjNMbtGE Jones and Sons https://www.norton.com/ Gabon Bit item loss maybe center can. Story expert budget these kitchen along. 18-1-1992 Wine / Spirits 47550
2 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Bauer Ltd HJwnJzuXxxmEqDI Bauer Ltd https://kent.net/ Heard Island and McDonald Islands Read second six out close shoulder card see. Beyond state back that common fine bank. 18-3-2010 Construction 43340
3 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Erickson, Smith and Jackson xZLxZCzOOLZavxz Erickson, Smith and Jackson https://www.walls-moore.biz/ United States of America Create spend summer job age eye able. Learn standard month question full defense. 12-11-2022 Research Industry 45570
4 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Anthony-Moyer eiuzuuAhwYFZlrg Anthony-Moyer https://www.chambers.com/ Vanuatu Meeting pass maintain nothing news. Big simply measure class lead him. 9-10-2007 Museums / Institutions 3400
5 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Richmond-Hudson hBtsJqBxNyRacQI Richmond-Hudson https://www.schneider-green.com/ Cote d'Ivoire Strategy team teach building already whose. Letter book both technology base sometimes. 19-5-2002 Alternative Medicine 1150
6 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Robles PLC budHsKmJHREufEo Robles PLC https://maldonado.biz/ Austria Face seven important weight will. East claim coach act. 6-1-2001 Government Relations 32500
...
(200 rows affected)
Incidentally, we can see in the above output the eternal presentation problem that plagues the iapi/idql command-line tools: the attributes are displayed in fixed-length columns with lots of trailing blanks and the rows are wrapped around if too long to fit on the screen width. This issue has been discussed and solutions have been given several times in this blog. Here is yet another work around (sometimes it is more fun to rewrite it from scratch than to search a script we wrote ourselves !), the script compact.awk:
# cec@dbi-services, January 2024;
# Purpose: compact a tabular DQL SELECT output by stripping the columns'unnecessary trailing blanks;
# Usage:
# gawk -f compact.awk output.lst
# as usual, input can also be taken from stdin;
# Examples:
# echo "?,c,select * from organization" | iapi dmtest73 -Udmadmin -Pxxx | gawk -f compact.awk | less
BEGIN {
while((getline > 0) && !match($0, /^-+/)) header = $0;
if (!RSTART)
exit
sep_line = $0
nb_cols = NF
match($0, / +/); sep_length = RLENGTH
FIELDWIDTHS = ""
for (i = 1; i <= nb_cols; i++)
FIELDWIDTHS = sprintf("%s%s%i", FIELDWIDTHS, i > 1 ? " " : "", length($i) + sep_length)
#attr_re = "^(.+) {" sep_length "}$"
nb_rows = 0
}
{
if (match($0, /\([0-9]+ row. affected\)/)) {
footer = $0
exit
}
row[nb_rows++] = $0
for (i = 1; i <= NF; i++) {
# reg exp would likely be faster but they don't work here because gawk's regular expressions are greedy and they would consume all the trailing blanks except the ending sep_length ones whereas we want to skip all of them;
# to work around this, we physically remove them;
#match($i, attr_re, found)
#w[i] = max(w[i], found[1])
gsub(/ +$/, "", $i)
w[i] = max(w[i], length($i))
}
}
END {
for (i = 1; i <= NF; i++)
pad[i] = repeat(" ", w[i])
print(compact(header))
print(compact(sep_line))
for (i = 0; i < nb_rows; i++)
print(compact(row[i]))
print footer
}
function max(a, b) {
if (a >= b)
return a
else
return b
}
function compact(line , s, i) {
$0 = line
s = ""
for (i = 1; i <= NF; i++)
s = sprintf("%s%s%s", s, i > 1 ? " " : "", substr($i pad[i], 1, w[i]))
return s
}
function repeat(ch, nb_times , s) {
s = ""
while (nb_times-- > 0)
s = s + ch
return s
}
Usage:
echo "?,c,select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization order by doc_index" | iapi dmtest73 -Udmadmin -Pxxx | gawk -f compact.awk
Output:
doc_ind subject object_name organizati _id organization_name website country description founded industry number_of_empl
------------ ----------------------------------------------------------------------------------- -------------------------------- --------------- -------------------------------- ----------------------------------- -------------------------------------------- --------------------------------------------------------------------------------------------------- ------------------- --------------------------------------------------- -------------------
1 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Jones and Sons LdxOVYaDjNMbtGE Jones and Sons https://www.norton.com/ Gabon Bit item loss maybe center can. Story expert budget these kitchen along. 1/18/1992 00:00:00 Wine / Spirits 47550
2 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Bauer Ltd HJwnJzuXxxmEqDI Bauer Ltd https://kent.net/ Heard Island and McDonald Islands Read second six out close shoulder card see. Beyond state back that common fine bank. 3/18/2010 00:00:00 Construction 43340
3 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Erickson, Smith and Jackson xZLxZCzOOLZavxz Erickson, Smith and Jackson https://www.walls-moore.biz/ United States of America Create spend summer job age eye able. Learn standard month question full defense. 11/12/2022 00:00:00 Research Industry 45570
4 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Anthony-Moyer eiuzuuAhwYFZlrg Anthony-Moyer https://www.chambers.com/ Vanuatu Meeting pass maintain nothing news. Big simply measure class lead him. 10/9/2007 00:00:00 Museums / Institutions 3400
5 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Richmond-Hudson hBtsJqBxNyRacQI Richmond-Hudson https://www.schneider-green.com/ Cote d'Ivoire Strategy team teach building already whose. Letter book both technology base sometimes. 5/19/2002 00:00:00 Alternative Medicine 1150
6 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Robles PLC budHsKmJHREufEo Robles PLC https://maldonado.biz/ Austria Face seven important weight will. East claim coach act. 1/6/2001 00:00:00 Government Relations 32500
7 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Nichols Ltd hJYonMSFNXaYaQG Nichols Ltd https://www.leon-sims.biz/ Falkland Islands (Malvinas) Certain hand impact method somebody thank song. Democrat travel article reach sea article. 5/31/1997 00:00:00 Leisure / Travel 35760
8 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Elliott-Valdez mstxDZfBafifCJn Elliott-Valdez https://www.ward.com/ Hungary Operation item nature stock dog attack into. Vote edge whose keep. 12/7/1995 00:00:00 Publishing Industry 21080
9 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Zamora PLC TuJPwIFoqEiYFUz Zamora PLC https://www.oliver.com/ Venezuela Chance expert general. Garden this positive. Edge young share beautiful admit thank name. 5/7/1993 00:00:00 Cosmetics 38410
10 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hampton Ltd jRZDRGrfgyWTpAn Hampton Ltd https://www.gilbert.com/ Estonia Chance discussion call yeah. Measure entire season scene behind radio. Than machine ball material. 4/16/1975 00:00:00 Accounting 36020
11 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hernandez, Smith and Mcintosh QLpUVwnuZVdIoCw Hernandez, Smith and Mcintosh https://garcia-peterson.com/ Switzerland Live hundred would let food. Else nice firm door still. Hair technology trial. 4/28/2007 00:00:00 Supermarkets 11630
12 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Fernandez LLC mCdBZkIqunyExsA Fernandez LLC https://clark-hernandez.com/ Ukraine Health paper child worry thus produce light. Get them hope garden show think. 5/19/1997 00:00:00 Market Research 46500
13 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Stevens, Greer and Young QzSMkppsZEbfJQF Stevens, Greer and Young https://www.fuller-moses.com/ Finland Mr discuss pretty after whose actually guy. Music pass movement still. 12/23/1988 00:00:00 Public Safety 3500
14 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Zuniga and Sons jVMjZmDdeUejohj Zuniga and Sons https://www.porter-richardson.net/ Gibraltar Agent heart who which dog father. Find day eight her loss. Fine agency say player training. 10/11/1994 00:00:00 Information Services 920
15 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Murphy Group zJDwYcXPAnmEvos Murphy Group https://parks.com/ Guyana Democratic room year nature fact century change. Me great fear able watch father compare. 7/2/1999 00:00:00 Textiles 44560
16 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Edwards, Howe and Munoz ChLMutRVzUKnzXN Edwards, Howe and Munoz https://nichols-walsh.com/ Vietnam Hotel bring fire laugh really interview remember. 4/21/2006 00:00:00 Aviation / Aerospace 30370
17 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Bowers Inc uBmqyxuKEBUcGSg Bowers Inc https://rivera.com/ Libyan Arab Jamahiriya Often than number story land particularly. Term sure surface court drop any crime. 12/1/1981 00:00:00 Outsourcing / Offshoring 7800
18 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Fisher-Walker NyMFDEmxlPertcI Fisher-Walker https://estrada.com/ Iraq Today feeling amount. Interview take wide report. Fire price foot probably follow sort. 11/29/2016 00:00:00 Legislative Office 28060
19 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Smith, Hawkins and Baker GtqNebcnXThOIEz Smith, Hawkins and Baker https://wheeler.info/ Senegal Manager eight forward each word. Choice defense responsibility develop half tax prove. 7/6/2017 00:00:00 Recreational Facilities / Services 7230
20 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Lynch PLC XBPvgaxcwUplVyX Lynch PLC https://www.ferguson.com/ Mauritius Career region along. School summer stock later item. Deal say seat customer. 2/21/2013 00:00:00 Political Organization 23000
21 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Harris Inc ahvZGlBZLSIxFSU Harris Inc https://www.porter-wells.info/ Sri Lanka Many religious sit enjoy south technology money. Center month stop clearly. Home like carry set. 1/29/1997 00:00:00 Medical Equipment 40240
22 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Powell Ltd UaDDsMjqjxnwdOt Powell Ltd https://alvarado.biz/ United Arab Emirates Everything people heavy off. Agree option growth majority economy music TV. 8/29/2012 00:00:00 Electrical / Electronic Manufacturing 25180
...
197 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hill Ltd OgennqjqitKpuYb Hill Ltd https://owen.com/ New Zealand Trade keep institution source you. Wife quality all risk to. 3/26/2003 00:00:00 Telecommunications 45690
199 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Davila and Sons rUdByKYjLVznQSf Davila and Sons https://ho.com/ Lesotho Themselves none reach realize accept. Style general energy medical address blood scene. 8/16/2004 00:00:00 Market Research 2780
199 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hancock, Turner and Robinson swilppvRqeWoIha Hancock, Turner and Robinson https://www.davila-collins.biz/ Sri Lanka Star right effort modern six. 10/15/1980 00:00:00 Biotechnology / Greentech 20300
200 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Spears Group zhLEEoIUUPmHqkF Spears Group https://www.vasquez.info/ Uganda Not staff success possible special. Exactly Congress local billion without fly staff expert. 11/4/2004 00:00:00 Food / Beverages 22240
(200 rows affected)
Much better.
We now have set up the stage for demonstrating the pagination feature of the RETURN_RANGE hint. Let’s show a few examples (for better readability, the output has been edited using the above compact.awk gawk script):
# first page:
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization enable(RETURN_RANGE 1 10 'doc_index ASC')
doc_index subject object_name organization_id organization_name website country description founded industry number_of_employees dm_rnum
------------ ----------------------------------------------------------------------------------- --------------------------- --------------- --------------------------- -------------------------------- --------------------------------- -------------------------------------------------------------------------------------------------- ------------------- ---------------------- ------------------- ----------------------
1 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Jones and Sons LdxOVYaDjNMbtGE Jones and Sons https://www.norton.com/ Gabon Bit item loss maybe center can. Story expert budget these kitchen along. 1/18/1992 00:00:00 Wine / Spirits 47550 1
2 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Bauer Ltd HJwnJzuXxxmEqDI Bauer Ltd https://kent.net/ Heard Island and McDonald Islands Read second six out close shoulder card see. Beyond state back that common fine bank. 3/18/2010 00:00:00 Construction 43340 2
3 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Erickson, Smith and Jackson xZLxZCzOOLZavxz Erickson, Smith and Jackson https://www.walls-moore.biz/ United States of America Create spend summer job age eye able. Learn standard month question full defense. 11/12/2022 00:00:00 Research Industry 45570 3
4 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Anthony-Moyer eiuzuuAhwYFZlrg Anthony-Moyer https://www.chambers.com/ Vanuatu Meeting pass maintain nothing news. Big simply measure class lead him. 10/9/2007 00:00:00 Museums / Institutions 3400 4
5 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Richmond-Hudson hBtsJqBxNyRacQI Richmond-Hudson https://www.schneider-green.com/ Cote d'Ivoire Strategy team teach building already whose. Letter book both technology base sometimes. 5/19/2002 00:00:00 Alternative Medicine 1150 5
6 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Robles PLC budHsKmJHREufEo Robles PLC https://maldonado.biz/ Austria Face seven important weight will. East claim coach act. 1/6/2001 00:00:00 Government Relations 32500 6
7 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Nichols Ltd hJYonMSFNXaYaQG Nichols Ltd https://www.leon-sims.biz/ Falkland Islands (Malvinas) Certain hand impact method somebody thank song. Democrat travel article reach sea article. 5/31/1997 00:00:00 Leisure / Travel 35760 7
8 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Elliott-Valdez mstxDZfBafifCJn Elliott-Valdez https://www.ward.com/ Hungary Operation item nature stock dog attack into. Vote edge whose keep. 12/7/1995 00:00:00 Publishing Industry 21080 8
9 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Zamora PLC TuJPwIFoqEiYFUz Zamora PLC https://www.oliver.com/ Venezuela Chance expert general. Garden this positive. Edge young share beautiful admit thank name. 5/7/1993 00:00:00 Cosmetics 38410 9
10 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hampton Ltd jRZDRGrfgyWTpAn Hampton Ltd https://www.gilbert.com/ Estonia Chance discussion call yeah. Measure entire season scene behind radio. Than machine ball material. 4/16/1975 00:00:00 Accounting 36020 10
(10 rows affected)
# next page:
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization enable(RETURN_RANGE 11 20 'doc_index ASC')
doc_index subject object_name organization_id organization_name website country description founded industry number_of_employees dm_rnum
------------ ----------------------------------------------------------------------------------- ----------------------------- --------------- ----------------------------- ---------------------------------- ---------------------- ------------------------------------------------------------------------------------------- ------------------- ---------------------------------- ------------------- ----------------------
11 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hernandez, Smith and Mcintosh QLpUVwnuZVdIoCw Hernandez, Smith and Mcintosh https://garcia-peterson.com/ Switzerland Live hundred would let food. Else nice firm door still. Hair technology trial. 4/28/2007 00:00:00 Supermarkets 11630 11
12 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Fernandez LLC mCdBZkIqunyExsA Fernandez LLC https://clark-hernandez.com/ Ukraine Health paper child worry thus produce light. Get them hope garden show think. 5/19/1997 00:00:00 Market Research 46500 12
13 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Stevens, Greer and Young QzSMkppsZEbfJQF Stevens, Greer and Young https://www.fuller-moses.com/ Finland Mr discuss pretty after whose actually guy. Music pass movement still. 12/23/1988 00:00:00 Public Safety 3500 13
14 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Zuniga and Sons jVMjZmDdeUejohj Zuniga and Sons https://www.porter-richardson.net/ Gibraltar Agent heart who which dog father. Find day eight her loss. Fine agency say player training. 10/11/1994 00:00:00 Information Services 920 14
15 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Murphy Group zJDwYcXPAnmEvos Murphy Group https://parks.com/ Guyana Democratic room year nature fact century change. Me great fear able watch father compare. 7/2/1999 00:00:00 Textiles 44560 15
16 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Edwards, Howe and Munoz ChLMutRVzUKnzXN Edwards, Howe and Munoz https://nichols-walsh.com/ Vietnam Hotel bring fire laugh really interview remember. 4/21/2006 00:00:00 Aviation / Aerospace 30370 16
17 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Bowers Inc uBmqyxuKEBUcGSg Bowers Inc https://rivera.com/ Libyan Arab Jamahiriya Often than number story land particularly. Term sure surface court drop any crime. 12/1/1981 00:00:00 Outsourcing / Offshoring 7800 17
18 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Fisher-Walker NyMFDEmxlPertcI Fisher-Walker https://estrada.com/ Iraq Today feeling amount. Interview take wide report. Fire price foot probably follow sort. 11/29/2016 00:00:00 Legislative Office 28060 18
19 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Smith, Hawkins and Baker GtqNebcnXThOIEz Smith, Hawkins and Baker https://wheeler.info/ Senegal Manager eight forward each word. Choice defense responsibility develop half tax prove. 7/6/2017 00:00:00 Recreational Facilities / Services 7230 19
20 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Lynch PLC XBPvgaxcwUplVyX Lynch PLC https://www.ferguson.com/ Mauritius Career region along. School summer stock later item. Deal say seat customer. 2/21/2013 00:00:00 Political Organization 23000 20
(10 rows affected)
# 3rd page, 20 rows instead of 10 this time;
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization enable(RETURN_RANGE 21 40 'doc_index ASC')
doc_index subject object_name organization_id organization_name website country description founded industry number_of_employees dm_rnum
------------ ----------------------------------------------------------------------------------- ----------------------------- --------------- ----------------------------- --------------------------------- --------------------------- -------------------------------------------------------------------------------------------------- ------------------- ------------------------------------- ------------------- ----------------------
21 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Harris Inc ahvZGlBZLSIxFSU Harris Inc https://www.porter-wells.info/ Sri Lanka Many religious sit enjoy south technology money. Center month stop clearly. Home like carry set. 1/29/1997 00:00:00 Medical Equipment 40240 21
22 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Powell Ltd UaDDsMjqjxnwdOt Powell Ltd https://alvarado.biz/ United Arab Emirates Everything people heavy off. Agree option growth majority economy music TV. 8/29/2012 00:00:00 Electrical / Electronic Manufacturing 25180 22
23 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Orr-Andrews mjxWxcxBFUHkdkw Orr-Andrews https://anderson.com/ El Salvador Main structure sense black front. Best capital opportunity national past truth kitchen radio. 2/9/1977 00:00:00 Alternative Medicine 9510 23
24 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Weeks and Sons EjzOYRMYOqPGcBh Weeks and Sons https://www.morgan.info/ Bouvet Island (Bouvetoya) Analysis close live box show even responsibility. Spring worker son common chair traditional. 3/10/2017 00:00:00 Consumer Services 1070 24
25 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Phillips-Sheppard vvpgEUvmDTvHVUj Phillips-Sheppard https://woods-romero.biz/ Dominican Republic Same some tough leader drug late. Manage skill media entire rise large two. 9/20/1993 00:00:00 Aviation / Aerospace 18120 25
26 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Mcguire, Shepherd and Johnson suJFaiReXkfmVqm Mcguire, Shepherd and Johnson https://www.carr-barker.com/ American Samoa Citizen approach be offer wear carry tend. Better difference age. 12/25/1981 00:00:00 Wireless 10380 26
27 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hess LLC kMLbTuGukEwDjDB Hess LLC https://carr.com/ Turks and Caicos Islands Adult tend night company guess only opportunity. Evening establish contain usually. 3/6/1996 00:00:00 Religious Institutions 14130 27
28 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Franklin Ltd zmYKtQduRPSEfit Franklin Ltd https://www.robinson.biz/ Romania First have seem standard protect want service. Occur knowledge loss popular friend number forward. 2/20/1998 00:00:00 Computer Hardware 32100 28
29 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Leonard Group SVkTDkwAHJnFYJl Leonard Group https://www.rojas.com/ Cuba Open lose between state capital vote describe. Game sit few nor. One lawyer involve. 7/18/1987 00:00:00 Primary / Secondary Education 15830 29
30 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Frank Ltd vAIaBKYKygOJPNV Frank Ltd https://www.mills.com/ Cayman Islands Institution they seat society. Over instead soon tonight improve collection traditional. 4/29/1977 00:00:00 Individual / Family Services 45100 30
31 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Taylor-Mcclure sLSOKbnznjxgnfu Taylor-Mcclure https://www.johnson.com/ Mayotte Bar bank sister. Old food threat range none front focus today. Another determine quality each. 8/31/2015 00:00:00 Consumer Goods 37740 31
32 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Cisneros-Mathews rlaCfcsmTVhhLHq Cisneros-Mathews https://www.rowland.info/ Martinique List reflect nice know. Section party interview cut surface evening. 4/1/2018 00:00:00 Warehousing 23880 32
33 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Brown, Nunez and Boyd NXxqXQabfPoLUKB Brown, Nunez and Boyd https://www.webb.com/ Denmark Score more Mrs stock perhaps. Situation food full raise. 9/20/1973 00:00:00 Information Technology / IT 22270 33
34 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Martin-Hodge NJDowVoehnKfzUj Martin-Hodge https://young.org/ Mongolia Every contain young tonight. Seat full food ready life. Serve eat yourself recent explain white. 5/4/2013 00:00:00 Aviation / Aerospace 49180 34
35 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Shannon, Harding and Sanchez uXxjkktfvjuzCov Shannon, Harding and Sanchez https://peterson.net/ French Southern Territories Business laugh again party. Opportunity arrive choose kitchen toward act under. 2/19/1979 00:00:00 Religious Institutions 10370 35
36 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Brown, White and Perkins VoeETffAHGfGXuZ Brown, White and Perkins https://www.anderson.com/ Spain Who base explain control country drive. Worry picture level forget audience body. 10/21/2022 00:00:00 Telecommunications 21910 36
37 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Watson, Murray and Owens kKmKHiJDZGEQwjn Watson, Murray and Owens https://www.baxter-contreras.com/ French Southern Territories Really drug western law note. Group actually what. Himself full himself into degree feeling talk. 1/2/2001 00:00:00 Wireless 45470 37
38 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Taylor-Lopez fJyeWvzKzIgJdya Taylor-Lopez https://www.schultz.com/ Nigeria Never million moment very. Management explain five Mr. 4/15/1977 00:00:00 Museums / Institutions 45630 38
39 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Owens PLC dysBZBBxVQqPcwZ Owens PLC https://barnes.info/ Jersey Her present shake without yard occur conference. Walk arrive learn. 6/21/1986 00:00:00 Plastics 3060 39
40 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hernandez LLC iUAnDDOlNVetiuu Hernandez LLC https://copeland.com/ Norway Story boy talk yourself. 8/13/1986 00:00:00 Other Industry 11100 40
(20 rows affected)
# last 10 rows;
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization enable(RETURN_RANGE 191 200 'doc_index ASC')
oc_index subject object_name organization_id organization_name website country description founded industry number_of_employees dm_rnum
------------ ----------------------------------------------------------------------------------- ---------------------------- --------------- ---------------------------- ------------------------------- ------------------------ -------------------------------------------------------------------------------------------- ------------------- -------------------------------- ------------------- ----------------------
191 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Miller, Watson and Gonzalez JgwNOKaVUquYcBk Miller, Watson and Gonzalez https://tucker-washington.com/ Qatar Parent entire religious method eye third. Middle report close get though meet gas. 1/29/2001 00:00:00 Oil / Energy / Solar / Greentech 41490 191
192 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Bishop-Perry wFTAmAlodFqGBRm Bishop-Perry https://www.davis.com/ Liberia Age forget house writer side exactly human. Design memory push else probably design. 11/26/2019 00:00:00 Telecommunications 14550 192
193 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Taylor, Mcintyre and Brady rLgBOoqjMcAwMzg Taylor, Mcintyre and Brady https://gonzalez-collins.org/ Reunion Brother just we for book year. 8/12/1992 00:00:00 Law Practice / Law Firms 19780 193
194 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Roth-Wright kLqlxKbggAjGGXT Roth-Wright https://ramirez-perkins.org/ Cape Verde Let believe every environmental. Too matter fly any. Painting exist religious campaign. 4/7/2020 00:00:00 Construction 13750 194
195 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Melton, Davis and Reed ePQOLAEfxBUXbDy Melton, Davis and Reed https://www.gates.com/ Northern Mariana Islands Building pass minute strategy program family across. Move save clear moment bring career. 7/21/1989 00:00:00 Automotive 5450 195
196 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Garcia Inc ElmnJXeZtrvjUbf Garcia Inc https://walters.org/ Russian Federation Your degree billion practice instead measure point much. Area strong notice phone buy. 7/14/1995 00:00:00 Legal Services 24030 196
197 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hill Ltd OgennqjqitKpuYb Hill Ltd https://owen.com/ New Zealand Trade keep institution source you. Wife quality all risk to. 3/26/2003 00:00:00 Telecommunications 45690 197
198 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Davila and Sons rUdByKYjLVznQSf Davila and Sons https://ho.com/ Lesotho Themselves none reach realize accept. Style general energy medical address blood scene. 8/16/2004 00:00:00 Market Research 2780 198
199 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hancock, Turner and Robinson swilppvRqeWoIha Hancock, Turner and Robinson https://www.davila-collins.biz/ Sri Lanka Star right effort modern six. 10/15/1980 00:00:00 Biotechnology / Greentech 20300 199
200 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Spears Group zhLEEoIUUPmHqkF Spears Group https://www.vasquez.info/ Uganda Not staff success possible special. Exactly Congress local billion without fly staff expert. 11/4/2004 00:00:00 Food / Beverages 22240 200
(10 rows affected)
So far, no surprise here, although we note that a new pseudo-column is appended, dm_rnum, for numbering the row from starting_row to ending_row. This column is not accessible directly and trying to do so yields the error [DM_QUERY_E_NOT_ATTRIBUTE]error: "You have specified an invalid attribute name (dm_rnum).".
If we mistakenly include an ORDER BY clause:
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization order by 1 enable(RETURN_RANGE 1 10 'doc_index ASC')
[DM_QUERY_E_CURSOR_ERROR]error: "A database error has occurred during the creation of a cursor ('ORA-00933: SQL command not properly ended')."
The error message is not very informative but it looks like the generated SQL has a syntax error. Let’s display that SQL statement:
execute get_last_sql
result
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from ( select dm_inline_view_1.*, ROW_NUMBER() OVER(ORDER BY dm_inline_view_1.doc_index ASC ) as dm_rnum from (select distinct organization.doc_index, organization.subject, organization.object_name, organization.organization_id, organization.organization_name, organization.website, organization.country, organization.description, organization.founded, organization.industry, organization.number_of_employees from organization_sp organization where (organization.i_has_folder = 1 and organization.i_is_deleted = 0) ) dm_inline_view_1) dm_inline_view_2 where dm_rnum >= 1 AND dm_rnum <= 10 order by dm_rnum order by 1
The offending clause is order by dm_rnum order by 1. In effect, our ORDER BY clause was appended to the generated SQL which already contained one such clause, which invalidate the statement’s syntax. Actually, we don’t need to provide such a clause because it can be included in the hint; indeed, it MUST be included since the sorting clause is mandatory.
The above generated SQL is interesting as it shows how the RETURN_RANGE hint is implemented, here in the underlying Oracle RDBMS; the analytical function ROW_NUMBER() is used to number the rows; it determines the facts that rows are 1-based and that sorting_clause is mandatory.
Are they any other quirks to be aware of? Let’s do a few more tests. What about the letter case of identifiers and reserved words?
select organization_id, organization_name, industry, number_of_employees from organization enable(return_range 1 10 'organization_name asc')
...
10 rows affected)
select ORGANIZATION_ID, ORGANIZATION_NAME, INDUSTRY, NUMBER_OF_EMPLOYEES from ORGANIZATION enable(return_range 1 10 'ORGANIZATION_ID asc', GENERATE_SQL_ONLY)
generate_sql
------------
select * from ( select dm_inline_view_1.*, ROW_NUMBER() OVER(ORDER BY dm_inline_view_1.ORGANIZATION_ID asc ) as dm_rnum from (select distinct organization.ORGANIZATION_ID, organization.ORGANIZATION_NAME, organization.INDUSTRY, organization.NUMBER_OF_EMPLOYEES from organization_sp organization where (organization.i_has_folder = 1 and organization.i_is_deleted = 0) ) dm_inline_view_1) dm_inline_view_2 where dm_rnum >= 1 AND dm_rnum <= 10 order by dm_rnum
(1 row affected)
The generated SQL is case-sensitive and handled to the underlying RDBMS, which processes it as expected.
Let’s test the lower range limit:
?,c,select organization_id, organization_name, industry, number_of_employees from organization enable(RETURN_RANGE 0 10 'organization_name ASC')
[DM_QUERY2_E_UNRECOGNIZED_HINT]error: "RETURN_RANGE is an unknown hint or is being used incorrectly."
The accepted range is 1-based and starting at 0 yields an error. This is a DQL thing because Oracle does not complain here.
What about setting the position of the sliding window outside the available range?
?,c,select organization_id, organization_name, industry, number_of_employees from organization enable(RETURN_RANGE 1000 2000 'organization_name ASC', GENERATE_SQL_ONLY)
...
(0 rows affected)
Specifying a range outside the result set is not a problem and is processed as expected, i.e. nothing gets returned.
Is the hint compatible with other SELECT’s clauses ? Let’s test it with an aggregate function.
select organization_name, count(*) as cc from organization group by organization_name enable(RETURN_RANGE 1 10 'organization_name ASC')
organization_name cc dm_rnum
---------------------------- ---------------------- ----------------------
Allen, Ortega and Herman 1 1
Alvarado and Sons 1 2
Anderson PLC 1 3
Anderson, Watkins and Hansen 1 4
Andrews Group 1 5
Anthony-Moyer 1 6
Ball PLC 1 7
Banks PLC 1 8
Banks, Garcia and Wilkins 1 9
Barron, Dennis and Hill 1 10
(10 rows affected)
It works fine, just make sure that the attribute in the sorting_clause is also referenced in the list of attributes (organization_name here).
Does it also work using in-line views?
select org_name, cc from (select organization_name as org_name, count(*) as cc from organization group by organization_name) enable(RETURN_RANGE 1 10 'org_name ASC')
organization_name cc dm_rnum
---------------------------- ---------------------- ----------------------
Allen, Ortega and Herman 1 1
Alvarado and Sons 1 2
Anderson PLC 1 3
Anderson, Watkins and Hansen 1 4
Andrews Group 1 5
Anthony-Moyer 1 6
Ball PLC 1 7
Banks PLC 1 8
Banks, Garcia and Wilkins 1 9
Barron, Dennis and Hill 1 10
(10 rows affected)
It works fine here too.
Paginating programmatically
Implementing a programmatic way to paginate is is quite easy, although possibly not that efficient as it may put more stress on the system memory and/or no be as fast if a query is to be executed multiple times. Here are a few ways to do it:
Reusing the generated SQL
The most obvious implementation is to directly use the generated SQL query ! Now that we now how the RETURN_RANGE hint was implemented, we can use it directly, example:
Get the SQL implementation:
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization enable(RETURN_RANGE 10 20 'doc_index asc', GENERATE_SQL_ONLY)
generate_sql
----------------------------------
select * from ( select dm_inline_view_1.*, ROW_NUMBER() OVER(ORDER BY dm_inline_view_1.doc_index asc ) as dm_rnum from (select distinct organization.doc_index, organization.subject, organization.object_name, organization.organization_id, organization.organization_name, organization.website, organization.country, organization.description, organization.founded, organization.industry, organization.number_of_employees from organization_sp organization where (organization.i_has_folder = 1 and organization.i_is_deleted = 0) ) dm_inline_view_1) dm_inline_view_2 where dm_rnum >= 10 AND dm_rnum <= 20 order by dm_rnum
(1 row affected)
Use it:
execute exec_sql with query = 'select * from ( select dm_inline_view_1.*, ROW_NUMBER() OVER(ORDER BY dm_inline_view_1.doc_index asc ) as dm_rnum from (select distinct organization.doc_index, organization.subject, organization.object_name, organization.organization_id, organization.organization_name, organization.website, organization.country, organization.description, organization.founded, organization.industry, organization.number_of_employees from organization_sp organization where (organization.i_has_folder = 1 and organization.i_is_deleted = 0) ) dm_inline_view_1) dm_inline_view_2 where dm_rnum >= 10 AND dm_rnum <= 20 order by dm_rnum'
result
------------
T
(1 row affected)
What? No output. Actually, exec_sql is not the right call to use here as it is targeted at DDLs and DMLs, not for SELECTs. We must use a DQL SELECT statement but the generated SQL is not DQL, thus it won’t work. Let’s use a temporary SQL table instead:
execute exec_sql with query = 'create table tmp_sql as select * from ( select dm_inline_view_1.*, ROW_NUMBER() OVER(ORDER BY dm_inline_view_1.doc_index asc ) as dm_rnum from (select distinct organization.doc_index, organization.subject, organization.object_name, organization.organization_id, organization.organization_name, organization.website, organization.country, organization.description, organization.founded, organization.industry, organization.number_of_employees from organization_sp organization where (organization.i_has_folder = 1 and organization.i_is_deleted = 0) ) dm_inline_view_1) dm_inline_view_2 where dm_rnum >= 10 AND dm_rnum <= 20 order by dm_rnum'
result
------------
T
(1 row affected)
And now, select from it:
select * from dm_dbo.tmp_sql order by doc_index
doc_index subject object_name organization_id organization_name website country description founded industry number_of_employees dm_rnum
------------ ----------------------------------------------------------------------------------- ----------------------------- --------------- ----------------------------- ---------------------------------- ---------------------- -------------------------------------------------------------------------------------------------- ---------- ---------------------------------- ------------------- ----------------------
10 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hampton Ltd jRZDRGrfgyWTpAn Hampton Ltd https://www.gilbert.com/ Estonia Chance discussion call yeah. Measure entire season scene behind radio. Than machine ball material. 16-4-1975 Accounting 36020 10
11 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hernandez, Smith and Mcintosh QLpUVwnuZVdIoCw Hernandez, Smith and Mcintosh https://garcia-peterson.com/ Switzerland Live hundred would let food. Else nice firm door still. Hair technology trial. 28-4-2007 Supermarkets 11630 11
12 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Fernandez LLC mCdBZkIqunyExsA Fernandez LLC https://clark-hernandez.com/ Ukraine Health paper child worry thus produce light. Get them hope garden show think. 19-5-1997 Market Research 46500 12
13 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Stevens, Greer and Young QzSMkppsZEbfJQF Stevens, Greer and Young https://www.fuller-moses.com/ Finland Mr discuss pretty after whose actually guy. Music pass movement still. 23-12-1988 Public Safety 3500 13
14 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Zuniga and Sons jVMjZmDdeUejohj Zuniga and Sons https://www.porter-richardson.net/ Gibraltar Agent heart who which dog father. Find day eight her loss. Fine agency say player training. 11-10-1994 Information Services 920 14
15 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Murphy Group zJDwYcXPAnmEvos Murphy Group https://parks.com/ Guyana Democratic room year nature fact century change. Me great fear able watch father compare. 2-7-1999 Textiles 44560 15
16 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Edwards, Howe and Munoz ChLMutRVzUKnzXN Edwards, Howe and Munoz https://nichols-walsh.com/ Vietnam Hotel bring fire laugh really interview remember. 21-4-2006 Aviation / Aerospace 30370 16
17 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Bowers Inc uBmqyxuKEBUcGSg Bowers Inc https://rivera.com/ Libyan Arab Jamahiriya Often than number story land particularly. Term sure surface court drop any crime. 1-12-1981 Outsourcing / Offshoring 7800 17
18 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Fisher-Walker NyMFDEmxlPertcI Fisher-Walker https://estrada.com/ Iraq Today feeling amount. Interview take wide report. Fire price foot probably follow sort. 29-11-2016 Legislative Office 28060 18
19 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Smith, Hawkins and Baker GtqNebcnXThOIEz Smith, Hawkins and Baker https://wheeler.info/ Senegal Manager eight forward each word. Choice defense responsibility develop half tax prove. 6-7-2017 Recreational Facilities / Services 7230 19
20 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Lynch PLC XBPvgaxcwUplVyX Lynch PLC https://www.ferguson.com/ Mauritius Career region along. School summer stock later item. Deal say seat customer. 21-2-2013 Political Organization 23000 20
(11 rows affected)
This time, it is OK.
Set-like operations
Intuitively, the following 11-document select statement:
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization enable(RETURN_RANGE 10 20 'doc_index asc')
is equivalent to:
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization enable(RETURN_TOP 20)
MINUS
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization enable(RETURN_TOP 9)
Unfortunately, there is no diff/MINUS statement in DQL, so this is not possible.
We could try the equivalent not in subquery clause, something like:
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization o where o.doc_index not in (select doc_index from organization enable(RETURN_TOP 10)) order by doc_index enable(RETURN_TOP 10)
but it does not work either; the reason is that although there are 2 RETURN_TOP hints, one in the subquery and one in the enclosing query, only one gets translated into the generated SQL:
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization o where o.doc_index not in (select doc_index from organization enable(RETURN_TOP 10)) order by doc_index enable(RETURN_TOP 10, GENERATE_SQL_ONLY)
select * from ( select distinct o.doc_index, o.subject, o.object_name, o.organization_id, o.organization_name, o.website, o.country, o.description, o.founded, o.industry, o.number_of_employees from organization_sp o where (o.doc_index not in (select distinct organization.doc_index from organization_sp organization where (organization.i_has_folder = 1 and organization.i_is_deleted = 0) )) and (o.i_has_folder = 1 and o.i_is_deleted = 0) order by o.doc_index ) where rownum <= 10
(1 row affected)
and thus the query always returns an empty result. But using a temporary SQL table can help here too:
execute exec_sql with query = 'create table tmp_doc_index as select doc_index from organization_s where rownum < 10'
result
------------
T
(1 row affected)
and then select 11 documents:
select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization o where doc_index not in (select doc_index from dm_dbo.tmp_doc_index) order by doc_index enable(RETURN_TOP 11)
doc_index subject object_name organization_id organization_name website country description founded industry number_of_employees
------------ ----------------------------------------------------------------------------------- ----------------------------- --------------- ----------------------------- ---------------------------------- ---------------------- -------------------------------------------------------------------------------------------------- ---------- ---------------------------------- -------------------
10 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hampton Ltd jRZDRGrfgyWTpAn Hampton Ltd https://www.gilbert.com/ Estonia Chance discussion call yeah. Measure entire season scene behind radio. Than machine ball material. 16-4-1975 Accounting 36020
11 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hernandez, Smith and Mcintosh QLpUVwnuZVdIoCw Hernandez, Smith and Mcintosh https://garcia-peterson.com/ Switzerland Live hundred would let food. Else nice firm door still. Hair technology trial. 28-4-2007 Supermarkets 11630
12 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Fernandez LLC mCdBZkIqunyExsA Fernandez LLC https://clark-hernandez.com/ Ukraine Health paper child worry thus produce light. Get them hope garden show think. 19-5-1997 Market Research 46500
13 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Stevens, Greer and Young QzSMkppsZEbfJQF Stevens, Greer and Young https://www.fuller-moses.com/ Finland Mr discuss pretty after whose actually guy. Music pass movement still. 23-12-1988 Public Safety 3500
14 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Zuniga and Sons jVMjZmDdeUejohj Zuniga and Sons https://www.porter-richardson.net/ Gibraltar Agent heart who which dog father. Find day eight her loss. Fine agency say player training. 11-10-1994 Information Services 920
15 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Murphy Group zJDwYcXPAnmEvos Murphy Group https://parks.com/ Guyana Democratic room year nature fact century change. Me great fear able watch father compare. 2-7-1999 Textiles 44560
16 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Edwards, Howe and Munoz ChLMutRVzUKnzXN Edwards, Howe and Munoz https://nichols-walsh.com/ Vietnam Hotel bring fire laugh really interview remember. 21-4-2006 Aviation / Aerospace 30370
17 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Bowers Inc uBmqyxuKEBUcGSg Bowers Inc https://rivera.com/ Libyan Arab Jamahiriya Often than number story land particularly. Term sure surface court drop any crime. 1-12-1981 Outsourcing / Offshoring 7800
18 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Fisher-Walker NyMFDEmxlPertcI Fisher-Walker https://estrada.com/ Iraq Today feeling amount. Interview take wide report. Fire price foot probably follow sort. 29-11-2016 Legislative Office 28060
19 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Smith, Hawkins and Baker GtqNebcnXThOIEz Smith, Hawkins and Baker https://wheeler.info/ Senegal Manager eight forward each word. Choice defense responsibility develop half tax prove. 6-7-2017 Recreational Facilities / Services 7230
20 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Lynch PLC XBPvgaxcwUplVyX Lynch PLC https://www.ferguson.com/ Mauritius Career region along. School summer stock later item. Deal say seat customer. 21-2-2013 Political Organization 23000
(11 rows affected)
So, this trick requires a 2-step operation:
- firstly, select the identifiant of the
Nrows to skip from the doctype’s SQL tableT_s, whereNcomes fromRETURN_RANGE N + 1 M, into a SQL table; - secondly, select all the needed attributes from the doctype
Tand set theRETURN_TOPhint toM - N + 1;
To make it more robust, add an appropriate sort clause in the SQL table creation statement.
Programmatic alternatives
Such alternatives depend on the programming language in use and require that said language be interfaced to Documentum dmapi run-time library or the java Documentum Foundation Classes (DFCs). In several articles in this blog (cf. Adding a Documentum Extension to gawk, Adding a Documentum extension into python, A few scripting languages for Documentum, DctmAPI.awk revisited), we already presented bindings for gawk, perl and python along with JVM-based languages using the DFCs such as java, jython and groovy. Generally speaking, a programmatically implemented pagination can be done several ways, e.g.:
- Read the entire result set at once into memory, e.g. an array or dictionary, and navigate inside that data structure.
- Use the best features provided by the language to minimize memory imprint so the result set gets cached while being loaded piecewise. For example, python’s generators allows just that; they can be used to cache and return one page of rows at a time during forward pagination. While paginating backwards, the rows can be fetched from the cache and returned, cf. DctmAPI.py revisited for an example. Python also has decorators and it is possible to define one that will invisibly and unobtrusively manage the cache while fetching the result set.
- etc…
There are also numerous scripted solutions, i.e. that don’t necessitate Documentum libraries but rely on external utilities, e.g. Enhancing idql/iapi with rlwrap. To conclude this article, let’s show a very simple alternative that uses the less pager command to navigate. The result set come from Documentum’s standard command-line tool, idql. While we are at it, let’s include the preceding compact.awk script for a better display:
echo "?,c,select doc_index, subject, object_name, organization_id, organization_name, website, country, description, founded, industry, number_of_employees from organization o where doc_index not in (select doc_index from dm_dbo.tmp_doc_index) order by doc_index" | iapi dmtest73 -Udmadmin -Pdmadmin | gawk -f compact.awk | less
API> doc_ind subject object_name organizati _id organization_name website country description founded industry number_of_empl
------------ ----------------------------------------------------------------------------------- -------------------------------- --------------- -------------------------------- ----------------------------------- -------------------------------------------- --------------------------------------------------------------------------------------------------- ------------------- --------------------------------------------------- -------------------
10 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hampton Ltd jRZDRGrfgyWTpAn Hampton Ltd https://www.gilbert.com/ Estonia Chance discussion call yeah. Measure entire season scene behind radio. Than machine ball material. 4/16/1975 00:00:00 Accounting 36020
11 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hernandez, Smith and Mcintosh QLpUVwnuZVdIoCw Hernandez, Smith and Mcintosh https://garcia-peterson.com/ Switzerland Live hundred would let food. Else nice firm door still. Hair technology trial. 4/28/2007 00:00:00 Supermarkets 11630
12 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Fernandez LLC mCdBZkIqunyExsA Fernandez LLC https://clark-hernandez.com/ Ukraine Health paper child worry thus produce light. Get them hope garden show think. 5/19/1997 00:00:00 Market Research 46500
13 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Stevens, Greer and Young QzSMkppsZEbfJQF Stevens, Greer and Young https://www.fuller-moses.com/ Finland Mr discuss pretty after whose actually guy. Music pass movement still. 12/23/1988 00:00:00 Public Safety 3500
14 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Zuniga and Sons jVMjZmDdeUejohj Zuniga and Sons https://www.porter-richardson.net/ Gibraltar Agent heart who which dog father. Find day eight her loss. Fine agency say player training. 10/11/1994 00:00:00 Information Services 920
15 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Murphy Group zJDwYcXPAnmEvos Murphy Group https://parks.com/ Guyana Democratic room year nature fact century change. Me great fear able watch father compare. 7/2/1999 00:00:00 Textiles 44560
16 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Edwards, Howe and Munoz ChLMutRVzUKnzXN Edwards, Howe and Munoz https://nichols-walsh.com/ Vietnam Hotel bring fire laugh really interview remember. 4/21/2006 00:00:00 Aviation / Aerospace 30370
17 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Bowers Inc uBmqyxuKEBUcGSg Bowers Inc https://rivera.com/ Libyan Arab Jamahiriya Often than number story land particularly. Term sure surface court drop any crime. 12/1/1981 00:00:00 Outsourcing / Offshoring 7800
18 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Fisher-Walker NyMFDEmxlPertcI Fisher-Walker https://estrada.com/ Iraq Today feeling amount. Interview take wide report. Fire price foot probably follow sort. 11/29/2016 00:00:00 Legislative Office 28060
19 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Smith, Hawkins and Baker GtqNebcnXThOIEz Smith, Hawkins and Baker https://wheeler.info/ Senegal Manager eight forward each word. Choice defense responsibility develop half tax prove. 7/6/2017 00:00:00 Recreational Facilities / Services 7230
20 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Lynch PLC XBPvgaxcwUplVyX Lynch PLC https://www.ferguson.com/ Mauritius Career region along. School summer stock later item. Deal say seat customer. 2/21/2013 00:00:00 Political Organization 23000
21 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Harris Inc ahvZGlBZLSIxFSU Harris Inc https://www.porter-wells.info/ Sri Lanka Many religious sit enjoy south technology money. Center month stop clearly. Home like carry set. 1/29/1997 00:00:00 Medical Equipment 40240
...
199 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Hancock, Turner and Robinson swilppvRqeWoIha Hancock, Turner and Robinson https://www.davila-collins.biz/ Sri Lanka Star right effort modern six. 10/15/1980 00:00:00 Biotechnology / Greentech 20300
200 Fake document for testing pagination in a result set through the RETURN_RANGE hint; Spears Group zhLEEoIUUPmHqkF Spears Group https://www.vasquez.info/ Uganda Not staff success possible special. Exactly Congress local billion without fly staff expert. 11/4/2004 00:00:00 Food / Beverages 22240
(191 rows affected)
Here, the result set is read into memory, page after page, as less requests them, until it gets entirely loaded in memory. Obviously, such an approach is quite awkward if the data are huge but for most interactive work, it is quite acceptable. Actually, the whole concept of paginating through a result set is mainly for interactive work, when data are displayed, paused to be scrutinized, and resumed. Therefore, it may be necessary to scope them as much as possible to reduce their size and also to speed up the query so the process gets swifter, especially if several iterations are necessary. We saw that the RETURN_RANGE hint has the optimize_top_row clause just for that need. In the above one-line, we would include the OPTIMIZE_TOP n to minimize the waiting time.
Conclusion
I’m afraid this is another TL;DR article. Anyway, several interesting takeaways were shown in this article:
- Using fake data from a website from within a python program;
- Generating fake data using python’s powerful Faker package;
- Compacting a iapi/idql SELECT query output by removing each line’s trailing blanks so it reduces the likelihood of line wrapping for a better look on screen;
- Of course, showing how to paginate through a result set using the
RETURN_RANGEhint; - Putting together a simple one-liner to paginate a result set potentially fully contained in memory;
I hope that some of them may eventually be useful to the reader.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/GME_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/MOP_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/ATR_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/CVI_web-min-scaled.jpg)