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
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)