Re: What's wrong with this sp?

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 - 18:46:19 CET