Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2006
    Posts
    47

    Unanswered: pl/sql equivalent of Evaluate() or EVAL()?

    All,

    I'm trying to do some dynamic comparisons in a pl/sql script. Am I going about
    this the wrong way? Or am I going to have to jump through hoops regardless.

    Please consider the following...

    PHP Code:
    DECLARE

    part1 VARCHAR2(100) :='v_';
    part2 VARCHAR2(100) :='name';

    v_name VARCHAR2(100) :='James';

    BEGIN
        
    IF (part1||part2)='James' THEN
            DBMS_OUTPUT
    .PUT_LINE('One and the same.');
        ELSE
            
    DBMS_OUTPUT.PUT_LINE('No luck.');
        
    END IF;

    END

    I hope that makes sense. I want to compare a variable, whose name is CONCAT(part1,part2). So I guess I want to do something like:

    PHP Code:
       IF EVALUATE(CONCAT(part1,part2))='James' THEN
            DBMS_OUTPUT
    .PUT_LINE('One and the same.');
        ELSE
            
    DBMS_OUTPUT.PUT_LINE('No luck.');
        
    END IF; 
    Can anyone point me in the right direction?

    Thanks,
    --james

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try something like this:
    PHP Code:
    DECLARE 

    part1 VARCHAR2(100) :='v_'
    part2 VARCHAR2(100) :='name'

    v_name VARCHAR2(100) :='James'
    v_result VARCHAR2(10):='?';

    BEGIN 
        EXECUTE IMMEDIATE 
           
    'Select ''OK'' From DUAL
              Where '
    ||part1||part2||' = :1 '
          
    USING 'James' INTO V_result;
        IF 
    v_result 'OK' THEN 
            DBMS_OUTPUT
    .PUT_LINE('One and the same.'); 
        ELSE 
            
    DBMS_OUTPUT.PUT_LINE('No luck.'); 
        
    END IF; 
    END
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by LKBrwn_DBA
    Try something like this:
    PHP Code:
    DECLARE 

    part1 VARCHAR2(100) :='v_'
    part2 VARCHAR2(100) :='name'

    v_name VARCHAR2(100) :='James'
    v_result VARCHAR2(10):='?';

    BEGIN 
        EXECUTE IMMEDIATE 
           
    'Select ''OK'' From DUAL
              Where '
    ||part1||part2||' = :1 '
          
    USING 'James' INTO V_result;
        IF 
    v_result 'OK' THEN 
            DBMS_OUTPUT
    .PUT_LINE('One and the same.'); 
        ELSE 
            
    DBMS_OUTPUT.PUT_LINE('No luck.'); 
        
    END IF; 
    END

    I tried using:
    PHP Code:
        EXECUTE IMMEDIATE 
           
    'Select ''OK'' From DUAL
              Where '
    ||part1||part2||' = :1 '
          
    USING 'James' INTO V_result
    But it throws an error. Is that supposed to be:

    PHP Code:
        EXECUTE IMMEDIATE 
           
    'Select ''OK'' From DUAL
              Where '
    ||part1||part2||' = :1 '
          
    INTO V_result USING 'James' 

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    It seems the order is important.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Mar 2006
    Posts
    47

    :)

    Thanks for your help so far.

    I reordered the fields, and am now getting an error that says:

    PHP Code:
    ORA-00904"V_NAME"invalid identifier 
    is V_NAME in the scope of the EXECUTE IMMEDIATE statement? If not, do you know how to fix that?

    Thanks again.

    --james

  6. #6
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    So, translating the query, it would be something like this:
    Code:
    Select 'OK' From DUAL
              Where v_name = 'James';
    Does it ring any bell?

    DUAL table does not contain any v_name column.

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hey LKBrwn_DBA, you need some vacation, don't you ?

    Jholder, AFAIK this is not possible to make PL/SQL evaluate any variable from its name in a string... and excuse me but I don't see the use of this since in your PLSQL code you KNOW what variables you are using ... You certainly don't need this sort of trick. What do you want to do exactly ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Can't you use PL/SQL indexed tables using a varchar as the index ?

  9. #9
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by RBARAER
    Hey LKBrwn_DBA, you need some vacation, don't you ?

    Jholder, AFAIK this is not possible to make PL/SQL evaluate any variable from its name in a string... and excuse me but I don't see the use of this since in your PLSQL code you KNOW what variables you are using ... You certainly don't need this sort of trick. What do you want to do exactly ?

    Regards,

    rbaraer
    rbaraer,

    I've got a comma delimited list coming into a procedure. Lets say the string is "field1,field2,field3". The string could contain up to, lets say, 100 fieldnames (i.e. field100).

    In the procedure I have a cursor that has fields 1-100 (field1...field100).

    What I need to do is only check the fields passed into the list. So my thought was rather than doing 100 case statements, or elseif's, I'd use 3 lines and do a loop using the eval or its pl/sql equivalent (which doesnt seem to exist). for instance. (below, imagine eval exists in pl/sql and you'll see what I'm trying to accomplish)

    PHP Code:
        IF NOT change_cur%ISOPEN 
        THEN
            OPEN change_cur
    ;
        
    END IF;
        
    LOOP
            FETCH change_cur INTO change_rec
    ;
                 EXIT 
    WHEN change_cur%NOTFOUND;
        
                  for 
    i in 1..100 loop
                   
                   
    IF EVAL('change_rec.field'||i)= "someValue" THEN
                      SELECT 
    FROM DUAL WHERE 1=2;
                   
    END IF;
                   
    end loop;

         
    END LOOP;
        
    CLOSE change_cur

    This is a pretty standard trick in my experience. (I'm not lazy, I'm efficient ). Rather than my loop looking something like:

    PHP Code:
    IF change_rec.field1 'someValue' THEN
                      SELECT 
    FROM DUAL WHERE 1=2;
    ELSEIF 
    change_rec.field1'someValue' THEN
    ELSEIF change_rec.field2'someValue' THEN
    ELSEIF change_rec.field3'someValue' THEN
    ELSEIF change_rec.field4'someValue' THEN
    ELSEIF change_rec.field5'someValue' THEN
    ELSEIF change_rec.field6'someValue' THEN
    ELSEIF change_rec.field7'someValue' THEN
    ELSEIF change_rec.field8'someValue' THEN
    ELSEIF change_rec.field9'someValue' THEN
    ELSEIF change_rec.field10'someValue' THEN
    ELSEIF change_rec.field11'someValue' THEN
    ELSEIF change_rec.field12'someValue' THEN
    ELSEIF change_rec.field13'someValue' THEN
    ELSEIF change_rec.field14'someValue' THEN
    ELSEIF change_rec.field15'someValue' THEN
    ELSEIF change_rec.field16'someValue' THEN
    ELSEIF change_rec.field17'someValue' THEN
    ELSEIF change_rec.field18'someValue' THEN
    ELSEIF change_rec.field19'someValue' THEN
    ELSEIF change_rec.field20'someValue' THEN
    ELSEIF change_rec.field21'someValue' THEN
    -- ... (through 100)
    END IF; 
    The core of the matter is, I get a random list of fields that exist in a cursor. And those field values are what I'm interested in.

    Thanks again for your help.

    --james
    Last edited by jholder; 08-17-06 at 13:24.

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Well, if you use REAL tables it works:
    PHP Code:
    SQL>set serverout on size 1000000;
    SQL>set echo on
    SQL
    >DECLARE
      
    2  
      3  part1 VARCHAR2
    (100) :='e';
      
    4  part2 VARCHAR2(100) :='name';
      
    5  
      6  v_sql  VARCHAR2
    (500) :='';
      
    7  v_name VARCHAR2(100) :='JAMES';
      
    8  v_result VARCHAR2(10):='?';
      
    9  
     10  BEGIN
     11      v_sql 
    := 'Select ''OK'' From SCOTT.EMP Where '||part1||part2||' = :1 ';
     
    12      dbms_output.put_line(v_sql);
     
    13      EXECUTE IMMEDIATE v_sql INTO V_result USING v_name ;
     
    14      IF v_result 'OK' THEN
     15          DBMS_OUTPUT
    .PUT_LINE('One and the same.');
     
    16      ELSE
     
    17          DBMS_OUTPUT.PUT_LINE('No luck.');
     
    18      END IF;
     
    19  END;
     
    20  /
    Select 'OK' From SCOTT.EMP Where ename = :1
    One 
    and the same.

    PL/SQL procedure successfully completed.

    SQL


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by LKBrwn_DBA
    Well, if you use REAL tables it works


    Well Jholder, now I see what you want to do, but I don't think it is possible.

    If you find a solution, please share it with us .

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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