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.

Comments

Post a Comment

Popular posts from this blog

String Manipulation in PeopleCode

Here are a few examples of string manipulation in PeopleCode. I will try to update this post with more examples if I think of more situations. Number to String This will convert a number variable to a string. This may be helpful with specific formatting or string concatenation.  For more details check out this entry in  PeopleBooks . Substring This will take a substring of a larger string.
Read more

"You are not authorized for this page" error on Worklist link

Today I was playing around with a new PeopleSoft Approval Workflow setup, and was testing Worklist notifications.  The Worklist entries were generating just fine for all users. However, some users were not able to successfully click on the transaction link. When clicking on the link, they were receiving an error message "You are not authorized for this page" .  I confirmed that the user did have access to the transaction Page and Component by navigating to it through the Portal menu. I found that the issue was the user needed access to: Menu: EOAW_APPROVAL_WORKFLOW Component: EOAW_REDIRECT Page: EOAW_REDIRECT I created a new Permission List with this access and gave it to a Role assigned to the users. This fixed the issue.
Read more

PeopleSoft Expense Report : Status stuck in "Submission in Progress"

Today I returned to the office after being at Oracle OpenWorld all last week.  After I caught up on emails, a co-worker came to me with an issue the Training team was seeing in our Training environment.  This environment gets refreshed every weekend, so there a normally very few issues. However, the past few weeks they were having issues submitting Expense report sheets for approval.  When the user would submit, the status would change to "Submission in Process" and wouldn't move forward in the approval process.  The sheet would show no approvers and the expected approvers had no new work in their worklist. They all assumed some AWE or Security configuration was missing or messed up.  This didn't make sense to me, since the environment was being refreshed weekly and working fine up to this point.  That being said, I still looked there for a solution, as I had no other ideas.  Everything seemed to look OK, matching our production environment for the most part.  I the
Read more