RE: FORALL / bulk bind insert

ex by tables: v_order_event_idmessage_contentorder_event_id%TYPE INDEX BY BINARY_INTEGER; v_message_field_id message_field_id%TYPE INDEX BY BINARY_INTEGER; v_message_content message_content%TYPE INDEX BY BINARY_INTEGER; FORALL i in 1..fcount insert

RE: FORALL / bulk bind insert

; v_message_content message_content%TYPE INDEX BY BINARY_INTEGER; FORALL i in 1..fcount insert into message_content values (v_order_event_id(i), v_message_field_id(i), v_message_content(i)); Kevin -Original

FORALL / bulk bind insert

I am trying, for the first time, to use bulk binding on insert. Assuming I have a type defined as MESSAGE_CONTENT%ROWTYPE INDEX BY BINARY_INTEGER, and a variable, v_message_content, of that type: I've tried the insert as FORALL i in 1..fcount insert into message_content v

RE: Insert Cardinalities into the data dictionary directly

If the production environment is already available, you could use DBMS_STATS.EXPORT_TABLE_STATS on the production table and import those stats into your development instance. Regards, Chris Gait On 18 Apr 2001, at 11:00, Murali Vallath wrote: > Thanks for the feedback, I am coming from the O

RE: Ora - 4091 error while trying to insert a record into a table - RESOLVED

Hi Gurus, I got the solution. Here is the modified code: create or replace trigger trig_dept1 after insert on dept for each row begin insert into dept1(deptno, dname, loc, date_time) values(:new.deptno, :new.dname, :new.loc, sysdate); end; Thanks Rob Fegan and Amol Joshi

Ora - 4091 error while trying to insert a record into a table

Dear DBA Gurus, I have Table A and Table B whose structures are almost similar except Table B has a date-time field column. Whenever I insert a new record into Table A I want to insert the same record into the Table B along with the date-time stamp at which the new record is inserted

RE: Getting ORA-01732 trying to insert into table (NOT view)

the former production server before it was converted from NT to Novell. I get the same error on some other tables, too, but I can insert on some as well. There are no specific privileges for this table or its columns as shown in dba_tab_privs or dba_col_privs. It gives me the same error if

RE: Getting ORA-01732 trying to insert into table (NOT view)

Ian, You are correct. If you're connected as FARS_OWNER the public synonym will not be referenced since you own a table named MFGALIASTYPE. I suspect the error is not being signaled by the insert itself, but by some underlying recursive sql. Is there a trigger on this table? If not

RE: Getting ORA-01732 trying to insert into table (NOT view)

SN 60 S F SN Sensor SN 50 6 rows selected. SQL> insert into MFGALIASTYPE values ('X', 'F', 'SN', 'Test', 80); insert into MFGALIASTYPE values ('X', 'F', 'SN', 'Test', 80) * ERROR at line 1:

Re: Getting ORA-01732 trying to insert into table (NOT view)

ghter Board SN 60 S F SN Sensor SN 50 6 rows selected. SQL> insert into MFGALIASTYPE values ('X', 'F', 'SN', 'Test', 80); insert into MFGALIASTYPE values ('X', 'F', 'SN', 'Test', 80) * ERROR at li

Re: Getting ORA-01732 trying to insert into table (NOT view)

er SN 20 D F SN Daughter Board SN 60 S F SN Sensor SN 50 6 rows selected. SQL> insert into MFGALIASTYPE values ('X', 'F', 'SN', 'Test', 80); insert into MFGALIASTYPE values ('X', 'F', 'SN', 'Test', 80)

Getting ORA-01732 trying to insert into table (NOT view)

