|
|
|
Parameters in OCL are associated with placeholders in SQL and PL/SQL statements and have a set of methods to store and retrieve relevant data.
OraCommand class manages the way placeholders are resolved into instances of OraParam class. This is done implicitly when a new SQL or PL/SQL statement is assigned to the OraCommand object either through constructor or using setCommandText method.
Individual parameters and their values are maintained by the methods of OraParam class. Objects of this class may be used to store various types of data ranging from character types to PL/SQL tables.
For example, first of all lets construct a new instance of OraCommand class:
OraConnection connection("scott/tiger@ora");
OraCommand cmd(connection);
Then we will set up an actual SQL statement to be executed by the Oracle:
cmd.setCommandText("BEGIN"
" SELECT * INTO :DeptNo, :DName, :Loc"
" FROM Dept"
" WHERE DeptNo = :DeptNo;"
"END");
Here :DeptNo, :DName and :Loc are placeholders for the values to be passed to and from the database. Now we need to describe them more precisely and when it is required to set their initial values:
cmd.param("DeptNo").setDataType(dtInt);
cmd.param("DeptNo").setInt(10);
cmd.param("DName").setDataType(dtString);
cmd.param("Loc").setDataType(dtString);
We accessed individual parameters by specifying their names in param method. Alternatively we could as well provide a numeric value which would tell param method to look for a corresponding placeholder position in command text. The following code is equivalent to the previous:
cmd.param(0).setDataType(dtInt);
cmd.param(0).setInt(10);
cmd.param(1).setDataType(dtString);
cmd.param(2).setDataType(dtString);
At this moment we are ready to execute the statement:
cmd.execute();
Oracle reads input parameter values and places results into the output parameters. To retrieve these values into our program we will just use get-methods of OraParam class:
cout << cmd.param("deptno").getInt() << endl;
cout << cmd.param("dname").getChars() << endl;
When you call stored procedures OCL implicitly provides necessary parameter placeholders. For more information see Working with stored procedures.
See Also
OraConnection, OraCommand, OraParam, Working with stored procedures
OCL | Using OCL | Index
