Re: What's wrong with this sp?

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 Tue Nov 13 2001 - 21:15:57 CET