Running Oracle PL/SQL or SQL Server/Sybase Transact-SQL

The section relates to the running of:

• PL/SQL (Oracle)
• Transact-SQL (SQL Server and Sybase)

In the Run SQL window, you can run a block of SQL code written in these languages. This may include Select / Update statements as well as conditional logic

Example (Oracle):

DECLARE maxsal NUMBER;

BEGIN

  SELECT MAX(SALARY) into maxsal FROM HR.EMPLOYEES;

  IF (maxsal > 0) THEN 

     UPDATE HR.EMPLOYEES SET SALARY = maxsal WHERE JOB_TITLE = 'DBA';

  END IF;

END;

However, in order to do run these, you must:

• select Options > Run SQL. Change Statement Delimiter to None
• select Options > Technical Parameters. Check Use SQLExecDirect.

The first of these options is needed if your code includes a semicolon. By default AQT will interpret a semicolon as a statement delimiter. You do not want this to happen when running a block of code.

The second option is only required when running Oracle PL/SQL which includes variables (such as :CUSTCODE). In the normal way of running, the Oracle ODBC Driver will see the :CUSTCODE and think it is a substitutional parameter; this will result in AQT prompting for it. By setting SQLExecDirect, AQT will process the SQL in a slightly different way that prevents this problem from happening.