Help with passing variables

[kanthbethi]

Hi all

I have a a procedure, I tried to replicate my need in the following lines...........The procedure has a variable, which gets populated at run time and later used in a select statement. The current way of doing it is :

DECLARE
V_INITIAL VARCHAR2(1000);
V_CHECK_UNITS VARCHAR2(1000);
ALREADY_UNITS varchar2(1000);

BEGIN
FOR V_VALID IN 1..100 LOOP
V_CHECK_UNITS := :ENAME;--being passed from FORM

IF V_CHECK_UNITS IS NOT NULL THEN
SELECT e1.ename
INTO V_INITIAL
FROM emp e1
WHERE e1.deptno = EPTNO --being passed from FORM (assume -- 99)
AND e1.job = :JOB --being passed from FORM (Assume -- MANAGER)
AND sua1.ename = V_CHECK_UNITS
AND EXISTS
(SELECT NULL FROM emp e2
WHERE e2.empno = deptno = EPTNO --being passed from FORM
AND e2.course_cd = :JOB --being passed from FORM
AND e2.ename = V_CHECK_UNITS);
END IF;
ALREADY_UNITS := ALREADY_UNITS||V_INITIAL||',';
NEXT_RECORD;
END LOOP;
END;

----------------------------------------------------------------------
My question:
Instead of passing the select statement 100 times because of the loop I want to populate a variable with all the enames and then do one select. But I cant do that.......
This is what I did
Assume at run time I got V_CHECK_UNITS with the following data
('JOHN', 'ANDY', 'KYM', 'PETER')
And I'm trying to do this:

DECLARE
V_INITIAL VARCHAR2(1000);
V_CHECK_UNITS VARCHAR2(1000);
ALREADY_UNITS varchar2(1000);
CURSOR c1 is
SELECT e1.ename
FROM emp e1
WHERE e1.deptno = EPTNO --being passed from FORM (assume -- 99)
AND e1.job = :JOB --being passed from FORM (Assume -- MANAGER)
AND sua1.ename in (V_CHECK_UNITS)
AND EXISTS
(SELECT NULL FROM emp e2
WHERE e2.empno = deptno = EPTNO --being passed from FORM
AND e2.course_cd = :JOB --being passed from FORM
AND e2.ename in (V_CHECK_UNITS));
BEGIN
--Assume V_CHECK_UNITS got populated as mentioned above.
OPEN c1;
LOOP
FETCH c1 INTO V_INITIAL;
EXIT WHEN c1%NOTFOUND;
ALREADY_UNITS := ALREADY_UNITS||V_INITIAL||',';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_ALREADY_EXISTS);
END;
/
----------------------------------------------------------------------
I know I can't do the text part marked in red, but can anyone suggest me a way to pass the select once instead of 100 times.

Thanks in advance.

[ddrozdov]

Hm,
in('JOHN', 'ANDY', 'KYM', 'PETER')
is not the same as
in('('JOHN', 'ANDY', 'KYM', 'PETER')')

'in' is a vararg operator each name is a different argument and you are only passing one.


Create this function

/* Checks whether a value is in the list of values */
 FUNCTION in_list
   (i_list VARCHAR2, i_string VARCHAR2,
    i_dlm VARCHAR2 := ',', i_repval VARCHAR2 := '@') RETURN BOOLEAN IS
  list VARCHAR2(999) := i_dlm||rtrim(ltrim(i_list,i_dlm),i_dlm)||i_dlm;
  string VARCHAR2(999) := i_dlm||i_string||i_dlm;
 BEGIN
   if instr(replace(list,string,i_repval),i_repval) <> 0
   then RETURN TRUE;
   else RETURN FALSE;
   end if;
 END in_list;

and call

in_list('JOHN,ANDY,KYM,PETER')

It will enable you to do what you want but I am not sure it'll speed things up for you because of all the context swithches between SQL and PL/SQL when calling this function.

[ddrozdov]

slimdave
Your private mailbox is full.

I can't reply to your message.
Can you do me a fovo r/ur that I asked you about?

[ddrozdov]

Now that I think about it, you might want to switch the return datatype to number 0,1 instead of boolean. The SQL might complain otherwise.