Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

exec procedure as parsing schema, not APEX_PUBLIC_USER

WillejaOct 29 2008 — edited Oct 31 2008
We have a package with a procedure:

CREATE OR REPLACE PACKAGE XGN4.xgn_generator$pck
AUTHID DEFINER AS
PROCEDURE cr_tab(tab_id IN NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY XGN4.xgn_generator$pck
PROCEDURE cr_tab(tab_id IN NUMBER)
IS
BEGIN
execute immediate('CREATE TABLE test(......)');
END;
END;

When I call this procedure from apex, it's always executed as 'APEX_PUBLIC_USER', but it should be as the parsing schema 'XGN4'.

I thought 'AUTHID DEFINER' should do the trick (it's standard when you're not using AUTHID), but it looks like it has no effect on APEX...

Anyone?
This post has been answered by 60437 on Oct 29 2008
Jump to Answer

Comments

MichaelS
One way could be
SQL>  select eno, rno, 
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[1]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r0,
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[2]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r1,
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[3]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r2,
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[4]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r3
  from student s

ENO        RNO           R0         R1         R2         R3
---------- ----- ---------- ---------- ---------- ----------
9352       A0000       6712       9478          0          0
15401      A0006      12410          0          0          0
9478       A0000       6712       9352          0          0
6712       A0000       9352       9478          0          0
12410      A0006      15401          0          0          0
9477       A0001          0          0          0          0

6 rows selected.
Girish Sharma

Thanks Sir.

SQL> select eno, rno,
  2        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[1]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r0,
  3        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[2]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r1,
  4        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[3]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r2,
  5        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[4]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r3
  6    from student s
  7  /
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[1]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r0,
                                            *
ERROR at line 2:
ORA-00907: missing right parenthesis

Please guide me; since i am not aware of XML query.

Thanks & Kind Regards
Girish Sharma

MichaelS
must be due to 9.2.0.1.0 not supporting order by in xmlagg.

Another modified version which avoids order by in xmlagg is
SQL>  select t2.column_value.extract('X/text()').getnumberval() eno, 
       rno,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][1]/text()').getnumberval(),0) r0,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][2]/text()').getnumberval(),0) r1,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][3]/text()').getnumberval(),0) r2,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][4]/text()').getnumberval(),0) r3                   
  from (select rno, xmlagg(xmlelement(x,eno)) x 
          from (select rno, eno from student order by eno)
         group by rno) t, 
       table(xmlsequence(t.x.extract('X'))) t2

       ENO RNO           R0         R1         R2         R3
---------- ----- ---------- ---------- ---------- ----------
      6712 A0000       9352       9478          0          0
      9352 A0000       6712       9478          0          0
      9478 A0000       6712       9352          0          0
      9477 A0001          0          0          0          0
     12410 A0006      15401          0          0          0
     15401 A0006      12410          0          0          0

6 rows selected.
Message was edited by:
michaels
Girish Sharma
So many thanks...!
Its all due to your hardworking and experience.

Kind Regards
Girish Sharma
Nicolas Gasparotto

Just an other way, maybe more understandable :

SQL> select a_eno eno,
  2         rno,
  3         nvl(max(decode(rn,1,b_eno)),0) as r0,
  4         nvl(max(decode(rn,2,b_eno)),0) as r1,
  5         nvl(max(decode(rn,3,b_eno)),0) as r2,
  6         nvl(max(decode(rn,4,b_eno)),0) as r3
  7  from   (select a.eno a_eno, a.rno, b.eno b_eno, dense_rank() over (partition by a.rno,a.eno order by b.eno) rn
  8          from   student a, student b
  9          where  a.rno=b.rno
 10          and    a.eno!=b.eno)
 11  group by a_eno,rno
 12  order by rno,eno;

ENO        RNO   R0         R1         R2         R3
---------- ----- ---------- ---------- ---------- ----------
6712       A0000 9352       9478       0          0
9352       A0000 6712       9478       0          0
9478       A0000 6712       9352       0          0
12410      A0006 15401      0          0          0
15401      A0006 12410      0          0          0

SQL>

Nicolas.

Doesn't work, please see next post.
Message was edited by:
N. Gasparotto

Nicolas Gasparotto

Small correction when a eno-rno is alone.
Now that should work :

SQL> select a_eno eno,
  2         rno,
  3         nvl(max(decode(rn,1,b_eno)),0) as r0,
  4         nvl(max(decode(rn,2,b_eno)),0) as r1,
  5         nvl(max(decode(rn,3,b_eno)),0) as r2,
  6         nvl(max(decode(rn,4,b_eno)),0) as r3
  7  from   (select a.eno a_eno, a.rno, b.eno b_eno, decode(a.eno,b.eno,null,row_number() over (partition by a.rno,a.eno order by decode(a.eno,b.eno,null,b.eno))) rn
  8          from   student a, student b
  9          where  a.rno=b.rno)
 10  group by a_eno,rno
 11  order by rno,eno;

