Home » SQL & PL/SQL » SQL & PL/SQL » How to log what parameter the API is passing to query (oracle 11g)
How to log what parameter the API is passing to query [message #657477] Wed, 09 November 2016 12:03 Go to next message
magnify
Messages: 6
Registered: July 2012
Location: delhi
Junior Member
Hi guys,

There is an API which is calling a view and passing some value as filter criteria to that view. I want to log that entry without changing the API call ( means no stored procedure call to log the parameter value in a table ).

API is calling the view as follows :

select * From my_view where phone_number ='1234456';

I just want to log at what time the query was called and what phone number was searched. I can write a procedure to do that but that would require a change at API end which is not feasible at this moment. Any help in this regard will be highly appreciated.

Re: How to log what parameter the API is passing to query [message #657478 is a reply to message #657477] Wed, 09 November 2016 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
AUDIT can log the time.

does the API Really, really produce hard coded SQL with different literal (phone_number ='1234456') for every SELECT?
or is SELECT done with Bind variable?
Re: How to log what parameter the API is passing to query [message #657479 is a reply to message #657478] Wed, 09 November 2016 12:19 Go to previous messageGo to next message
magnify
Messages: 6
Registered: July 2012
Location: delhi
Junior Member
Thanks for your quick response. its select with bind variable. Just want to log what is the value of bind variable for each API call.
Re: How to log what parameter the API is passing to query [message #657480 is a reply to message #657479] Wed, 09 November 2016 12:22 Go to previous message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
  1* select view_name from dba_views where view_name like '%BIND%'
SQL> /

VIEW_NAME
--------------------------------------------------------------------------------
V_$SQL_BIND_METADATA
V_$SQL_BIND_DATA
GV_$SQL_BIND_METADATA
GV_$SQL_BIND_DATA
DBA_OPBINDINGS
CDB_OPBINDINGS
USER_OPBINDINGS
ALL_OPBINDINGS
INT$DBA_HIST_SQL_BIND_METADATA
DBA_HIST_SQL_BIND_METADATA
CDB_HIST_SQL_BIND_METADATA

VIEW_NAME
--------------------------------------------------------------------------------
KU$_LOBINDEX_VIEW
KU$_OPBINDING_VIEW
DBA_HIST_SQLBIND
CDB_HIST_SQLBIND
DBA_SQLTUNE_BINDS
CDB_SQLTUNE_BINDS
USER_SQLTUNE_BINDS
DBA_SQLSET_BINDS
CDB_SQLSET_BINDS
USER_SQLSET_BINDS
ALL_SQLSET_BINDS

VIEW_NAME
--------------------------------------------------------------------------------
V_$SQL_BIND_CAPTURE
GV_$SQL_BIND_CAPTURE

24 rows selected.
Previous Topic: Search data between months
Next Topic: spool sql data in excel multi sheets
Goto Forum:
  


Current Time: Mon Oct 22 11:17:29 CDT 2018