DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Problem with bind variables in dynamic PLSQL

  1. #1
    Join Date
    Feb 2008
    Posts
    6

    Problem with bind variables in dynamic PLSQL

    Hi, I have a problem I am trying to solve using a very short piece of dynamic PLSQL but I am having problems getting the variable values in and out of the PLSQL block.

    I need to increment a counter which could be any one of 16 counters I am using and I want to avoid using nested IF statements.

    The variable to be incremented is made up of the 'scheme', the 'contributory category' and the 'employment category' (although not every combination is valid)

    The 'scheme' can be either 'no1', 'no2', 'off', 'cg' or 'amc'
    The 'contributory category' can be either 'cont' or 'noncont'
    The 'employment category' can be either 'ft' or 'pt'
    For example the total variable name could be 'v_cg_noncont_ft_count'

    I have created a variable by concatenating the various elements called v_incr_count which holds the name of the variable I want to increment.

    I am running this within an anonymous PLSQL block so I cannot use global variables meaning that my variables are not visible within a dynamic PLSQL block so I guess I need to bind the variable with a USING statement.

    I now have the following three lines of code which I hoped would solve the problem:
    v_incr_count := 'v_'||v_scheme||'_'||v_cont_cat||'_'||v_emp_cat||'_count';

    sql_stmt := 'BEGIN :a := :a + 1; END;';
    EXECUTE IMMEDIATE sql_stmt USING v_incr_count;

    Unfortunately I am getting the 'IN bind variable bound to an OUT position' error which I suppose makes sense as I am trying to change the value of a variable in the main PLSQL block from within the dynamic block.

    Every place I've looked on the internet seems to deal with SELECT INTO FROM queries (i.e. SQL not PLSQL) so I'm a bit lost...

    Any help appreciated

    Cheers, Dan

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    Try this:
    Code:
    sql_stmt := 'SELECT '||v_incr_count||'+ 1 FROM DUAL';
    EXECUTE IMMEDIATE sql_stmt INTO v_incr_count;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Feb 2008
    Posts
    6
    Thanks for the quick reply but I seem to be getting an 'Invalid Column Name' error presumably to do with looking for v_cg_noncont_ft_count in DUAL

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Talking

    If "v_cg_noncont_ft_count" contains the NAME of a local variable you cannot refer to it in dynamic sql.

    If "v_cg_noncont_ft_count" contains the NAME of a column in a table, then replace DUAL with the table name and add the adequate condition to select the correct row.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Feb 2008
    Posts
    6
    v_cg_noncont_ft_count is one of the 16 counters (all local variables, NUMBER type)

    v_incr_count is the NAME of the counter variable I want to increment (VARCHAR2) and is created by concatenating the three variable elements('cg', 'noncont' and 'ft' making 'v_cg_noncont_ft_count' in this case)

    Does this mean I will be unable to increment variables dynamically?
    Is there any other way I can do it as I really don't want loads of IF statements (I can't use CASE in PLSQL as we still have Oracle 8i)

  6. #6
    Join Date
    Feb 2008
    Posts
    6
    I've no idea if I'm actually any nearer to a solution but based on the suggestions above I've now got:

    v_incr_count_name := 'v_'||v_scheme||'_'||v_cont_cat||'_'||v_emp_cat||'_count';
    sql_stmt := 'SELECT '||v_incr_count_name||' + 1 INTO '||v_incr_count_name||' FROM DUAL';
    EXECUTE IMMEDIATE sql_stmt;

    Unfortunately now I'm getting a missing keyword error! Is that easier to solve?

  7. #7
    Join Date
    Feb 2008
    Posts
    6
    Sorry, I've just realised that's because I can't use INTO inside the SQL statement.

    Dammit.

    The problem is that I can't use it outside either as the variable that the returned value needs to be stored in is itself variable!

    Help!

  8. #8
    Join Date
    Feb 2008
    Posts
    6
    I've done it now using an array (which wasn't as simple as it should have been as we only have Oracle 8i so I can't index by varchar2, only binary_integer)

    Thanks for the help

    Dan

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