Wednesday 19 November 2014


PL/SQL 101 : Understanding Ref Cursors

BluShadowGuru Moderator
    PL/SQL 101: Understanding Ref Cursors
    Often on the forum we get a question along the lines of:
    "I'm passing a ref cursor back from a function, how do I use the data in that ref cursor in my select statement/query"
    And, equally as often, the problem comes from a lack of understanding what a ref cursor actually is.  So I thought I'd give a quick summary of the basics behind ref cursors with a view to giving a clearer understanding of them. (Please excuse any errors or ommissions)

    1. What is a ref cursor and how is the data fetched
    Let's start with a simple function that opens a ref cursor and passes it back..
    (We'll just select some employee details for a specified department)
    SQL> create or replace function get_dept_emps(p_deptno in number) return sys_refcursor is
      2    v_rc sys_refcursor;
      3  begin
      4    open v_rc for 'select empno, ename, mgr, sal from emp where deptno = :deptno' using p_deptno;
      5    return v_rc;
      6  end;
      7  /
    Function created.
    Now, if we look at using this through SQL*Plus we first create ourselves a ref cursor variable to accept the results of the function, and then call the function to get the ref cursor back..
    (Note: SQL*Plus variable type of "refcursor" is the equivalent of PL/SQL's "sys_refcursor")
    SQL> var rc refcursor
    SQL> exec :rc := get_dept_emps(10);
    PL/SQL procedure successfully completed.
    Ok, so our variable "rc" has our ref cursor.
    If we use SQL*Plus' print command now we get..
    SQL> print rc;
         EMPNO ENAME             MGR        SAL
    ---------- ---------- ---------- ----------
          7782 CLARK            7839       2450
          7839 KING                        5000
          7934 MILLER           7782       1300
    Brilliant, so our ref cursor returns the rows we wanted from the employee table.

    Let's look at that again..
    SQL> print rc;
    SP2-0625: Error printing variable "rc"
    Uh oh! What's happened here?  Why can't we print the data that's in our ref cursor again?
    A common mistake is that people believe that a ref cursor actually contains the result data from the query. In truth, the ref cursor doesn't contain any data at all, it's just a pointer to the query.
    So why did the first print statement print out the results?
    SQL*Plus looked at the ref cursor and saw that it was an open cursor.  As such it went into a loop, fetching each row of data from the database, using the ref cursor as it's reference (pointer) to the relevant query, and displaying each row of data until it had no more rows to fetch.  Once it's fetched all the rows it closes the cursor.  The power of SQL*Plus's "print" command eh!  
    Therefore, when we tried to print the ref cursor a second time, we got an error because SQL*Plus looked at the cursor, saw it was not an open cursor and couldn't perform the task of printing anything. Let's look at this in a bit more detail so it's a little clearer.
    We'll use some PL/SQL code so that we can see the type of thing SQL*Plus us doing internally..
    Firstly, let's get the open cursor and ask it how many rows it has..
    SQL> ed
    Wrote file afiedt.buf
      1  declare
      2    v_rc    sys_refcursor;
      3  begin
      4    v_rc := get_dept_emps(10);  -- This returns an open cursor
      5    dbms_output.put_line('Rows: '||v_rc%ROWCOUNT);
      6    close v_rc;
      7* end;
    SQL> /
    Rows: 0
    PL/SQL procedure successfully completed.
    Yep, sure enough it's reporting 0 rows.

    It's important that we remember that ROWCOUNT reports how many rows have been fetched through the cursor.  Just after opening the cursor we haven't fetched any rows yet.  If we fetch a row of data then we can see this change..
    SQL> ed
    Wrote file afiedt.buf
      1  declare
      2    v_rc    sys_refcursor;
      3    v_empno number;
      4    v_ename varchar2(10);
      5    v_mgr   number;
      6    v_sal   number;
      7  begin
      8    v_rc := get_dept_emps(10);  -- This returns an open cursor
      9    dbms_output.put_line('Pre Fetch: Rows: '||v_rc%ROWCOUNT);
    10    fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
    11    dbms_output.put_line('Post Fetch: Rows: '||v_rc%ROWCOUNT);
    12    close v_rc;
    13* end;
    SQL> /
    Pre Fetch: Rows: 0
    Post Fetch: Rows: 1
    PL/SQL procedure successfully completed.
    So let's fetch all our data and display it..
    SQL> ed
    Wrote file afiedt.buf
      1  declare
      2    v_rc    sys_refcursor;
      3    v_empno number;
      4    v_ename varchar2(10);
      5    v_mgr   number;
      6    v_sal   number;
      7  begin
      8    v_rc := get_dept_emps(10);  -- This returns an open cursor
      9    loop
    10      fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
    11      exit when v_rc%NOTFOUND;  -- Exit the loop when we've run out of data
    12      dbms_output.put_line('Row: '||v_rc%ROWCOUNT||' # '||v_empno||','||v_ename||','||v_mgr||','||v_sal);
    13    end loop;
    14    close v_rc;
    15* end;
    SQL> /
    Row: 1 # 7782,CLARK,7839,2450
    Row: 2 # 7839,KING,,5000
    Row: 3 # 7934,MILLER,7782,1300
    PL/SQL procedure successfully completed.
    And what happens if we try and fetch more data after it's finished, just like we tried to do in SQL*Plus..
    SQL> ed
    Wrote file afiedt.buf
      1  declare
      2    v_rc    sys_refcursor;
      3    v_empno number;
      4    v_ename varchar2(10);
      5    v_mgr   number;
      6    v_sal   number;
      7  begin
      8    v_rc := get_dept_emps(10);  -- This returns an open cursor
      9    loop
    10      fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
    11      exit when v_rc%NOTFOUND;  -- Exit the loop when we've run out of data
    12      dbms_output.put_line('Row: '||v_rc%ROWCOUNT||' # '||v_empno||','||v_ename||','||v_mgr||','||v_sal);
    13    end loop;
    14    close v_rc;
    15    fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
    16* end;
    SQL> /
    Row: 1 # 7782,CLARK,7839,2450
    Row: 2 # 7839,KING,,5000
    Row: 3 # 7934,MILLER,7782,1300
    declare
    *
    ERROR at line 1:
    ORA-01001: invalid cursor
    ORA-06512: at line 15
    As expected we get an error.  
    So now we understand the basics of what a ref cursor is.
    You can clearly see that it is just a pointer to the query and it doesn't contain any data itself, it just allows us to reference the query so that we can fetch data as we require it.

    2. How can we use a ref cursor in a SQL query?
    Ok, so now you've got your ref cursor you want to use it.  But how can you fetch the data from the ref cursor inside another SQL Select statement?
    Perhaps we can just select from it?
    SQL> select * from get_dept_emps(10);
    select * from get_dept_emps(10)
                               *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
    Nope.  How about if we tell SQL to treat it as a table?
    SQL> select * from table(get_dept_emps(10));
    select * from table(get_dept_emps(10))
                  *
    ERROR at line 1:
    ORA-22905: cannot access rows from a non-nested table item
    What about using it as a set of data in an IN condition?
    SQL> select * from emp where empno in (get_dept_emps(10));
    select * from emp where empno in (get_dept_emps(10))
                                      *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected - got CURSER
    N.B. The spelling error of "CURSER" is Oracle's, not mine.  

    (Ok, I know the ref cursor is referencing more than just the empno in that example, but it would still result in the same error if it just referenced the empno)

    The problem we're having is because the ref cursor isn't a table of data either in the literal database sense or in an array sense, and it's not a set of data that can be compared with the IN clause.
    So what's the point in these ref cursors? Is there a way we can use them?
    Yes there is..
    First let's create a type structure on the database.  Remember, SQL cannot access PL/SQL table structures so the type must be a database object..
    SQL> create or replace type emptype as object(empno number,
      2                                           ename varchar2(10),
      3                                           mgr   number,
      4                                           sal   number);
      5  /
    Type created.
    SQL> create or replace type t_emptype as table of emptype;
      2  /
    Type created.
    Ok, so we have a structure to hold a record and a type that is a table of that structure.  So far so good.  But in order to populate that structure with data coming from the ref cursor we can't just select from it as we saw above.  Instead we need to provide some PL/SQL to actually do the fetching of data for us and populate the structure..
    SQL> ed
    Wrote file afiedt.buf
      1  create or replace function populate_emps(deptno in number := null)
      2  return t_emptype is
      3    v_emptype t_emptype := t_emptype();  -- Declare a local table structure and initialize it
      4    v_cnt     number := 0;
      5    v_rc      sys_refcursor;
      6    v_empno   number;
      7    v_ename   varchar2(10);
      8    v_mgr     number;
      9    v_sal     number;
    10  begin
    11    v_rc := get_dept_emps(deptno);
    12    loop
    13      fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
    14      exit when v_rc%NOTFOUND;
    15      v_emptype.extend;
    16      v_cnt := v_cnt + 1;
    17      v_emptype(v_cnt) := emptype(v_empno, v_ename, v_mgr, v_sal);
    18    end loop;
    19    close v_rc;
    20    return v_emptype;
    21* end;
    SQL> /
    Function created.
    The above function calls the function that opens the ref cursor, then loops through, fetching each row and populating our SQL type structure.  When all rows have been fetched, the ref cursor is closed and the SQL table structure is passed back from the function.
    So now we have something in an structure that SQL understands, we should be able to query directly from it..
    SQL> select * from table(populate_emps(30));
         EMPNO ENAME             MGR        SAL
    ---------- ---------- ---------- ----------
          7499 ALLEN            7698       1600
          7521 WARD             7698       1250
          7654 MARTIN           7698       1250
          7698 BLAKE            7839       2850
          7844 TURNER           7698       1500
          7900 JAMES            7698        950
    6 rows selected.
    and
    SQL> select * from emp where empno in (select empno from table(populate_emps(30)));
         EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
          7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-1981       1250        500         30
          7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30
          7844 TURNER     SALESMAN        7698 08-SEP-1981       1500          0         30
          7900 JAMES      CLERK           7698 03-DEC-1981        950                    30
    6 rows selected.
    SQL>
    Hoorah!
    We've successfully taken our ref cursor (pointer) and used it to fetch the data back that we want in a structure that SQL can understand.  Ok, the examples are pretty meaningless as they stand as we could easily have achieved the same results through a basic select, but the method is what is important to understand here.

    3. What is the point of ref cursors?
    A good question.  Many people learn about ref cursors and then try and use them everywhere.  Although I personally know how to write and use ref cursors, I've found very little use for them in my production code. If you are thinking of using a ref cursor, the first thing you should ask yourself is "Why do I need one?"
    If your answer is that you need to write your code to dynamically generate SQL, then perhaps you should look at what you are trying to achieve.  Typically dynamic SQL, in a lot of cases, is unnecessary and a major cause of unmaintainable and unscalable code as well as possible performance issues and SQL injection.
    If you really have a valid need to dynamically create SQL and you fully understand the implications and risks involved, then a ref cursor is useful for this task.  What you will find however is that you are limited with ref cursors to a fixed result structure, so it may not be as generic a solution as you had initially planned for.  If you consider the examples from above, you will see that we had to define a SQL structure of known columns in order to receive the data from the ref cursor.  So, whilst the function that opens the ref cursor could do so for any piece of query text, the only way of successfully getting the data out of that ref cursor is to know what columns we are expecting back from it.
    There is a way around this however.  Either use the DBMS_SQL package to dynamically generate and process your queries or, from 11g onwards, take your ref cursor and convert it to a DBMS_SQL cursor using 11g's new DBMS_SQL.TO_CURSOR_NUMBER feature (http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sql.htm#CHDJDGDG).
    DBMS_SQL is a very powerful way of writing dynamic SQL, but that's for another article.  

    Summary
    In summary, the one key thing to remember is..
    REF CURSORS ARE NOT DATA CONTAINERS.  ONCE OPENED, THEY ARE SIMPLY A POINTER TO A QUERY THAT HAS YET TO FETCH DATA.
    Other articles in the PL/SQL 101 series:

    ------------------------------------------------------------------------------------------------------------------------------------------

    Hi Tom,
    
          Feels good that i got a chance to ask you a question. I attended an interview for 
    oracle pl/sql. There he asked one question as what is the difference between cursor and 
    ref cursor, and when would you appropriately use each of these. Could you please tell me 
    how can I effectively answer this question in short.
    
    Your input will be greatly appreciated.
    
    Thanks and regards,
    Praveen. 

    and we said...

    technically, under the covers, at the most "basic level", they are the same.
    
    A "normal" plsql cursor is static in defintion.
    
    Ref cursors may be dynamically opened or opened based on logic.
    
    Declare
       type rc is ref cursor;
       
       cursor c is select * from dual;
    
       l_cursor rc;
    begin
       if ( to_char(sysdate,'dd') = 30 ) then
         open l_cursor for 'select * from emp';
       elsif ( to_char(sysdate,'dd') = 29 ) then
         open l_cursor for select * from dept;
       else
         open l_cursor for select * from dual;
       end if;
       open c;
    end;
    /
    
    
    Given that block of code -- you see perhaps the most "salient" difference -- no matter 
    how many times you run that block -- cursor C will always be select * from dual.  The ref 
    cursor can be anything.
    
    Another difference is a ref cursor can be returned to a client.  a plsql "cursor cursor" 
    cannot be returned to a client.
    
    Another difference is a cursor can be global -- a ref cursor cannot (you cannot define 
    them OUTSIDE of a procedure / function)
    
    Another difference is a ref cursor can be passed from subroutine to subroutine -- a 
    cursor cannot be.
    
    Another difference is that static sql (not using a ref cursor) is much more efficient 
    then using ref cursors and that use of ref cursors should be limited to
    - returning result sets to clients
    - when there is NO other efficient/effective means of achieving the goal
    
    that is, you want to use static SQL (with implicit cursors really) first and use a ref 
    cursor only when you absolutely have to
    
    Then sit back and say "anything else you wanted to know about them" 
    
    
    
    
    -------------------------------------------------------------------------------
    
    
    Difference between REF Cursor & Normal Cursor in Oracle - Feb 18, 2010 at 11:00 PM by Rajmeet Ghai

    What is the difference between REF Cursor & Normal Cursor in oracle?

    REF cursor is typically used to return record set or a cursor from stored procedure. REF Cursor is basically a data type. It is normally declared as type r_cursor is REF CURSOR; REF cursor supports dynamic change of query.
    Normal cursor is a static cursor in which the query is assigned at design time and cant be changed at run time.
    Difference between REF Cursor & Normal Cursor in Oracle - April 08, 2009 at 13:00 PM by Rajmeet Ghai

    What is the difference between REF Cursor & Normal Cursor in oracle?

    Normal cursors fall under the category of static cursors while REF cursors are dynamic. This means that normal cursors can only be used again not defined. Ref cursors on the other hand can be changed. A Ref cursor can be passed from one procedure to another. A normal cursor cannot.
    http://careerride.com/Oracle-difference-REF-and-normal-cursor.aspx
    
    

      Thursday 6 March 2014

      Why should Apps & Applsys passwords always be the same?

      Why should Apps & Applsys passwords always be the same?

      The need to have the same password for Apps and Applsys is because when you sign on to apps, intially it connects to a public schema called APPLSYSPUB. This validates AOL username and password that we enter (operations/welcome using guest user account. Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to APPS schema.

      Since it uses both applsys and apps during signon process this expects both the password to be identical. Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login. Apps is a universal schema has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc.). Applsys schema has applications technology layer products like FND and AD etc.

       

       

      Thursday 6 February 2014

      FND and AD tables

      Concurrent Manager
      FND_CONCURRENT_QUEUES
      FND_CONCURRENT_PROGRAMS
      FND_CONCURRENT_REQUESTS
      FND_CONCURRENT_PROCESSES
      FND_CONCURRENT_QUEUE_SIZE
      FND
      FND_APPL_TOPS
      FND_LOGINS
      FND_USER
      FND_DM_NODES
      FND_TNS_ALIASES
      FND_NODES
      FND_RESPONSIBILITY
      FND_DATABASES
      FND_UNSUCCESSFUL_LOGINS
      FND_LANGUAGES
      FND_APPLICATION
      FND_PROFILE_OPTION_VALUES
      AD / Patches
      AD_APPLIED_PATCHES
      AD_PATCH_DRIVERS
      AD_BUGS
      AD_INSTALL_PROCESSES
      AD_SESSIONS
      AD_APPL_TOPS

      Log file location for Oracle EBS R12

      Log file location for Oracle EBS R12

      Log file location for Oracle EBS R12

      - Autoconfig log file:
      Apps:
      $INST_TOP/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log

      Db:
      $ORACLE_HOME/appsutil/log/$CONTEXT_NAME//adconfig.log $ORACLE_HOME/appsutil/log/$CONTEXT_NAME//NetServiceHandler.log

      - Startup/Shutdown Log files:
      $INST_TOP/logs/appl/admin/log

      - Apache, OC4J and OPMN:
      $LOG_HOME/ora/10.1.3/Apache $LOG_HOME/ora/10.1.3/j2ee $LOG_HOME/ora/10.1.3/opmn

      - Patch log:
      $APPL_TOP/admin/$SID/log/

      - Concurrent log:
      $INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log

      - Clone log:
      Preclone log files in source instance


      Apps:
      $INST_TOP/apps/$CONTEXT_NAME/admin/log/ (StageAppsTier_MMDDHHMM.log)

      Db:
      $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)

      Clone log files in target instance
      Apps :
      $INST_TOP/apps/$CONTEXT_NAME/admin/log/ApplyAppsTier_.log

      Db:
      $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log

      - Alert Log File:
      $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

      Reference:
      How to find location of Install, Autoconfig, Patching , Clone and other logs in R12 [ID 804603.1]
      Oracle Application Server Diagnostic Tools and Log Files in Applications Release 12 [ID 454178.1]
      Oracle Applications System Administrator’s Guide



      Read more: http://prasanthapps.blogspot.com/2011/05/log-file-location-for-oracle-ebs-r12.html#ixzz1ho0BhdfZ

      log files in R12

      Where do concurrent request log files and output files go?

      The concurrent manager first looks for the environment variable $APPLCSF
      If this is set, it creates a path using two other environment variables:
      $APPLLOG and $APPLOUT
      It places log files in $APPLCSF/$APPLLOG
      Output files go in $APPLCSF/$APPLOUT
      So for example, if you have this environment set:
      $APPLCSF = /u01/appl/common
      $APPLLOG = log
      $APPLOUT = out
      The concurrent manager will place log files in /u01/appl/common/log, and
      output files in /u01/appl/common/out
      Note that $APPLCSF must be a full, absolute path, and the other two are
      directory names.
      If $APPLCSF is not set, it places the files under the product top of the application associated with the request. So for example, a PO report would go under $PO_TOP/$APPLLOG and $PO_TOP/$APPLOUT
      Logfiles go to: /u01/appl/po/9.0/log
      Output files to: /u01/appl/po/9.0/out
      Of course, all these directories must exist and have the correct permissions.
      Note that all concurrent requests produce a log file, but not necessarily an output file.



      Where are the log files located in R12?

      For DBA’s who mostly worked on 11i environments, finding the log files (Concurrent manager log, apache log etc.,) on a R12 environment might pose a threat initially, because these log files no longer reside in their old location ie., $APPLCSF/$APPLLOG or $APACHE_TOP/Apache/logs.
      In R12, the log files are located in $LOG_HOME (which translates to $INST_TOP/logs)
      Concurrent Reqeust related logs
      $LOG_HOME/appl/conc -> location for concurrent requests log and out files
      $LOG_HOME/appl/admin -> location for mid tier startup scripts log files
      Apache Logs (10.1.3 Oracle Home which is equivalent to iAS Oracle Home)
      $LOG_HOME/ora/10.1.3/Apache -> Location for Apache Error and Access log files
      $LOG_HOME/ora/10.1.3/j2ee -> location for j2ee related log files
      $LOG_HOME/ora/10.1.3/opmn -> location for opmn related log files
      Forms & Reports related logs (10.1.2 Oracle home which is equivalent to 806 Oracle Home)
      $LOG_HOME/ora/10.1.2/forms
      $LOG_HOME/ora/10.1.2/reports
      Related metalink notes to enable additional debugging
      419839.1 – How to enable Apache, OC4J and OPMN logging in Oracle Applications R12
      422419.1 – R12 – How To Enable and Collect Debug for HTTP, OC4J and OPMN


      Patch info

       SELECT a.application_name,
      DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
      patch_level
      FROM apps.fnd_application_vl a, apps.fnd_product_installations b
      WHERE a.application_id = b.application_id;

      SELECT patch_name, patch_type, maint_pack_level, creation_date
      FROM applsys.ad_applied_patches
      ORDER BY creation_date DESC

      R12: Clone fails with error "ORA-28000 The account is locked"

      R12: Clone fails with error "ORA-28000 The account is locked"

      R12: Clone fails with error "ORA-28000 The account is locked" [ID 1114244.1]




      In this Document
        Symptoms
        Cause
        Solution
        References



      Applies to:

      Oracle Applications Manager - Version: 12.0.0 to 12.1.3 - Release: 12 to 12.1
      Information in this document applies to any platform.
      ***Checked for relevance on 06-Feb-2012***

      Symptoms

      The clone of an E-Business Suite Instance of Release 12 is failing with following error :

      ...
      Exception occurred: java.sql.SQLException: ORA-28000: the account is locked
      ORA-28000: the account is locked
      ...

      Cause

      The issue is caused by the APPS Database account, which is locked.

      The following script allows you to check the number of failed logins, which can lock the apps user :

      select profile , resource_name, limit
      from dba_profiles
      where resource_name ='FAILED_LOGIN_ATTEMPTS';


      Example output :

      DEFAULT FAILED_LOGIN_ATTEMPTS : 10
      AD_PATCH_MONITOR_PROFILE FAILED_LOGIN_ATTEMPTS : 3


      For additional information, please review Note 114930.1 - 'Oracle Password Management Policy'.

      Solution

      To implement the solution, please execute the following steps:
      1. Ensure that you have taken a backup of your Environment.

      2. Increase the number of failed login which locks apps user (optional) :

      alter profile AD_PATCH_MONITOR_PROFILE limit failed_login_attempts unlimited;
      alter profile default limit failed_login_attempts unlimited password_lock_time 1/1440;

      3. Unlock the locked APPS User account :

      alter user apps account unlock;


      4. Rerun the failed cloning commands.

      5. Migrate the solution as appropriate to other Environments