Home     |     Java    |     Php General    |     Oracle Database    |     Oracle Server  

MS Dynamics CRM 3.0

  •  Setting up and Configuring Microsoft Dynamics CRM 3.0
  •  Managing Security and Information Access
  •  Entity Customization: Concepts and Attributes
  •  Entity Customization: Forms and Views
  •  Entity Customization: Relationships, Custom Entities, and Site Map
  •  Reporting and Analysis
  •  Workflow
  •  Server-Side SDK
  •  Client-Side SDK
  •  Integration with External Applications

    • Cervo Technologies
    The Right Source to Outsource

    Sharepoint Portal Server KB

    Microsoft CRM Info

    WPF Interview Questions

    SilverLight Interview Qs

    Asp.Net 2.0 Interview Qs

    Asp.NET 1.1 FAQs

    Oracle Interview Questions

    SAP Interview Questions

    Oracle Database

    beginner: cursors


    I have put together the following plsql  and can verify that it does
    return the expected result..."the closest existing salary to a user
    entered salary of 8000"
    The problem i face with this having to make this a stored procedure or
    function without whole-sale changes.   Any ideas?  The required use of
    a cursor makes this especially challenging for me.

    DECLARE
      name    empbb02.ename%TYPE;
      salary empbb02.sal%TYPE;
      TYPE cursor_var IS REF CURSOR;
      myCursorVar cursor_var;
      TargetSalary empbb02.sal%TYPE;
      rk number(5);

    BEGIN

      OPEN myCursorVar FOR SELECT RANK()OVER(ORDER BY ABS(E.sal - 8000))AS
    RNK, E.ename
    , E.sal
      FROM empbb02 E;

      LOOP
        FETCH myCursorVar INTO rk, name,salary;
        EXIT WHEN myCursorVar%NOTFOUND OR rk ='2';
        DBMS_OUTPUT.PUT_LINE(rk||' '||name||'  '||salary);
      END LOOP;
      CLOSE myCursorVar;
    END;
    /

    Replace 'DECLARE' with 'CREATE OR REPLACE PROCEDURE <your_proc_name>
    AS'
    and you're pretty much done.

    -----------------------------------------------Reply-----------------------------------------------
    Other things you might want to consider are:
    1. Using a FOR LOOP
    2. If you're just want the first match (where there are equal ranks)
    then using SELECT ... INTO and no LOOP.
    3. Rather than bombing out of the loop when NOTFOUND or rk=2, restrict
    the rk=2 condition in the actual select:
    e.g. SELECT rnk,ename,sal FROM (SELECT RANK()OVER(ORDER BY ABS(E.sal -
    8000)) AS RNK, E.ename, E.sal FROM empbb02 E ORDER BY rnk) WHERE rnk =
    1;
    4. Creating a function that accepts the salary as an input and returns
    the refcursor, rather than outputting to dbms_output

    -----------------------------------------------Reply-----------------------------------------------
    On Apr 10, 1:41 am, "dombrooks" <dombro@hotmail.com> wrote:

    > Replace 'DECLARE' with 'CREATE OR REPLACE PROCEDURE <your_proc_name>
    > AS'
    > and you're pretty much done.

    I attempted to do this and receive a Procedure Created with
    Compilation Errors.

    Here is what I altered from the initial post above:

    CREATE OR REPLACE PROCEDURE sp_seniority(
         name OUT  empbb02.ename%TYPE,
         seniority OUT number,
         TYPE cursor_var IS REF CURSOR,
         myCursorVar OUT cursor_var;) AS
      BEGIN

    Does that TYPE cursor_var also need to be specified as an OUT?

    -----------------------------------------------Reply-----------------------------------------------

    On Apr 10, 9:58 am, "matt" <reflectio@gmail.com> wrote:

    -->    name_list empbb02.ename%type,
    -->    seniority_list number;

    >   BEGIN

    > Does that TYPE cursor_var also need to be specified as an OUT?

    correction...yet the same result.

    -----------------------------------------------Reply-----------------------------------------------

    CREATE OR REPLACE TYPE or declare it in a package header.

    Look at these examples:
    http://www.psoug.org/reference/ref_cursors.html
    --
    Daniel A. Morgan
    University of Washington
    damor@x.washington.edu
    (replace x with u to respond)
    Puget Sound Oracle Users Group
    www.psoug.org

    -----------------------------------------------Reply-----------------------------------------------

    On Apr 10, 10:15 am, DA Morgan <damor@psoug.org> wrote:

    Here's the modifications i've made to get the original plsql into a
    function:

    SQL> DECLARE
      2   TYPE cursor_var IS REF CURSOR;
      3   myCursorVar cursor_var;
      4
      5  FUNCTION get_seniority(name empbb02.ename%type, seniority number)
    RETURN cursor_var IS
      6  name_list empbb02.ename%type;
      7  seniority_list number;
      8
      9  BEGIN
     10    OPEN myCursorVar FOR SELECT E.ename,
    TRUNC(MONTHS_BETWEEN(sysdate,E.hiredate)) FROM empbb02 E;
     11    LOOP
     12      FETCH myCursorVar INTO name,seniority;
     13      EXIT WHEN myCursorVar%NOTFOUND;
     14     RETURN (name_list , seniority_list);
     15      DBMS_OUTPUT.PUT_LINE(name||'  '||seniority);
     16    END LOOP;
     17    CLOSE myCursorVar;
     18  END get_seniority;
     19  /
    END get_seniority;

    It seems to complain about the end-of-file on line#18.

    -----------------------------------------------Reply-----------------------------------------------

    On 10 Apr 2007 10:10:22 -0700, "matt" <reflectio@gmail.com> wrote:

    Please always post explicit error messages. You can look them up by
    either issue
    show errors
    or
    select * from user_errors where name=<your procedure name>
    Obviously this is documented.

    Also, you can't define a TYPE in a formal parameter list of a
    procedure.
    You can define a type on package level, or inside a procedure.
    Obviously this is also documented.

    If you run to this newsgroup for everything you don't understand, you
    will notice you will get ignored soon, as most people responding here,
    did learn it by reading manuals, as Usenet didn't yet exist or was
    inaccessible.

    --

    -----------------------------------------------Reply-----------------------------------------------

    The above code example is a horror story with so many things
    wrong I'm not sure where to start.

    I would suggest pushing back away from the keyboard and getting
    both Tom Kyte's books and a class in beginning PL/SQL. The c.d.o.
    groups are not an on-line tutorial.

    On your list of things to learn should be the following syntax:
    OPEN <refcursor> FOR
    --
    Daniel A. Morgan
    University of Washington
    damor@x.washington.edu
    (replace x with u to respond)
    Puget Sound Oracle Users Group
    www.psoug.org

    -----------------------------------------------Reply-----------------------------------------------

    On Apr 10, 12:51 pm, DA Morgan <damor@psoug.org> wrote:

    Haha..well put.   I did back away from the keyboard...and the copy/
    paste, for that matter.  I had a false sense of thinking i was close.
    I'll revisit the console after an hour or two of reading.   Thanks.
    Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc