Upload download blob synonym

Jeg ved ikke om du kan bruge nedennævnte. Det er taget fra http://www-rohan.sdsu.edu/doc/oracle/errmsg803/A54625_01/newch2l.htm


ORA-22816: unsupported feature with RETURNING clause

Cause: RETURNING clause is currently not supported for object type columns, LONG columns, remote tables and INSERT with subquery.

Action: Use separate SELECT statement to get the values.

Fandt også denne her:

HTML DB - Version: 1.5.0.00
This problem can occur on any platform.

Errors
ORA-22816 unsupported feature with RETURNING clause

Goal
QUESTION ###
How to create an HTMLDB application which can insert, update and delete records from a remote database table?

A public database link is created on a remote database. Could not insert, update and delete records from an HTMLDB application (Report with Edit option).

Create a table in the database "A" with the syntax

create table test_tab(empno number(2), ename varchar2(10));

Create a database link in database "B" connecting to database "A".

create public database link test_link connect to scott identified by tiger using 'orcl';

Create a database view in database "B" with the syntax

create view test_view as select * from test_tab;

Then create an HTMLDB application having a report with edit option. You can delete the records but can not update or insert records. Thus created an "instead of trigger" to do that.

Create a "Instead Of Trigger" with the syntax

create or replace TRIGGER test_view_INSTEAD_OF_TRG
INSTEAD OF UPDATE or INSERT
ON test_view
REFERENCING OLD AS OLD NEW AS NEW
declare
begin
if updating then
if :new.empno != :old.empno then
raise_application_error(20001,'EMPNO could not be modified');
end if;
if :new.ename != :old.ename then
update test_tab@test_link set ename=:new.ename where empno = :old.empno;
end if;
end if;
if inserting then
insert into test_tab@test_link(empno,ename) values
(:new.empno,:new.ename);
end if;
if deleting then
delete from test_tab@test_link where empno = :old.empno;
end if;
end;

Now, the records can be updated but can not be inserted. It throws the error :


ORA-22816 "unsupported feature with RETURNING clause"
Fix
ANSWER ###

The insert does not work is a Bug. Bug is logged for this issue.

Bug # 3602642 -- ORA-22816 WHILE INSERTING DATA FROM A DATABASE VIEW BASED ON A REMOTE TABLE

Probably this will be fixed in patch 1 for HTML DB.

The workaround is :

1) Edit the Process Row process on the page containing the form.

2) In the Allowed Operations section, uncheck Insert, so that the only checkboxes checked are
Update and Delete.

3) In the Conditional Processing region, specify Condition Type of "Request is Contained
Within Expression 1" and in Expression 1 itself, enter a value of :

DELETE,SAVE

4) Create a new process on the same page, Point of On Submit - After Computations and
Validations, type of PL/SQL anonymous block.

5) The PL/SQL page process should be:

insert into test_view(empno, ename) values(:P8_EMPNO, :P8_ENAME);
commit;

Adjust the item names (P8_EMPNO, P8_ENAME) to correspond to your own page items

6) Step through the rest of the wizard, supplying a success and failure message if you like.

7) On Process Conditions, for When Button Pressed choose the button that corresponds to CREATE.

Tak for hjælpen, men problemet skyldes at der er restriktioner på blob datatypen, man kan ikke sådan uden videre flytte rundt på blob's mellem forskellige databaser. Hvad man kan og ikke kan beskrives her:

http://www.cise.ufl.edu/help/database/oracle-docs/appdev.920/a96591/adl04mng.htm#98328

Hvis der til gengæld er nogen der har forslag til hvordan man gemmer og åbner filer i databasen fra Forms 6i, så er jeg meget interesseret i at vide det (måske er det dårlig skik at stille et nyt spørgsmål i et spørgsmål????).

-> joern_h & pnielsen: Jeg giver gerne point for jeres indsats

Med venlig hilsen
Kristoffer

Jeg har nu implementeret en løsning af dokumenthåndtering. Løsningen indeholder: upload/download af dokumenter til dok. tabel på webserver og til tabel på ikke-webserver. Der er to interfaces: et web og et i Forms 6i. Det har været virkelig besværligt at implementere og har taget lang tid. Derfor vil jeg tilråde alle, der kan komme til at skulle lave noget lignende, til at bogmærke denne side og henvende sig til mig når de får brug for nogle tips.

Mvh.
Kristoffer

ok.. lukker du lige?

luk