SQR
SQR is a specialized programming language designed for accessing, manipulating, and reporting enterprise data from database management systems.[1] It enables the development of complex procedures that incorporate multiple calls to data sources, along with nested, hierarchical, and object-oriented logic to process and present information efficiently.[1] As an integral component of enterprise resource planning (ERP) systems, particularly PeopleSoft, SQR functions as a powerful reporting tool that provides direct access to diverse data sources.[1] It supports the generation of clear, professional reports from intricate information systems, allowing for precise control through procedural logic integrated directly into data queries.[1] Beyond reporting, SQR facilitates essential database operations, including manipulation, maintenance, loading and unloading of tables, as well as interactive querying and data display functionalities.[1] This versatility makes it a foundational technology for handling large-scale data processing in business environments.Overview
Definition and Purpose
SQR, or Structured Query Reporter, is a fourth-generation programming language specifically designed for embedding SQL queries within report generation processes. It serves as a specialized tool for accessing, manipulating, and reporting enterprise data from relational databases, enabling users to create structured outputs without the need for more complex general-purpose programming languages.[2] The primary purpose of SQR is to facilitate the generation of custom reports from relational databases by integrating SQL queries for data extraction with procedural logic for processing and advanced output formatting options. This approach allows developers to produce diverse report types, including tabular, cross-tabular, and master-detail formats, while supporting outputs in text, HTML, or PDF.[2][3] At its core, the SQR workflow involves querying databases using embedded SQL to retrieve data, applying programmatic manipulation through variables, loops, and conditional statements, and then formatting the results for presentation or export. SQR was developed in the late 1980s by SQ Software, initially supporting Oracle databases, and later adapted for various relational database management systems, including Gupta Technologies' SQLBase, to simplify report creation and data handling tasks.[2][4]Key Characteristics
SQR is a hybrid programming language that integrates procedural constructs for logic and control flow with native SQL support, enabling seamless database queries and data manipulation within a single program.[5] This design allows developers to embed SQL statements directly alongside procedural commands, facilitating efficient extraction, transformation, and reporting of data from relational databases without needing separate tools or interfaces.[6] A distinguishing feature of SQR is its flexibility in generating outputs across diverse formats, including text files for data exchange, direct printer streams for physical reports, and web-compatible HTML for online viewing, all while supporting customizable elements such as variable fonts, colors, and page layouts.[7] For instance, developers can define color mappings and font specifications in the program setup to produce visually formatted reports tailored to specific output devices or browsers.[8] SQR emphasizes performance through its compiled execution model, where programs are translated into runtime files (.SQT) that execute more rapidly on subsequent runs, making it suitable for large-scale enterprise reporting tasks.[9] This compilation step optimizes code for repeated use, reducing overhead in environments processing high volumes of data.[10] The language achieves platform independence by operating across multiple operating systems and integrating natively with various database management systems, such as Oracle, Microsoft SQL Server, and IBM DB2, through standardized SQL interfaces.[11] This portability ensures that SQR programs can be deployed consistently in heterogeneous enterprise environments without platform-specific modifications.[2] Central to SQR's design is its focus on batch processing, which supports handling high-volume data operations in a non-interactive, command-line environment without reliance on a graphical user interface.[5] This approach is particularly effective for automated, server-side report generation where efficiency and scalability are paramount over real-time user interaction.[12]History
Origins and Early Development
SQR was developed by Israel Stern in Cleveland, Ohio, during the mid-1980s as a reporting tool designed to facilitate the creation of database reports through SQL integration. SQ Software, the company behind SQR, was formally incorporated by Stern in September 1988. The tool emerged as an add-on for database systems, with initial emphasis on enabling users to embed SQL queries within a procedural scripting framework to produce formatted outputs.[4] In March 1988, SQ Software released version 1.5 of SQR, which supported Oracle databases running on IBM PCs as well as minicomputers from vendors such as Prime, DEC, Data General, and Unisys. Concurrently, Gupta Technologies of Menlo Park, California, began offering a customized version of SQR tailored for its SQLBase relational database management system. This integration positioned SQR as a complementary utility for SQLBase users seeking advanced reporting capabilities beyond standard database functions.[4][13] Early releases of SQR concentrated on core functionalities like basic SQL embedding and text-based report generation using structures such as begin-select paragraphs. Marketing was handled by D&N Systems Inc., which also created the SQR Developer's Kit to support customization. By 1987–1990, SQR saw initial adoption among enterprise users for addressing custom reporting requirements, particularly in environments leveraging SQLBase and similar databases, as demonstrated by subsequent corporate milestones including D&N Systems' merger with SybaseSub Inc. (a subsidiary of Sybase) in January 1990 for $3.5 million and the resulting entity SQL Solutions Inc.'s $15 million in annual revenue.[4]Acquisitions and Corporate Evolution
SQR's corporate journey began in the early 1990s when Sybase Inc. acquired SQL Solutions Inc. (formerly D&N Systems, SQR's marketing partner) in January 1990. In June 1992, Sybase acquired the rights to the SQR technology directly from SQ Software.[14][15] This acquisition integrated SQR into Sybase's database ecosystem, enhancing its compatibility with Sybase SQL Server and broadening its use in enterprise reporting environments. In the mid-1990s, Management and Information Technologies Inc. (MITI) acquired SQR from Sybase, rebranding the division as SQRiBE Technologies in 1997 to focus on independent development and expansion of the reporting language's capabilities.[14] The ownership shifted again when Brio Technology agreed in February 1999 to acquire SQRiBE, completing the transaction in August 1999 for approximately $270 million in stock, incorporating SQR into Brio's business intelligence suite and extending its database connectivity to include Oracle, Microsoft SQL Server, and other platforms.[14][16] This move emphasized SQR's role in query and reporting tools, licensing its compiler source code to PeopleSoft Inc. around 2000 for integration into enterprise applications. In October 2003, Hyperion Solutions Corporation acquired Brio Software for $142 million, further embedding SQR within Hyperion's performance management and analytics portfolio, which expanded its support for multidimensional databases like Essbase.[17] Oracle Corporation completed its acquisition of Hyperion in April 2007 for $3.3 billion, consolidating SQR under Oracle's broader business intelligence and enterprise performance management offerings.[18] These successive acquisitions profoundly influenced SQR's evolution by progressively widening its database interoperability—from relational systems to OLAP environments—and facilitating its integration into comprehensive BI platforms. For instance, post-Hyperion acquisition, SQR was rebranded as Hyperion SQR Production Reporting, with System 9 (Release 9.3.1) launched in 2008 to support advanced reporting features aligned with Oracle's ecosystem.[19] Subsequent developments included System 11 (Release 11.1.2) in 2010, which enhanced scalability and web-based deployment options within Oracle Hyperion tools.[20] In 2011, SQR was integrated into PeopleTools 8.52, strengthening its role in PeopleSoft applications for custom reporting and data manipulation.[21] As of November 2025, Oracle continues to maintain SQR under its Lifetime Support Policy, providing indefinite sustaining support without major deprecations, ensuring ongoing compatibility with modern Oracle databases and enterprise systems.[19] This sustained stewardship reflects SQR's enduring value in legacy and hybrid BI environments, despite shifts toward newer reporting paradigms.Products and Implementations
Core SQR Tools
SQR Production Reporting, formerly known as Hyperion SQR, serves as the flagship software tool in the SQR suite, designed for generating high-volume, formatted reports from enterprise data sources. Central to this product is the sqr.exe compiler, which processes source files with the .sqr extension to produce executable reports, while an integrated runtime environment handles their execution. This setup allows developers to create custom reporting programs that access, manipulate, and output data efficiently.[22] The most recent major standalone release, Oracle SQR Production Reporting 11 (version 11.1.2, introduced in 2010), represents the culmination of independent development efforts, offering a complete toolkit for report creation. This version has been under sustaining support since April 2018, with indefinite availability. This version includes integrated development tools within SQR Production Reporting Studio for authoring .sqr files, alongside command-line capabilities for compilation and runtime invocation, enabling seamless workflow from design to deployment in non-integrated environments.[23][24] Key components of SQR Production Reporting encompass SQR Execute, which facilitates batch execution of precompiled programs for automated processing; SQR Print, responsible for rendering output in various formats such as printable files or screen displays from compiled binaries; and supporting utilities like command-line flags for debugging (e.g., -DEBUG for conditional code execution) and basic profiling to monitor cursor usage and performance metrics during report runs. These elements ensure robust handling of report lifecycle tasks without reliance on external platforms.[25] A distinctive capability of SQR Production Reporting lies in its command-line interface, which supports scripting and automation in server-based setups, allowing scheduled or triggered report generation. Additionally, the tool compiles .sqr source code into binary .sqt files, which obscures proprietary logic for enhanced security and optimizes execution speed by pre-processing SQL statements and logic flows. SQR Production Reporting natively supports database connectivity through ODBC or native drivers for major relational databases.[26]Integration with Enterprise Systems
SQR has been deeply integrated into Oracle PeopleSoft since the early 1990s, originally as a core reporting engine licensed by PeopleSoft for generating custom reports from enterprise data.[4] This embedding positions SQR as the go-to tool for handling complex reporting requirements in key modules such as human resources (HR), finance, and customer relationship management (CRM), where it processes large datasets and produces formatted outputs tailored to business needs.[2] The language's procedural capabilities allow developers to manipulate data from PeopleSoft's relational database schema, ensuring reports align with application-specific logic without requiring external tools. Beyond PeopleSoft, SQR finds application in Oracle Hyperion for business intelligence (BI) reporting, particularly through Hyperion SQR Production Reporting, which leverages the language's strengths in creating detailed, customizable production reports from multidimensional data sources.[23] Within the PeopleSoft ecosystem, SQR includes specialized extensions tailored for PeopleTools, such as enhanced functions for metadata handling and integration with the platform's development framework, enabling tighter coupling with application objects and runtime services.[27] In operational use, SQR programs are typically invoked via Application Engine processes or executed directly in batch modes through PeopleSoft's Process Scheduler, facilitating automated workflows in enterprise environments.[28] This invocation model grants SQR direct access to PeopleSoft's metadata repository for dynamic query construction and enforces the system's row-level and query security protocols to maintain data integrity and compliance during report generation.[1][29] As of 2025, SQR continues to serve as a standard for legacy reporting in PeopleSoft 9.2, with Oracle documentation affirming its role in environments requiring robust handling of complex, high-volume data outputs over alternatives like BI Publisher for scenarios demanding intricate procedural logic.[30][1] This enduring recommendation underscores SQR's reliability in maintaining backward compatibility while supporting ongoing customizations in mature PeopleSoft deployments.Technical Components
Language Structure
SQR programs are stored in source files with the .sqr extension, which contain the modular code organized into distinct sections for clarity and functionality.[31] The primary sections include BEGIN-SETUP, which handles initial declarations such as variables, report layouts, database connections, and printer configurations; BEGIN-PROGRAM, serving as the main entry point for executing program logic and calling procedures; BEGIN-REPORT, dedicated to report-specific elements like headings, footings, and output formatting; and BEGIN-SQL, used for embedding non-SELECT SQL statements within other sections to perform updates, inserts, or deletes.[31] This sectional structure promotes modularity, allowing developers to separate setup, logic, reporting, and data manipulation tasks.[31] The compilation process transforms .sqr source code into binary runtime files in .sqt format, using the SQR compiler invoked via command-line flags that specify database types (e.g., Oracle, SQL Server) and output formats.[10] Flags such as -RS enable saving the compiled program as a .sqt file for reuse, while options like -DEBUG activate debugging modes and -M adjust memory limits to handle larger programs.[31] SQR employs a multi-pass compilation approach, where the code is analyzed multiple times to resolve dependencies, enabling forward references to variables and procedures defined later in the file without requiring explicit ordering.[31] This feature, controlled by settings like FORWARDREFS, reduces errors from undefined references during initial passes and optimizes query execution.[31] Error handling in SQR integrates built-in mechanisms for diagnostics and recovery, such as the #DEBUG preprocessor directive, which conditionally compiles and outputs debug messages to trace program flow.[31] Trace flags, invoked via command-line options like -S for cursor status or -TR for detailed execution tracing, log runtime events to files or screens, aiding in identifying issues like SQL errors or version mismatches (e.g., error 004723).[31] Additionally, the ON-ERROR command traps database errors and redirects execution to specified procedures, enhancing robustness in production environments.[31] These tools collectively support efficient development and maintenance of SQR programs.[31]Runtime Environment
SQR programs execute through the SQR Execute runtime program (SQRT on Unix/Linux or SQRWT on Windows), which operates in a command-line interface or within server environments such as the PeopleSoft Process Scheduler. This runtime loads SQR program files (.sqt or .sqr) and processes them by connecting to a database management system (DBMS) for data retrieval and manipulation. Connections are established via ODBC for broad compatibility, JDBC in Java-integrated setups, or native drivers specific to the DBMS, allowing seamless integration with enterprise data sources without requiring embedded SQL compilation.[32][33] The runtime supports full native compatibility with Oracle Database, Microsoft SQL Server, and IBM DB2 (including z/OS), enabling optimized SQL execution and direct API access for high-performance reporting.[34] This compatibility aligns with PeopleTools certifications, ensuring SQR can operate across certified platform combinations without custom modifications.[2] Performance tuning in the SQR runtime focuses on optimizing resource usage for large-scale reports, with key options including the -B flag applied to BEGIN-SELECT statements for row buffering and batch fetching, which reduces database round trips by processing records in configurable batches (e.g., -B50 to fetch 50 rows at a time). The -F command-line flag specifies custom output file names and extensions, indirectly aiding performance by directing output streams efficiently, while font handling in print outputs is managed via printer-specific flags like -PRINTER:HP for line printer compatibility, ensuring consistent rendering without excessive memory allocation. For memory management with large datasets, runtime parameters in the sqr.ini file control array sizes and caching (e.g., MaxArraySize), preventing overflows during complex computations; these are critical for handling enterprise volumes exceeding millions of rows.[2][35] As of 2025, Oracle continues to maintain the SQR runtime environment for 64-bit architectures in PeopleTools 8.62, supporting modern server deployments on platforms like Oracle Linux and Windows Server; however, 32-bit support has been deprecated in PeopleSoft environments since PeopleTools 8.54, with warnings issued for legacy 32-bit clients to migrate for compatibility and security. This shift emphasizes 64-bit optimizations for enhanced scalability in cloud and on-premises setups.[36][37]Features
Reporting and Formatting
SQR's reporting capabilities emphasize precise control over output generation and presentation, enabling developers to create structured, professional reports tailored to enterprise needs. Central to this are output control commands such as PRINT, which positions and formats data on the page; NEW-PAGE, which inserts form feeds to manage pagination; and HEADINGS, which define report titles and dates in the page header area. These commands allow for dynamic layout adjustments, including automatic page breaks triggered by data volume to prevent overflow and ensure readability across varying dataset sizes.[11][38][39] Formatting options in SQR extend to visual enhancements like variable fonts, selectable via the PSSQR.INI file for Windows printer outputs; colors for text, backgrounds, and boxes using attributes such as BOX-FILL-COLOR and BOX-LINE-COLOR; and the inclusion of lines and images, particularly in HTML-generated reports where background images and text colors can be specified through extensions. The PRINT command supports these via qualifiers like BOLD, CENTER, and edit masks for numeric and date formatting, ensuring consistent styling without relying on external tools. Dynamic elements, such as conditional bolding or color changes based on data values, further enhance presentation while maintaining compatibility with multiple output formats.[40][41][42] SQR supports diverse export formats through command-line flags, including HTML via -PRINTER:EH or -PRINTER:HT for web-ready reports; PDF via -PRINTER:PD for printable documents; and CSV via -PRINTER:CSV or WRITE commands, which facilitate direct import into Excel for spreadsheet analysis. These flags enable seamless integration with enterprise systems, producing outputs like .htm, .pdf, or .csv files without additional processing.[35][43][44] A distinctive feature of SQR's report design is its ruler-based positioning system, where elements are placed using absolute or relative (row, column) coordinates on a grid defaulting to 10 characters per inch horizontally and 6 lines per inch vertically. This allows pixel-level precision in text outputs, akin to a word processor's ruler, by specifying positions in PRINT commands—e.g., PRINT variable (+1, 10) to advance one row and position at column 10—facilitating complex layouts like multi-column reports or forms without graphical design tools.[45][46][47] For graphical representations, SQR integrates charting capabilities through DECLARE-CHART and PRINT-CHART commands, supporting types such as bar, pie, line, stacked bar, area, and polar charts. These can visualize multidimensional data from sources like Essbase, with attributes for titles, legends, and scaling defined at declaration and overridden at print time for flexibility in reports combining tabular data with visuals.[48][49]Data Access and Manipulation
SQR provides robust mechanisms for embedding SQL statements directly within its programs, enabling seamless database interactions for data retrieval and modification. The language supports full execution of SELECT, INSERT, UPDATE, and DELETE operations inside BEGIN-SQL and END-SQL blocks, allowing developers to query and alter data without leaving the SQR environment.[50] For instance, a basic SELECT query can be written asBEGIN-SQL SELECT name FROM customers END-SQL, which fetches data into variables or cursors for further processing.[50] This integration ensures that database operations are tightly coupled with SQR's procedural logic, facilitating efficient data pipelines in enterprise reporting.[50]
Dynamic SQL construction enhances flexibility by permitting runtime query assembly through bind variables for substitution. Developers can adapt queries using bind variables, such as in WHERE clauses with ¶m (e.g., BEGIN-SELECT ... WHERE table_name = ¶m_value END-SELECT), to handle variable conditions or user inputs, though it requires careful management to avoid SQL injection risks and type errors.[50] This approach is particularly useful for parameterizing queries based on runtime criteria.[50]
Data handling in SQR emphasizes temporary storage and iterative processing to manage datasets effectively before reporting. Arrays serve as in-memory structures for holding multiple records, created with commands like CREATE-ARRAY NAME=emps SIZE=100 FIELD=[Salary](/page/Salary):number, and populated using PUT and GET operations for quick access and manipulation.[50] For file-based exchange, file I/O commands like OPEN and READ enable importing data from external files into variables and then into arrays, or WRITE for exporting results, such as reading delimited text for integration with non-database sources.[50] Cursors, implemented via BEGIN-SELECT ... END-SELECT blocks, allow row-by-row processing of query results, with features like LOOPS to control iteration limits, often combined with control structures for conditional traversal.[50]
SQR includes a suite of built-in functions for transforming data during processing, categorized by type to support diverse operations. String manipulation is handled by functions such as STRCAT for concatenation (e.g., LET $full_name = STRCAT($first, ' ', $last)) and STRTOKEN for parsing delimited strings into tokens.[50] Numeric operations utilize commands like ADD, SUB, MULTIPLY, and DIVIDE for arithmetic on variables, as in ADD 1 TO #counter to increment counters or totals.[50] Date arithmetic is facilitated by functions including DATEADD for adding intervals, DATEDIFF for calculating differences, and STRTODATE for conversions, enabling computations like LET #days = DATEDIFF(DATENOW(), $start_date).[50]
To manage large result sets efficiently, SQR employs ASK and LET commands for parameterizing queries and conditional data loading, reducing unnecessary processing. The ASK command prompts users for input at runtime, such as ASK $state 'Enter state', which can filter SQL WHERE clauses to load only relevant data.[50] LET statements then assign these values or apply conditions, like LET #limit = 1000 IF $param = 'Y', enabling dynamic limits on cursors or LOAD-LOOKUP operations to handle voluminous datasets without overwhelming memory.[50] This combination promotes performance in high-volume environments by allowing selective data ingestion based on runtime criteria.[50]
Syntax and Programming Constructs
Variables and Data Types
In SQR, variables are prefixed with specific characters to indicate their data types, facilitating implicit typing without formal declarations in most cases. Numeric variables, used for integers and decimals, are prefixed with #, such as #counter or #total_amount, and are initialized to zero upon first use. String variables, which handle character data and dates, are prefixed with , for example name or $report_date, and start as null strings. Additionally, database column variables are read-only and prefixed with &, like &employee_id, allowing direct reference to query results without assignment.[51][52] Variables are declared implicitly through assignment using the LET statement, which supports expressions for storing and manipulating data. For instance,let #sum = #a + #b assigns the sum of two numeric variables, while let $message = 'Total: ' || to_char(#sum) concatenates a string with a converted numeric value. Explicit declaration using DECLARE-VARIABLE is optional but recommended for date variables, particularly for date arithmetic; it can be done in the SETUP section or within procedures, such as declare-variable date $hire_date. Scope is managed by context: variables without an underscore prefix are local to procedures, whereas global variables are denoted by an initial underscore, like &_global_total, accessible across the program. For multi-dimensional storage, the CREATE-ARRAY command defines arrays with specified sizes and field types, enabling structured data like create-array customers size=1000 field name:char(30) balance:number, which supports complex reporting needs beyond simple scalars.[51][52][53]
Type conversions are handled via built-in functions to integrate different data types in expressions. The TO_CHAR function converts numbers or dates to strings, as in to_char(#amount, '9999.99') for formatted output, while TO_NUMBER parses strings to numerics, such as to_number($input_value). These functions are essential for operations involving mixed types, like arithmetic on string-derived values.[51][52]
SQR employs no strict typing, permitting flexible assignments where a variable's type is determined by its prefix and initial value, which allows dynamic usage but demands careful management to avoid errors. This approach supports rapid development for reports but requires explicit conversions and precision controls, particularly in financial applications where decimal numerics (up to 38 digits) are preferred over floating-point (approximately 15 digits) to maintain exact calculations.[51]
Control Flow and Procedures
SQR provides conditional statements for decision-making through the IF-THEN-ELSE construct, which evaluates a logical expression and executes commands accordingly. The syntax isIF condition THEN commands [ELSE commands] END-IF, where the condition uses operators such as equality (=), greater than (>), less than (<), and string matching with LIKE. For instance, IF #total > 0 THEN PRINT 'Positive' () ELSE PRINT 'Negative' () END-IF demonstrates basic usage, with nesting allowed up to system-defined limits to prevent stack overflow.[31]
For multi-case branching, SQR employs the EVALUATE statement, akin to a switch mechanism, which compares an expression against multiple values. The structure is EVALUATE expression WHEN value1 DO commands [WHEN value2 DO commands] [DEFAULT DO commands] END-EVALUATE, supporting up to 70 WHEN clauses by default and features like BREAK to exit early. An example is EVALUATE $status WHEN 'OK' DO [PRINT](/page/Print) 'Success' () WHEN 'ERROR' DO [PRINT](/page/Print) 'Failed' () [DEFAULT](/page/Default) DO [PRINT](/page/Print) 'Unknown' () END-EVALUATE, enabling efficient handling of discrete conditions such as status codes.[31]
Looping in SQR supports iterative execution via WHILE-END-WHILE for condition-based repetition, structured as WHILE condition DO commands END-WHILE, where nonzero evaluates to true. This allows nesting with IF or EVALUATE, as in WHILE #count < 10 DO [PRINT](/page/Print) #count () LET #count = #count + 1 END-WHILE. Additionally, DO-END-DO delimits blocks of commands or procedure calls, while BEGIN-SELECT implicitly loops over SQL query results, processing each row with embedded DO commands, such as BEGIN-SELECT DO [PRINT](/page/Print) &name () FROM employees END-SELECT. The LOOPS=nn option limits iterations to a maximum of 32,767 for performance control.[31]
Procedures in SQR enable code modularity, defined with BEGIN-PROCEDURE name [LOCAL] [(parameters)] commands END-PROCEDURE, where LOCAL confines variables to the procedure's scope and parameters accept numeric (#), string (), [date](/page/Date), or column (&) types. Invocation occurs via `DO name (arguments)` or `CALL name`, passing values like `DO CalcTotal (#sum, label), with return values prefixed by colon (e.g., :#result`). SQR lacks distinct functions but supports procedure recursion, maintaining a single persistent copy of local variables across calls to optimize memory, though depth is constrained by stack resources.[31][11]