Wanted to know some info about top 1 query in cursors

Wanted to know some info about top 1 query in cursors

Post by sudhaka » Sun, 31 Dec 1899 09:00:00



Hi all,
I want to know what are the limitations of cursors in terms
of sql
I am getting error when for the following procedures......
1.
create or replace procedure test is
Cursor Cur_SecRes is
select secresdesc,secrescode from
(SELECT SECRESDESC,secrescode FROM SECONDARYRES group BY
secrescode asc, SECRESDESC desc)
WHERE ROWNUM=1;
Begin
for x in Cur_SecRes
loop
dbms_output.put_line(x.secrescode | | ' ,' | |
x.secresdesc);
end loop;
End;
The above procedure is opens the top q query, which is
required for imporving performance. The query is running in
Sql Plus, but if I include the query in PL sql block it is
giving error.
Is there any way out for running the above query, If any
body knows then please let me know at

Similarly the second procedure is giving error......
2.
create or replace procedure test is
Cursor Cur_SecRes is
SELECT SECRESDESC,secrescode FROM SECONDARYRES order BY
SECRESDESC desc,secrescode
union all
SELECT priRESDESC,prirescode FROM priONDARYRES order BY
SECRESDESC desc,secrescode;
begin
for x in Cur_SecRes
loop
dbms_output.put_line(x.secrescode | | ' ,' | |
x.secresdesc);
end loop;
end;

overall I need to know the limitations of cursor or pl sql
block,

Thanks in advance.....

Regards,
sudhakar

* Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping.  Smart is Beautiful

 
 
 

Wanted to know some info about top 1 query in cursors

Post by Martin Haltmaye » Sun, 31 Dec 1899 09:00:00


Hi sudhakar,

this is two plain syntax errors:

group by does not have asc or desc (these are options for ordering)

order by in a union may only appear at the end of the statement.

Try your statements first outside of a cursor environment. It is much
easier to debug.

Martin


> Hi all,
> I want to know what are the limitations of cursors in terms
> of sql
> I am getting error when for the following procedures......
> 1.
> create or replace procedure test is
> Cursor Cur_SecRes is
> select secresdesc,secrescode from
> (SELECT SECRESDESC,secrescode FROM SECONDARYRES group BY
> secrescode asc, SECRESDESC desc)
> WHERE ROWNUM=1;
> Begin
> for x in Cur_SecRes
> loop
> dbms_output.put_line(x.secrescode | | ' ,' | |
> x.secresdesc);
> end loop;
> End;
> The above procedure is opens the top q query, which is
> required for imporving performance. The query is running in
> Sql Plus, but if I include the query in PL sql block it is
> giving error.
> Is there any way out for running the above query, If any
> body knows then please let me know at

> Similarly the second procedure is giving error......
> 2.
> create or replace procedure test is
> Cursor Cur_SecRes is
> SELECT SECRESDESC,secrescode FROM SECONDARYRES order BY
> SECRESDESC desc,secrescode
> union all
> SELECT priRESDESC,prirescode FROM priONDARYRES order BY
> SECRESDESC desc,secrescode;
> begin
> for x in Cur_SecRes
> loop
> dbms_output.put_line(x.secrescode | | ' ,' | |
> x.secresdesc);
> end loop;
> end;

> overall I need to know the limitations of cursor or pl sql
> block,

> Thanks in advance.....

> Regards,
> sudhakar

> * Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping.  Smart is Beautiful


 
 
 

Wanted to know some info about top 1 query in cursors

Post by sudhaka » Sun, 31 Dec 1899 09:00:00


Thanks martin,
It was mistake from my side. Union all query cursor was
working fine. In the query below I wrongly mentioned as
group by instead of order by Group by is working fine. I
want order by clause.
Martin if u know then please mail me.

I want to know what are the limitations of cursors in terms
of sql
I am getting error when for the following procedures......
1.
create or replace procedure test is
Cursor Cur_SecRes is
select secresdesc,secrescode from
(SELECT SECRESDESC,secrescode FROM SECONDARYRES order BY
secrescode asc, SECRESDESC desc)
WHERE ROWNUM=1;
Begin
for x in Cur_SecRes
loop
dbms_output.put_line(x.secrescode | | ' ,' | |
x.secresdesc);
end loop;
End;
The above procedure is opens the top q query, which is
required for imporving performance. The query is running in
Sql Plus, but if I include the query in PL sql block it is
giving error.
Is there any way out for running the above query, If any
body knows then please let me know at

overall I need to know the limitations of cursor or pl sql
block,

Thanks once again,

Regards,
sudhakar

* Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping.  Smart is Beautiful

 
 
 

Wanted to know some info about top 1 query in cursors

Post by Martin Haltmaye » Sun, 31 Dec 1899 09:00:00


