Home » SQL & PL/SQL » SQL & PL/SQL » get the Trigger script from SQL*plus
get the Trigger script from SQL*plus [message #380636] Mon, 12 January 2009 15:52 Go to next message
satishk
Messages: 26
Registered: October 2008
Location: HYD
Junior Member
Hi,

How do we get the Trigger script from SQL*plus 7.6 version ...

I have used the Dba_source, all_triggers ..but i am unable to extract all the code ...

select description,trigger_body from all_triggers where trigger_name = 'x';

can you please let me know .. is there any way to find out the Trigger/Database objects scripts from SQl*plus.....

Thanks,
Pavan Kalyan
Re: get the Trigger script from SQL*plus [message #380648 is a reply to message #380636] Mon, 12 January 2009 19:31 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
hi,

hope following code would help you

Raja>create or replace trigger sample_trigger
  2  before insert on test_table
  3  for each row
  4  begin
  5     null;
  6  end;
  7  /

Trigger created.

Raja>show error;
No errors.
Raja>select text from user_source
  2  where name = 'SAMPLE_TRIGGER';

TEXT
------------------------------------------------------------------
trigger sample_trigger
before insert on test_table
for each row
begin
   null;
end;

6 rows selected.


regards,
Delna
Re: get the Trigger script from SQL*plus [message #380649 is a reply to message #380648] Mon, 12 January 2009 20:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
Your Oracle version is ridiculously old and hasn't been supported for years. You should upgrade. I know the data dictionary views have changed somewhat from version to version, so I am not sure what was available. As Delna demonstrated, it is easier to extract the full trigger code from user_source than user_triggers. Bear in mind that, unless names were enclosed within double quotes upon creation, all objects are created in upper case. Also, in order to guarantee that you get your results in the correct order, you should add an order by clause.

select text 
from   user_source
where  name = upper ('x') 
order  by line;



Re: get the Trigger script from SQL*plus [message #380650 is a reply to message #380636] Mon, 12 January 2009 20:42 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
> SQL*plus 7.6
I doubt that number (7.6) is correct/valid.
IIRC, the terminal release for V7 was V7.3.4.5

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.
Re: get the Trigger script from SQL*plus [message #380655 is a reply to message #380649] Mon, 12 January 2009 22:32 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
right.......

I missed ORDER BY clause.

regards,
Delna
Re: get the Trigger script from SQL*plus [message #380667 is a reply to message #380636] Mon, 12 January 2009 23:37 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but i am unable to extract all the code ...

select description,trigger_body from all_triggers where trigger_name = 'x';

set long 10000

Regards
Michel
Previous Topic: UTL_FILE vs External Tables (merged by bb)
Next Topic: working with images
Goto Forum:
  


Current Time: Mon Dec 05 09:18:22 CST 2016

Total time taken to generate the page: 0.17010 seconds