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


ADVERTISEMENT

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

SQL & PL/SQL :: Invalid Identifier Error In Select Statement?

Mar 18, 2011

Where I run this update query, I get the error:

Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

UPDATE XXX_CURR_EOM SET ID =
(select CAPITALPLAN.ID from capitalplan, XXX_CURR_EOM
where
XXX_CURR_EOM.ID = CAPITALPLAN_id)

don't know what it means.

View 10 Replies View Related

Networking And Gateways :: Select Statement - Could Not Resolve Connect Identifier Specified

Jun 7, 2010

we have oracle
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Productio
NLSRTL Version 10.1.0.2.0 - Production

and SQL Server 2005 on MS windows Server 2003

i followed the article and i get succeed on each steps

[URL]

when i execute the select statement under user by name NEW I got below error

ORA-02019: connection description for remote database not found

View 1 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 :: 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

Number Of Rows Inserted Is Different In Bulk Insert Using Select Statement

Jul 1, 2010

I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.

strQuery :='INSERT INTO TAB3
(SELECT t1.c1,t2.c2
FROM TAB1 t1, TAB2 t2
WHERE t1.c1 = t2.c1
AND t1.c3 between 10 and 15 AND)' ....... some other conditions.

EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.

On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.

View 3 Replies View Related

PL/SQL :: Save Exceptions Clause Or Equivalent For Insert As Select Statement

Jan 29, 2013

Is there a save exceptions clause or an equivalent for an Insert as select* statement ?

How do I trap the errors in the below statement -

INSERT INTO copy_emp
SELECT * FROM emp;

Is it an all or nothing scenario ?

View 10 Replies View Related

SQL & PL/SQL :: Combining 2 Select Statements In A View?

Apr 8, 2012

1. I Wnat to analyze the buffer cache hit ratio. This is what i did.

DECLARE
bufcac NUMBER(10, 2);
BEGIN

[Code]....

2. I would like to analyze the PGA and determine what percentage out of the maximum allocated PGA is being used. I tried the code below but can't find the percentage. Sad

SELECT NAME,
To_char(Decode(unit, 'bytes', VALUE / ( 1024 * 1024 ),
VALUE), '999,999,999.9') VALUE,

[Code]....

Apart from the little problem I have with number 2, not figuring out how to get the precentage seeing that all the data is store in the field VALUE.

I would also like to create a view that has both 1 and 2 in it, but whenever I try creating it, I got and error stating and INTO clause is expected.

View 10 Replies View Related

Update Statement Not Working - Invalid Identifier?

Apr 15, 2009

I am having trouble getting the following update statement to work.

update Team
set Names.Team_Name = "Not Yet Assigned"
WHERE
Team.ROE_PROCESS = 'G' AND

[code]...

The error message I am receiving is:
ORA-00904: "Department"."UI_WKLY_EARNS": invalid identifier

View 4 Replies View Related

SQL & PL/SQL :: Oracle Error For Invalid Identifier In Execute Immediate Statement

Apr 5, 2011

I have a procedure as below. To sum up the procedure in one line it dynamically forms a string to get the values of the type which is passed as an input to the procedure.

I call the procedure as

exec exec_imm( exec_imm_t(1,'asd','1/2-34'));

ERROR:
Error starting at line 9 in command:
exec exec_imm( exec_imm_t(1,'asd','1/2-34'))
Error report:
ORA-00904: "P_TYPE_DATA"."ADDRESS": invalid identifier
ORA-06512: at "PTK_ADM.EXEC_IMM", line 26
ORA-06512: at line 1
00904. 00000 - "%s: invalid identifier"

NOTE:
When I try to execute the procedure with the execute immediate statement I get the above error. But when I execute the select statement which is nothing but the value in v_type_data directly (as seen in the comments in the code below) there is no error. But when the same v_type_data is used in execute immediate, I get an error.

CREATE OR REPLACE procedure exec_imm(p_type_data exec_imm_t)
AS
v_type_str CLOB := NULL;
v_type_data CLOB := NULL;
v_type_name VARCHAR2(25) := NULL;
BEGIN
[code]......

View 6 Replies View Related

Server Utilities :: Insert Data Without Writing Insert Statement In Oracle?

May 15, 2010

how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.

View 2 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

SQL & PL/SQL :: How To Insert Single Quote In Insert Statement

Feb 24, 2012

INSERT INTO LKP_ASSET_LOCATION (LOCATION) VALUES ('AMERICA'S CUP VILLAGE')

View 2 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

Jan 11, 2012

I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).

When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:

SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...

From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?

View 10 Replies View Related

SQL & PL/SQL :: Why Blind Select Is Better Than Conditional Select Statement

Dec 29, 2010

Why Blind select is better than Conditional select Statement?

View 10 Replies View Related

SQL & PL/SQL :: Combine Update And Insert Command?

Jul 23, 2012

i have a given pl/sql program that first deletes records out of a table and afterwards inserts new rows. now for example 2 rows out of 10 have a foreign constraint and can not be deleted that easily anymore. so i delete the ones i am able to (with the where not exists clause).

