SQL & PL/SQL :: Inserting Multiple Records With CLOB Using Select

Oct 11, 2013

I'm updating a large piece of legacy code that does the following type of insert:

INSERT INTO foo_temp
(id, varchar2_column)
SELECT id, varchar2_column
FROM foo;

We're changing varchar2_column to clob_column to accommodate text entries > 4000 characters. So I want to change the insert statement to something like:

INSERT INTO foo_temp
(id, clob_column)
SELECT id, clob_column
FROM foo;

This doesn't work, since clob_column stores the location of each text entry, rather than the actual content. But is there some way that I can achieve the insert with one call to a select statement, or do I need to select each individual record in foo, open the clob_column value, read it into a local variable and then write the content to the matching record in foo_temp?

View 2 Replies


ADVERTISEMENT

PL/SQL :: ORA-03113 When Inserting CLOB Value Casted As XML From SELECT Query Into Table?

Aug 15, 2013

I have a table that contains a CLOB column with pseudo-XML in it. I want to keep this data in an XMLType column so that I can leverage some of Oracle's built-in XML features to parse it more easily.

 The source table is defined as: CREATE TABLE "TSS_SRM_CBEBRE_LOGS_V" ( "INCIDENT_ID" NUMBER, "EVENT_TYPE" VARCHAR2(100 BYTE) NOT NULL ENABLE, "EVENT_KEY" VARCHAR2(100 BYTE), "CREATION_DATE" TIMESTAMP (6) NOT NULL ENABLE, "CREATED_BY" VARCHAR2(100 BYTE) NOT NULL ENABLE, "LOG_MSG" CLOB); 

The target (for testing this problem) table is defined as: CREATE TABLE "TESTME" ( "LOG_MSG" "XMLTYPE")  My query is: insert /*+ APPEND */ into testme ("LOG_MSG")select XMLTYPE.createXML("LOG_MSG") as LOG_MSG from "TSS_SRM_CBEBRE_LOGS_V" b; In SQL*Developer, my error is: Error report:SQL Error: No more data to read from socket In SQL*PLUS and Toad, my error is: ORA-03113: end-of-file on communication channelProcess ID: 13903Session ID: 414 Serial number: 32739

View 6 Replies View Related

Forms :: Inserting Multiple Records On Button Press

Jun 17, 2012

I have Multi record Block and for that block i have created one button, if we press that buttion it will open new block and it will post the records, Unfourtunately that block table dont have Primary key or any constraints .. so when we press that buttoon multiple times .. its posting multiple times..

Now i need to restrict to that which is should not post the records multiple timies i have tried by controling the paraemter..I have created one Non data base item initially value i assigned to 'N"

if the value is "N" then am doing process and showing the records and after processing am assigning the value to 'Y', if there are multiple records , at block level in pre-record trigger am assigning as 'N'.

View 2 Replies View Related

Forms :: Inserting Multiple Records In Table Through Tabular Form

Apr 1, 2012

i have a master-detail form.

detail block is tabular.

when-button-pressed trigger iam inserting records in another table.i write

insert into ONHAND_QTY_LOCATION(sno,matid,matcode,description,partno,onhand_qty,location)
values (:ship_dtl.slno,
:ship_dtl.mat_id,
:ship_dtl.mat_code,
:ship_dtl.description,
:ship_dtl.part_no,
:ship_dtl.rec_qty,
:ship_mstr.place_from)

it is inserting only one record in onhand_qty_location table that too the last record.

i want all the records which iam entering in detail block should get entered in onhand_qty_location table.

View 8 Replies View Related

Forms :: Inserting Multiple Selected Records Into Database When Click Button?

Jul 19, 2010

I got a form with few columns and a check_box. If the user selects n number of check boxes and click the button Save. The corresponding records should be inserted to XX_AP_CUSTOM table.

I have written the following below code in when-button-pressed trigger. With this i am able to insert only one record i.e where the current record indicator is there, even though multiple check-boxes(records) are selected.

declare

begin
IF :XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX = 'Yes' THEN
--IF CHECKBOX_CHECKED(:XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX) = TRUE Then
--app_insert.insert_record('WHEN-BUTTON-PRESSED');
insert into xxfbi.XXFBI_INV_QUOTE_ANAL(Item,

[code]......

I know that i have to use last_record and first_record and for loop to insert multiple selected records, but dont know how to do it.

View 5 Replies View Related

Forms :: How To Select Multiple Records

Feb 23, 2012

I'd want to select multiple records in my multi-record block. I'd also want to do that with checkboxes. When the user clicks on a particular checkbox, that should be selected and whatever record the user wants to check it should be added to selected records.

View 39 Replies View Related

SQL & PL/SQL :: How To Select Only Records That Have Multiple Values For A Column

Nov 21, 2011

I'm trying to select id's in a table that have 2 certain values for another column. Example below explains:

idCoupon Type
123Amount
123Percent
456Amount
789Percent

I would like to write a sql statement that would select all rows where id=123, because id 123 has both coupon types "Amount" and "Percent". So the result set of the sql statement would look like:

idCoupon Type
123Amount
123Percent

View 6 Replies View Related

PL/SQL :: Inserting PDF File Into Clob?

Oct 11, 2012

I tried inserting PDF file into a CLOB column , in the below example l_bfile is a bfile datatype. and I'm inserting l_bfile to CLOB column.

l_bfile := bfilename('C: est', 'test.pdf');

But It is throwing below error:

ORA-06550: line 9, column 42:
PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got FILE
ORA-06550: line 9, column 1:
PL/SQL: SQL Statement ignored

View 2 Replies View Related

SQL & PL/SQL :: Inserting Data From Clob To Varchar2?

Aug 21, 2006

I have to Insert data from one table to another table. First table contains some clob datatype columns and second table have carchar2 datatype.

Below are the structures of both tables;

SQL> DESC TBL_MAINSQL> DESC TBL_MAIN2
Name Type Name Type
----------------------------- ----------------------------------------- --------------
TXT_FEIN_NUMBER VARCHAR2(9) TXT_FEIN_NUMBER VARCHAR2(9)
TXT_QUOTE_NUMBER VARCHAR2(12)TXT_QUOTE_NUMBER VARCHAR2(12)
TXT_POLICY_NUMBER VARCHAR2(12)TXT_POLICY_NUMBER VARCHAR2(12)
TXT_AGENT_CODE VARCHAR2(10)TXT_AGENT_CODE VARCHAR2(10)

[code]....

The Table contains 5000 records. how to convert CLOB to VARCHAR2.

I used DBMS_LOB.SUBSTR but I received BUFFER TO SMALL ERROR.

View 10 Replies View Related

SQL & PL/SQL :: Trigger Not Inserting The Records?

Jun 21, 2012

I have written a trigger which insert and update on same table and the select statement for update and insert is same. My trigger update the record but doesn't insert data and doesn't throw error as well.

if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex.

create or replace
TRIGGER SRVCCLLS_RVW
AFTER INSERT OR UPDATE OR DELETE ON SD_SERVICECALLS
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE

[Code]....

if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex

View 2 Replies View Related

PL/SQL :: Inserting Records From Sql Loader

Mar 7, 2013

I am trying to insert record from sql loader but this is inserting only single row.

View 3 Replies View Related

SQL & PL/SQL :: How To Reduce Time Taken For Inserting Records

Jun 29, 2012

I have a view SV (say) which holds approximately 33,000 records. But, when I try to insert these many records into a table SV_T (say) it is taking huge amount of time i.e. 2-3 hrs (approx.)

how reduce the time taken for the insert.

View 3 Replies View Related

SQL & PL/SQL :: Inserting Bulk Records To The Table?

Jun 14, 2013

I want to insert bulk records to the table. I want to insert date rows for next 50 years in table ( from year 2001 to year 2050). I have following columns in my table :

YYYYMMDD MM/DD/YYYY Day of the week ( Monday, Tuesday etc) JulianDate

View 5 Replies View Related

SQL & PL/SQL :: Inserting New Record In Between List Of Records

May 2, 2012

I'd like to insert a record between the records which are already in the table. There are over 40000 records, and I would like to place this new record at 19th place. How may I do so?

Let's consider an example with 5 records:

Table data:

CREATE TABLE enum
(identifier VARCHAR2(64),
code VARCHAR2(512),
data VARCHAR2(4000))
/

[Code]...

So when we check the table, we get:

SELECT * FROM enum

IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7

What I would like to have when I run the select query:

SELECT * from enum

IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, L10 Halli, L10
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7

The only way I can think of is transfer the data till L9 line in a table (e.g enum_temp), insert L10 line and then transfer the remaining data in that table. But I can't seem to figure out the syntax of the query.

View 6 Replies View Related

SQL & PL/SQL :: Inserting More Than 30000 Records Into A Table?

Nov 16, 2012

How to insert more than 30000 records in a table using oracle procedure where I am having a table with number,varchar,data fields and columns like mpno,ename,sal,date of joining,data of leaving.

Data should populate using procedure.is there any way of doing it by procedure

View 6 Replies View Related

PL/SQL :: Inserting Successful Records Into One Table

Oct 9, 2013

I have a table of 10 records ,out of 10 records 2 records are having null values by using anonymous block  i need to  move the successful record(excluding null) into 'abc' tableand null records into 'err'table. 

View 3 Replies View Related

SQL & PL/SQL :: How To Handle NULL While Inserting Records In A Table

Feb 20, 2013

I have a data from the source table like this :

IdDate1 No1 Date2 No2

12/15/19959292 8/30/1997 11767
25/21/19932812 4/29/2002 2812
39/22/1978
410/11/19781157381 2/8/1979 929137
58/2/19891698430 11/30/1989 1365528
6
711/20/199541 1/23/1997 677
811/20/1995 1/23/1997 680

I want to insert into two separate tables using the following logic :

If date1 is not null or no1 is not null then insert into target_table1(id,date1,no1)
If date2 is not null or no2 is not null then insert into target_table2(id,date2,no2)

View 4 Replies View Related

SQL & PL/SQL :: Index Effect While Inserting Records In A Table

Dec 17, 2011

I have one indexed column in a table.for performance improval purpose i created index to that column .

my doubt is..it may decrease the performance while inserting data into that column?

View 5 Replies View Related

SQL & PL/SQL :: Inserting Number Of Records Loaded Into Log Table

Mar 8, 2013

I got Scenario that, i need to insert the number of records loaded in target table into the log table.

But the resultset is handled in the in the cursor. how to get the number of records the cursor handles.

/* Formatted on 08/03/2013 15:00:44 (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PROCEDURE DASHBOARD75.SP_STG_MLY_GL_HKP_V1_00
AS
CURSOR GL_HKP
IS
SELECT CAL.MTH_NM,
CAL.YEAR,
GLM.BU_CD,
[code].......

View 7 Replies View Related

SQL & PL/SQL :: Inserting Multiple Rows From Stored Procedure

Jun 20, 2012

Below are the data for rows that I want to insert into CUSTOMER_PRODUCT table from a stored procedure.

Instead of making round trips twice to insert these two rows, I looking for a way to pass in the data for both those rows and then insert them from within the stored procedure in one shot.

The stored procedure will be invoked by Java and .NET.

Sample Data for CUSTOMER_PRODUCT:

ROW 1:
CUSTOMER_ID : 1000
PRODUCT_TYPE : PROD123
IS_MEMERSHIP : Y
IS_EMAIL_SUBSCRIPTION: Y

ROW 2:
CUSTOMER_ID : 1001
PRODUCT_TYPE : PROD123
IS_MEMERSHIP : Y
IS_EMAIL_SUBSCRIPTION: Y

Question 1:
Should collection be used? (or) is there any other approach that could be utilized?

Question 2:
Are there any performance concerns in passing collection and iterating it to fetch value to insert into CUSTOMER_PRODUCT table?

I'm running Oracle 10g.

View 3 Replies View Related

Application Express :: Process Inserting Multiple Row

Feb 22, 2013

I have a process on my page which inserting some tables. one table may have more than one row. say

id1, id2, attr1, desc
1 1001, 1, abc
2, 1001, 4, xyz
3, 1001, 5, hhh

so on. id2 is a fk key in this table. for same id2 it may have more than one row. I have process and following is part of it but getting error that is PLS-00224: object 'P30_QUES' must be of type function or array to be used this way .

declare
v_count number;
v_code number;

begin

select count(*) into vv_count from table1; -- it may have 7 or 8 questions

FOR i IN 1 .. v_count LOOP

select code into v_code from table1 where code = :p30_ques(i);

if :p30_ques(i) is not null then
INSERT INTO table2 (id1, id2, attr1, desc)
values (null,fk_value, v_code,nvl(:p30_ques(i),null));
end if;
commit;
end loop;
end;

View 4 Replies View Related

SQL & PL/SQL :: Inserting Records Into Target Table From Three Source Tables?

Dec 14, 2012

I am trying to insert records into target table from three source tables by using function in a package and I am getting error as follows.

SQL> create or replace
2 PACKAGE
casadm.sis_load_cpl_sis_reb_pgm_hist
3 IS
4 /**********************************************************************
******************

[code]....

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'FN_LOAD1T_CPL_SIS_REB_PGM_HIST' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

View 16 Replies View Related

Fast Way Of Inserting 60 Millions Of Records From A View To A Table?

May 8, 2012

which is the fast way of inserting 60 millions of records from a view to a table.

method 1:

create table t_temp_table as select * from v_dump_data;

method 2:

through bulk collect

---Bulk_Collect With FORALL----------
DECLARE
TYPE srvc_tab IS TABLE OF t_temp_table%ROWTYPE;
l_srvc_tab srvc_tab := srvc_tab();
l_start_time NUMBER;
l_end_time NUMBER;
l_error_count NUMBER;

[code].....

View 4 Replies View Related

Inserting Into A Table By Selecting Multiple Rows Into A Single Row

Jul 27, 2012

I have a flat file as source wherein I am getting values like

Comp_id, Comp_name, ISIN, column_name, column_value

The structure is like this may contain multiple records like Comp_id, Comp_name, ISIN will be same, but column_name will contain the column_name to which its corresponding column_value needs to be populated to.

E.g. of Feed File -

Comp_id, Comp_name, column_name, column_value

1,HSBC,branch_name,HSBC-DELHI
1,HSBC,branch_add,24-Lajpat Nagar
1,HSBC,branch_phone,2322322
2,HSBC,branch_name,HSBC-MUMBAI
2,HSBC,branch_add,24Andheri
2,HSBC,branch_phone,4445221
2,HSBC,branch_postalcode,400023

Target table structure

Comp_id, Comp_name, branch_name, branch_add, branch_phone, branch_postalcode

I need to insert the above data to a table by selecting data from above scenario.

View 10 Replies View Related

Server Utilities :: SQL Loader - How To Ignore Inserting Duplicate Records

Apr 9, 2010

I'm calling sql loader recursively to load data from CSV files which has thousands of records in each file. If there are any duplicate records, the sql loader terminates with ORA00001. My query is how to ignore inserting duplicate records and continue with the load.

Most of the posts in forums suggests to use skip command. But i do not think that is a wise option in my case as we cannot predict the maximum error count. more over I have set up ERROR=0 in my code so that the code terminates in case thers is a data error.

any other way to ignore inserting duplicate records into the tables.

View 7 Replies View Related

SQL & PL/SQL :: Inserting 300 Million Records Into Oracle Table Using Database Links?

Dec 19, 2011

I would like to know if we can insert 300 million records into an oracle table using a database link. The target table is inproduction and the source table is in development on different servers.The target table will be empty and have its indexes disabled before the insert. if this can be accomplished in less than 1 hour.

View 26 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 :: Carriage Return Line Feed Multiple Times In Same CLOB

Jan 31, 2012

I'm looking for a way to make CRLFs show in a CLOB.I'm feeding the insert statement with a concatinated string like this:

insert into table(Data) values (MyConc)

(MyConc is a string put togheter by another application)

Because of this I can't use the "|| chr(13) || chr(10) ||" because I only have that one concatinated string.Is there a character I can set in my string that automatically translates to a CRLF?

My CLOB-data should look something like this:

1;blue;Woodstock;;
34;giant;squid;attack;
5;blue;squid;;

And in this case the "MyConc" would look like this "1;blue;Woodstock;;[X]34;giant;squid;attack;[X]5;blue;squid;;[X]

where [X]=the character I need for CRLF =)

View 11 Replies View Related

SQL & PL/SQL :: How To Extract Full Text From Clob Where Having Distinct Select

May 23, 2013

I am only able to extract only 4000 characters from the clob column "DESCRIPTION".how to get more characters or max for that column with the same query concept?

select distinct
o.id "Organization ID",
en.entity_id "Contact ID",
en.entity_cd "Note Entity Code",
ed.entity_name "Note Entity",
en.entity_id "Note Entity_id",

[code]....

View 28 Replies View Related

JDeveloper, Java & XML :: Select Entire Message From CLOB - ORA-22806

Jun 20, 2013

I have a "simple" query that needs to extract all of the XML messages stored in a CLOB column in a table for a data deidentification project. This query

select c.cc_data_area.getClobVal() as cc_xml
from ci_cc c;

This query returns an ORA-22806: not an object or REF

cc_data_area is the clob column, and contains messages longer than 4000 characters.

View 1 Replies View Related







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