TTRIBUTE PROMPTNAME SORTORDER -- - -- --- - T F SN Temic Accelerometer SN 30 T SN Actual Serial Number 10 A F SN Accelerometer SN 40 C F SN Customer SN 20 D F SN Daughter Board SN 60 S F SN Sensor SN 50 6 rows selected. SQL> insert into MFGALIASTYPE values ('X', 'F', 'SN'

RE: ORA - 1008 error when trying to insert data into a table

con=getConnection(); con.setAutoCommit(false); /* stmt = con.createStatement(); strSQL = "INSERT INTO SITE VALUES (" +pk_site_id +", "+fk_category +", empty_

ORA - 1008 error when trying to insert data into a table

con=getConnection(); con.setAutoCommit(false); /* stmt = con.createStatement(); strSQL = "INSERT INTO SITE VALUES (" +pk_site_id +", "+fk_category +", empty_clob(

Re: Insert from a table to another

You can do a simple insert/select statement within pl/sql declare begin insert into table_a (select * from table_b); end; / if all the columns are in the same order in both tables. If not list the columns out. James --- [EMAIL PROTECTED] wrote: > Any one whom can give me a good advice, o

Re: Insert from a table to another

CREATE MY_TABLE(TEST1 NUMBER,TEST2 VARCHAR2(10)); DELETE FROM MY_TABLE; INSERT INTO MY_TABLE SELECT VALUE1,STRING2 FROM MY_OTHER_TABLE WHERE VALUE3 = 7; This insert would insert into MY_TABLE the columns value1 and string2 from the table my_other_table where the column value3 is equal to 7

Insert from a table to another

Any one whom can give me a good advice, on a pl/sql procedure, that delete data from table a, then runs a query from table b and and that insert this query into table a. Pleas help me with a good example. Thanks Roland Sköldblom -- Please see the official ORACLE-L FAQ: http

RE: Insert ... nologging

Thank you. I wish I would have realized that the nologging was becoming an alias. <:-( Yosi > -Original Message- > From: Vadim Gorbounov [mailto:[EMAIL PROTECTED]] > Sent: Thursday, April 19, 2001 5:30 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: In

RE: Insert ... nologging

Yosi, I suggest you the following syntax for direct path insert with nologging option: insert /*+ APPEND NOLOGGING */ into tab1 select from tab2; The word NOLOGGING in your first insert is treated as table alias (and really does nothing with nologging), i believe, otherwise the statement

Insert ... nologging

Hello all, I'm trying to do inserts with NOLOGGING. It works if I don't specify insert columns OR if I have no where clause. If I have EITHER insert column specification OR a where clause, Oracle can't interpret the SQL. HELP PLEASE!! The following syntax works fine:

Re: PL/SQL-delete, insert

delete from my_table; insert into my_table values(1,'row 2',3); The delete without a where clause will delete all rows from the table. >>> [EMAIL PROTECTED] 04/19/01 04:20AM >>> Can anyone give me an example of an pl/sql-procedure, that first delete rows from a

PL/SQL-delete, insert

Can anyone give me an example of an pl/sql-procedure, that first delete rows from a table and afterwards insert values in that table. This should be in the same procedure. Thanks for the help I can get on this, a good example. Roland Sköldblom -- Please see the official ORACLE-L FAQ: http

RE: Insert Cardinalities into the data dictionary directly

One of interesting uses of this feature is creation of statistics for temporary tables which cannot be done otherwise. It is Tom Kite's idea. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 1:15 PM To: Multiple recipients of list ORACLE-L Take a look at dbms_stats.set_c

RE: Insert Cardinalities into the data dictionary directly

Thanks for the feedback, I am coming from the Oracle Rdb world, where these row counts/cardinialities could be inserted into the data dictionary to simulate the optimizer behaviour similar to a production environment. I see your point, this could be a negative impact to the optimizer. Thanks f

RE: Insert Cardinalities into the data dictionary directly

Take a look at dbms_stats.set_column_stats and dbms_stats.set_table_stats. The latter allows you to set the number of rows, the size of the table and such ; the first allows you to set the number of distinct values for a column the number of nulls, etc. I'm not sure what you are trying to ac

Insert Cardinalities into the data dictionary directly

I am trying again -- To obtain a good analysis of a SQL statement especially in a new development environment, based on the environment that it is to be deployed on, it would good to sometime reflect/simulate the production volume. Is there a way to input table cardinalities directly in

RE: Insert

Just to add my $0.02 I was working on a large ODS project where we had to process 8+ million records each night into an Oracle 8i database sitting on a RAID-5 disk array. At the start of the processing we trucated the table. We used parallel direct-path SQL*Loader (12 instances). We had SQL

Re: Insert

Yes inserting into a table with an index does cause degradation in the process, but it is very unrealistic to drop and rebuild the index every time you do an insert, unless it is a batch process that runs when no other processing is being done. Also, deleting from the index causes the index to be

RE: Insert

AIL PROTECTED]] Sent: Tuesday, March 27, 2001 3:31 AM To: Multiple recipients of list ORACLE-L Subject:Re: Insert well Kevin Kostyszyn, this is true that often we ignore such small issues which sometime impact the performance a lot.

Re: Insert

well Kevin Kostyszyn, this is true that often we ignore such small issues which sometime impact the performance a lot. ya, it's good if we do inserts and then rebuild the indexes, but generally not done during usual work., since every time u insert records , oracle rebuilds the index itself

Insert

Hi DBA's, One of my developers just asked me a question, and honestly I don't know what he's talking about. But, it made me think of my own that I do know the answer to or at least I used to:( Anyway, when doing an insert into a table, doesn't it increase perfo

Bulk insert or cursor processing

pplication code(Java) after each 5 records. Then for the following commands: insert into tableb select * from tablea; we have to write a stored procedure, either use a cursor: cursor cur_a as select * from tablea; count int :=0; for rec in cur_a LOOP insert into tableb values( rec.fields); count :

