Skip to main contentSkip to search
Powered by Zoomin Software. For more details please contactZoomin
Progress DocumentationProgress Documentation
Progress Documentation
  • Home
  • Home
  • EnglishČeštinaDeutsch (Germany)Español (Spain)FrançaisItaliano (Italy)Português (Brasil)日本語Русский (Russia)中文 (简体) (China)中文 (繁體, 台灣) (Taiwan)ar-AR
  • Login

Use the Oracle Data Server

Sample Queries

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
Print
Table of Contents
  • Introduction
    • Oracle DataServer overview
    • DataServer components
      • OpenEdge DataServer for Oracle logic
      • The schema holder
      • Security
        • OpenEdge security
        • Oracle security
      • DataServer utilities
      • DataServer demonstration database
      • DataServer configurations
      • TLS enabled DataServer
      • Distributed DataServer applications
        • Distributed DataServer configuration with TLS
        • The Distributed DataServer using the Unified Broker Framework
          • The Distributed DataServer using ProBroker
          • A closer look at OpenEdge Management or OpenEdge Explorer tool
          • Introducing the mergeprop utility
    • Software requirements
    • Guidelines for using the DataServer
      • Using the OpenEdge DataServer for Oracle for the first time
      • Using the DataServer to migrate a database from OpenEdge to Oracle
      • Upgrading to the OpenEdge Release 10 DataServer for Oracle
    • Documentation resources
      • Guide to related topics
  • Initial Programming Considerations
    • Database design issues
      • Oracle and OpenEdge objects and terminology
      • Naming conventions
        • Field and column names
        • Table names
      • Code pages
        • Client code page
      • Support for Unicode
        • Support for Unicode character sets
        • Support for Unicode data types
        • Configuration requirements
        • Implications of data widening
      • Indexes
        • Database index key widths
        • Leading and trailing spaces
      • Case-sensitive
      • Index repositioning
      • Initial values
      • Oracle views
        • Multi-table views
        • Views containing aggregates
      • Database triggers
      • Sequence generator
        • Comparing OpenEdge and Oracle sequence generator features
        • The INT64 data type effects on Oracle database sequences
      • Oracle synonyms
        • Synonyms and distributed databases
    • Data types
      • Oracle NUMBER data type
      • Oracle CHAR and VARCHAR2 data types
      • Oracle Number data
      • Logical data type and Oracle equivalents
      • ABL supports RAW data type
      • Oracle BLOB and BFILE data types
      • Oracle CLOB and CFILE data types
      • Oracle DATE data type
      • Working with Datetime data types
        • ABL datetime data types
        • Oracle datetime data types
        • Performing data type conversions
        • Converting data types within ABL
        • Pre-selection criteria for the schema pull
        • Converting datetime data types between OpenEdge and Oracle
        • Converting ABL data types to Oracle data types
        • Converting Oracle TIMESTAMP types to ABL DATETIME types
        • Working with Oracle 8 and earlier releases
        • Working with Oracle 9i and later releases
        • Data type value retention
      • Unsupported data types
    • Arrays
    • Unknown Value (?)
      • Zero-length character strings
    • Record creation
      • Comparing record creation behavior
      • Ensuring record are created in a predictable order
    • Record locking
      • Monitoring locks
    • Transactions
      • Handling rollback of sub-transactions
    • Error handling
      • Overflow checking
    • Cursors
      • Static FIND cursor repositioning
      • Stale cursors
      • Allocating cursors
    • ABL issues
      • RECID function
      • ROWID function
      • Progress_Recid change
      • DEFINE BROWSE statement
      • Field lists
      • FIND PREV/LAST statements
      • Compiling ABL procedures
        • r-code
      • Relaxed CRC validation rule for pre-compiled r-code
      • Unsupported ABL statements
      • National Language Support
    • Handling lock timeouts
    • RDBMS Stored Procedures
  • RDBMS Stored Procedure Details
    • Overview
      • Defining native stored procedures to ABL
    • RDBMS stored procedure basics
    • RUN STORED-PROCEDURE details
      • Syntax reference for RUN STORED-PROCEDURE
      • RUN STORED-PROCEDURE statement
        • LOAD-RESULT-INTO phrase
        • PROC-STATUS phrase
        • PROC-HANDLE phrase
        • NO-ERROR option
        • PARAM phrase
      • CLOSE STORED-PROCEDURE statement
      • RUN STORED-PROC statement execution without the LOAD-RESULT-INTO phrase
      • RUN STORED-PROC statement execution with the LOAD-RESULT-INTO phrase
      • RUN STORED-PROC statement with send-sql-statement option
        • Without the LOAD-RESULT-INTO option
        • With the LOAD-RESULT-INTO option
      • RUN STORED-PROC statement with special internal option
        • ds-close-cursor option
    • Data output and retrieval options
      • Return codes
      • Values of output parameters you define when you create a procedure
      • Results retrieved from a database
        • Using the OpenEdge-supplied proc-text-buffer for row results
        • Defining a special view on the Oracle data source to use as a buffer for row results
        • Loading results into a temp-table
    • Interfacing with RDBMS stored procedures
      • Retrieving return codes
      • Retrieving output parameter values
      • Retrieving results with cursor arguments
      • Retrieving results sets using proc-text-buffer
        • Technique to use proc-text-buffer
        • Assessing a result set obtained from the proc-text-buffer technique
      • Defining a view to use as a buffer
        • Technique to define a view to use as a buffer
        • Assessing result sets obtained by defining a view as buffer technique
        • Examples based on views created in the data source
      • Loading a result set into a temp-table
        • Getting started
        • Employing additional enhancements
        • Creating a temp-table layout plan
        • Using a temp-table handle with an unprepared dynamic temp-table
        • Details about a dynamic temp-table in an unprepared state
        • Details about a Dynamic temp-table in a prepared state
        • Additional temp-table examples
    • Handling errors
      • Error messages related to using the LOAD-RESULT-INTO phrase
    • ROWID support
      • Understanding the ROWID Implementation
      • ROWID characteristics
        • ROWID: Standard ABL behavior
      • Using ROWID with RUN STORED-PROCEDURE and LOAD-RESULT-INTO
      • Additional ProDataSet support
  • Additional Features to Enhance DataServer Performance
    • Enhancements overview
    • SQL and ABL queries
    • Bind variables
    • Caching records
    • Oracle hints
    • Using field lists when updating records
    • Setting the data-source result set retrieval option
    • Join by SQL DB
      • Improving join performance for queries with REVERSE-FROM option
      • Improving join performance for queries with FIRST or LAST criteria
    • Skipping schema verification
    • Writing queries for performance
    • Bulk-insert with ABL
    • Closing inactive cursors
    • Query tuning
  • Configuring the DataServer
    • DataServer modules
    • Configuring Windows modules
      • Configuring the local DataServer in Windows
      • Configuring the DataServer in the Windows host
    • Configuring UNIX modules
      • Configuring the local DataServer on UNIX
      • Configuring the DataServer on the UNIX host
    • Configuring an international environment
    • Configuring the DataServer in the Unified Broker framework
      • Configuring the Unified Broker framework in Windows
      • Configuring the Unified Broker framework on UNIX
        • Validating ubroker.properties content
        • Configuring multiple brokers
        • Using the NameServer
      • Establishing the TLS protocol in a DataServer broker instance of the Unified Broker
    • Creating, Maintaining, and Deploying a schema holder
      • Schema holder security
        • Permissions for creating a schema holder
        • Authorization and Authentication for connecting a schema holder
        • Database Authentication During Foreign Database Connect
      • Running Oracle
      • Starting the DataServer processes
      • Starting ProBroker distributed DataServer broker with TLS-enabled Progress Networking
        • Non-default TLS certificates
      • Creating an empty OpenEdge database
      • Using the DataServer utility to create a schema image
      • Maintaining a schema holder
      • Deploying a schema holder
      • Setting up a schema holder as a Windows service
    • Configuring a self-service DataServer agent in the OpenEdge Appserver and Progress Application Server
      • Configuring the OpenEdge AppServer agent as a self-service DataServer session
      • Configuring the Progress Application Server agent as a self-service DataServer session
  • Connecting the DataServer
    • Starting the local DataServer
    • Starting the remote DataServer
      • Starting the DataServer in the Unified Broker framework
        • In the Windows host
        • On the UNIX host
        • On the client
      • Starting and stopping the ProBroker process from the command line
      • Starting the UNIX client process
      • Starting the Windows client process
    • Connecting a schema holder
      • Connecting a schema holder at startup
        • Remote DataServer: Windows client
        • Remote DataServer via NameServer: Windows client
        • Local DataServer: Windows
        • Remote DataServer: UNIX client
        • Local DataServer: UNIX
        • Connecting a Schema holder and a broker using TLS-enabled Progress Networking
      • Connecting through Oracle Networking
        • Connecting with external credentials
      • Optional connection and startup parameters
      • Query tuning with connection and startup parameters
      • Setting a session's timestamp
      • Analyzing application execution with Enhanced Logger
        • LOG-MANAGER system handle attributes and methods
        • DSLOG-MANAGER system handle attributes and methods
        • Log entry types
        • Logging levels
        • Setting the DataServer logging levels and log entry types
        • Server Context Log File Naming
        • Creating a unique log file per connected client
        • Enabling ABL to SQL Correlation in Logging
        • qt_debug logging
      • Index cursors
      • Local schema caching
    • Connection failures and OpenEdge responses
      • Connection troubleshooting
      • Accessing the DataServer log using qt_debug logging options
        • Naming the log file
        • Setting the log file location
        • Handling log file creation errors
        • Restrictions on dynamic changes to log file
        • Using the $UNIQUE_DSLOG environment variable
  • The DataServer Tutorial
    • Oracle Utilities menu
    • Managing server attributes
      • Overview of foreign database constraint definitions
      • Constraint definition names
      • Defining constraint definitions in Data Administration
        • Defining a primary key constraint definition
        • Defining a unique key constraint definition
        • Defining a foreign key constraint definition
        • Defining a check constraint definition
      • Modifying an existing constraint definition
      • Activating or deactivating a class of existing constraint definitions
      • Deleting an existing constraint definition
    • Creating a schema image
    • Updating a schema image
    • Verifying a schema image
    • Changing logical name and connection information
    • Modifying a schema image
      • Modifying table-level information
      • Modifying field-level information
      • Defining the ROWID
    • Changing a code page
    • Deleting a schema image
    • Migrating an OpenEdge Database to Oracle with PRO/SQL
    • OpenEdge DB-to-Oracle utility
      • Preparing a database for the utility
      • Oracle size limitations
        • Column names
        • Column width
          • Changing the width of the column
      • Naming conventions
      • Oracle tablespaces for tables and indexes
      • Running the OpenEdge DB-to-Oracle utility
        • Running the OpenEdge DB-to-Oracle utility in batch mode
      • Handling character length during database migration
        • Handling character type conversions to CLOB
    • OpenEdge DB-to-Oracle Incremental Schema Migration utility
      • Adjusting field widths during migration
      • Updating the Oracle database
    • Adjust an Oracle schema image to an OpenEdge database
  • Upgrading DataServer Applications
    • Manually adding PROGRESS_RECID to Oracle
  • Environment Variables
  • Sample Queries
    • Query 1
    • Query 2
    • Query 3
    • Query 4
    • Query 5
    • Query 6
    • Query 7
    • Query 8
    • Query 9
    • Query 10
    • Query 11
    • Query 12
    • Query 13
  • Building DataServer Executables
    • Overview of the building process
    • Building executables for the UNIX Client
      • Replacing your default client executable:
      • Identifing your new executable with an environment variable and without relocating
    • Building executables for the UNIX Host
      • Building a UNIX host executable
      • Replacing your default server executable
      • Identifying your new executable with an environment variable and without relocating
  • DataServer Command Line Utilities and Startup Parameters
    • Command line utilities for the DataServer
      • NSCONFIG utility
      • NSMAN utility
      • ORACONFIG utility
      • ORAMAN utility
      • PROBRKR command line syntax
      • PROSHUT command
Table of Contents

Sample Queries

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
Print
  • Last Updated: January 17, 2024
  • 1 minute read
    • OpenEdge
    • Version 12.8
    • Documentation

This appendix contains sample queries and the information that the DataServer provides when you specify the QUERY-TUNING (DEBUG SQL) option. In each case, notes explain the DataServer and cursor behavior. The numbers in angle brackets (<n>) indicate cursor handles for SQL statements.

Contents
  • Query 1
  • Query 2
  • Query 3
  • Query 4
  • Query 5
  • Query 6
  • Query 7
  • Query 8
  • Query 9
  • Query 10
  • Query 11
  • Query 12
  • Query 13
TitleResults for “How to create a CRG?”Also Available inAlert