1 2 3 4 5 6 Previous Next 81 Replies Latest reply: Mar 12, 2013 11:22 AM by odie_63 Go to original post RSS
      • 30. Re: Dynamic SQL without using SQL
        Templestowe
        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
          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
            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
              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
                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
                  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
                    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
                      OK my bad APC, next time!
                      I guess I can close this thread.
                      • 38. Re: Dynamic SQL without using SQL
                        Templestowe
                        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
                          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
                            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
                              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
                                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
                                  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
                                    >
                                    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? ;)