JonWat wrote:Jon, you are still going to write code either way.
One option is: I give my program information about the columns in the view and have it create select statements with the required WHERE and ORDER BY clauses, or I could take your approach and have it call a stored procedure which returns the required subset of the view
user9113644 wrote:Yes. The client needs to typically pass parameter data - be that for SQL code or PL/SQL code.
Even if we use stored proc we will be having the same the transit of TCP packets between client and server processes ?
The client needs to pass 5 data values to the cursor (on the server) as input data. The cursor processes this data - in this case, performing an insert.
insert into footab values( :1, :2, :3, :4, :5 )
Again, the client needs to pass 5 data values as input data. The cursor process is different (it will execute a PL/SQL proc) - but from the client side, there is no difference.
begin FooTabInsert( :1, :2, :3, :4, :5 ); end;
If context switches is not an issues can you please let us know why oracle has introduced bulk bindings in PL/SQL ?I did not say it was not an issue. I said that in this specific case, the time for a context switch is far less than the transit time for the data from the client to the server. Which is why array binding by the client is more important than reducing context switching.
A view can hide data model complexities. It can do the tricky joins and even some data transformation in the SQL projection. So a view can decrease complexity by providing a level of abstractionOur applications provide data inhouse to a service bus mostly by views. Some are performance optimized for returning multiple rows, others for a single row (don't call them without the proper where clause, you will not be able to await the first row to return and most likely get an out of memory error)