Re: designer webserver library (wsgl?) problems SOLVED
From: Frank <frankbo_at_interaccess.nl>
Date: Thu, 12 Oct 2000 16:13:19 +0200
Message-ID: <8s4gui$53j$1_at_porthos.nl.uu.net>
As for the last option: this would mean wsgl, wsglm, wsgjsl and cdsaper.
Date: Thu, 12 Oct 2000 16:13:19 +0200
Message-ID: <8s4gui$53j$1_at_porthos.nl.uu.net>
The problem is in the fact, that CountHits (in package wsgl) is called without owner. You can resolve this in three ways:
- create public synonyms for the tables of bob - create private synonyms in wsgl_public for tables in bob - install wsgl in bob
As for the last option: this would mean wsgl, wsglm, wsgjsl and cdsaper.
As the CountHits is *not* being called in V6 code, but generated, this resolved the problem, too (see previously posted code snippet).
I would opt for the last option - all OWA and HT* packages come in a seperate owner (SYS (!!)) with iAS anyway.
-- Frank frank <fbortel_at_home.nl> schreef in berichtnieuws 39E36876.82EC73A1_at_home.nl...Received on Thu Oct 12 2000 - 16:13:19 CEST
> Well, I see the same over here now, and all I can say for now
> is that it is gone with V6 WSG.
> It's been a while since I've been digging in the generated code, but
> i'll see what I can come up with - could take some time, as I can only do
this
> at home in the evening, all else permitting.
> Rest of comments inline:
>
> Verna Legaspi wrote:
>
> > I've checked - all the tables being accessed are already owned by bob.
> > Therefore, I cannot grant bob access to his own stuff.
> >
> > Also... what do you mean "invokers' stuff?" Invoker meaning schema/user
who
> > executed the procedure? (In my case, this would be bob)
> >
> > Just to make sure I explained correctly - the bob schema has all the
tables.
> > The wsgl_public schema just has the wsgl packages such as wsgl, wsglm,
and
> > wsgjsl. Where it seems to be failing is in this code:
> > -----------------
> > function QueryHits(
> > P_GLOS_CATEGORY1 in varchar2,
> > P_GLOS_CATEGORY2 in varchar2,
> > P_GLOS_CATEGORY3 in varchar2,
> > P_GLOS_OWNER in varchar2) return number is
> > begin
> > if not BuildSQL(P_GLOS_CATEGORY1,
> > P_GLOS_CATEGORY2,
> > P_GLOS_CATEGORY3,
> > P_GLOS_OWNER) then
> > return -1;
>
> This is what returns the -1...
>
> >
> > end if;
> > return WSGL.CountHits(ZONE_SQL);
> > exception
> > when others then
> > WSGL.DisplayMessage(WSGL.MESS_EXCEPTION, SQLERRM, ''||' :
'||'',
> > BODY_ATTRIBUTES,
> > 'GLOSMAIN2$GLOSTRM.QueryHits');
> > return -1;
> > end;
> > -----------------
> > We get that "-1" instead of an actual hit count.
> >
> > Skimming through the code - all the tables are owned by bob, so it can't
be
> > a no SELECT priv issue, can it?
> >
> > Sorry for throwing all that code at you. I'm just not good enough of a
> > debugger to narrow it down further.
>
> V6 generated code looks like:
> function QueryHits(
> P_COMPANY_IDENTIFIER in varchar2,
> P_NAME2 in varchar2,
> P_CONTACT_NAME8 in varchar2,
> P_SUPPLIER_NUMBER10 in varchar2,
> P_APPROVED12 in varchar2) return number is
> I_QUERY varchar2(32767) := '';
> I_CURSOR integer;
> I_VOID integer;
> I_FROM_POS integer := 0;
> I_COUNT number(10);
> begin
>
> if not BuildSQL(P_COMPANY_IDENTIFIER,
> P_NAME2,
> P_CONTACT_NAME8,
> P_SUPPLIER_NUMBER10,
> P_APPROVED12) then
> return -1;
> end if;
>
> if not PreQuery(P_COMPANY_IDENTIFIER,
> P_NAME2,
> P_CONTACT_NAME8,
> P_SUPPLIER_NUMBER10,
> P_APPROVED12) then
> WSGL.DisplayMessage(WSGL.MESS_ERROR, cg$errors.GetErrors,
> 'Record and Maintain Suppliers'||' :
> '||'Suppliers', DEF_BODY_ATTRIBUTES);
> return -1;
> end if;
>
> I_FROM_POS := instr(upper(ZONE_SQL), ' FROM ');
>
> if I_FROM_POS = 0 then
> return -1;
> end if;
>
> I_QUERY := 'SELECT count(*)' ||
> substr(ZONE_SQL, I_FROM_POS);
>
> I_CURSOR := dbms_sql.open_cursor;
> dbms_sql.parse(I_CURSOR, I_QUERY, dbms_sql.v7);
> dbms_sql.define_column(I_CURSOR, 1, I_COUNT);
> I_VOID := dbms_sql.execute(I_CURSOR);
> I_VOID := dbms_sql.fetch_rows(I_CURSOR);
> dbms_sql.column_value(I_CURSOR, 1, I_COUNT);
> dbms_sql.close_cursor(I_CURSOR);
>
> return I_COUNT;
>
> exception
> when others then
> WSGL.DisplayMessage(WSGL.MESS_EXCEPTION, SQLERRM, 'Record and
Maintain
> Suppliers'||' : '||'Suppliers',
> DEF_BODY_ATTRIBUTES,
'supplier$co.QueryHits');
> return -1;
> end;
>
>