Re: What's wrong with this sp?

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 - 19:52:12 CET