Home » SQL & PL/SQL » SQL & PL/SQL » problem with "like" predicate
problem with "like" predicate [message #222980] Wed, 07 March 2007 02:11 Go to next message
firfly
Messages: 6
Registered: January 2007
Junior Member
hi all

select * from table where col like '%ab%'

I want:

do something before the sql is execute when I meet the "like" predicate,such as run some procedurce
so how can i catch the condition with oracle own function?(without other language,C or something)


maybe I cannt make my point clear. I'm sorry about it for english is not my native~:)

online waiting~~:)
thanks
Re: problem with "like" predicate [message #222983 is a reply to message #222980] Wed, 07 March 2007 02:16 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
What I understood is:

You want to catch the condition whether the query returned a record or not, and then run some procedure on the base of this.

Is that what you want?
Re: problem with "like" predicate [message #222988 is a reply to message #222980] Wed, 07 March 2007 02:22 Go to previous messageGo to next message
firfly
Messages: 6
Registered: January 2007
Junior Member

No
I want run some procedure before it execute~ Smile
Re: problem with "like" predicate [message #222992 is a reply to message #222988] Wed, 07 March 2007 02:26 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
Quote:
so how can i catch the condition with oracle own function?


Which condition do you want to catch then?
Re: problem with "like" predicate [message #222993 is a reply to message #222988] Wed, 07 March 2007 02:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you just want to execute a procedure before you run the query, then (assuming the procedure is called test_proc) do something like:
exec test_proc;

select * from table where col like '%ab%';
Re: problem with "like" predicate [message #222995 is a reply to message #222992] Wed, 07 March 2007 02:31 Go to previous messageGo to next message
firfly
Messages: 6
Registered: January 2007
Junior Member
Liza79 wrote on Wed, 07 March 2007 02:26
Quote:
so how can i catch the condition with oracle own function?


Which condition do you want to catch then?


when a predicate is appear such as "like"
Re: problem with "like" predicate [message #222996 is a reply to message #222993] Wed, 07 March 2007 02:34 Go to previous messageGo to next message
firfly
Messages: 6
Registered: January 2007
Junior Member
JRowbottom wrote on Wed, 07 March 2007 02:27
If you just want to execute a procedure before you run the query, then (assuming the procedure is called test_proc) do something like:
exec test_proc;

select * from table where col like '%ab%';




thanks


but I want trigger it transparently
so I can use this function with sqlplus or some outer application directly
Re: problem with "like" predicate [message #222997 is a reply to message #222996] Wed, 07 March 2007 02:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why don't you tell us what the problem you are trying to solve is, rather than ask us for a specific technical solution.
Re: problem with "like" predicate [message #223004 is a reply to message #222997] Wed, 07 March 2007 02:58 Go to previous messageGo to next message
firfly
Messages: 6
Registered: January 2007
Junior Member
JRowbottom wrote on Wed, 07 March 2007 02:38
Why don't you tell us what the problem you are trying to solve is, rather than ask us for a specific technical solution.



OK
My problem is about column encryption. I'm working out a arithmetic, but it request create a new column for encrypted column's characteristic.
when I query the column the arithmetic will use the new column.
but I want it transparent to upper application ,so when a query from upper application was arrived I should be "select col1 from table where col2 like '%abc%'" the new column is not exist to the upper application

so when a query is come,I want catch it and run something before it's execute

am i clear?
Re: problem with "like" predicate [message #223009 is a reply to message #223004] Wed, 07 March 2007 03:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I reckon your best bet would be to create a view on the table that only returns the list of columns that the application is allowed to see.
Re: problem with "like" predicate [message #223013 is a reply to message #223009] Wed, 07 March 2007 03:21 Go to previous messageGo to next message
firfly
Messages: 6
Registered: January 2007
Junior Member
JRowbottom wrote on Wed, 07 March 2007 03:12
I reckon your best bet would be to create a view on the table that only returns the list of columns that the application is allowed to see.



indeed.
I was thinking of it. but only when "like" predicate is appear,It can trigger my arithmetic.How can I tell the view "like" is appear?
Re: problem with "like" predicate [message #223027 is a reply to message #223013] Wed, 07 March 2007 04:30 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't.
There is no way of conditioning the query on whether or not the query contains a 'LIKE' clause.

By the sound of it (and I'm still not clear what you're trying to do) you want to change the set of columns returned depending on the contents of the query, and that is a definite no-no.


Previous Topic: Error while using UTL_FILE in Oracle10g
Next Topic: Truncate vs Delete
Goto Forum:
  


Current Time: Sat Dec 10 09:15:49 CST 2016

Total time taken to generate the page: 0.11401 seconds