ENO        RNO   R0         R1         R2         R3
---------- ----- ---------- ---------- ---------- ----------
6712       A0000 9352       9478       0          0
9352       A0000 6712       9478       0          0
9478       A0000 6712       9352       0          0
9477       A0001 0          0          0          0
12410      A0006 15401      0          0          0
15401      A0006 12410      0          0          0

6 rows selected.

Nicolas.

Message was edited by:
N. Gasparotto

Girish Sharma
Sir,
So many thanks...!

Kind Regards
Girish

Girish Sharma
Nicolas Gasparotto
ok, you see the modifications.

Message was edited by:
N. Gasparotto
Aketi Jyuuzou
612816
This thread deals similar question.
MichaelS
Just an other way, maybe more understandable :
Hopefully following is more understandable and also avoids a self join:
select eno,
       rno, 
       decode (r,1,rn5,2,rn4,3,rn3,4,rn2,5,rn1) r0,
       decode (r,1,rn6,2,rn5,3,rn4,4,rn3,5,rn2) r1,
       decode (r,1,rn7,2,rn6,3,rn5,4,rn4,5,rn3) r2,
       decode (r,1,rn8,2,rn7,3,rn6,4,rn5,5,rn4) r3
  from (select eno, rno,
               row_number()  over (partition by rno order by eno) r,
               lag(eno,4,0)   over (partition by rno order by eno) rn1,
               lag(eno,3,0)   over (partition by rno order by eno) rn2,
               lag(eno,2,0)   over (partition by rno order by eno) rn3,
               lag(eno,1,0)   over (partition by rno order by eno) rn4,
               lead(eno,1,0)  over (partition by rno order by eno) rn5,
               lead(eno,2,0)  over (partition by rno order by eno) rn6,
               lead(eno,3,0)  over (partition by rno order by eno) rn7,
               lead(eno,4,0)  over (partition by rno order by eno) rn8
          from student) s

ENO        RNO   R0         R1         R2         R3        
---------- ----- ---------- ---------- ---------- ----------
6712       A0000 9352       9478       0          0         
9352       A0000 6712       9478       0          0         
9478       A0000 6712       9352       0          0         
9477       A0001 0          0          0          0         
12410      A0006 15401      0          0          0         
15401      A0006 12410      0          0          0         

6 rows selected.
french, currently in the Netherlands
in either case not much luck in uefa cup since yesterday :(

made some minor fixes.

Message was edited by:
michaels
Aketi Jyuuzou

@michaels
your solution is excellent.

My complex solution ;-)

select a.eno,a.rno,
case when b.R0 > 0 then Lead(a.eno,abs(b.R0),0) over(partition by a.rno order by a.eno)
     else Lag(a.eno,abs(b.R0),0) over(partition by a.rno order by a.eno) end as R0,
case when b.R1 > 0 then Lead(a.eno,abs(b.R1),0) over(partition by a.rno order by a.eno)
     else Lag(a.eno,abs(b.R1),0) over(partition by a.rno order by a.eno) end as R1,
case when b.R2 > 0 then Lead(a.eno,abs(b.R2),0) over(partition by a.rno order by a.eno)
     else Lag(a.eno,abs(b.R2),0) over(partition by a.rno order by a.eno) end as R2,
case when b.R3 > 0 then Lead(a.eno,abs(b.R3),0) over(partition by a.rno order by a.eno)
     else Lag(a.eno,abs(b.R3),0) over(partition by a.rno order by a.eno) end as R3
  from (select eno,rno,r0,r1,r2,r3,
        Row_Number() over(partition by rno order by eno) as JoinKey
          from student) a Join
       (select 1 as JoinKey,1 as R0,2 as R1,3 as R2,4 as R3 from dual union all
        select 2,-1, 1, 2, 3 from dual union all
        select 3,-2,-1, 1, 2 from dual union all
        select 4,-3,-2,-1, 1 from dual union all
        select 5,-4,-3,-2,-1 from dual) b
 using(JoinKey)
order by a.eno,a.rno;
  ENO  RNO       R0    R1  R2  R3
-----  -----  -----  ----  --  --
 6712  A0000   9352  9478   0   0
 9352  A0000   6712  9478   0   0
 9477  A0001      0     0   0   0
 9478  A0000   6712  9352   0   0
12410  A0006  15401     0   0   0
15401  A0006  12410     0   0   0
Nicolas Gasparotto
Michaels,
Just an other way, maybe more understandable :
Hopefully following is more understandable and also avoids a self join:
Nice shot !
french, currently in the Netherlands
in either case not much luck in uefa cup since yesterday :(
...unlike Germans... so far.

Nicolas.
1 - 12
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 28 2008
Added on Oct 29 2008
8 comments
786 views