SQL & PL/SQL :: Insert Using Column Name To Indicate Inserted Value

Apr 16, 2012

I have the following tables:

CREATE TABLE test_abc(id NUMBER PRIMARY KEY, col1 NUMBER(10), col2 NUMBER(10), col3 NUMBER(10));

INSERT ALL
INTO test_abc VALUES (1,12345,34567,87654)
INTO test_abc VALUES (2,17345,37567,87754)
INTO test_abc VALUES (3,12745,34767,87674)
INTO test_abc VALUES (4,17045,30567,80754)
INTO test_abc VALUES (5,12740,34067,87604)
SELECT * FROM dual;

CREATE TABLE test_b (id NUMBER, col VARCHAR2(10), coltype_id NUMBER);

What I need to do is to convert the columns col1, clo2, col3 in test_abs into rows in test_b. but I need to do this so that the column name is what determines the value of colytype_id.So in test_b values will look like:

id col coltype
1 12345 1
1 34567 2
1 87654 3

and so on.

View 11 Replies


ADVERTISEMENT

SQL & PL/SQL :: Entire Insert Script Row Will Be Inserted Into Cbxrdlog Table Sqltext Column

Aug 24, 2011

I have 2 tables. Cbxrd and Cbxrdlog. If the Cbxrd table having creation date column. when it inserts the row. if the column is null value. then the entire insert script row will be inserted into Cbxrdlog table sqltext column.

i have attached the trigger script. when i execute the table but i shows error like

"Ora-0756 Quoted String not properly terminated".
"Ora-04098: trigger os_wm_sit_owner.cbx trigger is invalid and failed revalidation.

View 5 Replies View Related

Application Express :: Capturing Insert Done And Storing Value Inserted?

Mar 25, 2013

I am trying to insert In multiple table where one tbl PK is FK of other , I achieved that functionality using Instead of Insert trigger.(PK is handled by another trigger) -- main functionNow On successfully insert i need to captured the PK in Hidden variable to pass it on further for more insert on other table (Pk again being Fk to this table)--- secondary function

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

SQL & PL/SQL :: Creating Trigger - Check If Data Being Inserted Already In Table And If Not Insert It

Aug 24, 2011

I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:

create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;

what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:

create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)

[code]...

View 4 Replies View Related

How To Insert A BLOB Column

Nov 9, 2008

how to insert into a BLOB column. I have stored procedure with an input parameter of type BLOB now how do I insert this BLOB into a table.

View 2 Replies View Related

SQL & PL/SQL :: Insert Into BLOB Column

Jul 21, 2011

I have a table with a column of type blob. Now i want to create a procedure which will insert into that table. But I don't like to create a directory. How can i solve this.

I want to insert a row like this:

insert into table x(image)
values('d:photo est.jpg');

View 2 Replies View Related

SQL & PL/SQL :: Insert PDF File Into BLOB Column?

Jan 20, 2011

I want to store a pdf file into a database column of BLOB type. The pdf file on the client system not on the database server. Is there any way i can achieve this?

View 1 Replies View Related

SQL & PL/SQL :: Insert Value In Column Which Has Date Datatype?

Oct 12, 2012

i am having one column name pdate with date datatype

i am updating here value like this

update table1 set pdate='15-10-2012' where id=1;

but showing error: not a valid month.

how to update this.

View 7 Replies View Related

SQL & PL/SQL :: Insert Query Having Strings As A Column

Dec 13, 2012

tyring to insert an insery query having string as a column where i am supposed to insert a single quote casuing the problem.

insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')

getting missing comma with the above query.when i tried to give as

insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')

insert is happening but saving as "select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij"

how to avoid this and get the select query to store as

select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij

View 5 Replies View Related

SQL & PL/SQL :: Insert Data In Clob Column?

Jun 13, 2012

i have a problem when i try to insert a large character string of nearly 1 lac characters (code of html) in a clob column of my test table, then i get an error "ORA-01704: string literal too long" , i didnot understand that why clob column is not storing this data.

View 1 Replies View Related

SQL & PL/SQL :: Data Of Column Is Large - How To Insert

Oct 9, 2013

I have encountered some problems in SQL I want to create a table with a bunch of prepared data. For ease of use, I choose to generate a SQL file which contains all the sql clauses used to create the table and insert the data. So all the data can only be inserted to a table using sql clause.