now i want to update the records who have a foreign key constraint and the rest with a regular insert. how would i do this the easiest way. i thought i could use insert with a where clause!!

here i have some part from the original

declare
procedure add(
i_id pls_integer,

[Code].....

View 3 Replies View Related

SQL & PL/SQL :: Insert Into Table - Command Not Properly Ended

Oct 23, 2012

INSERT INTO Table (NID, NType) SELECT DTreeNotifySequence.NextVal, 1 FROM Dee WHERE ID =354344 commit;

I get the following error:

ORA-00933: SQL command not properly ended

I tried it in several ways...are more semicolons needed?

View 4 Replies View Related

Delete Statement Doesn't Work - SQL Command Not Properly Ended

Aug 28, 2007

The following script doesnt work.

DELETE
FROM PSPROJECTITEM I
WHERE I.PROJECTNAME = 'TEMP1'
and (I.OBJECTTYPE between 79 and 84
or (I.OBJECTTYPE = 58
and I.OBJECTID1 = 104

[code]...

It gives the following error:

Error at Command Line:12 Column:6
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

View 7 Replies View Related

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 :: Multiple Row Insert Not Working - Command Not Properly Ended

Mar 7, 2013

The multiple row insert is not working

CREATE TABLE example (
example_id INT NOT NULL,
name VARCHAR( 50 ) NOT NULL,
value VARCHAR( 50 ) NOT NULL,
OTHER_VALUE VARCHAR( 50 ) NOT NULL
);

For the below query its showing error .

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

the error message is.

Error starting at line 1 in command:
INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4')
Error at Command Line:3 Column:39
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

View 8 Replies View Related

Dynamically Executing A Select Command

Aug 4, 2010

How to dynamically execute a select command whether it is retrieved a single record or multiple record in oracle database 10g. i have tried with the command execute immediate but it was not successful.

is it possible can i delete or drop multiple tables in a single drop and delete statement.

View 5 Replies View Related

SQL & PL/SQL :: Insert Statement?

May 13, 2011

I have one query i have create one table T1 in this table i have this three colm (EMPNO,ENAME,HIREDATE) and i have to insert raw from this two table T2 and T3 in T2 table colms are (EMPNO,SAL) and T3 table colm are (EMPNO,MGRID) so which query i have to run

View 4 Replies View Related

SQL & PL/SQL :: Null In Insert Statement?

Jul 5, 2010

I wanted to print 'null' when the column value is null. Actually, i am doing something like this

select empno||','||''''||ename||'''''||','||comm||','||sal from emp

It gives the following output for example
7369,'pointers',,200
If I use the above values to form a insert statement it throws
an error. As 'comm' value is not there.

I wish to get something like
7369,'pointers','',200
or
7369,'pointers',null,200
from the above select query

note I dint copy paste the query exactly from my sql*plus session as I am away from my oracle machine

View 14 Replies View Related

SQL & PL/SQL :: Using Subquery In Insert Statement

Jun 5, 2010

I read in a book that you can't use subquery in an insert statement . E.g:

1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:

2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');

This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.

View 5 Replies View Related

Insert Statement Causing ORA-01858?

Dec 4, 2012

i'm executing a load test using the below statement:

INSERT INTO BPMBI.PPROCINSTANCE SELECT * FROM BPMBI.PPROCINSTANCE_BKP WHERE ROWNUM < 501;
COMMIT;
EXIT;

I've been using the same for a few days without any error while now wehn i try to use the same i get below error:

SQL> insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500;
insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected.

I've been trying to check what the issue is and when i execute the rownum < 140 works fine while rownum < 150 is showing the same error.

View 6 Replies View Related

Concurrency Wait On Insert Statement?

Feb 19, 2013

I am running Oracle RAC 2 nodes 11g R2 on AIX 7.1

I have a table with unique index, and the application is doing inserts/updates into this table.Suddenly and for about half a minute I faced a high concurrency waits on all the processes running these inserts for one node. I saw this high concurrency wait in the top activity screen of the OEM only on one of the nodes. knowing that the processes doing these inserts are running on both nodes.

All what I have that in this half minute I see high concurrency wait in OEM top activity screen related to this insert statement and when I clicked on the insert I found high "enq: TX - index contention". Again this was only on one node.After this half minute everything went back to normal.What could be the reason and how can I investigate it ?

View 3 Replies View Related

PL/SQL :: Using Sqlerrm Directly On Insert Statement?

Oct 3, 2013

Given below a block of code, this code compiled successfully on one DB but returns error on other.DB version is same. I know sqlerrm can not be used directly but how it compiled successfully on one DB. 

declare
l_procedure_name CONSTANT VARCHAR2(100) := 'copy_device_status_tables';
l_procedure_id CONSTANT INTEGER := 301;

[Code].....

Error report:

ORA-06550: line 12, column 75:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 11, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.

View 5 Replies View Related

SQL & PL/SQL :: Insert Statement Which Will Commit Transaction By Itself

May 22, 2010

What is the syntax to write an insert statement which will commit the transaction by it self,not using an exclusive commit statement after wards.

View 2 Replies View Related







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