Re: "a cross join" in cursor

ja-a wrote:

> Sorry, my version is Oracle9i
> and it should be:
> DECLARE CURSOR GET_DEP IS
>                  SELECT * FROM (SELECT COLUMN_NAME
>                                 FROM   DBA_TAB_COLUMNS
>                                 WHERE TABLE_NAME='DEPT')
>                                 a cross JOIN  (SELECT DNAME,LOC
>                                              FROM DEPT
>                                              GROUP BY DNAME,LOC)
>                                 ON COLUMN_NAME <> 'DEPTNO';
>          REC_CUR GET_DEP%ROWTYPE;
>
> [I was trying to do any join and check if it works]
>
> thx, ja-a
<snipped>

Lets start with the fact that the keyword DECLARE does not belong in a 
procedure. It is only used in anonymous blocks and triggers. So that 
alone will generate an error.

Second your join to dba_tab_columns seems to do absolutely nothing. 
What, as Sybrand has asked, are you trying to do.

Third your declaration of a cursor and record are not an optimal way to 
create and utilize a cursor in a stored procedure. I would suggest you 
look toward using an implicit cursor with a cursor FOR loop.

But most critically, what is it you are trying to accomplish with 
dba_tab_columns? And why dba_ rather than user_ or all_?

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
[EMAIL PROTECTED]
(replace 'x' with a 'u' to reply)