Help with passing variables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Help with passing variables

  1. #1
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74

    Help with passing variables

    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.
    rajorcl

  2. #2
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    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
    Code:
    /* 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.
    Last edited by ddrozdov; 05-05-2004 at 12:04 AM.

  3. #3
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    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?

  4. #4
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width