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:

DEFINE VAR h1 AS INTEGER.
RUN STORED-PROC send-sql-statement h1 = PROC-HANDLE
("SELECT cust_num, name
FROM customer WHERE name like ’A_’")
FOR EACH proc-text-buffer WHERE PROC-HANDLE = h1:
  DISPLAY proc-text-buffer.
END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = h1. 

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