SQL & PL/SQL :: Insertion Of Data From One To Other Schema Table In Same Database

Aug 9, 2011

I need to insert data from one schema table to other schema's table in same database.The thing is columns are not equal.so when I am trying to use insert statement it is throwing error as not enough values. The situation is explained clearly below.The insert stmt is implemented in second schema whose table name is b.

SQL> create table a(ID_A NUMBER,Name varchar2(10),rollno NUMBER,address varchar2(10));
Table created.
SQL> insert into a(ID_A,Name,rollno,address)values(1,'ravi', 101,'hyd');

1 row created.

SQL> insert into a(ID_A,Name,rollno,address)values(2,'rav', 102,'delhi');

1 row created.
SQL> insert into a(ID_A,Name,rollno,address)values(3,'ra', 103,'chn');

1 row created.

SQL> create table b(ID NUMBER,Name varchar2(10),rollno NUMBER,address varchar2(1
0),route varchar2(10),direction varchar2(10));

Table created.
SQL> ALTER TABLE B ADD
2 CONSTRAINT B_PK1
3 PRIMARY KEY
4 (ID);

Table altered.
SQL> create sequence b_seg start with 1;

Sequence created.
SQL> insert into b select b_seg.nextval,lexcom.a.* from lexcom.a,dual;
insert into b select b_seg.nextval,lexcom.a.* from lexcom.a,dual
*
ERROR at line 1:
ORA-00947: not enough values

So,for table b in ID column sequence needed to be inserted and other columns need to be taken from table a.I can understand the error is because two tables are not having equal columns.So,the insert stmt is throwing error.

I can manually write by taking columns from a and b and write insert stmt as follows,but this is tedious process.

SQL> insert into b(ID,Name,rollno,address)select b_seg.nextval,lexcom.a.Name,lex
com.a.rollno,lexcom.a.address from lexcom.a,dual;

3 rows created.

But this is time taking and I had tables which has many columns to be inserted.So is there any other way to solve it and implement insert stmt.

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Insertion / Data Loading To A Table

Apr 10, 2012

We are getting the below error frequently from the application while doing insertion/dataloading to a table. The mentioned error is in the Primary key index

Error: 'ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state'.

I set the value SKIP_UNUSABLE_INDEXES = TRUE using the command 'ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = TRUE' to avoid this. Again we are getting the same error and Every time Iam rebuilding('alter index INDEX_NAME rebuild') the index and doing the DML Operation.

View 22 Replies View Related

BLOB Based Schema - Performance Of Loading / Insertion

May 17, 2012

Would using a blob based schema load noticeably faster than a binary_double based schema?

CODEBlob Scenario:

Load 1 row: 5 columns (1 integer column, 4 blob columns) of size X
VERSUS

CODEDouble Scenario:

Load 10,000 rows: 5 columns (1 integer column, 4 binary_double columns) of size X

While the benefit of using the rows approach is obviously the capability to query the values, I'd like quick answer concerning the loading/insertion performance. Associative array binding is used for loading from a .NET client. Also, would the answer also hold true for 200 columns instead of just 5 columns.

View 1 Replies View Related

Forms :: Insertion In Database

Jan 22, 2013

i have a simple insert statement in oracle form, which is sucessfully run in oracle database(sql). but it is in oracle form trigger: WHEN BUTTON PRESSED as in this format:

Declare
cnt number;
begin
select count(*) into :control.cnt from ol_lcy_ndc where aan=:control.aan and event_id= 'ACL';
if cnt = 0 then
insert into ol_lcy_ndc (form_no, aan, regno, event_id, doev, status, edt, ludt, username)
values (12345, 255257,10030661,'ACL', SYSDATE, 'DRAFT', SYSDATE, SYSDATE, ' ');
else
update ol_lcy_ndc set LUDT= to_date('09-09-2009','DD-MM-YYYY') where aan=:control.aan and event_id= 'ACL';
end if;
end;

but after giving count in cnt, it is not doing anything like insert or update from oracle form, but both the statements are correctly execute in oracle database. may problem is linked with some properties of property palette, upto my knowledge i checked: insertion allowed--> yes.

View 9 Replies View Related

Convert Oracle Database (schema Data) To Postgresql Database

Oct 11, 2012

i want to convert oracle database(schema data) to postgresql database

which tool would be best and url to download and steps to convert from one database to another ?

View 2 Replies View Related

Server Utilities :: Import A Schema From One Database Schema To Another Schema B?

Aug 10, 2010

I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA

I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.

So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:

impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp

I'm getting this:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data

[code]....

View 4 Replies View Related

Slow Insertion In Particular TABLE

Apr 17, 2012

The issue is slow insertion in particular table(i.e A Table) it means insertion in all other tables(i.e B, C, D tables) in same schema is going properly but only when i am trying to insert in one particular table(i.e A table) in same schema it takes long time to complete insertion. Daily insertion is 6000 rows.

