Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2012
    Posts
    6

    Unanswered: eval function in PL/SQL

    Hi,

    I am not very proficient in oracle PL/SQL.

    I was wondering if there is a true eval function in PL/SQL. I wanted to achieve the below in a trigger. Just want the value from trigger bind variable into a local variable with eval.

    ----------------------------------------------------
    CREATE OR REPLACE TRIGGER TRG_TEST
    BEFORE INSERT ON TABLE_TEST
    FOR EACH ROW
    DECLARE
    V_VALUE VARCHAR2(128);
    BEGIN
    EVAL('V_VALUE := :NEW.DATA');
    ----------------------------------------------------

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What does the "eval" function do?

  3. #3
    Join Date
    Sep 2012
    Posts
    6
    Hi, EVAL function is available in any programming language mainly scripting languages where you can pass code as string and it will evaluate the code. I am looking for similar function in oracle PL/SQL.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see; thank you for the explanation.

    So, it actually says whether statement you passed is valid or not? Such as
    Code:
    eval('v_value = 23') is wrong (misses the colon sign)
    eval('v_value := 23') is OK
    As far as I can tell, there's nothing like that in Oracle. Hopefully, someone will know better.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by munendra View Post
    Hi, EVAL function is available in any programming language mainly scripting languages where you can pass code as string and it will evaluate the code. I am looking for similar function in oracle PL/SQL.
    Maybe you should take care about readability and maintainability of your code too. Or do not be surprised when it fails because V_VALUE was not declared in the caller.

    Yes, it is possible call any string containing valid statement using PL/SQL Dynamic SQL. Just search for it in PL/SQL Language Reference book for your Oracle version. It is available e.g. online on http://tahiti.oracle.com/

    However, its scope is separated from the caller scope, so you will not be able to access local variables in the calling trigger (here V_VALUE) in the dynamic SQL. Could be achieved via binding though. In this demonstration, I used another variable instead of the :NEW one because of its unavailability in anonymous PL/SQL block:
    Code:
    declare
      v_value VARCHAR2(128);
      new_data VARCHAR2(128) := 'ssss';
    begin
      execute immediate 'begin :1 := :2; end;'  using out v_value, new_data;
      dbms_output.put_line( v_value );
    end;
    /
    So, as I said in the beginning, you should quite seriously think about properly designing your application.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Just want to emphasize one thing: you should use dynamic SQL only if the task is not achievable by static SQL.
    So, the demonstration I posted in previous post, should be coded like this:
    Code:
    declare
      v_value VARCHAR2(128);
      new_data VARCHAR2(128) := 'ssss';
    begin
      v_value := new_data;
      dbms_output.put_line( v_value );
    end;
    /

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    So you (flyboy) read "evaluate" as "execute" ... I thought that he wants to evaluate that string and return TRUE (read: yes, string you passed to that function is a valid (PL/)SQL statement) or FALSE (read: huh, no, that's not a valid statement!).

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    @Littlefoot: Yes, I hope I did not misunderstood this wikipedia article: http://en.wikipedia.org/wiki/Eval

    Your expected goal could be probably achieved DBMS_SQL.PARSE (probably enhancing this idea: https://forums.oracle.com/forums/thr...hreadID=897227); however as the posted code is invalid due to reference to out-of-scope variable (could be wrong due to type mismatch anyway), it would have to be also "workarounded".

  9. #9
    Join Date
    Sep 2012
    Posts
    6
    Dears, thanks for your opinions. But I specifically want to use :NEW and :OLD which you says is not available in anonymous block... my bad... I will be grateful if there is some other workaround. I want to write a generalized statement in trigger i.e. dynamically access properties on :NEW and :OLD. Thanks.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by munendra View Post
    Hi, EVAL function is available in any programming language mainly scripting languages where you can pass code as string and it will evaluate the code. I am looking for similar function in oracle PL/SQL.
    can/will you post real world examples using any language you choose that shows the inputs & results from the EVAL() function?

    I still don't understand what problem you are trying to solve.
    I could not differentiate a correct answer from an incorrect answer; if both were posted here.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Sep 2012
    Posts
    6
    Dear,

    I am trying to develop a generic Audit logging feature using Trigger. As you know Trigger provides :NEW and :OLD bind variables on which we can access properties (table column values) of the current Rowset. I was trying to write Dynamic code so that I can copy paste the snippet into each Trigger on which I need the audit logging feature and the function starts working as expected. Here is the what I am looking for in a sequence.

    1. Trigger called
    2. Get table metadata (i.e. all column information), I am not sure if this metadata is available with :NEW or :OLD variable? If yes then wonderful!
    3. Iterate through all columns
    4. Get value of each column for the current Row - this is where the challenge is - I need to dynamically mention the Property Name like :NEW.[column_name] or :OLD.[column_name] for which I need eval().
    5. Match both old and new value for each column, if same then do nothing, if not same then log old and new values into the audit log table.

    I hope I am able to make you understand the issue here, please let me know if there are still confusions and I will try to explain in more details.

    Thanks much in advance.

  12. #12
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Lightbulb

    Get table metadata
    Iterate through all columns
    dynamically mention the Property Name like :NEW.[column_name] or :OLD.[column_name]
    That is going to be painfully slow. You would need to do it per row too. Thats going to seriously hurt your db performance. I would not recommend this.
    Also note that when you use "dynamic" (a.k.a EXECUTE IMMEDIATE), that requires a parse. A parse requires significant latching and CPU. Latches are memory locks, and locks prevent concurrency. No concurrency = one person at a time = slow. Your users will be phoning you asking why the system is "hanging". You won't notice it with one or two users; put 50 on it and it just won't work, no matter how much hardware you throw at it.

    My two cents on the design:
    1. Rather than trying to do everything dynamically (super slow), you should rather generate the code for each table at design time. Each table would have it's own allocated trigger.
    2. The trigger should be a "after insert/update/delete". It must be after because other triggers could modify values before applying to the datafile. By using after triggers, you ensure you have the true values being commited.
    3. Put the actual audit logic code in package/procedure. This will give better performance and also keep audit code logically together.
    4. Don't bother comparing before/after on each column. The fact that your trigger is executing is enough to signal "something has changed". Rather serialize the data into your audit table so that you can see the entire row state at that update (insert/update/delete). You would be able to compare row versions when viewing the audits to determine what changed. Doing it this way, you will relieve your OLTP processes from having to work out what's changing.

  13. #13
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by munendra View Post
    I am trying to develop a generic Audit logging feature using Trigger.
    I do not doubt Oracle has some inbuilt tools for achieving it (e.g. AUDIT statement), however I have no experience with them.

    Anyway, you are not looking for the sequence of implementation steps you posted. If you intend to implement it via trigger, you may benefit from reading this article on AskTom: http://asktom.oracle.com/pls/asktom/...ID:59412348055

    Note, that table has fixed number of columns - there is no need to change the trigger until you add/drop column (which should be rare). It would need only simple script (or whatever it will contain) call for re-creating the trigger after DDL statement changing column definition anyway.

Tags for this Thread

Posting Permissions

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