Home » SQL & PL/SQL » SQL & PL/SQL » how to create view dynamically (10g)
how to create view dynamically [message #331988] Mon, 07 July 2008 02:46 Go to next message
masterborj
Messages: 21
Registered: October 2007
Location: Philippines
Junior Member

how could i create a view dynamically by using a trigger.
i try to do it inside the trigger "key-next-item" but it give me an error "encountered the symbol "CREATE" when expecting......."
--my code--
CREATE OR REPLACE FORCE VIEW "MASTERBORJ"."JVDETAIL" ("JV_NO", "OR_NO", "ACCOUNT_CODE", "OR_AMOUNT", "LASTUSER") AS
select jv_no,or_no, account_code, or_amount, lastuser
from jv_agt_dtl
union
select jv_no,or_no, accnt_code, or_amount, lastuser
from jv_appl_dtl
union
select jv_no,or_no, account_code, or_amount, lastuser
from jv_contract_dtl
union
select jv_no,or_no, account_code, or_amount, lastuser
from jv_loan_dtl;






experts help please....
regards
master_borj
Re: how to create view dynamically [message #331990 is a reply to message #331988] Mon, 07 July 2008 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You should read guidelines and format your post
2/ I bet "union" should be replaced by "union all"
3/ I don't think any reason to create a view on the fly above all in a trigger. It is like creating new code in your application on the fly, do you do this?

Regards
Michel
Re: how to create view dynamically [message #331991 is a reply to message #331990] Mon, 07 July 2008 03:05 Go to previous messageGo to next message
masterborj
Messages: 21
Registered: October 2007
Location: Philippines
Junior Member

what do you mean when you say "fly"
Re: how to create view dynamically [message #331992 is a reply to message #331991] Mon, 07 July 2008 03:20 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
"fly" means at RunTime.

Why you need to create view inside a trigger.
What will you achieve via doing this.

Regards,
Rajat
Re: how to create view dynamically [message #331999 is a reply to message #331992] Mon, 07 July 2008 03:48 Go to previous messageGo to next message
masterborj
Messages: 21
Registered: October 2007
Location: Philippines
Junior Member

my purpose is to combine the 4 tables in the view then filter it depending on the inquired JV_NO...thou it may look like there are a lot of codings, i see it will work faster than connecting to the four tables directly (beginner's point of view).

by the way,any suggestions?

I'm actually using developer 2k connecting oracle 10g database and windows XP operating system


regards
master_borj
Re: how to create view dynamically [message #332002 is a reply to message #331999] Mon, 07 July 2008 03:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First of all, you should make clear that you are referring to Forms-trigger, not database triggers.
Second: why do you think you need to create the view from within the Form? Why not create it now, in sqlplus and use it in the form?
Re: how to create view dynamically [message #332009 is a reply to message #331999] Mon, 07 July 2008 04:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If your application would benefit from this view, then get it created once on the server, by your DBAs, or your server side developers. That way everyone will know about it, and it will be included in your source control and releases.


Alternatively, you can just use a FROM CLAUSE query on one of your data blocks in Forms, and include the text of the view query in there - that way you can select the data that the view would return without needing to create the view.
Re: how to create view dynamically [message #332019 is a reply to message #332009] Mon, 07 July 2008 04:56 Go to previous message
masterborj
Messages: 21
Registered: October 2007
Location: Philippines
Junior Member

thanks a lot guys for giving me an expert opinion...
your suggestions were really helpful. you really did a great job.
until next time


regards
master_borj
Previous Topic: Need a query for updating a table with 1 billion records
Next Topic: LOV/ Foreign values
Goto Forum:
  


Current Time: Sat Dec 10 18:55:58 CST 2016

Total time taken to generate the page: 0.12394 seconds