I have check all the details like Tablespace size, Analyzing of table, Analyzing of indexes and all. There is no any error alertlog file.

View 1 Replies View Related

Forms :: Checkbox Value Insertion In Table

Nov 4, 2010

I am using oracle form builder 6i and oracle database 10g

1:I have table named 'info' column name 'InfoId'and some other.And another table named 'Handing' with column names HtId, Value1 and value2.

2:I made form that consist of three data blocks, first block takes criteria and second block display record against that criteria from table 'info'.

3:i want checkboxes agianst that display record,and want that when I select some checkboxes against 'InfoId' these selected 'InfoIds'

should save in another table named 'handing' in column 'HtId'.and in same table data in column value1 and value2 will be inserted through textboxes that are in the third datablock of the thae same form .

View 2 Replies View Related

Writing Same Data In Two Database Schema

Aug 3, 2011

I have two same DB schema (same structure, same data) and I need to provide update in one of them when data in the other one is updated. It is singe direction only (we change data in DB Schema A and synchronize data in the DB Schema B; there is not opposite direction). Only small portion of data (compared to the size of DB Schema) might be changed or added this way.

View 1 Replies View Related

Performance Tuning :: How To Increase Data Retrieval / Insertion Speed

Oct 24, 2013

How To Increase Data Retrieval / Insertion Speed my data base has more than 0.5 million records Forms Some Time Respond Very Slow .

View 8 Replies View Related

SQL & PL/SQL :: Format For Accessing Data From Other Database Or Schema?

Aug 25, 2011

Can we use this format for accesing data from other DB or Schema?

In From clause

database_name.schema_name.table_name

View 7 Replies View Related

Globalization :: Junk Character Insertion In Oracle Table

Feb 6, 2013

How to avoid Junk character insertion in oracle table. I have prepared scripts like this Say

customer - info

After insertion the data is inserted like below in production

Customer ¿ info

We are using command prompt for script execution in production environment. I am using PLSQL developer and SQL developer for development. i cannot see junk data in PLSQL developer and latest SQL developer , but its caught in old version of SQL developer. Also in Application also i can able to figure out junk data.

View 6 Replies View Related

Server Utilities :: Exporting Database Schema / Tables Without Data

Sep 22, 2010

I'm trying to export a relatively large database but it's a bit more complicated than that.For one schema I need a full export / import (data included).

For another 10 schemas I need them empty, with the exception of a table in some of them which needs to be exported / imported with all data inside.Is it possible to do this with datapump utility (impdp, expdp)?

Afterwards I will be running some scripts to populate the DB instance with critical data / metadata.

View 1 Replies View Related

SQL & PL/SQL :: Refer A Table In Different Schema - Avoid Database Links

Jul 23, 2013

is there any way to refer a table which is in different schema other than using DB links.

View 5 Replies View Related

SQL & PL/SQL :: How To Restrict User (Schema) From Deleting Data From Table

Nov 2, 2012

I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

Below is the example.

I have created a table employee in abc schema which has two values.

EMPLOYEE
ABC
XYZ

In the above scenario the abc user can only fire select query on the EMPLOYEE table.

SELECT * FROM EMPLOYEE;

He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.

View 10 Replies View Related

SQL & PL/SQL :: Very Slow Access And Updation Of Table Data Of Another Schema

Jun 3, 2010

I am trying to access and modify data of a table of another schema which contains 80,000-90,000 records. My procedure is taking near about 30 mins to complete the operation. faster access and updation of table data.

Details:
I have two schema: TEST and PROD
I am running the below code from TEST Schema.
/* CODE START HERE*/
DECLARE
exc_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_bulk_errors, -24381);
v_block_count NUMBER := 1000;

[Code]....

The above code is taking near about 30mins to process.

I have also tried another approch: Creating a procedure in PROD schema to update COMPONENT_MASTER table and by calling the procedure from above code by passing component code.

/* PROCEDURE CALL FROM ABOCE CODE INTEST SCHEMA*/
PROD.PROCEDURE_TO_UPDATE(v_comp_code);

But still it is taking same time.

View 12 Replies View Related

PL/SQL :: User Copied Table Data From Prod To Other Schema?

Apr 22, 2013

has copied one table data from Production to any other schema , know which user has done this ?

View 1 Replies View Related

PL/SQL :: How To Restrict User (Schema) From Deleting Data From A Table

Nov 2, 2012

I have scenario here.

I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

Below is the example.

I have created a table employee in abc schema which has two values.

EMPLOYEE
ABC
XYZ

In the above scenario the abc user can only fire select query on the EMPLOYEE table.

SELECT * FROM EMPLOYEE;

He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.

View 6 Replies View Related

Server Utilities :: How To Move Schema To Another Schema In Same Database

Jan 5, 2009

move the tables with data present in the user scott(full) to another schema named test. In my case scott is in user tablespace and for test schema i have created different tablespace named test_tbs.

View 14 Replies View Related

Sequence Name In Oracle Database Schema As Well As Timesten Schema?

