I had similar requirement earlier but now i have been given a clear approach that needs to be implemented. I need to write a procedure(preferably)/function, which would on basis of app_id passed as parameter, change the column headers for corresponding values pivoted in the dynamic view. ***The entire sample data and structure is available here:*** https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=63c08123fbf2fb13de81df0bff360c0b The table with column headers is DATA_HEADER. The table with value is DATA_VALUE. The column headers and values need to be pivoted in order. Both tables have app_id on common. So for app_id=1, labels from DATA_HEADER would be used against values in DATA_VALUE. However, the values change on basis of PID only, so they would remain constant, only headers would change per app id. When app_id is passed in proc/funct, the expected view should be: So basically, the headers change for each app_id, and **the max number of column headers will be 20**. So the number or name shall vary as in the table DATA_HEADER. The values are uniquely identified on the basis of pid. The order of column headers would be as per the seq column in DATA_HEADER. Similarly the order of values would be as per seq column in data value, so the sequence must be followed and pivoted accordingly. P.S. Application at the end is Oracle apex from where the proc/function would be called. Oracle Version: 12.1

We have provided sql tuning set as input to the analysis task of performance analyzer

variable l_task_id VARCHAR2(64);

exec :l_task_id:=dbms_sqlpa.create_analysis_task(
     sqlset_name => ''
    , sqlset_owner=>''
    ,task_name => '');

Now i want to list all the tasks associated with the sql tuning set. I have tried below ways but they are not working

select parameter_name,parameter_value 
from dba_advisor_exec_parameters 
where task_name='' and parameter_name='SQLSET_NAME';

But the result is showing "NOT USED" for column parameter_value |parameter_name | parameter_value| |-------------------- | ------------------| |SQLSET_NAME UNUSED||

consider this working pl/sql block: declare o_result boolean; io_error varchar2(30000); tab1 someTable1; tab2 someTable2; begin tab1(1).col1 := 'str'; tab1(1).col2 := 1; tab2(1).col1 := 'str'; tab2(1).col2 := 0.5; tab2(1).col3 := 6; o_result := some_function(io_error, 2,'str', 5, to_date('01-JAN-23','DD-MON-YY'), to_date('31-DEC-23','DD-MON-YY'), 4, 'A', tab1, tab2, 'str', 'B', 'str'); end; how to get the explain plan for pl/sql function calls where its inputs are dates and custom types?

Greeting, I have a beginner experience with programming in Oracle PL/SQL. The problem I am trying to solve is that in a hypothetical situation, I want to compare records in the same table based on the arbitrary columns called effdt(effective date) and position_nbr(position number). They would be based on new and old records. If the the new effdt field is greater than or equal to(>=) the old effdt based on the old and new position_nbr being equal, I want to delete the old effdt record from the table. Below is the small procedure I have developed to execute. DECLARE table_cnt_001 NUMBER; CURSOR ITR1 IS (SELECT * FROM TABLE1 O INNER JOIN TABLE1 N ON N.POSITION_NBR = O.POSITION_NBR WHERE N.EFFDT >= O.EFFDT); BEGIN SELECT COUNT(*) INTO effdt_cnt_001 FROM TABLE1; --Checking if table is empty IF table_cnt_001 !=0 THEN FOR IT IN ITR1 LOOP --BEGIN IF (N.EFFDT >= O.EFFDT) AND (N.POSITION_NBR = O.POSITION_NBR) THEN DELETE FROM TABLE1 O WHERE EXISTS(SELECT O.EFFDT FROM TABLE1 O); END IF; COMMIT; --END; END LOOP; END IF; END; I have gotten the error identifier 'N.EFFDT' must be declared. As far as I am concerned, I am refencing the cursor in the for loop and I am not sure why the if statement requests for this column to be declared. Please let me know what could be wrong with this logic or if there is a better way to solve this problem. Thanks in advance.

**This question concerns Oracle PL/SQL (Oracle version 12c/19c) and Oracle SQL Developer (version 20.4).** I have a procedure which calls a dynamically generated SELECT statement and save results into sys_refcursor. I would like to print content of the refcursor within the procedure and show it in ouput window of Oracle SQL Developer. So far, I was able to only return the refcursor from procedure through OUT parameter and bind variable, and then print it. My current code of the procedure looks like this:

create or replace procedure cursor_show (rc_out OUT sys_refcursor) is
    v_sql           varchar2(1000);      
    v_rc            sys_refcursor;
begin           
    --other statements generating SELECT 
    v_sql := 'select ...'; --my SELECT
    
    open v_rc for v_sql; --get data 
    rc_out := v_rc; --return refcursor
end;

To print results, I need to call these statements:

var x refcursor;
execute cursor_show (:x);
print x;

I would like to encapsulate print into procedure cursor_show to get something like this:

create or replace procedure cursor_show is
    v_sql           varchar2(1000);      
    v_rc            sys_refcursor;
begin            
    --other statements generating SELECT 
    v_sql := 'select ...'; --my SELECT
    
    open v_rc for v_sql; --get data 

    print v_rc; --print data
end;

After that, I would be able to call the procedure and print the refcursor content with one-row statement execute cursor_show;. However, once I tried to compile such procedure, I received this error message:

Error(51,11): PLS-00103: Encountered the symbol "V_RC" when 
expecting one of the following: := . ( @ % ; 
The symbol ":=" was substituted for "V_RC" to continue.

Could you please advise how to call print statement within body of the procedure?

Can anyone explain why a utPLSQL unit test works when calling a stored procedure with the following code in: RETURN CASE WHEN pi_is_scientific_notation = 'T' THEN v_display_format || 'EEEE' ELSE v_display_format END; However, when I use a constant instead of 'T' the unit test fails RETURN CASE WHEN pi_is_scientific_notation = OraISSConstantsPkg.c_true THEN v_display_format || 'EEEE' ELSE v_display_format END; The errors being received were ORA-20218: SQL exception thrown when fetching data from cursor: ORA-01861: literal does not match format string However, the stack trace did not point to the line above. When I addded output statements in the stored procedure it did not show that the stored procedure had reached this code.

***Environment:*** - ***DB Version:*** Oracle Database 12cR2 EE on Exadata - ***RAC:*** True - ***Problem:*** ORA-06508 on procedure. > One of our users had a DBA role privilege, we don't want such a big > privilege to be defined anymore. When we revoke the DBA role privilege > from this user, all procedures, packages and triggers became invalid. > This was something we expected at that moment and we accepted it as > normal. When we compiled all of them, there was no problem and we > reassigned the DBA role privilege to this user in order to postpone > this work until later. > > The problem starts from this point. We get an error when one of the > procedures wants to run. But the error is not constantly getting, for > example it was working correctly for the last 26 hours, but then it > got the error. When we drop / create or compile, the problem is > solved. However, the strange part is that when this problem occurs, > the procedure and dependencies all seem to be valid. The error is as > follows: > > > > > > > > Error raised in: PROCEDURE_NAME at line xxxx - ORA-06508: PL/SQL: > could not find program unit being called ***Line-xxxx:*** EXCEPTION WHEN OTHERS THEN ROLLBACK; R_CODE:= 400; R_MESSAGE:='Error raised in: '|| $$plsql_unit ||' at line ' || $$plsql_line || ' - '||sqlerrm; I could not find a suitable solution for this scenario on Oracle Support or on dba.stackexchange and I am still researching. Anyone have a good idea? Best Regards,

We're maintaining a "hairy" vendor-supplied application, which sometimes forgets to drop its temporary tables. Years ago we've created a simple script to get rid of all of them after the application-instance is shut down:

BEGIN
                for rec in (select table_name
                    from user_tables
                    where table_name like 'R%#%#%_TMP')
                loop
                        execute immediate 'drop table '|| rec.table_name;
                end loop;
        END;

Lately this script started throwing errors occasionally, one of these two: * ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired * ORA-00942: table or view does not exist It seems, something is competing with the script trying to drop the same tables, but there shouldn't be anything -- the application in question is already shut down. Could it be, the application's own request to drop these tables is, actually, _deferred_ by Oracle -- and is acted upon later, when our own script tries to delete them too? Currently using Oracle Database 19c Enterprise Edition 19.0.0.0.0, with master->slave replication enabled.

My goal: Set PID_FM in every row to a randomly picked value out of all the values matched by the subquery. However the FM_ID is only picked once, and therefore the whole column has the same value. I would want it to be different for every row. How can I achieve this? I am working with PL/SQL (ORACLE) UPDATE Distribution SET PID_FM = (SELECT * FROM(SELECT * FROM ( SELECT Fm_Id FROM FAMILIES_IN_NEED UNION SELECT PID FROM HELPED_FAMILY ) T ORDER BY DBMS_RANDOM.value) WHERE rownum=1 AND pid_fm is not NULL)

I have a list of emails of staff members and would like to: 1. insert them into multiple rows on the same column (single column only for emails) 2. the same question above but would like to replace them with old ones.

Hi i'm currently working with oracle that will give me my requered output. I able to make the query but when i'm putting it in stored procedure it gives me error of Error(11,1): PL/SQL: SQL Statement ignored and Error(15,20): PL/SQL: ORA-00936: missing expression here's my query not in procedure: with t1 as ( select * from vw_break_time_doublebreak /* where clause that satisfies your query */ where date_time >= date '2020-01-01' and date_time = date in_date and date_time < date in_date_end <------ error here ) ,t2 as ( select shift, trunc(min(min(date_time)) over (), 'mm') mn, last_day(max(max(date_time)) over ()) mx from t1 group by shift ) ,t3 as ( select shift, mn + level - 1 dateshift from t2 connect by mn + level - 1 <= mx and prior shift = shift and prior sys_guid() is not null ) SELECT t3.shift, t3.dateshift AS date_time, count (CNT) AS COUNT FROM t3 left join vw_break_time_doublebreak db on (t3.shift = db.shift and t3.dateshift = TRUNC (db.date_time)) GROUP BY t3.shift, dateshift ORDER BY t3.shift, dateshift; END; sorry i'm just kinda new in oracle and don't know what i'm doing wrong. hope someone help me out with this.

My procedure giving numeric overflow error: > ERROR at line 1: > ORA-01426: numeric overflow > ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196 because it exceeds pls_integer maximum value +2,147,483,647. How to overcome this error? I have to purge 2234202989 rows. So I am getting error. >sp_log_message (LOG_ERROR, 'sp_instrumentationdata', lsCurrentTable, 'Exception', NULL, SQLERRM); Here it is showing .. This table having starttime and endtime two .. date fileds .. I am using endtime .. I find out max.endtime .. min.endtime and max-min(endtime). I got 144 days .. I have given package.sp_prc(keep days); exec hdm_cleanup_util.sp_instrumentationdata(143). Means i am going to purge Just one day data. whenever I execute the above command .. immediately I am getting ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196 .. error.

I have a table "active_t" in a schema called "test", having the following structure:

instance      active
          IN          1
          SI          0

The ask is to loop through this table and for each active schema(i.e., having active=1), the code should query another table "company_t" having the following structure:

code    deldate
 1234     "some date"
 9876     null
 7321     null
 9097     "some date"

The code needs to retrieve those company codes from company_t which have deldate as null. Once picked up, the code should further run an insert statement and insert some data in the another table "rec_t" in the test schema. The basic code that I tried was this:

SET SERVEROUTPUT ON;
declare
v_sql varchar2(200);
v_cntry active_t.instancename%type;
cursor c1 is
select instancename, active from active_t
where active = '1';
begin
for rec in c1
loop
execute IMMEDIATE 'select code from '||rec.instancename||'.company_t where deldate is null' into v_sql;
dbms_output.put_line(v_sql);
end loop;
end;
/

But I get an error as below:

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 10
ORA-06512: at line 10
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

Please help what needs to be fixed. If this simple code works then I will try to implement the insert statement. Thanks in advance.

I am trying to create a Trigger that will fire when a new student is being added to the table. It should update the a table called: MAJOR, by incrementing its MCOUNT domain by one. CREATE TABLE STUDENT( SID CHAR(7), SName VARCHAR2(20), SMajor CHAR(3) CHECK (SMajor in ('CSC', 'MIS', 'TDC')), CONSTRAINT PK_STUDENT PRIMARY KEY (SID) ); CREATE TABLE MAJOR( MName CHAR(3) PRIMARY KEY, MCount NUMBER(3) ); INSERT INTO MAJOR VALUES ('CSC', 0); INSERT INTO MAJOR VALUES ('MIS', 0); INSERT INTO MAJOR VALUES ('TDC', 0); SELECT * FROM MAJOR; COMMIT; My trigger: CREATE OR REPLACE TRIGGER addingS AFTER INSERT ON STUDENT FOR EACH ROW DECLARE counter NUMBER (3); BEGIN DBMS_OUTPUT.PUT_LINE('Adding student.'); counter := counter + 1; UPDATE MAJOR SET MCOUNT = counter WHERE MCOUNT = 0; END; / It is not updating, now my MCOUNT's value is empty, why is that?

I have two schemes with multiple tables. The queries in packages heavily reference schema names. Is it possible to remove schema names from the queries and oracle to identify the tables based on roles or privileges?? Query - Select * from schema1.app_table1 ap1 inner join schema2.app_table2 ap2 The query would be run from oracle user app_user who has access to both the tables - app_table1 and app_table2. It is possible to run the query without schema prefixes or synonyms just by modifying the user app_user. Thanks for your help.

I'm kinda new(like 1 year of experience) in PL/SQL and i have to make a kind of tricky trigger, and i want to check if my syntax is correct, mainly the selects, inserts and variable value store i make . So, i want to have a trigger that, when i insert a new row on a table, i have to make a select to that table to store inserted values into 2 variables i created: create or replace trigger schema.trg_CP after insert on "schema"."tdlrp" referencing old as old for each row --------------------------------------------------------------------------------------------------------- declare v_fkidnc schema.tdlrp.fkidnc%type; v_errortype schema.tdlrp.xerrort%type; v_fkerrorID schema.tepm.ferror%type; v_linerror number; v_pr schema.tpm.pipm%type v_pkdocid_r schema.tddr.pidr%type --------------------------------------------------------------------------------------------------------- begin if inserting then select fkidnc, xerrort into v_fkidnc, v_errortype from schema.tdlrp; -- This is correctly made, right? After that i have to make some if-elsif validations: 1. if v_fkidnc = 1 and if v_errortype = 1 i have to make a set of selects and inserts in a row 2. if v_fkidnc = 1 and if v_errortype = 2 i have to make another set of anothers selects and inserts, but the logic is all the same to every if-elsif validation:

create or replace trigger schema.trg_CP 
          after insert on "schema"."tdlrp"
          referencing old as old 
          for each row
          
          ---------------------------------------------------------------------------------------------------------      
          declare 
          v_fkidnc     					 schema.tdlrp.fkidnc%type;   
          v_errortype                    schema.tdlrp.xerrort%type;
          v_fkerrorID                    schema.tepm.ferror%type;
          v_linerror                     number;
          v_pr                     		 schema.tpm.pipm%type
          v_pkdocid_r                    schema.tddr.pidr%type
          ---------------------------------------------------------------------------------------------------------
          
          begin
            if inserting then
              select fkidnc, xerrort
                into v_fkidnc, v_errortype
                from schema.tdlrp;
              --
              if v_fkidnc = 1 then
                if v_errortype = 1 then
                  select ferror, fipcm
                  into v_fkerrorID, v_linerror
                  from schema.tepm;
                  
                  select pipm 
                  into v_pr
                  from schema.tpm
                  where fipcm := v_linerror;
                  
                  insert into schema.tddr(pidr, fipc, xuser, datea, fiptm) 
                  values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);
                  
                  select pidr
                  into v_pkdocid_r
                  from tddr 
                  where fiptm := v_pr; 
                  
                  insert into schema.tere(pidr, ferror, fidre, xuser, datea, fipcm) 
                  values(schema.seq_tere.nextval, v_fkerrorID, v_pkdocid_r, 'A', SYSTIMESTAMP, v_linerror);
                  
                elsif v_errortype = 2 then
                 select...
EXCEPTION
  WHEN OTHERS THEN
  RAISE;
  
END trg_CP;

For example: on these 2 select i made: if v_errortype = 1 then select ferror, fipcm into v_fkerrorID, v_linerror from schema.tepm; select pipm into v_pr from schema.tpm where fipcm := v_linerror; i'm assigning values to v_fkerrorID and v_linerror (first select). On the second select i want a condition where fipcm is equal to the variable value i stored on the first select: select ferror, **fipcm** into v_fkerrorID, **v_linerror** After that i want to make insert to another tables with the values i stored in above queries: 1. Before the second insert i have to make a select to the table where i first inserted to get the v_pkdocid_r value for the second insert

insert into schema.tddr(pidr, fipc, xuser, datea, fiptm) 
    values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);
    
    select pidr
    into **v_pkdocid_r**
    from tddr 
    where fiptm := v_pr;
    ----------------------------
    insert into schema.tere(pidr, ferror, fidre, xuser, datea, fipcm) 
                  values(schema.seq_tere.nextval, v_fkerrorID, **v_pkdocid_r**, 'A', SYSTIMESTAMP, v_linerror);

Am i doing it correctly? Edit 16/09/2022 With all the suggestions, i changed my trigger to this: create or replace trigger schema.trg_CP after insert on tdlrp referencing old as old new as new for each row --------------------------------------------------------------------------------------------------------- declare v_fkerrorID schema.tepm.ferror%type; v_linerror number; v_pr schema.tpm.pipm%type; v_pkdocid_r schema.tddr.pidr%type; --------------------------------------------------------------------------------------------------------- -- begin -- if :new.fkidnc = 1 then if :new.errortype = 1 then select ferror, fipcm into v_fkerrorID, v_linerror from schema.tepm; --this select only inserts one row to each variable select pipm into v_pr from schema.tpm where fipcm = v_linerror; insert into schema.tddr(pidr, fipc, xuser, datea, fiptm) values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr); select pidr into v_pkdocid_r from tddr where fiptm = v_pr; insert into schema.tere(pidr, ferror, fidre, xuser, datea, fipcm) values(schema.seq_tere.nextval, v_fkerrorID, v_pkdocid_r, 'A', SYSTIMESTAMP, v_linerror); end if; end if; -- END trg_CP; / But i'm getting: PL/SQL: ORA-00984 on: select pipm into v_pr from schema.tpm where fipcm = v_linerror; the problem is not on the select statement, i removed both selects after both ifs, and now it tells me that the collumn is not allowed on the if statement... if i remove both ifs, turns me back to error on select statements The error is when i put this insert: insert into schema.tddr(pidr, fipc, xuser, datea, fiptm) values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr); after this select: select pipm into v_pr from schema.tpm where fipcm = v_linerror;

