This discussion is archived
1 2 3 4 5 6 Previous Next 81 Replies Latest reply: Mar 12, 2013 9:22 AM by odie_63 Go to original post RSS
  • 30. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Strange the describe of OLAP_AW shows function EVAL_NUMBER but it doesn't work.
    Any ideas? PS. How to put COURIER font in here?

    SQL> desc DBMS_AW
    PROCEDURE ADVISE_CUBE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    AGGMAP VARCHAR2 IN
    PCT BINARY_INTEGER IN DEFAULT
    PROCEDURE ADVISE_REL
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    RELNAME VARCHAR2 IN
    VALUESET VARCHAR2 IN
    PCT BINARY_INTEGER IN DEFAULT
    PROCEDURE AW_ATTACH
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    NAME VARCHAR2 IN
    FORWRITE BOOLEAN IN DEFAULT
    CREATEAW BOOLEAN IN DEFAULT
    ATTARGS VARCHAR2 IN DEFAULT
    TBSPACE VARCHAR2 IN DEFAULT
    PROCEDURE AW_ATTACH
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA VARCHAR2 IN
    NAME VARCHAR2 IN
    FORWRITE BOOLEAN IN DEFAULT
    CREATEAW BOOLEAN IN DEFAULT
    ATTARGS VARCHAR2 IN DEFAULT
    TBSPACE VARCHAR2 IN DEFAULT
    PROCEDURE AW_CREATE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    NAME VARCHAR2 IN
    TBSPACE VARCHAR2 IN DEFAULT
    PARTNUM NUMBER IN DEFAULT
    PROCEDURE AW_CREATE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA VARCHAR2 IN
    NAME VARCHAR2 IN
    TBSPACE VARCHAR2 IN DEFAULT
    PROCEDURE AW_DELETE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    NAME VARCHAR2 IN
    PROCEDURE AW_DELETE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA VARCHAR2 IN
    NAME VARCHAR2 IN
    PROCEDURE AW_DETACH
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    NAME VARCHAR2 IN
    DISCARD BOOLEAN IN DEFAULT
    PROCEDURE AW_DETACH
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA VARCHAR2 IN
    NAME VARCHAR2 IN
    DISCARD BOOLEAN IN DEFAULT
    PROCEDURE AW_RENAME
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    INNAME VARCHAR2 IN
    OUTNAME VARCHAR2 IN
    PROCEDURE AW_UPDATE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    NAME VARCHAR2 IN DEFAULT
    PROCEDURE AW_UPDATE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA VARCHAR2 IN
    NAME VARCHAR2 IN
    PROCEDURE EIF_BLOB_IN
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    NAME VARCHAR2 IN
    IMPLOB BLOB IN
    DATADFNS BINARY_INTEGER IN DEFAULT
    OBJLIST DBMS_AW IN DEFAULT
    PROCEDURE EIF_BLOB_IN
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA VARCHAR2 IN
    NAME VARCHAR2 IN
    IMPLOB BLOB IN
    DATADFNS BINARY_INTEGER IN DEFAULT
    OBJLIST DBMS_AW IN DEFAULT
    FUNCTION EIF_BLOB_OUT RETURNS BLOB
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    NAME VARCHAR2 IN
    OBJLIST DBMS_AW IN DEFAULT
    FUNCTION EIF_BLOB_OUT RETURNS BLOB
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA VARCHAR2 IN
    NAME VARCHAR2 IN
    OBJLIST DBMS_AW IN DEFAULT
    PROCEDURE EIF_DELETE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    EIF DBMS_AW IN/OUT
    PROCEDURE EIF_IN
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    NAME VARCHAR2 IN
    IMPEIF DBMS_AW IN
    DATADFNS BINARY_INTEGER IN DEFAULT
    OBJLIST DBMS_AW IN DEFAULT
    PROCEDURE EIF_IN
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA VARCHAR2 IN
    NAME VARCHAR2 IN
    IMPEIF DBMS_AW IN
    DATADFNS BINARY_INTEGER IN DEFAULT
    OBJLIST DBMS_AW IN DEFAULT
    PROCEDURE EIF_OUT
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    NAME VARCHAR2 IN
    EXPEIF DBMS_AW OUT
    OBJLIST DBMS_AW IN DEFAULT
    PROCEDURE EIF_OUT
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA VARCHAR2 IN
    NAME VARCHAR2 IN
    EXPEIF DBMS_AW OUT
    OBJLIST DBMS_AW IN DEFAULT
    FUNCTION EVAL_NUMBER RETURNS NUMBER
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    CMD VARCHAR2 IN
    FUNCTION EVAL_TEXT RETURNS VARCHAR2
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    CMD VARCHAR2 IN
    PROCEDURE EXECUTE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    CMD VARCHAR2 IN
    FUNCTION GETLOG RETURNS CLOB
    PROCEDURE INFILE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    IFILENAME VARCHAR2 IN
    PROCEDURE INITDRIVER
    FUNCTION INTERP RETURNS CLOB
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    CMD VARCHAR2 IN
    FUNCTION INTERPCLOB RETURNS CLOB
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    CMD_CLOB CLOB IN
    PROCEDURE INTERP_SILENT
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    CMD VARCHAR2 IN
    FUNCTION OLAP_ON RETURNS BOOLEAN
    PROCEDURE PRINTLOG
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    LOG_CLOB CLOB IN
    PROCEDURE SHUTDOWN
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    FORCE BOOLEAN IN DEFAULT
    PROCEDURE STARTUP
    PROCEDURE TOGGLEDBCREATE
    PROCEDURE XSSRFCLOSE
    FUNCTION XSSRFFETCH RETURNS NUMBER
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    R2C RAW IN
    STR VARCHAR2 IN
  • 31. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Hi Odie,
    Finally noticed the link to your ZIP file that contains the objects etc for your function, but I don't think I could get all those DB objects created in our production system.

    Pity it's not a self-contained function, maybe using a PL/SQL table if a table is necessary.

    It's amazing such a basic requirement is not already available in PL/SQL.

    thanks anyway,
    Steve
  • 32. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    970779 wrote:
    Strange the describe of OLAP_AW shows function EVAL_NUMBER but it doesn't work.
    Any ideas? PS. How to put COURIER font in here?
    Read: {message:id=9360002} which explains how you use {noformat}
    {noformat} tags to make your code/data appear formatted                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 33. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Anyway I found a self-contained function that I can use - here it is in case anyone else needs it:

    Usage e.g.
    v_result number;
    v_formula varchar2(100) := '5*33/44-22+87/3.2';
    v_result := evaluate_expression(v_formula);

    ------------------------------------------
    create or replace function evaluate_expression
    (p_expression in varchar2) return number is
    ret_val number;
    sep varchar2(1) := '|';
    operand_stack varchar2(4000) := sep;
    operator_stack varchar2(4000) := sep;
    function_stack varchar2(4000) := sep;
    w_expression varchar2(4000) := replace(p_expression,' ','');
    i pls_integer := 1;
    next_char varchar2(1);
    num_in boolean := false;
    opr_in boolean := false;
    fun_in boolean := false;
    top_function varchar2(100);
    top_operator varchar2(100);
    left_operand varchar2(100);
    right_operand varchar2(100);

    function pop_stack(p_stack in out varchar2) return varchar2 is
    ret_val varchar2(4000);
    begin
    ret_val := rtrim(substr(p_stack,instr(p_stack,sep,-1,2) + 1),sep);
    p_stack := substr(p_stack,1,instr(p_stack,ret_val||sep,-1,1) - 1);
    return ret_val;
    end;

    function peek_stack(p_stack in varchar2) return varchar2 is
    ret_val varchar2(4000);
    begin
    ret_val := substr(rtrim(p_stack,sep),instr(rtrim(p_stack,sep),sep,-1) + 1);
    return ret_val;
    end;

    procedure push_stack(p_stack in out varchar2,p_item in varchar2) is
    begin
    p_stack := replace(p_stack || p_item || sep,sep || sep,sep);
    end;

    function is_empty(p_stack in varchar2) return boolean is
    ret_val boolean;
    begin
    if p_stack = sep then
    ret_val := true;
    else
    ret_val := false;
    end if;
    return ret_val;
    end;

    function precedence(p_operator in varchar2) return pls_integer is
    ret_val pls_integer;
    begin
    if p_operator in ('+','-') then
    ret_val := 1;
    elsif p_operator in ('*','/') then
    ret_val := 2;
    elsif p_operator in ('^') then
    ret_val := 3;
    elsif p_operator in ('(') then
    ret_val := 0;
    elsif p_operator in (')') then
    ret_val := 0;
    elsif p_operator in ('F') then
    ret_val := -1;
    else
    ret_val := 4;
    end if;
    return ret_val;
    end;

    function evaluate_operation(p_left in varchar2,p_right in varchar2,p_operator in varchar2) return number is
    ret_val number := to_number(null);
    begin
    if p_operator = '+' then
    ret_val := to_number(p_left) + to_number(p_right);
    elsif p_operator = '-' then
    ret_val := to_number(p_left) - to_number(p_right);
    elsif p_operator = '*' then
    ret_val := to_number(p_left) * to_number(p_right);
    elsif p_operator = '/' then
    ret_val := to_number(p_left) / to_number(p_right);
    elsif p_operator = '^' then
    ret_val := power(to_number(p_left),to_number(p_right));
    end if;
    return ret_val;
    end;

    function evaluate_function(p_function in varchar2) return number is
    ret_val number := to_number(null);
    begin
    if p_function = 'ROUND' then
    right_operand := pop_stack(operand_stack);
    left_operand := pop_stack(operand_stack);
    ret_val := round(to_number(left_operand),to_number(right_operand));
    elsif p_function = 'TRUNC' then
    right_operand := pop_stack(operand_stack);
    left_operand := pop_stack(operand_stack);
    ret_val := trunc(to_number(left_operand),to_number(right_operand));
    end if;
    return ret_val;
    end;

    procedure treat_operand(p_char in varchar2) is
    begin
    operand_stack := operand_stack || p_char;
    end;

    procedure treat_operator(p_char in varchar2) is
    done boolean := false;
    result number;
    begin
    if num_in then
    push_stack(operand_stack,'');
    num_in := false;
    end if;
    while not done loop
    if is_empty(operator_stack) or p_char = '(' then
    push_stack(operator_stack,p_char);
    done := true;
    else
    top_operator := peek_stack(operator_stack);
    if precedence(p_char) > precedence(top_operator) then
    push_stack(operator_stack,p_char);
    done := true;
    else
    top_operator := pop_stack(operator_stack);
    if top_operator = '(' then
    done := true;
    else
    right_operand := pop_stack(operand_stack);
    left_operand := pop_stack(operand_stack);
    result := evaluate_operation(left_operand,right_operand,top_operator);
    push_stack(operand_stack,to_char(result));
    end if;
    end if;
    end if;
    end loop;
    end;

    procedure treat_function(p_char in varchar2) is
    result number;
    begin
    if p_char = '[' then -- function parameters in square brackets
    push_stack(function_stack,'');
    push_stack(operator_stack,'F');
    elsif p_char = ';' then -- ';' function parameter separator (my decimal point is comma)
    push_stack(operand_stack,'');
    while peek_stack(operator_stack) != 'F' loop
    top_operator := pop_stack(operator_stack);
    right_operand := pop_stack(operand_stack);
    left_operand := pop_stack(operand_stack);
    ret_val := evaluate_operation(left_operand,right_operand,top_operator);
    push_stack(operand_stack,to_char(ret_val));
    end loop;
    elsif p_char = ']' then -- function parameters in square brackets
    push_stack(operand_stack,'');
    while peek_stack(operator_stack) != 'F' loop
    top_operator := pop_stack(operator_stack);
    right_operand := pop_stack(operand_stack);
    left_operand := pop_stack(operand_stack);
    ret_val := evaluate_operation(left_operand,right_operand,top_operator);
    push_stack(operand_stack,to_char(ret_val));
    end loop;
    top_operator := pop_stack(operator_stack);
    top_function := pop_stack(function_stack);
    ret_val := evaluate_function(upper(top_function));
    push_stack(operand_stack,to_char(ret_val));
    while peek_stack(operator_stack) not in ('F','(') loop
    top_operator := pop_stack(operator_stack);
    right_operand := pop_stack(operand_stack);
    left_operand := pop_stack(operand_stack);
    ret_val := evaluate_operation(left_operand,right_operand,top_operator);
    push_stack(operand_stack,to_char(ret_val));
    end loop;
    else
    function_stack := function_stack || p_char;
    end if;
    end;

    begin
    if substr(w_expression,1,1) = '-' then
    w_expression := '0' || w_expression;
    elsif substr(w_expression,1,1) = '+' then
    w_expression := substr(w_expression,2);
    end if;
    w_expression := replace(replace(replace(w_expression,'(-','(0-'),'(+','('),'[-','[0-');
    while true loop
    next_char := substr(w_expression,i,1);
    exit when next_char is null;
    if next_char in ('0','1','2','3','4','5','6','7','8','9',',') then -- ',' = decimal point
    treat_operand(next_char);
    num_in := true;
    elsif next_char in ('+','-','*','/','^','(',')') then
    treat_operator(next_char);
    if not fun_in then
    opr_in := true;
    end if;
    else
    treat_function(next_char);
    fun_in := true;
    end if;
    i := i + 1;
    end loop;
    push_stack(operand_stack,'');
    while not is_empty(operator_stack) loop
    top_operator := pop_stack(operator_stack);
    if top_operator = 'F' then
    top_function := pop_stack(function_stack);
    ret_val := evaluate_function(upper(top_function));
    else
    if top_operator != '(' then
    right_operand := pop_stack(operand_stack);
    left_operand := pop_stack(operand_stack);
    ret_val := evaluate_operation(left_operand,right_operand,top_operator);
    end if;
    end if;
    push_stack(operand_stack,to_char(ret_val));
    end loop;
    left_operand := pop_stack(operand_stack);
    ret_val := to_number(left_operand);
    if is_empty(operand_stack) and is_empty(operator_stack) and is_empty(function_stack) then
    return ret_val;
    else
    return to_number(null);
    end if;
    end;
    /
  • 34. Re: Dynamic SQL without using SQL
    APC Oracle ACE
    Currently Being Moderated
    You found this on another Forum thread. Why not just link to that thread instead of duplicating the code here? Apart from anything else that would give due credit to Etbin who wrote this thing. {message:id=3527468}

    Lose ten culture points.

    Cheers, APC

    Edited by: APC on Feb 20, 2013 12:33 PM
  • 35. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    970779 wrote:
    Anyway I found a self-contained function that I can use - here it is in case anyone else needs it:
    So you still didn't bother to learn how to post code, even after you asked and were told how to.

    Aside from that, Etbin's solution may work for your needs (and all credit to Etbin, it's going about it in the right way as an expression interpreter), though I would consider implementing a proper stack structure rather than using a limited varchar2 string, and also look at implementing all possible operators and mathematical functions.
  • 36. Re: Dynamic SQL without using SQL
    odie_63 Guru
    Currently Being Moderated
    970779 wrote:
    Finally noticed the link to your ZIP file that contains the objects etc for your function, but I don't think I could get all those DB objects created in our production system.

    Pity it's not a self-contained function, maybe using a PL/SQL table if a table is necessary.
    Think about modularity and data structuration.

    My requirement was to perform set-based operations in SQL, that's why I needed those additional objects.

    There's not that much to install anyway :
    - KV_OBJ and KV_TABLE are used only if we want to bind variables to the expression, so you can eliminate those if you want.
    - TOKEN_STACK is a O-O implementation of a stack, with all push, pop, etc. methods, it's used for parsing the expression with the Shunting-Yard algorithm.
    - TOKEN_LIST is used to hold the tokenized expression. You can make it a PL/SQL collection if you want.

    Etbin's code does exactly the same, with a different modular approach.
  • 37. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    OK my bad APC, next time!
    I guess I can close this thread.
  • 38. Re: Dynamic SQL without using SQL
    Templestowe Newbie
    Currently Being Moderated
    Apparently not possible in version 9.
    So a parsing function to laboriously evaluate a string expression will have to do.
    Remarkable oversight from Oracle.
  • 39. Re: Dynamic SQL without using SQL
    Marwim Expert
    Currently Being Moderated
    Apparently not possible in version 9.
    So a parsing function to laboriously evaluate a string expression will have to do.
    Remarkable oversight from Oracle.
    Could you explain further? Does it mean Oracle should stop to implement new functionality just because someday someone will try to use it in a long deprecated version to reinvent a functionality that is easily available but is refused by him because he wants do do it another way?
  • 40. Re: Dynamic SQL without using SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    Marwim wrote:
    Apparently not possible in version 9.
    So a parsing function to laboriously evaluate a string expression will have to do.
    Remarkable oversight from Oracle.
    Could you explain further? Does it mean Oracle should stop to implement new functionality just because someday someone will try to use it in a long deprecated version to reinvent a functionality that is easily available but is refused by him because he wants do do it another way?
    It certainly sounds like that. Oracle doesn't do that obscure and ridiculously unlikely thing that one person in a million may try and do because they haven't got their design right... therfore it must be a remarkable oversight from Oracle.

    But as this member is an Oracle Expert from Redwood Shores..

    >
    Handle: 970779
    Status Level: Newbie
    Registered: Nov 12, 2012
    Total Posts: 21
    Total Questions: 1
    Name Guru Bob
    Location Redwood Shores
    Occupation Oracle Expert
    >

    he should be able to go and pop into the office next door and speak with the developers to get it fixed. ;)
  • 41. Re: Dynamic SQL without using SQL
    Paul Horth Expert
    Currently Being Moderated
    BluShadow wrote:
    Marwim wrote:
    Apparently not possible in version 9.
    So a parsing function to laboriously evaluate a string expression will have to do.
    Remarkable oversight from Oracle.
    Could you explain further? Does it mean Oracle should stop to implement new functionality just because someday someone will try to use it in a long deprecated version to reinvent a functionality that is easily available but is refused by him because he wants do do it another way?
    It certainly sounds like that. Oracle doesn't do that obscure and ridiculously unlikely thing that one person in a million may try and do because they haven't got their design right... therfore it must be a remarkable oversigh from Oracle.

    But as this member is an Oracle Expert from Redwood Shores..

    >
    Handle: 970779
    Status Level: Newbie
    Registered: Nov 12, 2012
    Total Posts: 21
    Total Questions: 1
    Name Guru Bob
    Location Redwood Shores
    Occupation Oracle Expert
    >

    he should be able to go and pop into the office next door and speak with the developers to get it fixed. ;)
    Surely, as a Guru he should write it himself and provide it the the grateful millions who are all waiting for it.
  • 42. Re: Dynamic SQL without using SQL
    APC Oracle ACE
    Currently Being Moderated
    I rather think "steve" is pulling our leg with that profile. If there's one thing all my years on the forums has taught me it's that people who put "guru" in their handles aren't.

    Cheers, APC
  • 43. Re: Dynamic SQL without using SQL
    6363 Guru
    Currently Being Moderated
    APC wrote:
    If there's one thing all my years on the forums has taught me it's that people who put "guru" in their handles aren't.
    If there is one thing all my years in the software industry has taught me it is that the rise in use of the term guru has coincided with the decline in the fundamental knowledge found in the industry as it is replaced by blind, unquestioning acceptance of received wisdom.

    >
    the meaning of "guru" has been used to cover anyone who acquires followers, especially by exploiting their naiveté, due to the inflationary use of the term in new religious movements.
    >

    http://en.wikipedia.org/wiki/Guru
  • 44. Re: Dynamic SQL without using SQL
    rp0428 Guru
    Currently Being Moderated
    >
    the meaning of "guru" has been used to cover anyone who acquires followers, especially by exploiting their naiveté, due to the inflationary use of the term in new religious movements.
    >
    Ahh. . . perhaps like those who acquire some number of, say, 'points' beyond some arbitrary and capricious level, say 2500? ;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points