ASP.Net Maker 2020.0.5
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0
Anyone else using Oracle that can test adding a Custom Field to an Oracle table? I get the following error even if I leave the Expression blank.
Failed to add Custom Field.
ORA-00942: table or view does not exist
Works fine on a SQL Server table.
Thanks,
Craig
Show your table schema and the expression you are trying to add as custom field.
Here is the driving table:
SQL> desc WDTGUID
Name Type Nullable Default Comments
WDTGUID_GUID RAW(16)
WDTGUID_PERSON_PRINCIPAL_NAME VARCHAR2(60) Y
WDTGUID_BANNER_PIDM NUMBER(8) Y
WDTGUID_PEOPLESOFT_EMPLID VARCHAR2(11) Y
WDTGUID_CREATE_DATE DATE Y
WDTGUID_CREATE_USER VARCHAR2(30) Y
WDTGUID_ACTIVITY_DATE DATE Y
WDTGUID_ACTIVITY_USER VARCHAR2(30) Y
WDTGUID_INITIAL_PASSWORD VARCHAR2(30) Y
WDTGUID_NEW_USER_BATCH_ID NUMBER(8) Y
WDTGUID_TOTO_ADDRESS VARCHAR2(60) Y
WDTGUID_UPDATE_PS_EMAIL VARCHAR2(1) Y ‘N’
WDTGUID_INITIAL_LDAP_PW VARCHAR2(30) Y
WDTGUID_EXCLUDE_INSERT CHAR(1) Y ‘N’
WDTGUID_EXCLUDE_UPDATE CHAR(1) Y ‘N’
WDTGUID_EXCLUDE_DELETE CHAR(1) Y ‘N’
WDTGUID_EXPIRATION_DATE DATE Y
WDTGUID_DELETED_FROM_AD CHAR(1) ‘N’
WDTGUID_DISABLE_DATE DATE Y
WDTGUID_DISABLED_IN_AD CHAR(1) ‘N’
WDTGUID_JOB_INACTIVE_DATE DATE Y
WDTGUID_EXCLUDE_DISABLE CHAR(1) Y
WDTGUID_DISABLE_EMAIL_SENT_DT DATE Y
WDTGUID_CALSTATE_GUID CHAR(32) Y
WDTGUID_1ST_DIS_EMAIL_SENT_DT DATE Y
WDTGUID_DATE_DISABLED DATE Y
WDTGUID_ORIG_PEOPLESOFT_EMPLID VARCHAR2(11) Y
WDTGUID_ID NUMBER “BANCSUS”.“ISEQ$$_111456”.nextval
Here is the table I’m doing the subquery on:
SQL> desc WDTPERS
Name Type Nullable Default Comments
WDTPERS_GUID RAW(16)
WDTPERS_BANNER_ID VARCHAR2(9) Y
WDTPERS_PEOPLESOFT_OPRID VARCHAR2(30) Y
WDTPERS_BARG_UNIT VARCHAR2(3) Y
WDTPERS_RESTRICT_FLAG VARCHAR2(100) Y
WDTPERS_CS_AFFILIATION VARCHAR2(100) Y
WDTPERS_MAJOR VARCHAR2(100) Y
WDTPERS_MAJOR_CODE VARCHAR2(11) Y
WDTPERS_PRI_AFFILIATION VARCHAR2(30) Y
WDTPERS_STATE_ID VARCHAR2(11) Y
WDTPERS_LIBRARY_ID VARCHAR2(16) Y
WDTPERS_SSN VARCHAR2(9) Y
WDTPERS_EP_AFFILIATION VARCHAR2(100) Y
WDTPERS_NICKNAME VARCHAR2(40) Y
WDTPERS_PRI_ORG_UNIT_DN VARCHAR2(25) Y
WDTPERS_ORG_UNIT_DN VARCHAR2(77) Y
WDTPERS_CN VARCHAR2(70) Y
WDTPERS_GIVEN_NAME VARCHAR2(40) Y
WDTPERS_EMAIL VARCHAR2(60) Y
WDTPERS_SURNAME VARCHAR2(40) Y
WDTPERS_COMM_URI VARCHAR2(100) Y
WDTPERS_FAX_TELEPHONE_NUMBER VARCHAR2(15) Y
WDTPERS_INITIALS VARCHAR2(3) Y
WDTPERS_JPEG_PHOTO VARCHAR2(1) Y
WDTPERS_LOCATION VARCHAR2(40) Y
WDTPERS_LABELED_URI VARCHAR2(1) Y
WDTPERS_MANAGER VARCHAR2(50) Y
WDTPERS_MOBILE VARCHAR2(15) Y
WDTPERS_OU VARCHAR2(100) Y
WDTPERS_PAGER VARCHAR2(15) Y
WDTPERS_PREFERRED_LANGUAGE VARCHAR2(15) Y
WDTPERS_ROOM_NUMBER VARCHAR2(10) Y
WDTPERS_SECRETARY VARCHAR2(50) Y
WDTPERS_TELEPHONE VARCHAR2(15) Y
WDTPERS_TITLE VARCHAR2(50) Y
WDTPERS_BIRTH_DATE VARCHAR2(50) Y
WDTPERS_GENDER VARCHAR2(1) Y
WDTPERS_DEPTID VARCHAR2(10) Y
WDTPERS_DEPARTMENT VARCHAR2(30) Y
WDTPERS_MIDDLE_NAME VARCHAR2(30) Y
WDTPERS_ORGANIZATION VARCHAR2(50) Y
WDTPERS_ADDRESS1 VARCHAR2(55) Y
WDTPERS_CITY VARCHAR2(30) Y
WDTPERS_STATE VARCHAR2(6) Y
WDTPERS_ZIP VARCHAR2(12) Y
WDTPERS_COUNTRY VARCHAR2(30) Y
WDTPERS_CALSTATE_GUID CHAR(32) Y
Here is the Expression I’m trying to add (Note: I get the error even with a blank expression):
SELECT WDTPERS.WDTPERS_CN FROM BANCSUS.WDTPERS WHERE WDTPERS_GUID = WDTGUID_GUID AND ROWNUM = 1
I also tried putting parentheses around the expression and double quoting the schema, table and column names.
Thanks,
Craig
You cannot use blank expression or you’ll get ORA-01723: zero-length columns are not allowed.
You get the error because your SQL for the Custom Field is invalid.
To use SELECT statement as Custom Field expression, try enclose your expression with “(” and “)”.
Your fields in your SQL should includes the table name, e.g.
SELECT WDTPERS.WDTPERS_CN FROM WDTPERS WHERE WDTPERS.WDTPERS_GUID = WDTGUID_GUID AND ROWNUM = 1
Thanks for the suggestion but unfortunately that didn’t work. I also tried entering ‘Test’ and just another column in the table, WDTGUID_PEOPLESOFT_EMPLID, and I still get the same ORA-00942 error.
Could it be a security issue? Maybe some system table or stored procedure that ASP.Net Maker is calling behind the scenes that I don’t have access to? I’ll see if there is a way to see the SQL statements that are being executed.
Thanks,
Craig
I created a trigger on the database to log errors to a table and get the following.
ora_sysevent = SERVERERROR
ora_login_user = CBOUCHER
ora_server_error = 1031
l_sql_text(1) = CREATE GLOBAL TEMPORARY TABLE TEMP_CUSTOM_VIEW ON COMMIT DELETE
l_sql_text(2) = ROWS AS SELECT (WDTGUID_PEOPLESOFT_EMPLID) AS “Emplid” FROM “B
l_sql_text(3) = ANCSUS”.“WDTGUID” WHERE 0=1
ora_sysevent = SERVERERROR
ora_login_user = CBOUCHER
ora_server_error = 942
l_sql_text(1) = DROP TABLE TEMP_CUSTOM_VIEW
The error 1031 is Insufficient Privileges and 942 is Table or View doesn’t exist.
What’s strange is that if I log into SQL Plus as the same user and run these same SQL statements, it’s successful (note: I changed the Expression to just another column in the table instead of a subquery). So I don’t know what’s going on. If someone could confirm that they are able to add a Custom Field to an Oracle table then I’ll know it’s a privileges issue.
Thanks,
Craig
Tested with the HR schema to add a Custom Field to the EMPLOYEES table and it was added fine:
Field name: TEST
Field caption: Test
SQL: SELECT DEPARTMENT_NAME FROM DEPARTMENTS WHERE DEPARTMENTS.DEPARTMENT_ID = DEPARTMENT_ID AND ROWNUM = 1