In SQL Server I can do something like: DECLARE @VAR VARCHAR(100); SELECT @VAR = SELECT * FROM SYS.DATABASES EXECUTE(@VAR) How can I execute a string that I've created inside a variable in Oracle? This is the query: DECLARE BUSCACOLUNA VARCHAR(2000); BEGIN BUSCACOLUNA:= 'SELECT '' SELECT '' || LTRIM(listagg ( '' , '' || ''"'' || T1.COLUMN_NAME || ''"'' || '' AS '' ||''"''|| T2.DESCRICAO ||''"'' ) within group ( order by t1.column_name ),'' , '') || '' FROM TABELA_ENTRADA '' FROM ALL_TAB_COLUMNS T1 INNER JOIN TABBASE T2 ON T1.COLUMN_NAME=T2.NO_COL_TABBASE WHERE T1.TABLE_NAME=''TABELA_ENTRADA'' AND T2.CD_CONTEUDO_ARQUIVO= ''X'''; END; The result of that dynamic SQL is something like: SELECT C1 AS B1, C2 AS B2...FROM TABLE And I would like to execute this SELECT by executing the VARIABLE. I tried with a loop, with EXECUTE IMMEDIATE but the query only returns: > anonymous block completed So I can understand it's working. If it was an UPDATE or INSERT, it would work, But I would like the return of that.

We have some pretty high-load service in oltp database built using Oracle's procedures, functions, views and etc.
Whenever we want to update our code base, it's usually throws an error like 1. ORA-06550 (invalidations because of changing depending objects) 2. ORA-04068 3. ORA-04065 (not executed, altered or dropped stored procedure)
because parent procedure that triggers view execution that triggers functions execution is called by outside API at this right moment when we are updating functions, views, procedures.
We usually update it via CREATE OR REPLACE and it takes about 0.3 - 0.4 seconds. Then we recompile invalid objects if there any.
#### Problem ### Because we can't have any downtime during daytime, we usually proceed with this operations at night, which is very inconvenient. Oracle promises eliminating downtime using their EBR, but aren't for example functions EDITIONABLE by default ? And is recompiling them using simple DDL any different from using EBR? Will changing editions using EBR throw ORA-04065 while objects being called? Or any better solutions for Oracle 19c version? There isn't much about EBR besides some marketing crap from Oracle

I'm using UTL_MATCH's Jaro Winkler similarity function and it seems to be performing well. However, I would like to adjust the prefix scale according to the situation. Is this possible? Is it possible to see what the default prefix scale is? I could not find any documentation on this, but it seems that in order to be a J-W distance, it must use a prefix scale.

Hi I have a table in Oracle APEX to store documents as well as their revisions:

CREATE TABLE M_DOCUMENTS
(
    DOCUMENT_GUID                   VARCHAR2(38) DEFAULT ON NULL sys_guid(),

    DOCUMENT_TITLE                  VARCHAR2(100) NOT NULL,
    DOCUMENT_DESC                   VARCHAR2(2000),
    LATEST_REVISION_GUID            VARCHAR2(38),
    
    constraint M_DOCUMENTS_PK primary key (DOCUMENT_GUID)
)
;

and a table to store revisions of said documents (as well as fk constraint on M_DOCUMENTS for the subject of this question):

CREATE TABLE M_DOCUMENT_REVISIONS
(
    REVISION_GUID           VARCHAR2(38) DEFAULT ON NULL sys_guid(), 

    DOCUMENT_GUID           VARCHAR2(38) NOT NULL,
    REVISION_TITLE          VARCHAR2(100) NOT NULL,
    REVISION_DESC           VARCHAR2(2000),
    REVISION_DOWNLOAD_LINK  VARCHAR2(2048),
    
    constraint M_DOCUMENT_REVISIONS_PK primary key (REVISION_GUID),
    constraint M_DOCUMENT_REVISIONS_FK_DOCUMENT_GUID foreign key (DOCUMENT_GUID) references M_DOCUMENTS (DOCUMENT_GUID)
)
;

ALTER TABLE M_DOCUMENTS ADD CONSTRAINT M_DOCUMENTS_FK_LATEST_REVISION_GUID foreign key (LATEST_REVISION_GUID) references M_DOCUMENT_REVISIONS (REVISION_GUID);

CREATE OR REPLACE EDITIONABLE TRIGGER AI_M_DOCUMENT_REVISIONS
AFTER INSERT ON M_DOCUMENT_REVISIONS
FOR EACH ROW
DECLARE
BEGIN            
    UPDATE M_DOCUMENTS
    SET LATEST_REVISION_GUID = :new.revision_guid
    WHERE DOCUMENT_GUID = :new.DOCUMENT_GUID;
END
;
/

ALTER TRIGGER AI_M_DOCUMENT_REVISIONS ENABLE
;

As you can see the latest revision is stored as a field in the M_DOCUMENTS table and the M_DOCUMENT_REVISIONS table has an after insert trigger that updates the parent documents latest revision. When thinking of handling deletes of revisions and realizing I need to create a before delete trigger to remove the latest revision relation if it exists and update the latest revision again, I began to wonder if this is the right way to approach this. Is there any better ways of doing this? Some things off the top of my head are: 1. Manually calculating the latest revision from its create date (not shown in the tables above) 2. Materialized view of 1. 3. Creating another table to store latest revision for each document and adding a unique constraint on it on the parent guid. 4. Letting the revision itself store whether or not its the latest revision. 5. Letting the revision store what revision order it is (the number revision it is. e.g. 1st revision, 2nd revision) My ideal method should be quick (in run time and in development time), have an easy way to delete revisions and retrieve the new revision, and should also be good to use with Oracle APEX development.