Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2009
    Posts
    6

    Unanswered: how to view results of function containing DML?

    Selecting the results of an Oracle stored function that doesn't contain DML can be done by simply selecting the function:

    select function_name() from dual;

    If the function contains DML (in this case some inserts to log the arguments passed to the function), the above query is not allowed. (ORA-14551)

    How can I select/view the results of this function?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can I select/view the results of this function?
    One does "view the results" of a function; you invoke/execute the function.

    DECLARE
    RESULTS NUMBER;
    BEGIN
    RESULTS := THIS_FUNCTION('INPUT');
    DBMS_OUTPUT.PUT_LINE(RESULTS);
    END;
    /
    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.

  3. #3
    Join Date
    Dec 2009
    Posts
    6
    Quote Originally Posted by anacedent View Post
    >How can I select/view the results of this function?
    One does "view the results" of a function; you invoke/execute the function.

    DECLARE
    RESULTS NUMBER;
    BEGIN
    RESULTS := THIS_FUNCTION('INPUT');
    DBMS_OUTPUT.PUT_LINE(RESULTS);
    END;
    /
    Thanks, but the function returns an xmltype. PLSQL Developer has a nice XML viewer when you select an xmltype column, so it would be ideal to be able to use that instead of dumping the string/clob value to dbms_output

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >PLSQL has a nice XML viewer when you select an xmltype column
    PL/SQL runs deep inside the Oracle RDBMS & as such the person invoking any PL/CODE has no direct visibility as to any PL/SQL datatype variable.

    You are free to submit any Enhancement Request you deem appropriate via Metalink.
    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.

  5. #5
    Join Date
    Dec 2009
    Posts
    6
    if I choose "test" in plsql developer, plsqldev produces something like:

    Code:
    declare
      -- Non-scalar parameters require additional processing 
      result xmltype;
    begin
      -- Call the function
      result := find_person(as_surname => :as_surname,
                           as_given => :as_given,
                           ad_birth_date_from => :ad_birth_date_from,
                           ad_birth_date_to => :ad_birth_date_to,
                           as_gender => :as_gender);
    end;
    How can I view the value of the "result" variable?

    Code:
    select result from dual;
    inside the begin/end block produces

    Code:
    ORA-06550: PLS-00428: an INTO clause is expected in this SELECT statement

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL (Structured Query Language) is not the same as PL/SQL.
    PL/SQL is a completely portable, high-performance transaction processing language.
    Neither is the same as SQL*Plus; an Oracle supplied DB client.

    Code:
    SQL> var results xmltype
    Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
    		    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
    		    NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
    		    BINARY_FLOAT | BINARY_DOUBLE ] ]
    With Regard To XMLType, when all else fails Read The Fine Manual.
    Using XMLType

    This AskTom thread may assist
    http://asktom.oracle.com/pls/asktom/...14700346186423
    Last edited by anacedent; 12-12-09 at 19:55.
    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.

  7. #7
    Join Date
    Dec 2009
    Posts
    6
    Thanks Anacedent, but I don't think we're on the same page here. Some background: I come from using SQLAnywhere and am new to Oracle -- we all start somewhere. Selecting a function, whether it contains DML or not is straightforward in sqlanywhere. I can simply do:

    select function_name();

    and get a result that is viewable.

    I can do the same in Oracle for functions that do not contain DML:

    select function_name() from dual;

    If I strip the DML out of the function in question and run the above query, I get the xmltype result. My question is simply, how can I view the xmltype result when the function contains DML? I have had a suggestion to convert the function to a procedure with a sys_refcursor out parameter...

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    17:22:05 SQL> @fdml
    17:22:08 SQL> create or replace function ftest(istr varchar2)
    17:22:08   2  return number
    17:22:08   3  as
    17:22:08   4  begin
    17:22:08   5  insert into tbl values(istr,sysdate);
    17:22:08   6  return length(istr);
    17:22:08   7  end ftest;
    17:22:08   8  /
    
    Function created.
    
    17:22:09 SQL> select ftest('hi') from dual;
    select ftest('hi') from dual
           *
    ERROR at line 1:
    ORA-14551: cannot perform a DML operation inside a query
    ORA-06512: at "DBADMIN.FTEST", line 5
    
    
    17:22:09 SQL> create or replace function ftest(istr varchar2)
    17:22:09   2  return number
    17:22:09   3  as
    17:22:09   4  begin
    17:22:09   5  return length(istr);
    17:22:09   6  end ftest;
    17:22:09   7  /
    
    Function created.
    
    17:22:09 SQL> select ftest('hi') from dual;
    
    FTEST('HI')
    -----------
    	  2
    
    17:22:09 SQL> create or replace procedure ptest(istr varchar2)
    17:22:09   2  as
    17:22:09   3  PRAGMA AUTONOMOUS_TRANSACTION;
    17:22:09   4  begin
    17:22:09   5  insert into tbl values(istr,sysdate);
    17:22:09   6  commit;
    17:22:09   7  end ptest;
    17:22:09   8  /
    
    Procedure created.
    
    17:22:09 SQL> create or replace function ftest(istr varchar2)
    17:22:09   2  return number
    17:22:09   3  as
    17:22:09   4  begin
    17:22:09   5  ptest(istr);
    17:22:09   6  return length(istr);
    17:22:09   7  end ftest;
    17:22:09   8  /
    
    Function created.
    
    17:22:09 SQL> select ftest('hi') from dual;
    
    FTEST('HI')
    -----------
    	  2
    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.

  9. #9
    Join Date
    Dec 2009
    Posts
    6
    Thank you!! I added

    pragma autonomous_transaction

    to the function and now it can be selected from dual. Wow, that is not something I would have discovered on my own any time soon. I don't know what negative impacts there might be in doing that, but it works, good enough for me

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >pragma autonomous_transaction
    >I don't know what negative impacts there might be in doing that,
    See a SELECT is "assumed" to be a "read-only" operation & never change data.
    This is why Oracle throws an error by default when this "rule" is violated.
    The downside of PRAGMA AUTONOMOUS_TRANSACTION is that it can/may have negative impact on transactional integrity.

    For example assume a multiple DML transaction is underway and part of it includes
    SELECT FTEST('INPUT') FROM DUAL;
    and then some more DML.
    For whatever reason the business logic requires a ROLLBACK to occur.
    The DML inside the AUTONOMOUS_TRANSACTION will NOT be included in ROLLBACK.
    If this behaviour is acceptable, then you have a workable solution.
    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
    Dec 2009
    Posts
    6
    Quote Originally Posted by anacedent View Post
    The DML inside the AUTONOMOUS_TRANSACTION will NOT be included in ROLLBACK.
    If this behaviour is acceptable, then you have a workable solution.
    Yes, the DML is simply logging the arguments to the function in a log table, which should never be rolled back. Thanks again for your help!!

Posting Permissions

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