Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2007
    Posts
    4

    Unanswered: how to access the value of a variable in a block written using dynamic sql from out

    Here when i try accessing l_latest_rev in the return statement , i will not get the value iam intending to get ( pls see in the dynamic sql , it is being assigned value from the cursor).

    Incase if any once has idea on it?

    declare
    l_latest_rev varchar2(150) := '0';
    l_apps_release varchar2(30);
    CR constant varchar2(1) := chr(10);
    l_c number;
    l_rc number;
    l_s varchar(32000);
    begin

    l_s:= 'declare '||CR;
    l_s:= l_s || 'cursor latest_revision_cur is '||CR;
    l_s:= l_s || 'select version '||CR;
    l_s:= l_s || 'from ad_file_versions afv, '||CR;
    l_s:= l_s || 'ad_files af '||CR;
    l_s:= l_s || 'where af.file_id = afv.file_id '||CR;
    l_s:= l_s || 'and af.filename = p_file_name'||CR;
    l_s:= l_s || 'begin '||CR;
    l_s:= l_s || 'for cur_rec in latest_revision_cur loop '||CR;
    l_s:= l_s || 'if compare_version(cur_rec.version,:ll_latest_rev) = ''>'' then '||CR;
    l_s:= l_s || 'l_latest_rev := cur_rec.version;'||CR;
    l_s:= l_s || 'end if; '||CR;
    l_s:= l_s || 'end loop; '||CR;
    l_s:=l_s || 'end;'||CR;

    if l_latest_rev != ''0'' then
    return(l_latest_rev);
    else
    raise_application_error (-20108, 'error in get_latest_file_version: '|| 'file does not exist');
    end if;
    end;

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    Are you doing anything with the dynamic SQL in l_s ?

  3. #3
    Join Date
    Apr 2007
    Posts
    4
    Iam making it dynamic sql ie l_s because ad_file_version and ad_files tables do not exist in 11.0.3 apps version where as they do in 11.5 onwards.

    Earlier the piece of code looked as given below ie :

    function get_latest_file_version(p_file_name varchar2) return varchar2 is

    cursor latest_revision_cur is
    select version
    from ad_file_versions afv,
    ad_files af
    where af.file_id = afv.file_id
    and af.filename = p_file_name;

    l_latest_rev varchar2(150) := '0';

    begin

    for cur_rec in latest_revision_cur
    loop

    if compare_version(cur_rec.version,l_latest_rev) = '>' then
    l_latest_rev := cur_rec.version;
    end if;

    end loop;

    if l_latest_rev != '0' then
    return(l_latest_rev);
    else
    raise_application_error (-20108, 'error in get_latest_file_version: '|| 'file does not exist');
    end if;

    end get_latest_file_version;

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    What I actually meant to ask was: shouldn't you be executing the dynamic SQL? It looks like you are just building it in l_s and then do nothing with it.

  5. #5
    Join Date
    Apr 2007
    Posts
    4
    Oh! iam sorry that i havent added the processing part earlier , this time i had added. Thanks

    declare
    l_latest_rev varchar2(150) := '0';
    l_apps_release varchar2(30);
    CR constant varchar2(1) := chr(10);
    l_c number;
    l_rc number;
    l_s varchar(32000);
    begin

    l_s:= 'declare '||CR;
    l_s:= l_s || 'cursor latest_revision_cur is '||CR;
    l_s:= l_s || 'select version '||CR;
    l_s:= l_s || 'from ad_file_versions afv, '||CR;
    l_s:= l_s || 'ad_files af '||CR;
    l_s:= l_s || 'where af.file_id = afv.file_id '||CR;
    l_s:= l_s || 'and af.filename = p_file_name'||CR;
    l_s:= l_s || 'begin '||CR;
    l_s:= l_s || 'for cur_rec in latest_revision_cur loop '||CR;
    l_s:= l_s || 'if compare_version(cur_rec.version,:ll_latest_rev) = ''>'' then '||CR;
    l_s:= l_s || 'l_latest_rev := cur_rec.version;'||CR;
    l_s:= l_s || 'end if; '||CR;
    l_s:= l_s || 'end loop; '||CR;
    l_s:=l_s || 'end;'||CR;

    l_c := dbms_sql.open_cursor;
    dbms_sql.parse(l_c,l_s,dbms_sql.native);
    dbms_sql.bind_variable(l_c,'pp_file_name',p_file_n ame);
    dbms_sql.bind_variable(l_c,'ll_latest_rev',l_lates t_rev);
    l_rc := dbms_sql.execute(l_c);
    dbms_sql.close_cursor(l_c);

    if l_latest_rev != ''0'' then
    return(l_latest_rev);
    else
    raise_application_error (-20108, 'error in get_latest_file_version: '|| 'file does not exist');
    end if;
    end;

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It looks like you are making it a bit too complicated. Why not just make the sql statement dynamic rather than creating a whole plsql block dynamically. Just use execute immediate '<your sql statement>' and catch the exception when it doesnt find the tables.

    Alan

  7. #7
    Join Date
    Apr 2007
    Posts
    4
    Alan

    The hole requirement is to make the cursor dynamic as it is refering to tables which are availiable in 11.5 and not in 11.03 version of apps. So how could i say execute_immediate < Cursor > ?

    Requirement is , it should not error out during compile time .

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you need the cursor just do

    open c_cursor for sqltext

    look up oracle dynamic cursor in google for more info or the plsql manual.

    Alan

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I have frequently used SQL to write SQL, but I have never, ever saw anyone writing PL/SQl to write PL/SQL.
    A shovel is a great tool for creating a whole in the ground,
    but only when the "right" end of the shovel contacts the Earth.
    IMO, somebody is using the "wrong end" of PL/SQL.
    I HAVE to believe a simpler solution can be created.
    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.

Posting Permissions

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