Jan 13, 2011

How to use same oracle sequence name in Oracle Database schema as well as timesten schema?

View 1 Replies View Related

Client Tools :: How To Create A Table In Another Schema As In Existing Schema

Apr 26, 2010

I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.

How could I do this without using export import. I am using TOAD 9.0.1.

View 10 Replies View Related

Export/Import/SQL Loader :: Imp Records Of One Schema Into Another Schema Of Same Table

Nov 3, 2012

I had done following steps,

schemas(toy,toys)

1) i open the session of toy schema

First i taken backup of table

create table bck20121103_himan as select * from himan;

Backup table is created.

After taking the Backup table

delete himan;(deleting the records)

2) i log in to another session(toys)

exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/

3) i log in to toy schema

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y

i tried the above statement it taken so much of time..

Later i tried

I log in to toy session

i rename the table with other name.

later i imported

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y

it's successfully imported.

View 3 Replies View Related

SQL & PL/SQL :: Regular Table Or Materialized View - Clone Table Data In Another Database

Jul 19, 2010

There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.

Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.

View 19 Replies View Related

XML Data Into Table Within Database

Jun 6, 2013

With no oracle or database background, I have been tasked to figure out how to analyze reading values within xml files. I initially started by writing c# code to read through the xml files, pull out the values I need and summarize the results. Unfortunately, this took atleast 5 hours to open all the xml files and summarize the results for a small portion of EndPointChannelId's and xml files. I decided to store all the reading values within the xml files into a table so I can use SQL queries to do the analysis instead of having to process through all the files each time.

Using the same c# code as before, I have used insert and update sql statements to insert the reading values into a table. To my dismay, it takes about 3 and a half hours to import 1 file where there is 20 files a day :S. There are over 19,000 ChannelID's per file. I am currently using SQL Developer to view the data and have noticed XML Schemas and XML DB Repository but am not sure if that would be the best approach to my problem. I need to increase the efficiency of the import process but I am having difficulties. What is the most efficient way you have used to import xml files into a table structure. !

Below is a portion of the xml file that I am working with.

- <Channel ReadingsInPulse="false" IsRegister="false" IsReadingDecoded="true" MarketType="Electric" IntervalLength="60">
<ChannelID EndPointChannelID="57432:1" />
- <ContiguousIntervalSets>
- <ContiguousIntervalSet NumberOfReadings="6">
<TimePeriod EndRead="22467.80" EndTime="2013-05-09T13:00:00Z" StartTime="2013-05-09T07:00:00Z" />

[Code].....

View 1 Replies View Related

SQL & PL/SQL :: Insertion In Clob Column?

Jul 3, 2012

i have another problem with clob column , when i try to insert data in it through the stored procedure then it shows an

error- 'ORA-01460: unimplemented or unreasonable conversion requested clob' and this error especially arise when data to e inserted in clob column , have more than 4000 characters.

sql>exec pi_test(1,'sysdate','text of clob column'); ORA-01460: unimplemented or unreasonable conversion requested clob

where is the error.

View 8 Replies View Related

SQL & PL/SQL :: Procedure For Update / Insertion?

Jan 5, 2012

In procedure "update_emp", i am updating a row based on p_empno and if it is not present i.e. SQL%ROWCOUNT = 0, then I am inserting that row into emp table.

where as in procedure "update_emp1" , first I am checking whether any row with that p_empno is present or not,if presentthen update the row, else raise an exception to insert the row.

In both procedure, I am doing the same thing, But I am unable to understand which one is good and why

create or replace procedure update_emp( p_empno int) is
begin
update emp set ename='raj' where empno=p_empno;

[code]....

View 5 Replies View Related

PL/SQL :: How To Access  Table Data From One Into Another Database

Oct 19, 2012

I have 2 databases name A and B

without creation of DBLINK , how to acess B database tables into A database in oracle.

because by using database link is quite a bit slower than directly connecting.

is there any option to use B database tables data into A database ?

i tried with keyword "CONNECT" , but it is not working.

View 12 Replies View Related

Insertion Of Any Character After Writing 2 Words

May 3, 2006

How can i insert any character or symbol automatically after writing 2 words

E.g.

i want to enter date 20-may-06 , in this i mean it that when i enter 20 then automatically - is inserted.

is there any query in Oracle. Or Oracle Hasn't created this type of query.

View 3 Replies View Related

Forms :: CLOB Insertion And Updation?

Feb 13, 2012

I simply want to insert data in CLOB column but the size of the data is almost 1 MB. (Copying from a MS word document)

View 2 Replies View Related

Utility To Load Data Into Database Table?

Aug 24, 2011

I wanted to know the best utility in oracle to load data in crores from excel sheets in the database temporary tables in a minimum time.

Is sqlldr the best utility to use in this scenario or to use the parallel and append hint in the insert statment.

how much time the sqlldr and above mentioned hints take to load 10 crore data in the database table.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved