Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Double Post ~ Please delete

    Hi everyone...I've got an ASP page that has an oracle stored proc in it. The problem is im paging records and since I have to run the entire query in the SP even though I'm only viewing say 15 at a time. This is coming at a very large costs because of the sheer amount of total records (20k +??)
    I've found this article:
    http://www.15seconds.com/issue/010308.htm

    On writing a stored proc to do record paging on SQL server....Can anyone give me an example or a link with further info on settnig up record paging with Oracle instead? This would get me out of a huge jam...

    Thank you in advance
    Last edited by RhythmAddict; 09-30-04 at 11:07. Reason: dbl pst

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    What you can do is to actually set your paging within the query it self.

    Consider the following.. suppose you want to display at most 4 rows out of the EMP table for each Page you are requesting. You would set up a querystring for the pagesize you want (in this case is 4), and another for the pagenumber.

    Then, you can take this values out of ASP, and form your query like this:

    Code:
    select *
      from (
    select empno, ename, job, mgr, hiredate, sal, comm, deptno
      from emp
     order by empno asc
           )
     where ceil(rownum/pagesize) = pagenumber
    So, in the case I described above, your pagesize will be 4 (i.e. how many rows a given pagenumber will have) and you will get the pagenumber out of the query to know which page to display. This will save a lot of work and memory since it will only fetch those rows you want, without sacrifying memory in either, the server or the client.

    P.S.: Make sure to make use of bind variables for pagenumber and pagesize there, so you will in fact be gaining a lot of performance!

  3. #3
    Join Date
    Dec 2003
    Posts
    148
    Hi, first off thank you for your quick and helpful reply. This sounds like exactly what I'm looking for however im experiencing some complications applying it. I am sure I must've misunderstood something....
    My code will explain:

    Code:
    SQL> ed
    Wrote file afiedt.buf
    
      1  select *
      2    from (
      3  select empno, ename, job, mgr, hiredate, sal, comm, deptno
      4    from emp
      5   order by empno asc
      6         )
      7*  where ceil(rownum/5) = 1
    SQL> /
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800
            20
    
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
            30
    
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
            30
    
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
    ---------- ---------- --------- ---------- --------- ---------- ----------
        DEPTNO
    ----------
          7566 JONES      MANAGER         7839 02-APR-81       2975
            20
    
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
            30
    
    
    SQL>
    At this point I am all good, I have paged out 5 records...
    Am I doing somethin incorrectly by specifying pagenumber = 2?

    Code:
    SQL> ed
    Wrote file afiedt.buf
    
      1  select *
      2    from (
      3  select empno, ename, job, mgr, hiredate, sal, comm, deptno
      4    from emp
      5   order by empno asc
      6         )
      7*  where ceil(rownum/5) = 2
    SQL> /
    
    no rows selected
    
    SQL>
    Thank you for your help - it is appreciated.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Yes, thank you for spotting that out! I forgot rownum is fetched AFTER the successfull of a row fetch!

    Try this work around instead:

    Code:
    select t.*
      from (
    select t.*, rownum rn
      from (
    select empno, ename, job, mgr, hiredate, sal, comm, deptno
      from emp
     order by empno asc
           ) t
           ) t
     where ceil(t.rn/5) = 2
    Last edited by JMartinez; 09-30-04 at 11:57.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    An issue with this approach is that it will always process all rows in the inner query. It will be slower than stopping once the upper page bound has been found:
    Code:
    SQL> create table big as
      2  select object_id, object_name
      3  from all_objects
      4  where rownum <= 10000
      5  /
    
    Table created.
    
    SQL> alter table big add constraint big_pk primary key (object_id);
    
    Table altered.
    SQL> select t.*
      2    from (
      3  select t.*, rownum rn
      4    from (
      5  select *
      6    from big
      7   order by object_id asc
      8         ) t
      9         ) t
     10   where ceil(t.rn/5) = 1
     11  /
    
     OBJECT_ID OBJECT_NAME                            RN
    ---------- ------------------------------ ----------
           195 DUAL                                    1
           228 SYSTEM_PRIVILEGE_MAP                    2
           231 TABLE_PRIVILEGE_MAP                     3
           234 STMT_AUDIT_OPTION_MAP                   4
           435 STANDARD                                5
    
     real: 406
    SQL> /
    
     OBJECT_ID OBJECT_NAME                            RN
    ---------- ------------------------------ ----------
           195 DUAL                                    1
           228 SYSTEM_PRIVILEGE_MAP                    2
           231 TABLE_PRIVILEGE_MAP                     3
           234 STMT_AUDIT_OPTION_MAP                   4
           435 STANDARD                                5
    
     real: 375
    Compare timings with:
    Code:
    SQL> select t.* from
      2  ( select t.*, rownum rn from
      3    ( select * from big
      4      order by object_id
      5    ) t
      6    where rownum <= 5
      7  ) t
      8  where rn >= 1
      9  /
    
     OBJECT_ID OBJECT_NAME                            RN
    ---------- ------------------------------ ----------
           195 DUAL                                    1
           228 SYSTEM_PRIVILEGE_MAP                    2
           231 TABLE_PRIVILEGE_MAP                     3
           234 STMT_AUDIT_OPTION_MAP                   4
           435 STANDARD                                5
    
     real: 125
    tandrews@IDEV
    SQL> /
    
     OBJECT_ID OBJECT_NAME                            RN
    ---------- ------------------------------ ----------
           195 DUAL                                    1
           228 SYSTEM_PRIVILEGE_MAP                    2
           231 TABLE_PRIVILEGE_MAP                     3
           234 STMT_AUDIT_OPTION_MAP                   4
           435 STANDARD                                5
    
     real: 109

  6. #6
    Join Date
    Dec 2003
    Posts
    148
    Hi All.
    I am using the method mentioned earlier in this thread and it works very well....(the method from JM)

    I do have an issue with one of my SP's however....It seems to be because I am using aliases in my stored proc...Check it out
    This is the query in my stored proc:
    Code:
    select t.* from
    ( select t.*, rownum rn from
     ( select to_char(owr.dCreated,''mm/dd/yy'') as dCreated, owr.id, owr.dCreated,
    owr.cWhoCreated, owr.cComment, owr.dCompleted, owr.cWhoCompleted, owr.cBillSys, bs.cDesc 
    FROM tbl_superbatch owr INNER JOIN
    tbl_owr_BillingSystem bs on owr.cbillsys = bs.cid ' || wh || ' ' || srt || ' 
        ) t
        where rownum <= '||PgTo||'
      ) t
     where rn >= '||PgFrom||'';
    It compiles fine, however when I try to run it:
    Code:
    SQL> var r refcursor
    SQL> EXECUTE getsuperbatches.getsuperbatches('wHERE owr.cArea = ''MW''','order by owr.id DESC','15',
    '1',:r);
    BEGIN getsuperbatches.getsuperbatches('wHERE owr.cArea = ''MW''','order by owr.id DESC','15','1',:r)
    
    *
    ERROR at line 1:
    ORA-00918: column ambiguously defined
    ORA-06512: at "OWR.GETSUPERBATCHES", line 14
    ORA-06512: at line 1
    
    
    SQL>
    If I just run the query:

    Code:
    SQL> select t.* from
      2   ( select t.*, rownum rn from
      3     ( select to_char(tbl_superbatch.dCreated,'mm/dd/yy') as dCreated, tbl_superbatch.id, tbl_sup
    erbatch.dCreated,
      4    tbl_superbatch.cWhoCreated, tbl_superbatch.cComment, tbl_superbatch.dCompleted, tbl_superbatc
    h.cWhoCompleted,   tbl_superbatch.cBillSys, tbl_owr_BillingSystem.cDesc 
      5    FROM tbl_superbatch  INNER JOIN
      6    tbl_owr_BillingSystem on tbl_superbatch.cbillsys = tbl_owr_BillingSystem.cid wHERE tbl_superb
    atch.cArea =   'MW'
      7       ) t
      8      where rownum <= '15' 
      9  ) t
     10  where rn >= '1' 
     11  ;
     ( select t.*, rownum rn from
                *
    ERROR at line 2:
    ORA-00918: column ambiguously defined
    
    
    SQL>
    Seems like an issue with the t.* 's...Any suggestions guys?

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    select to_char(tbl_superbatch.dCreated,'mm/dd/yy') as dCreated, tbl_superbatch.id, tbl_superbatch.dCreated,.... also, I dont know why are you using '' around a number ?

    andrewst, strangely enough, they both reproduce the same stats out of SQL*Plus!

    Code:
    SQL> analyze table big compute statistics
      2  for table
      3  for all indexed columns;
    
    Table analyzed.
    
    SQL> set timing on autotrace traceonly explain
    SQL> ed
    Wrote file afiedt.buf
    
      1  select t.*
      2      from (
      3    select t.*, rownum rn
      4      from (
      5    select *
      6      from big
      7     order by object_id asc
      8           ) t
      9           ) t
     10*    where ceil(t.rn/5) = 1
    SQL> /
    Elapsed: 00:00:00.00
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=10000 Bytes=430000)
       1    0   VIEW (Cost=48 Card=10000 Bytes=430000)
       2    1     COUNT
       3    2       VIEW (Cost=48 Card=10000 Bytes=300000)
       4    3         SORT (ORDER BY) (Cost=48 Card=10000 Bytes=210000)
       5    4           TABLE ACCESS (FULL) OF 'BIG' (Cost=4 Card=10000 Bytes=210000)
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  select t.* from
      2    ( select t.*, rownum rn from
      3      ( select * from big
      4        order by object_id
      5      ) t
      6      where rownum <= 5
      7    ) t
      8*   where rn >= 1
    SQL> /
    Elapsed: 00:00:00.00
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=10000 Bytes=430000)
       1    0   VIEW (Cost=48 Card=10000 Bytes=430000)
       2    1     COUNT (STOPKEY)
       3    2       VIEW (Cost=48 Card=10000 Bytes=300000)
       4    3         SORT (ORDER BY STOPKEY) (Cost=48 Card=10000 Bytes=210000)
       5    4           TABLE ACCESS (FULL) OF 'BIG' (Cost=4 Card=10000 Bytes=210000)
    ... but, TKPROF really tell us the truth!

    Code:
    select /* the good*/ t.* from
      ( select t.*, rownum rn from
        ( select * from big
          order by object_id
        ) t
        where rownum <= 5
      ) t
      where rn >= 1
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.02          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.02       0.02          0         53         12           5
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.02       0.04          0         53         12           5
    
    
    select /* the bad */ t.*
        from (
      select t.*, rownum rn
        from (
      select *
        from big
       order by object_id asc
             ) t
             ) t
       where ceil(t.rn/5) = 1
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      2      0.03       0.03          0          0          1           0
    Fetch        2      0.08       0.17         47         53         48           5
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        5      0.11       0.20         47         53         49           5
    Last edited by JMartinez; 09-30-04 at 18:16.

  8. #8
    Join Date
    Dec 2003
    Posts
    148
    Hi again.. You're absolutley right about the two dcreated's being my issue....I changed it and I am fine now....As far as the quotes around a number....this is what happens when I do not have quotes around my number:

    Code:
    SQL> EXECUTE getsuperbatches.getsuperbatches('wHERE owr.cArea = ''MW''','order by owr.id DESC','15',
    '1',:r);
    BEGIN getsuperbatches.getsuperbatches('wHERE owr.cArea = ''MW''','order by owr.id DESC','15','1',:r)
    
    *
    ERROR at line 1:
    ORA-00936: missing expression
    ORA-06512: at "OWR.GETSUPERBATCHES", line 14
    ORA-06512: at line 1
    my the body..
    Code:
     AS
     PROCEDURE getsuperbatches
     (
    
      WH    IN  VARCHAR2,
      SRT    IN  VARCHAR2,
      PGTO  IN  NUMBER,
      PGFROM  IN  NUMBER,
      RC1                     IN  OUT  REF_CUR
     )
     AS
     BEGIN
    
      OPEN RC1 FOR '
    select t.* from
     ( select t.*, rownum rn from
        (select to_char(owr.dCreated,''mm/dd/yy'') as dCreated_sa, 
        owr.id, owr.dCreated,
        owr.cWhoCreated, 
        owr.cComment, 
        owr.dCompleted, 
        owr.cWhoCompleted, 
        owr.cBillSys, 
        bs.cDesc FROM 
        tbl_superbatch owr INNER JOIN
        tbl_owr_BillingSystem bs on owr.cbillsys = bs.cid 
        '|| wh ||' '|| srt ||'
     ) t
        where rownum <= ||PgTo||
      ) t
      where rn >= ||PgFrom||';
    END;
    
    
    END;
    More than happy to take any advice on improving this....Also, please excuse my ignorance here as I am very new to PL/SQL but...What is the diff. between a normal variable and a bind variable...Can i utilize a bind variable here to improve performance as was suggested earlier in this thread?
    Thank to you to both of you this thread has been a large help improving my overall application performance dramatically....!

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It is because this two lines

    Code:
       where rownum <= ||PgTo||
     ...
      where rn >= ||PgFrom||';
    If you change them to
    Code:
       where rownum <= ' || PgTo || ' 
     ...
      where rn >= ' || PgFrom;
    in your code, they should work. (Avoid sending the parameters as strings -- with '' around them -- as well, and if this being executed from ASP, you shouldn't be hardcoding these, use the Command and Parameters objects, they are for this kind of stuff -- bind).

    Well, just to show you off how you would use Bind variables in the package of yours on the rownum stop keys, if you change your code to:
    Code:
    create or replace package body getsuperbatches
    AS
     PROCEDURE getsuperbatches
     (
    
      WH    IN  VARCHAR2,
      SRT    IN  VARCHAR2,
      PGTO  IN  NUMBER,
      PGFROM  IN  NUMBER,
      RC1                     IN  OUT  REF_CUR
     )
     AS
     BEGIN
    
      OPEN RC1 FOR '
    select t.* from
     ( select t.*, rownum rn from
        (select to_char(owr.dCreated,''mm/dd/yy'') as dCreated_sa, 
        owr.id, owr.dCreated,
        owr.cWhoCreated, 
        owr.cComment, 
        owr.dCompleted, 
        owr.cWhoCompleted, 
        owr.cBillSys, 
        bs.cDesc FROM 
        tbl_superbatch owr INNER JOIN
        tbl_owr_BillingSystem bs on owr.cbillsys = bs.cid 
        '|| wh ||' '|| srt ||'
     ) t
        where rownum <= :1
      ) t
      where rn >= :2'
      using PgTo, PgFrom;
    END;
    END;
    It would have used it if the WHERE (wh) construct and the ORDER BY (str) would not be there. But since they are there, and you are actually adding them from the procedure as a paremeter, it will blow up the use of Bind variables here at all. You should be thinking about changing that to a more 'elegant' and efficient way therefore.

  10. #10
    Join Date
    Dec 2003
    Posts
    148
    Hey JM. Thanks again for all your help.
    Just out of curiousity, what would be your suggestion for a more elegant solution on having the where and order by clauses in my SP?

    Also, hypothetically, if I were using bind variables - could I not pass one IN to my SP?

    Thanks!

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    The more efficient and elegant solution is to use bind variables. Like you are coding it right now, you are causing Oracle to trash the shared pool each time you execute this query. Every time you send a statement, Oracle must check syntax, semantics, plans, etc.. all of those that can take sometime if you are sending a different statement each time, incurring this in a hard parse. When you use bind variables, Oracle already has this statement in the shared pool, and it doesn't have to make the checks it did when the statement was first parsed. It will only replace those variable predicates with the new ones you are parsing, incurring this in a soft parse. The key is to: parse one, execute many.

  12. #12
    Join Date
    Jan 2004
    Posts
    492
    One suggestion I have to make your code more logical to others (in case another developer needs to take over your code) is to put the where and order statement in the actual package.

    Of course I dont have any idea how many where statements you could use, but a better way to do it would be to have the where statement coded in there, and then put in code to determine what the value should be.

    Ex: (taken from your code)
    Code:
    AS
     PROCEDURE getsuperbatches
     (
    
      P_AREA_IN   IN  VARCHAR2,
      SRT_COL    IN  VARCHAR2,
      PGTO  IN  NUMBER,
      PGFROM  IN  NUMBER,
      RC1                     IN  OUT  REF_CUR
     )
     AS
     BEGIN
    
      OPEN RC1 FOR '
    select t.* from
     ( select t.*, rownum rn from
        (select to_char(owr.dCreated,''mm/dd/yy'') as dCreated_sa, 
        owr.id, owr.dCreated,
        owr.cWhoCreated, 
        owr.cComment, 
        owr.dCompleted, 
        owr.cWhoCompleted, 
        owr.cBillSys, 
        bs.cDesc FROM 
        tbl_superbatch owr INNER JOIN
        tbl_owr_BillingSystem bs on owr.cbillsys = bs.cid 
        where owc.area = p_area_in
        order by srt_col
     ) t
        where rownum <= ||PgTo||
      ) t
      where rn >= ||PgFrom||';
    END;
    END;
    Now this assumes you will always have a "static" where clause, and just change the value of owc.area. You could also define different where clauses based on parameters within the code. This is the key here - Not a good idea to be passing entire where statements to the procedure like that. So heres another example for what its worth.

    EX2
    Code:
    PROCEDURE getsuperbatches
     (
    
      P_AREA_IN   IN  VARCHAR2,
      P_SRT_COL    IN  VARCHAR2,
      PGTO  IN  NUMBER,
      PGFROM  IN  NUMBER,
      RC1                     IN  OUT  REF_CUR
     )
     AS
    v_where    varchar2(1000);
    v_order_by varchar2(1000);
     BEGIN
    
    -- Handle the where clause based on P_AREA_IN
    
    IF P_AREA_IN = 'VALUE A' then
    v_where = ' WHERE ocw.area = 'A';
    
    ELSIF P_AREA_IN = 'VALUE B' then
    v_where = ' WHERE table1.col2 = 'B';
    
    ELSE -- Handle all other cases then
    v_where = ' WHERE 1 = 1 ';
    
    END IF;
    
    -- Handle order by clause
    IF P_SRT_COL = 'ID' then
    v_order_by = ' Order by ID ';
    
    ELSIF P_SRT_COL = 'CREATED_BY' then
    v_order_by = ' Order by CREATED_BY ' ;
    
    ELSE -- Handle default case
    v_order_by = ' Order by 1 ' -- or whatever you want to default to
    
      OPEN RC1 FOR '
    select t.* from
     ( select t.*, rownum rn from
        (select to_char(owr.dCreated,''mm/dd/yy'') as dCreated_sa, 
        owr.id, owr.dCreated,
        owr.cWhoCreated, 
        owr.cComment, 
        owr.dCompleted, 
        owr.cWhoCompleted, 
        owr.cBillSys, 
        bs.cDesc FROM 
        tbl_superbatch owr INNER JOIN
        tbl_owr_BillingSystem bs on owr.cbillsys = bs.cid 
        ||v_where
        ||v_order_by
     ) t
        where rownum <= ||PgTo||
      ) t
      where rn >= ||PgFrom||';
    END;
    END;
    DISCLAIMER : I know this last method does work with ref cursors. I cannot guarantee it will work out of the box, but I have done it with using variable statements like so.

    Just some ideas - make of it what you will
    Last edited by ss659; 10-01-04 at 10:35.
    Oracle OCPI (Certified Practicing Idiot)

  13. #13
    Join Date
    Dec 2003
    Posts
    148
    SS - I was actually debating doing that, although I feel like there was some factor preventing me from doing so (or at least making it too difficult due to time constraints) However, I will set that up when this program gets re-written (I feel like that will happen very soon - this program was not written recently at all)

    I do have a question for all though - I realize now Im going to have to pass a value back to my ASP - not a problem, but I do have a question regarding it. Basically, I need to see if there is a next record. Simply put; I'm paging 15 rows at a time (this is working well, and speedy now thank you everyone) Okay thats no problem...However, certain queries will have less than 15 results, and certain queries will have considerably more. I need to pass a value back to ASP to see if the next record exists so I can make a "Next" button appear, if "the next record does not exist" then the "next" button will be grayed out....So simply, what is the best/easiest/most efficient way for me to check if the next record exists within the query?

    I can grasp this logically, but the subquery I'm using coupled with the use of the ref cursor completley throws off what little I know about PL/SQL syntax
    :-/

    Thank you for your help in advance!

  14. #14
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565

    Exclamation

    It is dangerous to accept arbitrary SQL strings in a dynamic procedure, unless you are very sure that you can trust the source.

    For example,
    Code:
    SQL> CREATE OR REPLACE PROCEDURE get_employees
      2     ( p_order_by VARCHAR2
      3     , p_results_out OUT SYS_REFCURSOR )
      4  AS
      5  BEGIN
      6     OPEN p_results_out FOR '
      7             SELECT e.ename, d.dname
      8             FROM   emp e, dept d
      9             WHERE  d.deptno = e.deptno
     10             AND    d.dname = ''SALES'' '
     11             || p_order_by;
     12* END;
    SQL> /
    
    Procedure created.
    
    SQL> exec get_employees('ORDER BY sal', :results)
    
    PL/SQL procedure successfully completed.
    
    
    ENAME      DNAME
    ---------- --------------
    JAMES      SALES
    WARD       SALES
    MARTIN     SALES
    TURNER     SALES
    ALLEN      SALES
    BLAKE      SALES
    
    6 rows selected.
    So far so good. But what if I call the procedure like this:
    Code:
    SQL> EXEC get_employees -
    > ('AND 1=2 UNION SELECT grantee, granted_role FROM dba_role_privs', :results)
    
    PL/SQL procedure successfully completed.
    
    
    ENAME                          DNAME
    ------------------------------ ------------------------------
    DBA                            DELETE_CATALOG_ROLE
    DBA                            EXECUTE_CATALOG_ROLE
    DBA                            EXP_FULL_DATABASE
    DBA                            GATHER_SYSTEM_STATISTICS
    DBA                            IMP_FULL_DATABASE
    DBA                            SELECT_CATALOG_ROLE
    DBSNMP                         CONNECT
    DEVELOPER                      AQ_ADMINISTRATOR_ROLE
    DEVELOPER                      AQ_USER_ROLE
    DEVELOPER                      EXECUTE_CATALOG_ROLE
    DEVELOPER                      GATHER_SYSTEM_STATISTICS
    DEVELOPER                      HS_ADMIN_ROLE
    DEVELOPER                      OEM_MONITOR
    DEVELOPER                      SELECT_CATALOG_ROLE
    EXECUTE_CATALOG_ROLE           HS_ADMIN_ROLE
    EXP_FULL_DATABASE              EXECUTE_CATALOG_ROLE
    EXP_FULL_DATABASE              SELECT_CATALOG_ROLE
    IMP_FULL_DATABASE              EXECUTE_CATALOG_ROLE
    IMP_FULL_DATABASE              SELECT_CATALOG_ROLE
    JAVASYSPRIV                    JAVAUSERPRIV
    OEM_MONITOR                    CONNECT
    OEM_MONITOR                    RESOURCE
    OUTLN                          CONNECT
    OUTLN                          RESOURCE
    SCOTT                          CONNECT
    SCOTT                          RESOURCE
    SELECT_CATALOG_ROLE            HS_ADMIN_ROLE
    SYS                            AQ_ADMINISTRATOR_ROLE
    SYS                            AQ_USER_ROLE
    SYS                            CONNECT
    SYS                            DBA
    SYS                            DELETE_CATALOG_ROLE
    SYS                            EJBCLIENT
    SYS                            EXECUTE_CATALOG_ROLE
    SYS                            EXP_FULL_DATABASE
    SYS                            GATHER_SYSTEM_STATISTICS
    SYS                            HS_ADMIN_ROLE
    SYS                            IMP_FULL_DATABASE
    SYS                            JAVADEBUGPRIV
    SYS                            JAVAIDPRIV
    SYS                            JAVASYSPRIV
    SYS                            JAVAUSERPRIV
    SYS                            LOGSTDBY_ADMINISTRATOR
    SYS                            OEM_MONITOR
    SYS                            RECOVERY_CATALOG_OWNER
    SYS                            RESOURCE
    SYS                            SELECT_CATALOG_ROLE
    SYSTEM                         AQ_ADMINISTRATOR_ROLE
    SYSTEM                         DBA
    SYSTEM                         DEVELOPER
    WILLIAMR                       DBA
    WILLIAMR                       DEVELOPER
    
    52 rows selected.
    
    SQL>

  15. #15
    Join Date
    Dec 2003
    Posts
    148
    Fortunately, this is intranet based so I am not too concerned about security in this regard (at the moment)

    Currently, I'm still trying to figure out how to/what is the best way to return a value from my stored proc to inform me if there are more results....Really, I can just run the query I have in the package, and then run the query again adding 1 to the PgTo value. If a record exsits, I'll just pass a value out of the package say, 1..if not pass a 0.

    So far I have gotten this far, but it does not work...

    Code:
     AS
    
    
            PROCEDURE GETREQSTEST
            (
      
                  WH                               IN              VARCHAR2,
                  SRT                              IN              VARCHAR2,
                  PgTo                             IN              NUMBER,
                  PgFrom                           IN              NUMBER,
                  RC1                              IN  OUT         REF_CUR,
                  MrRecords                        OUT             VARCHAR2
            )
            AS
            BEGIN
     
                    OPEN RC1 FOR 'select t.* from
    ( select t.*, rownum rn from
     ( select ID, 
     CREQID, 
     CREQNAME, 
     CREQEMAIL, 
     CREGION, 
     CMARKET, 
     CCHANNEL,
     CBILLSYS,
     CACCOUNT,
     CMOBILE, 
     CPRICEPLAN, 
     CPROMOCODE, 
     CDISCOUNTCODE, 
     CFEATURECODE, 
     DBACKDATE,
     IREASON, 
     CREASON, 
     DSUBMIT, 
     DSUBMITTIME,
     DAPPRREJ, 
     DAPPRREJTIME,
     CAPPRNAME,
     CAPPR2ID,
     IAPPRSTATUS,
     IAPPR2STATUS,
     DAPPR2REJ, 
     DAPPR2REJTIME,
     CISGID,
     IRESUBMIT, 
     DISGPROCESSED, 
     IISGREASON, 
     CAPPRCOMMENTS,
     IBATCH, 
     IFINRVWREQUIRED,
     IFINSTATUS,DFINANCE,
     CFEATURECODE2,
     CFEATURECODE3,
     CFEATURECODE4,
     CFEATURECODE5,            
     CBILLACCT, 
     CBGSA
      from tbl_OpenWindowRequests  '||wh||' '||srt||'
        ) t
        where rownum <= '||PgTo||'  
      ) t
     where rn >= '||PgFrom||'';  
    
      
    Select count (*) as Cnt from (
    select t.* from
    ( select t.*, rownum rn from
     ( select ID, 
     CREQID, 
     CREQNAME, 
     CREQEMAIL, 
     CREGION, 
     CMARKET, 
     CCHANNEL,
     CBILLSYS,
     CACCOUNT,
     CMOBILE, 
     CPRICEPLAN, 
     CPROMOCODE, 
     CDISCOUNTCODE, 
     CFEATURECODE, 
     DBACKDATE,
     IREASON, 
     CREASON, 
     DSUBMIT, 
     DSUBMITTIME,
     DAPPRREJ, 
     DAPPRREJTIME,
     CAPPRNAME,
     CAPPR2ID,
     IAPPRSTATUS,
     IAPPR2STATUS,
     DAPPR2REJ, 
     DAPPR2REJTIME,
     CISGID,
     IRESUBMIT, 
     DISGPROCESSED, 
     IISGREASON, 
     CAPPRCOMMENTS,
     IBATCH, 
     IFINRVWREQUIRED,
     IFINSTATUS,DFINANCE,
     CFEATURECODE2,
     CFEATURECODE3,
     CFEATURECODE4,
     CFEATURECODE5,            
     CBILLACCT, 
     CBGSA
      from tbl_OpenWindowRequests  '||wh||' '||srt||'
        ) t
        where rownum <= '||PgTo||' + 1  
      ) t
     where rn >= '||PgFrom||' 
    
    );
    Returning Cnt Into MrRecords;
     
    END;
    
    END;
    Could anyone help me on this?

Posting Permissions

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