Progress
DataServer
for ORACLE Guide
Sending SQL Statements
Progress uses stored-procedure syntax to allow you to send SQL statements directly to the ORACLE RDBMS. You can send either one SQL statement or one PL/SQL block. The DataServer uses the RUN STORED-PROCEDURE statement with the send-sql-statement option to pass SQL statements to ORACLE. Although Progress SQL also allows you to use SQL statements, send-sql-statement option gives you access to the complete ORACLE PL/SQL syntax. For example, you can issue SQL statements from within Progress procedures to modify the data definitions of your ORACLE database.
You use the RUN STORED-PROC statement with the send-sql-statement option and pass the PL/SQL statements as a parameter. The SQL statements that you pass must conform to PL/SQL syntax. For example, this code passes a SELECT statement as a parameter:
This example returns the customer number and name of each customer whose name begins with A. You can use the results just as you use results from your ORACLE or Progress database. This procedure reads the results into the proc-text-buffer, which is a buffer defined by Progress that accepts only CHARACTER data. A buffer allows you to access individual columns. If you do not use a buffer, each database row returns as a character string. You can also define a buffer that accepts a data type other than the CHARACTER data type, such as LONG or LONG RAW. See the "Defining a Buffer" section, below.
NOTE: Specify a handle for the stored procedure, as in the example. Do not use the default system handle with the send-sql-statement option. The DataServer passes the SQL statement directly to ORACLE. The Progress compiler does not process it, so errors (including syntactical errors) occur only at run time and not when you compile a procedure.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |