Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

Re: PL/SQL Question

Melanie Caffrey

2004-08-17

Replies:
Hi Alan,

The functionality you'd like doesn't appear to be
supported in 8.1.7.

Though it's not quite as nice and neat as the single
statement you have below, would something like the
following help get you partially there?

declare
 type my_type is table of number;
 type my_type_string is table of varchar2(10);
 my_list     my_type;
 my_list_string my_type_string;
begin
 insert into test_bulk (x, y)
 select rownum, substr(object_name,1,10)
      bulk collect into
  my_list, my_list_string
   from all_objects
  where rownum < 10;
 end;

Then insert them into test_bulk using "forall i"
functionality.

Yeah, I know. Not quite what you were looking for.

Cheers,
Melanie

--- "Davey, Alan" <Alan.Davey@(protected)>
wrote:

>
> Hi,
>
> Is it possible to use bulk collect in the "returning
> into" clause when
> doing an insert as select?
>
> Oracle 8.1.7
>
> SQL> create table test_bulk (x integer, y
> varchar2(10));
>
> Table created.
>
> SQL> ed
> Wrote file afiedt.buf
>
>  1 declare
>  2   type my_type is table of number;
>  3   my_list my_type;
>  4 begin
>  5   insert into test_bulk (x, y)
>  6   select rownum, substr(object_name,1,10)
>  7   from all_objects
>  8   returning x bulk collect into my_list;
>  9* end;
> SQL> /
> declare
> *
> ERROR at line 1:
> ORA-00933: SQL command not properly ended
> ORA-06512: at line 5
>
> The returning clause seems to work just fine for
> other DML statements,
> so I know that the datatypes are declared correctly:
> SQL> insert into test_bulk (x,y)
>  2 select rownum, substr(object_name,1,10)
>  3 from all_objects
>  4 where rownum < 10;
>
> 9 rows created.SQL> ed
> Wrote file afiedt.buf
>
>  1 declare
>  2   type my_type is table of number;
>  3   my_list my_type;
>  4 begin
>  5   update test_bulk
>  6   set y =3D 'XXX'
>  7   returning x bulk collect into my_list;
>  8   for i in my_list.first..my_list.last
>  9   loop
> 10    dbms_output.put_line(my_list(i));
> 11   end loop;
> 12* end;
> SQL> /
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
>
> PL/SQL procedure successfully completed.
>
> Looking at the documentation, I don't see mention
> that bulk collect is
> not allowed with a insert as select statement, but
> all examples only
> seem to show it when using the values clause. =0D
>
> My goal is to insert a bunch of records and keep a
> list of the primary
> key Ids, possibly do some other actions with that
> list, and then finally
> return that list to the calling program.
>
> Short of adding an indicator column for these new
> inserts and then
> having to issue another select to retrieve the Ids,
> is there anyway to
> accomplish what I am trying to do more cleanly?
> Hopefully, I am just
> missing something obvious.
>
> Thanks,
>
> Alan
>
>
> "This information in this e-mail is intended
> solely=0D
> for the addressee and may contain information=0D
> which is confidential or privileged. Access to this
> e-mail by anyone else is unauthorized. If you=0D
> are not the intended recipient, or believe that=0D
> you have received this communication in error,=0D
> please do not print, copy, retransmit,=0D
> disseminate, or otherwise use the information.=0D
> Also, please notify the sender that you have=0D
> received this e-mail in error, and delete the=0D
> copy you received."
>
----------------------------------------------------------------
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
>
----------------------------------------------------------------
> To unsubscribe send email to:
> oracle-l-request@(protected)
> put 'unsubscribe' in the subject line.
> --
> Archives are at
> http://www.freelists.org/archives/oracle-l/
> FAQ is at
> http://www.freelists.org/help/fom-serve/cache/1.html
>
-----------------------------------------------------------------
>

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------