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();