Insert Into Select From Another Schema?

Jan 11, 2011

I have a schema northwind which has tables and then i have another schema northwind_staging which has its own tables.
CREATE TABLE "NORTHWIND"."CUSTOMERS"
(
"CUSTOMERID" VARCHAR2(5 CHAR),
"COMPANYNAME" VARCHAR2(40 CHAR) NOT NULL ENABLE,
"CONTACTNAME" VARCHAR2(30 CHAR),
"CONTACTTITLE" VARCHAR2(30 CHAR),
"ADDRESS" VARCHAR2(60 CHAR),

[code]...

I created a procedure

CONNECT NORTHWIND_STAGING/NENAGH1;
create or replace procedure INSERTCUSTOMERS_STAGING
as
begin
INSERT INTO NORTHWIND_STAGING.customers_staging
SELECT DISTINCT c.customerid, c.companyname,c.contactname,c.region, c.country
FROM NORTHWIND.CUSTOMERS c;
end;
/

show errors This works fine when i ran my test scripts in SQL developer. I created a role Select on Northwind

View 10 Replies


ADVERTISEMENT

Select Insert Failing To Insert In 11g?

May 7, 2013

In 11g, When I am trying to insert the records with select insert option it is failing.

Below is my Query:

insert into table_1 select * from table_2

The above query is not inserting any records into table_1. But when i query select * from table it is returning records.

The same select Insert query is inserting records properly in 10g.

View 5 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related

Select All Views From Schema

Apr 27, 2010

I want to list out all the views in a schema. What table can I query?

View 2 Replies View Related

SQL & PL/SQL :: Select Data Different Schema

Jan 12, 2011

I have 3 users in schema.

User09
User10
User11

Table Name PURCHASE exists in all above three schema with different data(according to date)

Like
User09 ----> purchase ---- data date Jan09 to Dec09
User10 ----> purchase ---- data date Jan10 to Dec10
User11 ----> purchase ---- data date Jan11 to Dec11

I want to select * from purchase, from above all three user with select statement.I can select data one by one with union all

select * from user09.pruchase Union all
select * from user10.pruchase Union all
select * from user11.pruchase

after creating this query I add another User User12 and that User has same purchase table so I will have to add one another line IN Union all Query. when I see this query. It gives me the all owner name of User09,User10,User11......

SELECT * FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'USER%'
and table_name ='PURCHASE'

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
USER PURCHASE USERS
USER09 PURCHASE USERS
USER10 PURCHASE USERS
3 rows selected

Can I get data from Purchase Table of all users with select statement .

View 4 Replies View Related

How To Grant Select On All Tables Of User1 To Another Schema

Jan 18, 2012

User1 is having 10000 tables in his schema...How can i grant "select" on a all tables of a user1 to another schema(user2) so that in future when user1 will create tables , the user2 will have "select" access on those tables automatically.

I dont want user2 to have "select any table" privillege.

User2 should not have "drop" privillege on his own tables.

View 1 Replies View Related

SQL & PL/SQL :: Select French Characters Generic Entire Schema

Nov 17, 2010

I need to select all names that can possibly have ether accents or french characters what is the easiest way?

View 1 Replies View Related

Schema Permissions - Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

I have an automated process which runs on an Oracle 8i database server as user abc.This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 1 Replies View Related

SQL & PL/SQL :: Trigger - Automatically Grant Select Permission To ODI-MASTER Schema?

Jan 4, 2011

I have two schema named ODI_MASTER and ODI_WORK.Under ODI_WORK there are some tables like TEMP1, TEMP2
Further more when any new tables will create under ODI_WORK, then i need automatically grant select permission to ODI _MASTER schema.

for this purpose i choose trigger, and a Stored procedure.

CREATE OR REPLACE TRIGGER ODI_WORK.TRIG1
AFTER CREATE ON ODI_WORK.SCHEMA
ENABLE
call sp1 (ora_login_user)

[code]...

I searched a lot over blogs, if EXECUTE IMMEDIATE commands exist under Trigger it gets problem. Insert/update/delete statement on trigger seems no problem.

View 13 Replies View Related

Insert With Nested Select

Jul 24, 2007

I have here my SQL

-> INSERT into myTable (col1, col2, col3) values(SELECT table_seq.nextval from dual, 'value2', 'value3');

That's not working...

View 8 Replies View Related

SQL & PL/SQL :: Bulk Select And Insert

Jan 7, 2011

We are doing a bulk select and insert (10,000 rows processed in each transaction). If one record fails, the entire transaction is rolled out. We need to fix this and re-run. the process is repeated unless all errors are fixed.

How to capture all errors in a single run ?

View 3 Replies View Related

SQL & PL/SQL :: Insert And Update Through A Select Statements

Jul 7, 2010

how can i insert and update to a table in oracle database 10g through a select statement. not using merge.

View 2 Replies View Related

SQL & PL/SQL :: Insert As Select With Clob Field

Oct 15, 2013

Oracle 11.2.0.3 on Linux.

I need to execute this insert as select:
INSERT INTO TAB1 (SQL_ID,SQL_FULLTEXT)
SELECT DISTINCT t.sql_id, t.sql_text FROM DBA_HIST_SQLTEXT;

But I get this error:
ORA-00932: inconsistent datatypes: expected - got clob

This is TAB1:

SQL_ID VARCHAR2(13)
SQL_FULLTEXT CLOB Y
SQL_CUSTOM_HASH VARCHAR2(60) Y

Should I user dbms_lob package? Is so, I do not understand how.

View 2 Replies View Related

SQL & PL/SQL :: Insert Into Table A With Select Query On B And C?

Mar 22, 2013

I have a table A on dev with definition as TAble A(address,name) and the same table on Prod is defined as Table A(name,address).

my question is Ihave one package in that am trying to insert into this table as follows:

INSERT INTO A
SELECT b.name name,
a.address address,

[Code]....

so the query works on Prod but fails on Dev because column order is different.

I have 2 solutions:

1. I can mention column names in insert line and modify the query but tomorro some body changes again the definition of table A I need to change the query, so do I have solution in oracle sql that can handle the column order without specifying the column names in insert line.

so tomorrow On prod column order and on Dev column order is different though my sql should successfully execute.

View 5 Replies View Related

Insert Or Select Date Data Type Value In AM Or PM?

Oct 26, 2006

How do i insert or select date data type value in am or pm Eg 10:30:00 am or 10:35:00 pm

View 3 Replies View Related

Record Count Mismatch Between Select And Insert

Mar 20, 2013

We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?

It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.

Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem
Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.

View 3 Replies View Related

SQL & PL/SQL :: How Many Select / Insert / Update Transactions Executing Per Day In DB

Jan 20, 2011

I need to generate a report by showing the select, insert, update transactions count per day.

I have been use V$SYSSTAT veiw but there is not include my requested data exactly!

[URL]

View 3 Replies View Related

SQL & PL/SQL :: Insert With Select And Subquery Inside Trigger

May 5, 2011

I hit a bottleneck where my insert trigger won't execute the insert statement (with subquery). See illustration below:

Step# 1 - Table definition:
Table_A(a1 number, a2 varchar(10), a3 varchar(10))
Table_B(b1 number, b2 varchar(10), b3 varchar(10))
Table_C(c1 number, c2 varchar(10), c3 varchar(10))

Step# 2 manipulated the tables:
Inserted 3 records in Table_C.

Then I created an Insert Trigger to Table_A with an insert statement into Table_B and a subquery to Table_C. Please see below:

CREATE OR REPLACE TRIGGER TABLE_A_TR
after INSERT OR UPDATE OR DELETE ON TABLE_A
FOR EACH ROW
DECLARE
[code]......

Step# 3 compiled the created trigger and I've successfully compiled it.
Step# 4 Tested the trigger (TABLE_A_TR) using an insert statement to TABLE_A.
Insert into TABLE_A values (1,'testa','testb')

I've successfully insert the values into TABLE_A however I've observed that the trigger didn't execute the insert statement because TABLE_B has an empty rows. I tried to manually execute the insert statement just to see if there's an issue in my insert statement but I've successfully populated the values into TABLE_B. So I'm wondering why the trigger didn't execute the insert statement.

View 4 Replies View Related

SQL & PL/SQL :: Bulk Collect And For All Statements To Select And Insert

Feb 19, 2013

I used bulk collect and for all statements to select and insert the data in temp table.The select SQl is returning one row. But its not inserting this row into temp table.Its not throwing any exceptions. Used ref cursor because the select statement is going for every cursor.

here modified the code and provided only one cursor.

Create Or Replace Procedure Sales_Hist_Update_Bkp Is
Type Type_Name Is Record(
Sku_Item_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Sku_Item_Key%Type,
Locationno Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Locationno%Type,
Bsns_Unit_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Bsns_Unit_Key%Type,
Act_Item_Cost_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Item_Cost_Amt%Type,
Act_Rglr_Unit_Price_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
[code]...

View 11 Replies View Related

SQL & PL/SQL :: Insert Taking Significantly Longer Than Select?

May 23, 2011

Recently upgraded from Oracle 10.2.04 to 11g with a few bumps on the road most of which I've been able to resolve, but there's one that continues to confuse me.

Pretty vanilla INSERT statement in which the SELECT portion on its own runs in about 2 to 5 seconds (all results returned) on a facility by facility basis. When I try to combine this with an INSERT statement it ends up running for 12+ minutes per facility. The explain plan looks good and I've even tried emptying the target table prior to running the INSERT.

I've gathered schema/table statistics to no avail. I also tried using it as a CREATE TABLE AS statement and it still takes the 12 minutes per facility.

View 3 Replies View Related

SQL & PL/SQL :: Insert Select Statement Limit Inserts?

Apr 7, 2011

this is my problem,

insert into t1
select *
from t2
where condition;

Select * from t2 where condition retrieves in example 1000000 records but I only need to insert into t1 the first 100 records.

Is there anyway to abort/limit the insert into t1 to 100 records?

This canĀ“t be used because only applies the condition to the first 100 rows of t2 and it's not valid.

insert into t1
select *
from t2
where condition
where rowcount<100;

View 16 Replies View Related

SQL & PL/SQL :: Select Data From 2 Tables And Insert Into Another Table

Apr 25, 2013

I want to select data from different tables and insert this into one table based on some conditions:

SELECT *
FROM welltest_msr
WHERE well_s = 3419740
AND check_ind = 1

[Code]....

So I tried doing this with selecting the data and looping through it to do the insert.

DECLARE
--
-- WELLS
--
CURSOR c_well

[code].....

View 6 Replies View Related

PL/SQL :: Row Level Security - Select / Insert And Delete

Dec 17, 2012

I have 4 tables , Certain users need to (Select ,insert,delete) certain rows only based on nApplication id

All the tables have Application Id , Based on this application id user will (Select ,insert,delete)

I tried row level security DBMS_RLS.add_policy but it hangs every time when i query the data or when i try to drop the policy. Is there is any method to resolve this issue.

If only Row level security will solve this issue , give me some example

View 18 Replies View Related

SQL & PL/SQL :: Insert Into Select Or Cursor For 100k Rows

Jan 22, 2012

I am trying to insert 100K rows, I have written this proc using cursor. But it is saying anonymous block completed, and no rows are inserted. If I just run the select it returns the rows.Could if just insert into select would be fine or should I use cursor.

CREATE OR replace PROCEDURE Insert_data (l_from_date IN VARCHAR2,
l_to_date IN VARCHAR2)
IS
lc_err_msg VARCHAR2 (2000);
ln_count NUMBER := 0;
CURSOR ins_d IS
SELECT a.col1 AS url,
b.col1 AS ref_url,
COUNT (*) AS total_views
[code]....

View 5 Replies View Related

Get All Tables That A User Can Select / Insert / Update Or Delete

Dec 17, 2010

How to get all the name of tables that a user can select, insert, update or delete?

View 2 Replies View Related

SQL & PL/SQL :: Select Into Temporary Variable On Stored Procedure For Second Insert?

Mar 30, 2011

the moment my 11g database is connecting to a php web front end. this following procedure is the one I'm having trouble with.

CREATE OR REPLACE PROCEDURE "BSISSONS"."CREATE_EXCURSION" (
min_places IN excursion.min_places%TYPE,
max_places IN excursion.max_places%TYPE,
additional_charge IN excursion.additional_charge%TYPE,

[code]...

I can select into an output variable to return the value of the primary key of the newly inserted row back into the webpage, but i need to be able to 'select into' a temp variable to insert this value into another table on the same procedure. I get complie errors when i try to 'DECLARE' a variable after the 'AS' keyword

View 2 Replies View Related

SQL & PL/SQL :: Insert Records From A Select Query Into Temporary Table?

Mar 21, 2013

We are trying insert records from a select query into temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?

It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.

Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.

View 16 Replies View Related

SQL & PL/SQL :: Insert Statement In Trigger Accessing And A Select From A Table

Jun 6, 2010

In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.

But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger :
-------

IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN
INSERT INTO TEST.W_THRESHOLD_LOG
(THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE)
VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW);
END IF;
-------

In the above code if the hardcoded value 'Customer1' need to be picked from another table,
i.e .

SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID

how can I rewrite my query to the above value from the select into my insert statement..?

View 24 Replies View Related

PL/SQL :: Combining Identifier And Select Statement In Insert Into Command

Dec 11, 2012

Is there any way to combine an identifier and select statement in PL/SQL when using the insert into command?

e.g.

DECLARE
name := 'BOB';
BEGIN
insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP) name, (select min(time), max(time) from lLAP_TIMES);
END;

In the above statement I am trying to insert the identifier "name" (BOB) into MYTABLE.NAME, along with the result of the select query from the table LAP_TIMES ... min(time) into MYTABLE.SLOWEST_LAP, and, .... max(time) into MYTABLE.FASTEST_LAP.

If the above is possible in one statement how would I also combine an identifier, with two select statements into an "insert into" statement?

e.g.

name := 'BOB';
insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP, EVENT) name, (select min(time), max(time) from LAP_TIMES), (select race_event from MEET);

In the above example I am also trying to insert the result from MEET.RACE_EVENT into the column MYTABLE.EVENT

View 4 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







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