Using PL/SQL via the TOraSQL Class

This topic describes how to use PL/SQL with ODAC by the help of TOraSQL class.

For work with PL/SQL blocks, the TOraSQL component is used, that allows using parameters and macros in PL/SQL code.

A sample application updating data in a table and returning the number of modified records to the console:

[Delphi]

  OraSQL1.SQL.Text := 'DECLARE' + #13#10 +
	                    '  cnt NUMBER;' + #13#10 +
                      'BEGIN' + #13#10 +
	                    'SELECT DEPTNO' + #13#10 +
                      '  INTO cnt' + #13#10 +
		                  'FROM DEPT' + #13#10 +
		                  'WHERE DNAME = :DNAME;' + #13#10 +
                      '  UPDATE EMP SET SAL=SAL + 100 WHERE DEPTNO = cnt;' + #13#10 +
                      '  :RES := sql%rowcount;' + #13#10 +
                      'EXCEPTION' + #13#10 +
                      '  WHEN NO_DATA_FOUND THEN' + #13#10 +
			                '    DBMS_OUTPUT.PUT_LINE(''Row for update in EMP no found'');' + #13#10 +
                      '  WHEN TOO_MANY_ROWS THEN' + #13#10 +
                      '    DBMS_OUTPUT.put_line(''Query returm more that one row'');' + #13#10 +
                      'END;';
  OraSQL1.ParamByName('DNAME').DataType := ftString;
  OraSQL1.ParamByName('DNAME').ParamType := ptInput;
  OraSQL1.ParamByName('DNAME').AsString := 'ACCOUNT';
  OraSQL1.ParamByName('RES').DataType := ftInteger;
  OraSQL1.ParamByName('RES').ParamType := ptOutput;
  OraSQL1.Execute;
  ShowMessage(Format('Rows in EMP updated: %d',[OraSQL1.ParamByName('RES').AsInteger]));

[C++ Builder]

  OraSQL1->SQL->Text = "DECLARE\n"
						"  cnt NUMBER;\n"
						"BEGIN\n"
						"SELECT DEPTNO\n"
						"  INTO cnt\n"
						"FROM DEPT\n"
						"WHERE DNAME = :DNAME;\n"
						"  UPDATE EMP SET SAL=SAL + 100 WHERE DEPTNO = cnt;\n"
						"  :RES := sql%rowcount;\n"
						"EXCEPTION\n"
						"  WHEN NO_DATA_FOUND THEN\n"
						"    DBMS_OUTPUT->PUT_LINE('Row for update in EMP no found');\n"
						"  WHEN TOO_MANY_ROWS THEN\n"
						"    DBMS_OUTPUT->put_line('Query returm more that one row');\n"
						"END;";
  OraSQL1->ParamByName("DNAME")->DataType = ftString;
  OraSQL1->ParamByName("DNAME")->ParamType = ptInput;
  OraSQL1->ParamByName("DNAME")->AsString = "ACCOUNT";
  OraSQL1->ParamByName("RES")->DataType = ftInteger;
  OraSQL1->ParamByName("RES")->ParamType = ptOutput;
  OraSQL1->Execute();