sakthikumar
asked on
how we can filter out a nested table? or how we can use where clause in nested table?
I have nested table like this
CREATE TABLE "MMS_STG1"."MMS_AUDIT"
( "AUDIT_ID" NUMBER,
"TABLE_NAME" VARCHAR2(30),
"DML_TYPE" VARCHAR2(30),
"MODIFIED_USER" VARCHAR2(100),
"TIMESTAMP" DATE,
"CHANGE_HISTORY" "MMS_STG1"."TABLE_HISTORY_ TAB" ,
PRIMARY KEY ("AUDIT_ID")
NESTED TABLE "CHANGE_HISTORY" STORE AS "HIST"
CREATE OR REPLACE TYPE "MMS_STG1"."TABLE_HISTORY_ TAB" IS TABLE OF table_history;
CREATE OR REPLACE TYPE "MMS_STG1"."TABLE_HISTORY" AS OBJECT (
column_name varchar2(30),
new_value varchar2(4000),
old_value varchar2(4000)
);
>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>
I can select from MMS_AUDIT in plsql developer, which gives a column for collection.
but how I can filter out the collection. Based on the conditions in collections, I wanted to retrieve the records in MMS_AUDIT.
CREATE TABLE "MMS_STG1"."MMS_AUDIT"
( "AUDIT_ID" NUMBER,
"TABLE_NAME" VARCHAR2(30),
"DML_TYPE" VARCHAR2(30),
"MODIFIED_USER" VARCHAR2(100),
"TIMESTAMP" DATE,
"CHANGE_HISTORY" "MMS_STG1"."TABLE_HISTORY_
PRIMARY KEY ("AUDIT_ID")
NESTED TABLE "CHANGE_HISTORY" STORE AS "HIST"
CREATE OR REPLACE TYPE "MMS_STG1"."TABLE_HISTORY_
CREATE OR REPLACE TYPE "MMS_STG1"."TABLE_HISTORY"
column_name varchar2(30),
new_value varchar2(4000),
old_value varchar2(4000)
);
>>>>>>>>>>>>>>>>>>>>>>>>>>
I can select from MMS_AUDIT in plsql developer, which gives a column for collection.
but how I can filter out the collection. Based on the conditions in collections, I wanted to retrieve the records in MMS_AUDIT.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
please provide sample data and expected output
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>You can use TABLE clause to un-nest the nested table.
Aside from using the names in the original post, isn't that basically the same as the example in the link I posted?
Aside from using the names in the original post, isn't that basically the same as the example in the link I posted?