Sunday, February 19, 2012

ORA-01733 error on View insert

I came across an interesting PeopleSoft error when trying to use a custom Component Interface I created. I was using the CI in an ExcelToCi, in CREATE mode. Everything seemed to be working fine until I submitted the data. All the rows submitted went to error, giving the "ORA-01733:virtual column not allowed here" SQL error.

The error was happening when the CI was trying to insert new rows into a custom View I created on the Component. The View was built using the %SelectAll() meta-sql. When I resolved the SQL to take a better look, I noticed there were date fields. Since the meta-sql resolves these date fields with to_char() functions, the insert was throwing the error.

I changed the View SQL to list all the fields manually and removed the meta-sql. Once this was done, and I re-created the view, the ExcelToCi worked just fine. All the data rows were submitted successfully.