Re: order by problem with stored procedure

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Wed, 31 May 2006 16:14:00 +0200
Message-ID: <447da5f6$0$4511$9b4e6d93@newsread2.arcor-online.net>

Alen Cappelletti schrieb:

>>Which user/schema is invoking this code?

>
> An user with EXECUTE PROCEDURE PERMISSION.
>
> I've simplify but alvays same error.
> 1) Can be return client cursor?
>
>
>
> var_Query := 'SELECT '||
> 'u.*, '||
> '14 TotRs '||
> 'FROM UTENTI u '||
> 'WHERE u.STATO = 1 '||
> 'ORDER BY '||:strOrdinamento;
> --Rs principale
> OPEN OBJ_CUR_Utenti FOR var_Query USING strOrdinamento;
>

maxim_at_ORA102> CREATE TABLE utenti

   2 AS
   3 SELECT ROWNUM AS intdars,MOD(ROWNUM,2) AS stato    4 FROM user_objects
   5 WHERE ROWNUM <=5;

Table created.

maxim_at_ORA102>
maxim_at_ORA102> CREATE OR REPLACE PACKAGE Pkg_Utenti IS

   2 TYPE Cur_Utenti IS REF CURSOR;
   3 PROCEDURE Utenti
   4 (

   5      Strcookiemd5   IN VARCHAR2,
   6      Intdars        IN NUMBER,
   7      Intars         IN NUMBER,
   8      Strordinamento IN VARCHAR2,
   9      Obj_Cur_Utenti OUT Cur_Utenti

  10 );
  11 END;
  12 /

Package created.

maxim_at_ORA102>
maxim_at_ORA102> CREATE OR REPLACE PACKAGE BODY Pkg_Utenti AS

   2 PROCEDURE Utenti
   3 (

   4      Strcookiemd5   IN VARCHAR2,
   5      Intdars        IN NUMBER,
   6      Intars         IN NUMBER,
   7      Strordinamento IN VARCHAR2,
   8      Obj_Cur_Utenti OUT Cur_Utenti
   9    ) AS
  10      Var_Totalers NUMBER;
  11      Var_Query    VARCHAR2(2000);

  12 BEGIN
  13

  14      --Tot record
  15      SELECT Nvl(COUNT(*), 0)
  16      INTO   Var_Totalers
  17      FROM   Utenti
  18      WHERE  (Stato = 1);
  19
  20      Var_Query := 'SELECT * FROM ( ' || 'SELECT ' || 'u.*, ' || 
Var_Totalers ||
  21                   ' TotRs, ' || 'rownum SeqNum ' || 'FROM UTENTI u ' ||
  22                   'WHERE u.STATO = 1 ' || 'ORDER BY ' || 
Strordinamento || ' )' ||
  23                   'WHERE SeqNum > ' || Intdars || ' AND SeqNum <= ' ||
  24                   Intars;
  25      --Rs principale
  26      OPEN Obj_Cur_Utenti FOR Var_Query
  27      ;

  28 END Utenti;
  29 END Pkg_Utenti;
  30 /

Package body created.

maxim_at_ORA102> var r refcursor
maxim_at_ORA102> exec Pkg_Utenti.utenti('Bla',1,5,'1',:r)

PL/SQL procedure successfully completed.

maxim_at_ORA102> print r

    INTDARS STATO TOTRS SEQNUM ---------- ---------- ---------- ----------

          3          1          3          2
          5          1          3          3

Notice, the "USING" isn't needed here
Best regards

Maxim Received on Wed May 31 2006 - 09:14:00 CDT