ORDS, SODA & JSON in the Database

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!

Export and Import ORDS

Mohammad ShaddadMay 20 2020 — edited May 20 2020

Hi All

Kindly, I have get and post ORDS web service, is there is a SQL script to extract all web service source code (DML) so I can extract and execute in another database?

DB:11.2.0.4.0

I found this simple SQL but it's showing only the trigger procedure not the full code which I used when I create the web service (ex: enable_schema, define_module, define_template, define_handler and define_parameter)

SELECT NAME
      ,Uri_Prefix
      ,Uri_Template
      ,Method
      ,Source_Type
      ,SOURCE
  FROM User_Ords_Modules   a
      ,User_Ords_Templates b
      ,User_Ords_Handlers  c
 WHERE a.Id = b.Module_Id
   AND b.Id = c.Template_Id

Thanks ...

This post has been answered by thatJeffSmith-Oracle on May 20 2020

Jump to Answer

598338

Sorry, deleted !

jeneesh

SQL> select dt,cnt,sum(cnt1) over(order by dt) cnt1
  2  from(
  3  select dt,count(distinct visitor) cnt,sum(flg) cnt1
  4  from
  5   (select dt,visitor,(select decode(count(*),0,1,0)
  6                           from test
  7                           where rowid < t.rowid
  8                           and visitor = t.visitor) flg
  9   from test t)
 10  group by dt);

DT                CNT       CNT1
---------- ---------- ----------
10-01               3          3
10-02               3          4
10-03               3          5
10-04               2          5

Message was edited by: 
        jeneesh
Wrong...

450441

WITH t1 AS (select trunc(date) date, count(distinct visitor) cnt
 from sample
group by trunc(date))
, t2 as (select trunc(t1.date) date, count(distinct t2.visitor) cnt
from sample t2, t1
 where trunc(t2.date) <= trunc(t1.date)
 group by trunc(t1.date))
select t1.date, t1.cnt visitors_per_day, t2.cnt cum_visitors
from t1, t2
where t1.date = t2.date

Message was edited by:
Dave Hemming
This was much more complicated than I first thought it would be...

611012

thanks a lot for quick response

Aketi Jyuuzou

create table visitTable(day1,visitor) as
select '10-01','A' from dual union all
select '10-01','A' from dual union all
select '10-01','B' from dual union all
select '10-01','B' from dual union all
select '10-01','C' from dual union all
select '10-01','C' from dual union all
select '10-02','A' from dual union all
select '10-02','C' from dual union all
select '10-02','C' from dual union all
select '10-02','D' from dual union all
select '10-02','D' from dual union all
select '10-03','B' from dual union all
select '10-03','B' from dual union all
select '10-03','B' from dual union all
select '10-03','A' from dual union all
select '10-03','A' from dual union all
select '10-03','F' from dual union all
select '10-04','A' from dual union all
select '10-04','A' from dual union all
select '10-04','F' from dual;

select day1,count(distinct visitor) as cnt,
sum(sum(willSum)) over(order by day1) as cnt1
from (select day1,visitor,
      case Row_Number() over(partition by visitor order by day1)
           when 1 then 1 else 0 end as willSum
        from visitTable)
group by day1
order by day1;

DAY1   CNT  CNT1
-----  ---  ----
10-01    3     3
10-02    3     4
10-03    3     5
10-04    2     5

select distinct day1,count(distinct visitor) over(partition by day1) as cnt,
sum(willSum) over(order by day1) as cnt1
from (select day1,visitor,
      case Row_Number() over(partition by visitor order by day1)
           when 1 then 1 else 0 end as willSum
        from visitTable)
order by day1;

similar thread
569685

jeneesh

Nicely Done, Aketi Jyuuzou ...

572471

playing with model:

SQL> with t as (
  2  select '10-01' day1,'A' visitor from dual union all
  3  select '10-01','A' from dual union all
  4  select '10-01','B' from dual union all
  5  select '10-01','B' from dual union all
  6  select '10-01','C' from dual union all
  7  select '10-01','C' from dual union all
  8  select '10-02','A' from dual union all
  9  select '10-02','C' from dual union all
 10  select '10-02','C' from dual union all
 11  select '10-02','D' from dual union all
 12  select '10-02','D' from dual union all
 13  select '10-03','B' from dual union all
 14  select '10-03','B' from dual union all
 15  select '10-03','B' from dual union all
 16  select '10-03','A' from dual union all
 17  select '10-03','A' from dual union all
 18  select '10-03','F' from dual union all
 19  select '10-04','A' from dual union all
 20  select '10-04','A' from dual union all
 21  select '10-04','F' from dual)
 22  --
 23  select d,cnt1,cnt2 from t
 24   model
 25    return updated rows
 26    dimension by (day1 d, rownum rn)
 27    measures(visitor, 0 cnt1, 0 cnt2)
 28     (cnt1[for d in (select day1 from t), 0]=count(distinct visitor)[CV(),any],
 29      cnt2[any,0]=count(distinct visitor)[d<=CV(),any])
 30      order by 1
 31  /

D           CNT1       CNT2
----- ---------- ----------
10-01          3          3
10-02          3          4
10-03          3          5
10-04          2          5

SQL>

Message was edited by:
Volder

or a better version with "upsert all":

...
 22  --
 23  select d,cnt1,cnt2 from t
 24   model
 25    return updated rows
 26    dimension by (day1 d, rownum rn)
 27    measures(visitor, 0 cnt1, 0 cnt2)
 28    rules upsert all
 29     (cnt1[any,0]=count(distinct visitor)[CV(),any],
 30      cnt2[any,0]=count(distinct visitor)[d<=CV(),any])
 31      order by 1
 32  /

D           CNT1       CNT2
----- ---------- ----------
10-01          3          3
10-02          3          4
10-03          3          5
10-04          2          5

SQL>

ORDS, SODA & JSON in the Database

Export and Import ORDS

Comments

Post Details