SQL and toad

Question

I have a select query that fetches some values based on user input, for EG :-

SELECT 
  company, accounts 
from testdrive 
WHERE company LIKE '&Company', accounts = '&Account' 
order by company desc

On running this in Toad, the user is prompted for values.. but I want to know how can the USER specify the "conditions" ALONG with the values?? Detno >, <, != etc

Answer 1

he can't. for this you need dynamic sql.

Answer 2

There is no direct way to do this.

For simple cases, you can work around this limitation by providing several parameters for the different comparisons.

E.g.

SELECT company, accounts from testdrive WHERE company LIKE '&Company', accounts = '&Account' AND date<&datesmaller AND date<=&datesmallerequal order by company desc

etc. Then you can supply both datesmaller and datesmallerequal. Just set the one you do not need to some date far in the future, and the condition will do nothing.

But that only works for simple cases, in general you'll need dynamic SQL, or a stored procedure (which can handle complex parameters internall).

Answer 3

Some reference? example? hints?

Answer 4

I'm not a TOAD user, but it looks like TOAD is just relying on the substitution variable feature of SQL*Plus. SQL*Plus substitution variables are not bind variables - they're just dumb string substitution - so you can put anything in them you want. That being the case, have you seen what happens if you do something like this:

  SELECT company
       , accounts
    FROM testdrive
   WHERE company &Company_Comparison '&Company'
     AND accounts &Account_Comparison '&Account'
ORDER BY company DESC;

The user should be prompted for Company_Comparison and Account_Comparison, where they could enter !=, LIKE, etc. Of course, you won't be able to validate their input, and something like this is wide open to SQL Injection attacks, but since your client is TOAD, I assume you're not really looking for a bulletproof solution.

Answer 5

Where you say "Some reference? example? hints?"

If you tell us how your user will be accessing this functionality we will be able to provide more appropriate help.

Answer 6

Write a function for each column that accepts an operator as an argument. That way the operators can be supplied via bind variables as well as the operands themselves. This method will probably suffer from performance issues, however, since the optimiser will not be able to calculate the selectivity of the predicates very well at all.

e.g.

FUNCTION company_is (company IN VARCHAR2, op IN VARCHAR2, value IN VARCHAR2) IS
BEGIN
   RETURN CASE op
   WHEN '=' THEN (company = value)
   WHEN 'LIKE' THEN (company LIKE value)
   WHEN '<' THEN (company < value)
   ...
   END;
END;

FUNCTION date_is (thedate IN DATE, op IN VARCHAR2, value IN DATE) IS
BEGIN
   RETURN CASE op
   WHEN '=' THEN (thedate = value)
   WHEN '<' THEN (thedate < value)
   ...
   END;
END;

Then you can supply predicates in this way:

SELECT company, accounts 
FROM testdrive AS t
WHERE company_is(t.company,'&CompanyOp','&Company')
AND   date_is(t.date_created,'&CreatedOp',TO_DATE('&Created','DD/MM/YYYY'))
ORDER BY company DESC

But watch out for the performance issues - unless you can specify some other predicates in the normal way, you're gonna see nothing but full scans!