I am modifying an application in APEX 5.1.2. I extended the table ctp_bid_bond_security_t by adding 6 variables as follows.
MWBE_SUBMISSION_TYPE_KEY VARCHAR2(35 byte);
APPROVAL_STATUS_KEY VARCHAR2(35 byte);
VENDOR_TYPE_KEY VARCHAR2(35 byte);
MINORITY_GROUP_KEY VARCHAR2(35 byte);
PLAN_PERCENTAGE_MEN NUMBER(5,2);
PLAN_PERCENTAGE_WOMEN NUMBER(5,2);
I have to add these variables to an interactive grid in a page in the application. The driving sql of the grid is…
SELECT bb.bid_number
, bb.auction_header_id
, bb.vendor_id
, bb.bid_factor
, bb.problem_name
, bb.remarks
, '' vendor_name
, '' contact_person
, '' bid_status
, '' quote_total
, '' quote_time
, '' quote_expiration_date
, bb.additional_letter_info
, bb.last_updated_by
, bb.last_update_date
FROM ctp_bid_bond_security_t bb
WHERE bb.auction_header_id = :P112_AUCTION_HEADER_ID
The sql script above is working fine in production. It's only when I modify it that I get these error messages.
When I change the sql in any way and save it I get the error message:
COLUMN <column name> -> Identification -> Column name must be unique!
For each of the Null variables. These null variables are populated with a call to a package, e.g.
Source>
Type: SQL Expression
SQL Expression: CTP_CONTRACTS_COMMON_PKG.vendor_name('', vendor_id, 'Yes')
Data Type: VARCHAR2
I tried several approaches to eliminate the error.
CAST Function…
SELECT bb.bid_number
, bb.auction_header_id
, bb.vendor_id
, bb.bid_factor
, bb.problem_name
, bb.remarks
, CAST(Null AS VARCHAR2(50)) vendor_name
, CAST(Null AS VARCHAR2(50)) contact_person
, CAST(Null AS VARCHAR2(25)) bid_status
, CAST(Null AS NUMBER) quote_total
, CAST(Null AS VARCHAR2(25) quote_time
, CAST(Null AS DATE) quote_expiration_date
, bb.additional_letter_info
, bb.last_updated_by
, bb.last_update_date
FROM ctp_bid_bond_security_t bb
WHERE bb.auction_header_id = :P112_AUCTION_HEADER_ID
I defined 6 global application variables and mofoed the sql with CAST and without CAST function…
SELECT bb.bid_number
, bb.auction_header_id
, bb.vendor_id
, bb.bid_factor
, bb.problem_name
, bb.remarks
, CAST(GS_VAR1 AS VARCHAR2(50)) vendor_name
, CAST(GS_VAR2 AS VARCHAR2(50)) contact_person
, CAST(GS_VAR3 AS VARCHAR2(25)) bid_status
, CAST(GS_VAR4 AS NUMBER) quote_total
, CAST(GS_VAR5 AS VARCHAR2(25) quote_time
, CAST(GS_VAR6 AS DATE) quote_expiration_date
, bb.additional_letter_info
, bb.last_updated_by
, bb.last_update_date
FROM ctp_bid_bond_security_t bb
WHERE bb.auction_header_id = :P112_AUCTION_HEADER_ID
Either way I get the error…
Error message: ORA-06550: Line 11, column 41: ORA: 00904: “GS_VAR6”: Invalid identifier.
Before I added the new variables to the SQL I need to eliminate this error caused by the Null column. Can anyone please suggest a work-around. (I'm sorry about the formatting but this text area inserts lines in my sql.)
Thank you.