|
| 10:07:11 | Jeffrey Kemp | Customisable TAPIs + XAPIs FTW! :) <ducks and runs> |
| 10:10:32 | Jeffrey Kemp | I've found a lot of value in using TAPIs that provide bulk ins/upd/del operations useful to be called from XAPIs that provide the orchestration. |
| 10:12:51 | syntegris information solutions GmbH | Sven Weller: Agree with JSON. I often write APIs to convert plsql collections into json. And even if it is just for logging purposes. |
| 10:12:53 | Michael Moore | I'm working on some JSON APIs right now |
| 10:14:02 | syntegris information solutions GmbH | Just to trace what values I have im my sometimes fairly complex collections. I convert them to json and write them to logger tables. |
| 10:14:26 | syntegris information solutions GmbH | Yes |
| 10:14:26 | mentzel iudith | In a real life application, with a table have many columns, it is maybe true that there are not too many types of INSERTS to that table, but it is very likely that there are lots of types of UPDATES, of different columns and combinations of columns. Is a TAPI supposed to contain ALL those types of different update statements ? Wouldn't this make the caller XAPI too cumbersome ? |
| 10:18:04 | Jeff Kemp | Most tables only need a single update procedure; the more complex tables typically have *groups* of columns that are typically updated at the same time; callers would choose the update procedure most appropriate for them; occasionally I'll add a new update procedure for particular purposes. Most complex TAPI I've worked with had maybe 7-8 update procedure variations. |
| 10:18:36 | * Steven Feuerstein | Excellent! Thanks for the real world experience. |
| 10:22:31 | Pete K | QuickSQL is handy for creating simple TAPI packages to play around with the concept. |
| 10:22:43 | * Steven Feuerstein | Great point! |
| 10:24:23 | mentzel iudith | What about many variations of WHERE clauses. for updating a specific set of records ? I guess that the API will always update rows based on PK-s only, most of types probably using FORALL. |
| 10:25:15 | Jeff Kemp | In my XAPIs, they query the table to determine which rows it wants to update (that's where the WHERE is); it then passes the array to the TAPI to update. |
| 10:25:35 | syntegris information solutions GmbH | Sven: I agree with Chris. Where clause rarwly needed for updates. Where clause needed for the select which fetches the data. But rarly for the update. |
| 10:25:45 | Michael Moore | Much more likely to get into where clause chaos with the select APIs. We have some wacky dynamic SQL to handle it |
| 10:26:05 | Jeff Kemp | Not always, of course. Depends on each situation :) |
| 10:27:24 | Jeff Kemp | I avoid dynamic SQL as much as possible. |
| 10:27:54 | Jeff Kemp | I like queries everywhere - in APEX, in XAPIs, etc.; but all ins/upd/del encapsulated in the TAPI. |
| 10:28:20 | mentzel iudith | The WHERE clause for updating one table may be a result of some logic involving related but completely different tables than the one being updated. This means that we will need a superflouous query to be executed on the UPDATE target table, just for constructing the input PK-s for the update ... |
| 10:29:22 | Jeff Kemp | Good point mentzel |
| 10:30:57 | mentzel iudith | Exactly, an XAPI can contain exactly and only the statements that you need executed, without necessarily limiting them to very rigid TAPI constructs. |
| 10:34:29 | syntegris information solutions GmbH | TAPI=Totally overrated API |
| 10:35:20 | mentzel iudith | That's nice :) |
| 10:35:37 | Pete K | You definitely want to call overloaded procedures with named parameters if there are a lot of similar overloads. Otherwise the compiler will decide which one to call... |
| 10:37:22 | Pete K | (or indeed fail like you've just demo'd ;-) |
| 10:37:42 | Oren Nakdimon | Another use-case for overloading: maintaining backward compatibility when adding a new OUT parameter to a procedure. Keep the old API and change its implementation to call the new API (and remove it when it becomes completely obsolete). |
| 10:37:47 | Jeff Kemp | I disagree - I require named parameters everywhere. |
| 10:37:52 | mentzel iudith | It would be nice to have a flag or a compilation settings to enable/diaable implicit conversions for a given PL/SQL unit or session. |
| 10:38:01 | syntegris information solutions GmbH | +1 Oren |
| 10:38:40 | Jeff Kemp | Using named parameters everywhere means you *can* safely add an overload and know it won't break existing code. |
| 10:44:34 | syntegris information solutions GmbH | I use subroutines like doCommit. The subroutine then has the commit and a flag to influence/change commit behaviour. Which is especially useful for testing purposes. During test I can switch off committing and roll back the test at the end. |
| 10:46:46 | Jeff Kemp | I prefer to let APEX handle the commits for me :) |
| 10:49:07 | syntegris information solutions GmbH | I like XAPIs / Business APIs. I don't like TAPIs. I think we have special use cases where they are useful, but not to generate them for each and every table. |
| 10:49:11 | Michael Moore | What works for many apps doesn't necessarily work for all apps. |
| 10:49:43 | Karen | Ah, but some things need to work both in and out of APEX ... and then the commit on/off needs to be covered. |
| 10:51:06 | mentzel iudith | APEX (and all stateless environments) might require complex enough logic to handle optimistic locking scenarios ... which is not always simple ... |