Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

apex_string.string_to_table giving ORA-00902: invalid datatype

Mr.PeabodyApr 10 2021 — edited Apr 10 2021

I want to convert colon delimited list into separate rows. I am passing col1 VARCHAR2(4000) to apex_string.string_to_table.

select (select column_value from table(apex_string.string_to_table(col1))) from tab1;

getting ORA-00902: invalid datatype.
Thanks!!!

This post has been answered by Hub Tijhuis on Apr 10 2021
Jump to Answer

Comments

21205
Answer
Welcome to the forum!

You can't pass the complete row
you can pass individual column values, so it would be something like
create or replace trigger logTrigger after update on test_table for each row
begin
   logProcedure(:old.col1, :new.col1);
end;
Marked as Answer by herzal · Sep 27 2020
21205
or something like this
create or replace trigger logTrigger after update on test_table for each row
declare
   old_row test_table%rowtype;
   new_row test_table%rowtype;
begin
   old_row.col1 := :old.col1;
   ....
   new_row.col1 := :new.col1;

   logProcedure(old_row, new_row);
end;
herzal
wow, that was quick o_O

thanks for your reply.

I'll explain my problem: we have more than 300 tables and every insert/update/delete should be logged, so I need more than 300 triggers. I want to avoid that the trigger has to be rewritten every time the structure of the table changes. So I thouht I use a trigger and a log-procedure. The only things the trigger does are checking if the log-procedure is up2date, recreating the log-procedure if necessary, and calling the log-procedure with :old and :new as parameters.

But since that doesn't work: any suggestions how to solve that?

thanks for your help =) (sitting here with my stop watch)
21205
Before writing your own implementation, check auditing.


sorry for the delay, but I had to run out do some groceries, feed the cat and debug some applications...
;)

Edited by: Alex Nuijten on Aug 13, 2009 1:03 PM
21205
maybe something like http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm#sthref1769
herzal
thanks your your help, Alex =) but auditing is no option. I think I will end up using a DDL-trigger that recreates the log-triggers and then the log-triggers do the logging.
21205
doing it with DDL triggers might proof quite tricky.
It would be pretty straight forward to write your own generator to create these triggers. Or take a look at codegen, a utility to generate source code.
http://codegen.inside.quest.com/index.jspa
herzal
thanks, I will have a look ;)
1 - 8

Post Details

Added on Apr 10 2021
8 comments
929 views