Hi Sudhakar,

I tried the following:

SQL>
SQL> create table secondaryres (
  2          secrescode varchar2 (5)
  3          , secresdesc varchar2 (10)
  4  )
  5  /

Table created.

 real: 30
SQL>
SQL> create or replace procedure test is
  2  Cursor Cur_SecRes is
  3  select secresdesc,secrescode from
  4  (SELECT SECRESDESC,secrescode FROM SECONDARYRES order BY
  5  secrescode asc, SECRESDESC desc)
  6  WHERE ROWNUM=1;
  7  Begin
  8  for x in Cur_SecRes
  9  loop
 10  dbms_output.put_line(x.secrescode || ' ,' ||
 11  x.secresdesc);
 12  end loop;
 13  End;
 14  /

Warning: Procedure created with compilation errors.

 real: 110
SQL> show errors
Errors for PROCEDURE TEST:
4/49     PLS-00103: Encountered the symbol "ORDER" when expecting one of
         the following:

         <a double-quoted delimited-identifier> group having intersect
         minus partition start union where connect
         The symbol ")" was substituted for "ORDER" to continue.

5/32     PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         , ; for

SQL>
SQL> create or replace view v_secondaryres as
  2  select secresdesc,secrescode from
  3  (SELECT SECRESDESC,secrescode FROM SECONDARYRES order BY
  4  secrescode asc, SECRESDESC desc)
  5  WHERE ROWNUM=1;

View created.

 real: 30
SQL>
SQL> create or replace procedure test is
  2  Cursor Cur_SecRes is
  3  select secresdesc,secrescode from
  4  v_secondaryres;
  5  Begin
  6  for x in Cur_SecRes
  7  loop
  8  dbms_output.put_line(x.secrescode || ' ,' ||
  9  x.secresdesc);
 10  end loop;
 11  End;
 12  /

Procedure created.

 real: 90
SQL> show errors
No errors.
SQL>
SQL> spool off

So this looks like a bug, not a limitation. Any hints? I ran against
this problem as well and I worked around it by using a view.

Martin


> Thanks martin,
> It was mistake from my side. Union all query cursor was
> working fine. In the query below I wrongly mentioned as
> group by instead of order by Group by is working fine. I
> want order by clause.
> Martin if u know then please mail me.

> I want to know what are the limitations of cursors in terms
> of sql
> I am getting error when for the following procedures......
> 1.
> create or replace procedure test is
> Cursor Cur_SecRes is
> select secresdesc,secrescode from
> (SELECT SECRESDESC,secrescode FROM SECONDARYRES order BY
> secrescode asc, SECRESDESC desc)
> WHERE ROWNUM=1;
> Begin
> for x in Cur_SecRes
> loop
> dbms_output.put_line(x.secrescode | | ' ,' | |
> x.secresdesc);
> end loop;
> End;
> The above procedure is opens the top q query, which is
> required for imporving performance. The query is running in
> Sql Plus, but if I include the query in PL sql block it is
> giving error.
> Is there any way out for running the above query, If any
> body knows then please let me know at

> overall I need to know the limitations of cursor or pl sql
> block,

> Thanks once again,

> Regards,
> sudhakar

> * Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping.  Smart is Beautiful

 
 
 

1. Wanted some info for top1 query -using cursors

Hi all,
I want to know what are the limitations of cursors in terms
of sql
I am getting error when for the following procedures......
1.
create or replace procedure test is
Cursor Cur_SecRes is
select secresdesc,secrescode from
(SELECT SECRESDESC,secrescode FROM SECONDARYRES order BY
secrescode asc, SECRESDESC desc)
WHERE ROWNUM=1;
Begin
for x in Cur_SecRes
loop
dbms_output.put_line(x.secrescode | | ' ,' | |
x.secresdesc);
end loop;
End;
The above procedure is opens the top q query, which is
required for imporving performance. The query is running in
Sql Plus, but if I include the query in PL sql block it is
giving error.
Is there any way out for running the above query, If any
body knows then please let me know at

overall I need to know the limitations of cursor or pl sql
block,

Thanks in advance.....

Regards,
sudhakar

* Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping.  Smart is Beautiful

2. sql mail and smtp mail server

3. how to make os(Linux) know db2's user and a query start/top

4. FoxPro 2.5 DOS: fichier d'aide, help file

5. Info wanted on Info-Lease/Decision Systems Inc

6. Changing index numbers?

7. Top 10 things a DBA should know

8. Best Cigars

9. Iterative SQL query - correlated query using TOP

10. wanted top programmer

11. FL - Oracle DBA's Wanted -- Top$$$!!

12. WA-SEATTLE-154912--ORACLE-Management-ORACLE DBA WANTED for top 100 company- CAZ