Re: What's wrong with this sp?

Thanks, Jim, for throwing one stone (that almost hits two birds). I'll find an Oracle book tomorrow to check on ref cursor. As for the "distinct" function, I've commented it out and selects just one column now, however, it has not reduced any processing time, strange.

DL

"Jim Kennedy" <kennedy-family_at_home.com> wrote in message news:<_LTH7.17193$XJ4.11730249_at_news1.sttln1.wa.home.com>...
> You need to read the documentation on ref cursor. You are not in sqlserver.
> Also if you can avoid distinct in the query - it makes the rdbms compare
> every row with every other row - very ineffecient.
> Jim
> "DL" <donlcs_at_yahoo.com> wrote in message
> news:604bb90c.0111120929.5612d6f8_at_posting.google.com...
> > Create procedure first_Oracle_sp(v_owner IN varchar2, v_tn IN varchar2)
> > AS
> > Begin
> > select distinct cols.table_name as tblname,c.nullable, cols.column_name
> > from dba_constraints cons, dba_cons_columns cols, dba_tab_columns c
> > where cons.table_name = cols.table_name
> > and cons.owner = v_owner
> > and c.table_name = v_tn
> > and cols.column_name = c.column_name
> > and cons.constraint_name = cols.constraint_name
> > and cons.constraint_type = 'R'
> > and cons.table_name = v_tn
> > End;
> >
> > Thanks.
Received on Tue Nov 13 2001 - 05:27:55 CET