-none-

Links

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

-none-

-none-

2004-06-16 - By -not available-

Reply: << 111 112 113 114 115 116 117 118 119 120 >>

If that is the case, I would import the data to a temp table.
CREATE TABLE temp
(id varchar2(1),
Generic_num varchar2(20),
Generic_Name varchar2(20));

Then write a stored procedure to do the insert statements to your real
tables.

Declare a cursor of
Cursor cur is
SELECT * from temp;
And some variables to store your data:

V_dept_no varchar2(50);

Then, in your procedure body:

FOR cur_rec IN cur LOOP
IF cur_rec.id = '1 ' THEN
/*new department*/
V_dept_no:= cur_rec.generic_num;
INSERT INTO dept (deptno, deptname)
VALUES (cur_rec.generic_num, cur_rec.generic_name);

ELSIF cur_rec.id= '2 ' THEN
/*employee record*/
INSERT INTO emp ( deptno, empno, empname)
VALUES (V_dept_no, cur_rec.generic_num, cur_rec.generic_name);
END IF;
END LOOP;
COMMIT;

Please note:
This assumes that your first record is a department record and that the way
the data retrieved from the cursor is in the same order that it was in the
datafile used for the import.

HTH,
Melissa Paglia

-- --Original Message-- --
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]
On Behalf Of Kevin Lange
Sent: Wednesday, June 16, 2004 4:25 PM
To: 'oracle-l@(protected) '
Subject: SQL Loader Question on 8.1.7

Evening;
I figured if anyone would know ... you all would. Here is a simple
example given in the Oracle SQLLDR manual:

You have conditional data in a singl file to be loaded into different
tables:

1 50 Manufacturing
2 1100 Smith
2 1200 Snyder
1 60 Shipping
2 1121 Stevens

Based on the Record ID, you want either the Department or the Employee
table to be loaded:

Into Table dept
When recid = 1
(recid Position(1:1) Integer External,
deptno Position(3:4) Integer External,
depname Position(8:21) Char)
Into Table emp
When recid = 2
(recid Position(1:1) Integer External,
empno Position(3:6) Integer External,
empname Position(8:17) Char
commission Position(19:25))

That is fairly simple.

It gets more difficult when you ask the question .... Can you carry the
data from one of the When sections over into another section ?? i.e. Is
there a way to make a presistant variable that is set in one section and
then used in another section ??

Using the sample above, we need to have the "Department Number " (depno)
inserted into the EMP table without having to add it to each individual
record where recid = 2 since it is already on the record where recid = 1.

Any help or direction to go for samples if possible would be greatly
appreciated.

Kevin
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --

"Bringing people together to advance their lives. "

-- ---_=_NextPart_001_01C453ED.A069DB7F
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN " >
<HTML >
<HEAD >
<META HTTP-EQUIV=3D "Content-Type " CONTENT=3D "text/html; =
charset=3Dus-ascii " >
<META NAME=3D "Generator " CONTENT=3D "MS Exchange Server version =
5.5.2653.12 " >
<TITLE >RE: SQL Loader Question on 8.1.7 </TITLE >
</HEAD >
<BODY >

 Hi Kevin- 


 From the sample data you provided, it looks like the =
only way to know which department the employee is from is that the =
department precedes the employee record. Not an easy =
relation. 

 If that is the case, I would import the data to a =
temp table. 
 CREATE TABLE temp 
 (id varchar2(1), 
 Generic_num varchar2(20), 
 Generic_Name =
varchar2(20)); 


 Then write a stored procedure to do the insert =
statements to your real tables. 


 Declare a cursor of 
 Cursor cur is 
 SELECT * from temp; 
 And some variables to store your data: 


 V_dept_no varchar2(50); 


 Then, in your procedure body: 


 FOR cur_rec IN cur LOOP 
 IF cur_rec.id =3D '1 ' THEN 
 /*new =
department*/ 
 V_dept_no:=3D =
cur_rec.generic_num; 
 INSERT INTO dept =
(deptno, deptname) 
 VALUES =
(cur_rec.generic_num, cur_rec.generic_name); 


 ELSIF cur_rec.id=3D '2 ' THEN 
 /*employee =
record*/ 
 INSERT INTO emp ( =
deptno, empno, empname) 
 VALUES =
(V_dept_no, cur_rec.generic_num, cur_rec.generic_name); 
 END IF; 
 END LOOP; 
 COMMIT; 


 Please note: 
 This assumes that your first record is a department =
record and that the way the data retrieved from the cursor is in the =
same order that it was in the datafile used for the import. 

 HTH, 
 Melissa Paglia 


 -- --Original Message-- -- 
 From: oracle-l-bounce@(protected) [ <A =
HREF=3D "mailto:oracle-l-bounce@(protected) " >mailto:oracle-l-bounce@(protected)=
elists.org </A >] On Behalf Of Kevin Lange 
 Sent: Wednesday, June 16, 2004 4:25 PM 
 To: 'oracle-l@(protected) ' 
 Subject: SQL Loader Question on 8.1.7 


 Evening; 
 I figured if anyone would know ... you all =
would. Here is a simple 
 example given in the Oracle SQLLDR manual: 


 You have conditional data in a singl file to =
be loaded into different 
 tables: 
 
 1 50 =
Manufacturing 
 2 1100 Smith =

 2 1200 Snyder 
 1 60 Shipping 
 2 1121 Stevens =



 Based on the Record ID, you want either the =
Department or the Employee 
 table to be loaded: 


 Into Table dept 
 When recid =3D =
1 
 (recid Position(1:1) =
Integer External, 
 deptno =
Position(3:4) Integer External, 
 depname =
Position(8:21) Char) 
 Into Table emp 
 When recid =3D =
2 
 (recid Position(1:1) =
Integer External, 
 empno =
Position(3:6) Integer External, 
 empname =
Position(8:17) Char 
 commission =
Position(19:25)) 


 That is fairly simple. 


 It gets more difficult when you ask the =
question .... Can you carry the 
 data from one of the When sections over into another =
section ?? i.e. Is 
 there a way to make a presistant variable that is =
set in one section and 
 then used in another section ?? 
 
 Using the sample above, we need to have the =
"Department Number" (depno) 
 inserted into the EMP table without having to add it =
to each individual 
 record where recid =3D 2 since it is already on the =
record where recid =3D 1. 

 

 Any help or direction to go for samples if possible =
would be greatly 
 appreciated. 


 Kevin 
 -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----=
- 
 Please see the official ORACLE-L FAQ: <A =
HREF=3D "http://www.orafaq.com " =
TARGET=3D "_blank " >http://www.orafaq.com </A > 
 -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----=
- 
 To unsubscribe send email to: =
oracle-l-request@(protected) 
 put 'unsubscribe ' in the subject line. 
 -- 
 Archives are at <A =
HREF=3D "http://www.freelists.org/archives/oracle-l/ " =
TARGET=3D "_blank " >http://www.freelists.org/archives/oracle-l/ </A > =

 FAQ is at <A =
HREF=3D "http://www.freelists.org/help/fom-serve/cache/1.html " =
TARGET=3D "_blank " >http://www.freelists.org/help/fom-serve/cache/1.html </=
A > 
 -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----=
-- 

 

 "Bringing people together to advance their =
lives." =20


</BODY >
</HTML >
-- ---_=_NextPart_001_01C453ED.A069DB7F--
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --