| Best Data Dictionary table to use in a trigger [message #38289] |
Mon, 08 April 2002 07:27  |
Anthony
Messages: 48
Registered: September 2000
|
Member |
|
|
Hi All,
I need to write a trigger that will take the contents of a table and write it to another table. The trouble the data is going to a field of length 2000.
The information from the table could be larger than this, so I would like to create some sort of cursor which would access a Data Dictionary table which held the names of the columns of my table (about 100), get the information from the field and add it to the field if there is enough space.
My question is, what is the best table to use for this type of query? are there any tables which shouldnīt be used etc. etc. Anything that I should be aware of before doing this?
Thanks in advance
Anthony
|
|
|
|
| Re: Best Data Dictionary table to use in a trigger [message #38297 is a reply to message #38289] |
Mon, 08 April 2002 12:19  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
user_tab_columns will give you the column lengths. If you are going to concatenate strings with "dates" and "numbers" then all then the data dictionary isn't goin to help.
In pl/sql, create your new temp string as varchar2(32767) or something else big, then just test the length of the temp result. e.g.
v_str varchar2(4000) := char_field||' '||date_field||' '||number_field;
if length(v_str) > 2000 then
-- trim it down and put some ... on the end
v_str := substr(v_str, 1, 1997)||'...';
-- OR raise an exception
raise_application_error();
|
|
|
|
| Re: Best Data Dictionary table to use in a trigger [message #38301 is a reply to message #38297] |
Tue, 09 April 2002 00:48 |
Anthony
Messages: 48
Registered: September 2000
|
Member |
|
|
Thanks, I think that table is pretty much the one I need.
My next question is, Can I access the values from user_tab_columns.column_name (using some sort of indirection) and use them in the trigger so that I could have the line.
v_long_str = v_long_str||'#'||:new.user_tab_columns.column_name ?
I am using Oracle 8.0.6. (use DBMS_SQL package here for indirection?)
Thinking about it, I donīt see how I can, but it would be great if I could, as the table in question has over a 100 fields and will very probably have more added in the future.
TIA
|
|
|
|
| DMBS_SQL package in a trigger [message #38302 is a reply to message #38297] |
Tue, 09 April 2002 01:29 |
Anthony
Messages: 48
Registered: September 2000
|
Member |
|
|
Iīve been thinking that in this case I would need to execute the PL/SQL statement using DBMS_SQL package
v_str := v_str||'#'||new.||user_tab_columns.column_name
But this is not a simple sql select, update or delete. I would like to execute the above statement, be able to get the value of v_str returned so I can examine it.
Can this be done? What is the order of statements I need to call in the package, something like
open cursor
bind_variable
execute
variable_value
close_cursor
I imagine.
TIA
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
