• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

How to insert specific columns of a table created through objects?

I have created a type and used it in the table using the following scripts

CREATE TYPE change_history_t AS OBJECT (
   column_name VARCHAR2(30),
   old_value   VARCHAR2(2000),
   new_value   VARCHAR2(2000));
/
CREATE TYPE change_history IS TABLE OF change_history_t;
/
CREATE TABLE ETL_ADT (
   Table_name  varchar2(30),
   keycolumn1  varchar2(30),
   timestamp   date,
   change_his change_history)
NESTED TABLE change_his STORE AS history;

Now I need to insert only to specific columns in the type that is
only column_name and old_value with out new_value.

how to do this?
0
sakthikumar
Asked:
sakthikumar
  • 4
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
Here is quick example showing how to populate the change_history table and insert into etl_adt.

From your previous questions I assume you have the trigger code to add your column names/table_names/key values?
declare
	myTest change_history := change_history();
begin
	myTest.extend;
	myTest(1) := change_history_t('a','b','c');
	myTest.extend;
	myTest(2) := change_history_t('1','2','3');

	insert into etl_adt values('a','b', sysdate, myTest);
end;
/

select * from etl_adt;

Open in new window

0
 
sakthikumarAuthor Commented:
I am trying for an insert like this

insert into ETL_ADT(TABLE_NAME,TIMESTAMP,change_his) values ('table1', sysdate,change_history(
change_history_t('table1','122'),
change_history_t('table1','112')))

I am getting the error incorrect number of arguments for default constructor.
If I give all the three values for change_history_t then it is inserting.
But I want to give values only for two columns.
0
 
slightwv (䄆 Netminder) Commented:
>>But I want to give values only for two columns.

If the object has 3 values, you need 3 to initialize it.

Use a 'null' for the 3rd:

...
change_history_t('table1','122',null)
...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sakthikumarAuthor Commented:
ok. Is there any other way? can we use any select list kind of things.
as we use to insert for normal tables.
0
 
slightwv (䄆 Netminder) Commented:
I do not understand what 'select list kind of things' means.

Do you have an example?
0
 
sakthikumarAuthor Commented:
for a normal table, if we want to insert specific columns only.

then we will use "insert into table_name(column1,column2..) values(1,2..)

can we give something like this for a nested table object.
0
 
slightwv (䄆 Netminder) Commented:
I showed you what was wrong in http:#a37069735

Your object has 3 fields.  You need to provide all 3.


insert into ETL_ADT(TABLE_NAME,TIMESTAMP,change_his) values ('table1', sysdate,change_history(
change_history_t('table1','122',null),
change_history_t('table1','112',null)))
;
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now