RE: Insert without telling column names

estions >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: Insert without telling column names >Date: Tue, 20 Mar 2001 14:18:13 -0800 > > >Well of course it's a bad idea, I thought >

RE: Insert without telling column names

Well of course it's a bad idea, I thought that was obvious. ;) Jared On Tue, 20 Mar 2001, Jacques Kilchoer wrote: > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > > > How can I create an insert statement without tel

Re: Insert without telling column names

Hi all - I stand (sit?) before you with egg on my face. I KNOW you can insert without naming column names if you're filling the entire row, I just didn't consider that this might be what Roland was attempting to accomplish. As Regina and Jacques both pointed out, if your package, pr

Re: Insert without telling column names

You are misinformed. If you have a table of the form col1varchar2(1) col2 number col3 date You can insert into it with the insert statement INSERT INTO MYTABLE VALUES('X',23,SYSDATE); As long as ALL the columns are included in the values clause in the order they were crea

Re: Insert without telling column names

At 08:36 AM 3/20/01 -0800, you wrote: >Roland, > >You can't. There are a number of ways to really cut down on the code >required to do an insert, but if you think about it, how will Oracle >know where the data is supposed to go if you don't give it the column >name(at

RE: Insert without telling column names

Roland, Check out the SQL manual, the syntax diagram clearly illustrates that you don't need to explicitly name the column names as long as you are inserting into all of the columns in the order they are defined in the data dictionary. insert into table_A values ('Hi', '

RE: Insert without telling column names

Title: RE: Insert without telling column names > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > How can I create an insert statement without telling all the > names of the columns? > Give an example, please. As Jared mentioned, if

Re: Insert without telling column names

Here is what my sqlplus training (Oracle) says: Inserting New Rows Insert a new tow containing values for each column. Optionally lisgt the columsn in the INSERT clause. HTH, Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]&g

Re: Insert without telling column names

On Tue, 20 Mar 2001 [EMAIL PROTECTED] wrote: > Hallo, > > How can I create an insert statement without telling all the names of the columns? > Give an example, please. > > Roland Sköldblom > Roland, You can omit column names only if you are inserting into all columns of th

Re: Insert without telling column names

Roland, You can't. There are a number of ways to really cut down on the code required to do an insert, but if you think about it, how will Oracle know where the data is supposed to go if you don't give it the column name(at some point)? If you're using 8i, there is a direct load

Insert without telling column names

Hallo, How can I create an insert statement without telling all the names of the columns? Give an example, please. Roland Sköldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858

Re: to insert into blob

hi, gurus: i read the article of early time e and tried my self, hoping to insert a bin file into a lob column of the database.but failed. my env: win2000 prof+oracle817. SQL> desc lobtable Name Null?T

Re: to insert into blob

- Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Monday, February 19, 2001 23:40 > I have a wav file and I have to insert it into a table with column blob > type. > How do I do this. > If u're working with JDBC then

to insert into blob

I have a wav file and I have to insert it into a table with column blob type. How do I do this. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051

RE: HELP - Insert/Update trigger

>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: HELP - Insert/Update trigger >Date: Tue, 13 Feb 2001 10:51:22 -0800 > >Dang it, >Brian you're right, I didn't read the original post thoroughly. > >Instead of batch inserts, do batch

RE: HELP - Insert/Update trigger

I have never worked directly with "instead of" triggers, but their intent is to be used on a non-updateable view. For instance: SQL> create view all_emps as select * from emps UNION select * from archived_emps; SQL> insert into all_emps (...); The RDBMS then says "Which

RE: HELP - Insert/Update trigger

Yes, it can be done, I'd write the trig to do a select on that PK and if %FOUND is true, update otherwise in the exception handler for that block, do the insert. begin .. begin select PK from table; UPDATE exception INSERT end .. end Dan -Original Message-

RE: HELP - Insert/Update trigger

Dang it, Brian you're right, I didn't read the original post thoroughly. Instead of batch inserts, do batch updates and in the exception handler, do the insert. Or you could use the %FOUND flag based on a select PK from table. Dan -Original Message- Sent: Tuesday, February 1

RE: HELP - Insert/Update trigger

What is the purpose of the non-updateable view? -FS >From: "Norrell, Brian" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: HELP - Insert/Update trigger >Date: Tue, 13 Feb 2001 09:5

RE: HELP - Insert/Update trigger

Not with a normal table trigger. Try creating a non-updateable view with an "instead of" trigger for the insert. Brian Norrell Senior Software Engineer QuadraMed 972-831-6600 -Original Message- Sent: Tuesday, February 13, 2001 9:26 AM To: Multiple recipients of list ORACLE-L

