Re: What's wrong with this sp?

From: Jim Kennedy <kennedy-family_at_home.com>
Date: Tue, 13 Nov 2001 05:15:22 GMT
Message-ID: <K%1I7.19325$XJ4.12861627_at_news1.sttln1.wa.home.com>


The ref cursor documentation is in the Oracle docs you got with the software. (in html). Look in the Application Developers guide PL/SQL. The distinct will have more effect as the number of rows grow.(in the result set)
Jim
"DL" <donlcs_at_yahoo.com> wrote in message news:604bb90c.0111122027.58c30dd6_at_posting.google.com...
> 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 - 06:15:22 CET

Original text of this message