Re: What's wrong with this sp?

Actually these are not tables but views. The query should be fast. Also most likely in production this data is cached in memory.
Jim
"DL" <donlcs_at_yahoo.com> wrote in message news:604bb90c.0111140946.4e2abdf5_at_posting.google.com...
> Well, I commented out cols.column_name column by adding "--" before
> it, I bet
> "--" is also a comment sign for Oracle. All right, what I really want
> to do in this case is to improve query efficiency (it involves three
> system tables), and I now found out that none of these tables
> (dba_constraints, dba_cons_columns, dba_tab_columns) have any indexes,
> without index query is sure slow, so, now, the issue is, first to
> check if there's index for each table (for its relevant column), if
> not, create an index for it. What do you think? Thanks.
>
> DL
>
> "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
 news:<1cfI7.22711$XJ4.14082736_at_news1.sttln1.wa.home.com>...
> > I don't have a database to try this on. But possibly the error is at
> > > select c.nullable --, cols.column_name
> >
> > the -- is a comment in pl/sql.
> >
> > before defining the package do
> > set serverout on
> > define the package
> > then
> > show errors
> >
> > It should show the line and column where the problem is occuring.
> > My guess is some sort of syntax error.
> > Jim
> >
> > "DL" <donlcs_at_yahoo.com> wrote in message
> > news:604bb90c.0111131052.73e0fc0_at_posting.google.com...
> > > Hi,
> > >
> > > The doc is very limited in explaining the usage. I grab the concept
> > > and turn it into the following code, running on PL*SQL 8.1.7 received
> > > err:
> > > package created with compilation errors:
> > >
> > > Create or replace package Pak
> > > type PakType IS ref cursor;
> > > Create or replace procedure cardi (ow IN varchar2, tbn IN varchar2,
> > > cnYN OUT Pak.PakType)
> > > IS
> > > Begin
> > > open cnYN for
> > > select 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 = own
> > > and c.table_name=tbn
> > > and cols.column_name = c.column_name
> > > and cons.constraint_name = cols.constraint_name
> > > and cons.constraint_type = 'R'
> > > and cons.table_name =tbn
> > > End
> > >
> > >
> > > Thanks.
> > >
> > > "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
 news:<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 Wed Nov 14 2001 - 19:12:29 CET