HELP - Insert/Update trigger

Listers, I am attempting to write a trigger on a table that will have batch inserts nightly. I want each insert to check to see if a record with that primary key exists, and if it does, update the record. If the primary key does not yet exist in the table, insert the record. The batch

Re: How to insert data in LOBs

No, you can't just use an insert statement to do this. There is a lot of information out on metalink about this. Take a look at the dbms_lob package or sql*loader. - Brian --- Saurabh Sharma <[EMAIL PROTECTED]> wrote: > hi all, > i want to know how to insert values i.e. te

Re: How to insert data in LOBs

thanks it worked fine. but how if i want to insert some file(text,word) or some image file to a blob. and i did'nt understood how to querry such table with LOBs. thanks again for the guidance. saurabh - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL P

RE: How to insert data in LOBs

If you use CLOB, you can insert text in the same way as with varchar2. If you have BLOB, you'll probably need the function utl_raw or dbms_lob. However with BFILE, you should create a directory with the stmt: CREATE DIRECTORY and insert using BFILENAME expression. Then you have the fun

RE: How to insert data in LOBs

sday, February 08, 2001 8:32 AM To: Multiple recipients of list ORACLE-L hi all, i want to know how to insert values i.e. text/word or images in a table's LOB column. can we insert them with the regular insert command. please help in this regard. thanks in advance.. saurabh FCS Software Solu

How to insert data in LOBs

hi all, i want to know how to insert values i.e. text/word or images in a table's LOB column. can we insert them with the regular insert command. please help in this regard. thanks in advance..   saurabh FCS Software Solutions Ltd. Noida.(India) [EMAIL PROTECTED]

Insert data into LOB column

Hi! I am new to working with LOBs. I want to insert data (through SQL or PL/SQL) into a table that looks like this: column1 varchar2(100) column2 BLOB column3 number How can I insert data into that table directly? insert into table (column1, colum2, column3) values ('sample

Insert data into LOB column

> Hi! > > I am new to working with LOBs. I want to insert data (through > SQL or PL/SQL) > into a table that looks like this: > > column1 varchar2(100) > column2 BLOB > column3 number > > How can I insert data into that table directly? > > inse

Re: Insert data into LOB column

- Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, February 07, 2001 02:35 > > I am new to working with LOBs. I want to insert data (through SQL or PL/SQL) > into a table that looks like this: > > column1 varchar2(

Insert data into LOB column

Hi! I am new to working with LOBs. I want to insert data (through SQL or PL/SQL) into a table that looks like this: column1 varchar2(100) column2 BLOB column3 number How can I insert data into that table directly? insert into table (column1, colum2, column3) values ('sample

Re: Does Oracle Allow Retrieval of Parsed Insert Statement??

ithout the primary key. If you do have it, such as EMPID for the EMP table and EMPID is provided by a sequence on firing a trigger, you can use this SQL to get it: insert into EMP (name) values ('John') returning EMPID into myPLSQLvar; Yong Huang [EMAIL PROTECTED] you wrote: We have a situa

RE: Does Oracle Allow Retrieval of Parsed Insert Statement??

Title: RE: Does Oracle Allow Retrieval of Parsed Insert Statement?? You do not need OCI to do it. Returning supported everywhere one can use SQL.   Alex Hillman -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 31, 2001 1:57 PMTo

RE: Does Oracle Allow Retrieval of Parsed Insert Statement??

Title: RE: Does Oracle Allow Retrieval of Parsed Insert Statement?? >-Original Message- >From: Alex Hillman [mailto:[EMAIL PROTECTED]] >Sent: mercredi, 31. janvier 2001 13:49 > >You do not need OCI to do it. Returning supported >everywhere one can use SQL.   T

RE: Does Oracle Allow Retrieval of Parsed Insert Statement??

Title: RE: Does Oracle Allow Retrieval of Parsed Insert Statement?? > -Original Message- > From: Sam Bootsma [mailto:[EMAIL PROTECTED]] > Sent: mercredi, 31. janvier 2001 10:31 > To: Multiple recipients of list ORACLE-L > Subject: Does Oracle Allow Retrieval of Parsed I

RE: Does Oracle Allow Retrieval of Parsed Insert Statement??

Half-solved: We have discovered that a "returning" clause can be added after the "values" clause in the INSERT statement to return a rowid immediately after a row is inserted. However, it would still be helpful to have a procedure or Oracle OCI call to Parse a sql stateme

Does Oracle Allow Retrieval of Parsed Insert Statement??

We have a situation where we insert rows into a table, without having knowledge of the primary key. One of our developers needs to determine the rowid of such a row (primary key unknown) immediately after the row is inserted into the table. Does anybody know if the rowid can be retrieved (or

<    1   2   3   4