My questions:
1) If data of a column is large (for example, 1 M text), how to insert it using SQL, is there a piecewise method.
2) And how can I insert BLOB data using SQL clause.

What I what is to enclose all the operations in a single SQL file, and when the table is needed, just execute this SQL file.

View 2 Replies View Related

SQL & PL/SQL :: Insert Image Files Into Blob Column?

Mar 3, 2011

I am trying to insert an image files into a blob column using the following code.

CREATE TABLE MY_IMAGE_TABLE (
ID NUMBER,
NAME VARCHAR2(20),
IMAGE BLOB);
CREATE OR REPLACE DIRECTORY MY_FILES AS '\dppdb-dev est';
GRANT ALL ON DIRECTORY MY_FILES TO PUBLIC;

[code]....

However, I am getting the this error

ORA-22288: file or LOB operation FILEOPEN failed

I've done some tracing on toad and it seems that the file am trying to insert exists yet am unable to open it.

View 4 Replies View Related

Insert Formatted Template In A Table Column

Jan 17, 2011

I want to insert a formatted template into a table column. Is there any function for this requirement.

want is this format

Best Regards,
XYZ

View 1 Replies View Related

PL/SQL :: How To Insert Sequence Numbers In A Table Column

Aug 23, 2012

I am having the emp table :

empno enmae job
1111 ramu S.E
7658 VENU S.S.E
8280 GETA TRAINEE

alter table emp add sno number

for that sno i have to generate 1,2,3,...

View 4 Replies View Related

Forms :: How To Insert Column Values Into Text Items

Dec 30, 2011

I have created the Textitem with five instances.

i am trying to assign column values to text item through query.

if my table contain one record,it shown in textitems.But if my table contains more records it is showing ora-1422 err.how to solve it.

View 1 Replies View Related

PL/SQL :: How To Insert Null Record (some Column) In Table Using Loop

Jul 5, 2012

How to insert null record (for some columns) in table using loop.

sample data of x_tab

order_id order_name

231 xxx
123
345
111 vvvv

View 5 Replies View Related

XML DB :: Insert Element Into Data Stored In CLOB Column?

Dec 14, 2012

My ORACLE DB version is:

('Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production');
('PL/SQL Release 11.2.0.2.0 - Production');
('CORE 11.2.0.2.0 Production');
('TNS for Linux: Version 11.2.0.2.0 - Production');
('NLSRTL Version 11.2.0.2.0 - Production');

I have this XML data stored in a CLOB column:

<Activity>
<Changes>     
</Changes>
<Inserts>     
</Inserts>
<Definition>     

[code]....

I need creating an update that will insert another <Activity>SomeGUID</Activity> into the <Spawned> parent.

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Way To Insert Data Into Correct Column In Table?

Dec 26, 2011

i used sql loader to import data from csv file to my db.but every time the columns places are changed.o i need dynamic way to insert data into correct column in the table.

in csv file contains column name and i insert this data to temp table, after that i want to read data over column name.also i read the column names from (All_Tab_Columns) to make combination of column name between temp table and All_Tab_Columns table to insert data to right place...

View 39 Replies View Related

SQL & PL/SQL :: Using Sequence To Insert In Child Table Group By A Counter Column

Oct 1, 2011

I found nothing in SQL (all in PL/SQL).I have a table:

create table Parent (pk_id number primary key); --which is filled using sequence seq_Parent.

And I have a child table:

create table Child (rRef number, fk_parent number primary key (rRef, fk_parent);

that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.

View 7 Replies View Related

All The Row Not Getting Inserted

Oct 19, 2010

My code format is:

Insert into table1
select val1,val2
from table2,table3
where table2.col2=table3.col2
and....(some more conditions)

I am working with Oracle 10g.This insert statement is called from a procedure for nearly 3000 to 4000 time.Now my problem is even though the select statement selects correct no. of rows but still it insert only 2 or 3 rows.Apart from it ,it's working fine for some cases i.e it insert the actual no. of rows that is being selected from the select statement.

View 3 Replies View Related

SQL & PL/SQL :: All The Row Not Getting Inserted?

Oct 20, 2010

My code format is:

Insert into table1
select val1,val2
from table2,table3
where table2.col2=table3.col2
and....(some more conditions)

I am working with Oracle 10g.This insert statement is called from a procedure for nearly 3000 to 4000 time.Now my problem is even though the select statement selects correct no. of rows but still it insert only 2 or 3 rows.Apart from it ,it's working fine for some cases i.e it insert the actual no. of rows that is being selected from the select statement.

View 5 Replies View Related

Inserted Value Too Large?

Jun 12, 2008

I keep getting the "ORA-01401:inserted value too large for column". No biggie - I've dealt with this multiple times before (but obviously not enough in this instance).

The data being entered is a SINGLE digit number - a number like 1, 2 or 3 - nothing fancy, just a plain straight everyday single digit number. The field in question is / was set as field type "Integer". Now, there is no set field size for integers! - not in Oracle anyway. Since it wasn't happy, I decided I'll try field types of 'Number' and also "Varchar2" set to 10 bytes. I have deleted the column from the table and re-created it as well.

Here's the even more puzzling bit: I can INSERT data into this field, BUT I can not UPDATE the field with the exact same data. The data is being inserted from a csv file. The same exact csv file used to insert works, but the same data in the same file will not update only that particular column.

If I delete the specific column data from the csv file, all goes through fine. If I hard code the update for the field (eg SET field2 = '1' or even SET field2 = ' ') it still doesn't work. So I know it is not the csv file that is causing problems. I deleted all data from the csv file except the field in question - still no luck.

So after eliminating:
1. The field type
2. The field length
3. The data being inserted
4. The external source of the data

What else could possibly be the problem?

View 1 Replies View Related

SQL & PL/SQL :: Find Inserted Row

May 30, 2012

i m create a table emp . all user of database have privileged to read and write the emp table. now How can identify that which user have insert row in emp file ?

View 4 Replies View Related

PL/SQL :: Last Inserted Record

Aug 7, 2012

I need select last added/inserted records where value = 'test1' and = 'test2' and = 'test3' in column 'NAME' should be one record for each 'test1','test2' ...

aslo..table have column 'DATE_USR' with data_type = timestamp(6)

View 12 Replies View Related

SQL & PL/SQL :: Table Is Not Getting Inserted?

May 12, 2011

i am trying to insert a table through select statement. if i execute the select stmnt alone its working fine. but the table is not getting inserted?

View 10 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.

The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.

fixing the errors or if there is any new logic that can be implemented.

DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)

[Code]...

View 9 Replies View Related

Semantic Technologies :: Can Bind LONG Value Only For Insert Into LONG Column

Dec 22, 2012

I got an exception when I was using sesame adapter to dump a turtle file which contains long texts as objects into oracle semantic database. The exception information is:

org.openrdf.repository.RepositoryException: org.openrdf.sail.SailException: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

ORA-06512: in "SF.ORACLE_ORARDF_ADDHELPER", line 1
ORA-06512: in line 1
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) ...

View 1 Replies View Related

SQL & PL/SQL :: How Special Characters Got Inserted Into DB

Jul 9, 2013

We are using Release 11.2.0.3.0 of Oracle. I am having below special characters inserted into one of my columns, how this value got inserted, (what is the source) i need to track it down.

We dont have any audit trigger on this table to track one level below. As per JAVA guys this is uploaded through a file and the file is having well defined characters and no special characters for this column value also they uploaded the file again but its now going fine with no such special characters. So they put it on DBA's to find how special characters came into database?

Again the editor is not recognising all the characters , so i got the ASCII value for each of the characters in the string, its as below.

String - ‡Mw‹O--ggсÆÔéÓÞ³µmT¤OˆÓ`ôiyïÎ!Ž
ASCII character is : ‡ ASCII Value Is : 14844065
ASCII character is : ‹ ASCII Value Is : 14844089
ASCII character is : -- ASCII Value Is : 14844052
ASCII character is :  ASCII Value Is : 49793
ASCII character is : Ñ ASCII Value Is : 50065

[code]....

View 8 Replies View Related

SQL & PL/SQL :: How To Find Which Record Is Inserted First

Mar 25, 2010

I have a table with lot of records.In this table, how do I find, which record is inserted just before a record in my question.

consider this example..
SQL> desc test1
Name Null? Type
------------------------------- -------- ----
SEQ_ID NUMBER
NAME VARCHAR2(1)

[code]....

in the above data, if I want to know what record is inserted before "1018", how can I find it.

Can we find out based on ROWID.?

View 13 Replies View Related







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