Home » SQL & PL/SQL » SQL & PL/SQL » Create view - execute a function where it inserts a record
Create view - execute a function where it inserts a record [message #239428] Tue, 22 May 2007 07:03 Go to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
I would like a solution for the following:-

Create a view.
Where one of the value is from a function.
The above function does a another function call, where it inserts a record (log), and at the moment the create view fails saying "DDL or TCL are not allowed"....

Anybody has any clue....

If the above message is not clear, let me know and I will provide you with more info.

Please let me know.... how to fix this...
Re: Create view - execute a function where it inserts a record [message #239433 is a reply to message #239428] Tue, 22 May 2007 07:15 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Post the code for the view and function.
Post Oracle version
Format your post in a readable manner
http://www.orafaq.com/forum/t/59964/91729/
Re: Create view - execute a function where it inserts a record [message #239434 is a reply to message #239433] Tue, 22 May 2007 07:22 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Sorry didn't read the post thrue....

You cant do DML inside a query

[Updated on: Tue, 22 May 2007 07:23]

Report message to a moderator

Re: Create view - execute a function where it inserts a record [message #239437 is a reply to message #239434] Tue, 22 May 2007 07:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Although if you put the DML in an autonomous transaction then it might work
Re: Create view - execute a function where it inserts a record [message #239438 is a reply to message #239437] Tue, 22 May 2007 07:32 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hmm true
Just tried it , seems to be working fine
Re: Create view - execute a function where it inserts a record [message #239473 is a reply to message #239428] Tue, 22 May 2007 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is not clear. Please post:
- your Oracle version
- what you have on your screen if this is a problem you already have
- an example of what you want it this something you want to achieve

Regards
Michel
Re: Create view - execute a function where it inserts a record [message #239475 is a reply to message #239428] Tue, 22 May 2007 08:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sarans wrote on Tue, 22 May 2007 14:03
saying "DDL or TCL are not allowed"....

You DO realise that this is an ORACLE forum, not a SQLServer forum, right?
Re: Create view - execute a function where it inserts a record [message #239483 is a reply to message #239434] Tue, 22 May 2007 08:45 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
I will try the autonomous transaction, thanks.
Re: Create view - execute a function where it inserts a record [message #239487 is a reply to message #239473] Tue, 22 May 2007 08:47 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Frank,

Yes thats what the message said, I dont want to replicate/produce the error again, as other guys are working on this view now....

And the oracle version is 10g - 10.1.0.5.0....
Re: Create view - execute a function where it inserts a record [message #239491 is a reply to message #239438] Tue, 22 May 2007 08:53 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Michel

- your Oracle version

oracle version is 10g - 10.1.0.5.0


- what you have on your screen if this is a problem you already have

?????

- an example of what you want it this something you want to achieve

1. We have a function which returns a code.
2. We have created a view using the above function to display that returned code (since we have this convenient function, used it instead writing a new one).
3. The above function has a logging routine which records an insert into the log table about the activities and capture the error accordingly within the log table.

Requirement:-
I want to use the function which returns a code in the view (but couldnt do it because the log function inserts a record, and the view is failing to create).
Re: Create view - execute a function where it inserts a record [message #239492 is a reply to message #239491] Tue, 22 May 2007 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The solution is to use "pragma autonomous_transaction;" in your logging function.

Regards
Michel
Re: Create view - execute a function where it inserts a record [message #239501 is a reply to message #239491] Tue, 22 May 2007 09:12 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Michel

I am trying the autonomous transaction now...

Re: Create view - execute a function where it inserts a record [message #239521 is a reply to message #239491] Tue, 22 May 2007 10:15 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Michel

I have created a procedure with the autonomous transaction, and inturn called the original function hooray it worked fine.....

thanks a lot...

Re: Create view - execute a function where it inserts a record [message #239522 is a reply to message #239434] Tue, 22 May 2007 10:15 Go to previous message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
JRowBottom

I have created a procedure with the autonomous transaction, and inturn called the original function hooray it worked fine.....

thanks a lot...
Previous Topic: Using Date Variable vs. Hardcoding
Next Topic: List of modified tables Modifications
Goto Forum:
  


Current Time: Fri Dec 09 15:17:52 CST 2016

Total time taken to generate the page: 0.25646 seconds