-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)
<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
<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/
<http://www.freelists.org/archives/oracle-l/ >
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
<http://www.freelists.org/help/fom-serve/cache/1.html >
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --

"Bringing people together to advance their lives. "

-- ---_=_NextPart_001_01C453EF.9D4488A0
Content-Type: text/html; charset= "iso-8859-1 "

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML > <HEAD >
<META HTTP-EQUIV= "Content-Type " CONTENT= "text/html; charset=iso-8859-1 " >
<TITLE >RE: SQL Loader Question on 8.1.7 </TITLE >

<META content= "MSHTML 6.00.2800.1400 " name=GENERATOR > </HEAD >
<BODY >
<DIV > You
are right. The dependent data (in this case Employee Data) always follows
the Parent Data (in this case the Department Data). Thats why I was
wondering if a variable could be set inside of SQLLDR to hold the data for the
"Department Number " from the Parent Data so it could be used in the Dependent
Data. </DIV >
<DIV > </DIV >
<DIV > Of
couse a procedure could be written to do this just fine. But, all of
the facilities and dependent code already exists to make these loads using
SQLLDR. We wanted to do the least work possible i.e. only change a SQLLDR
control file intead of changing all the scripts that run
this. </DIV >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > -- --Original Message-- -- From: Paglia, Melissa
[mailto:mpaglia@(protected)] Sent: Wednesday, June 16, 2004 5:03
PM To: oracle-l@(protected) Subject: RE: SQL Loader
Question on 8.1.7 </DIV >
 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 = '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) [ <A
href= "mailto:oracle-l-bounce@(protected) " >mailto:oracle-l-bounce@(protected) </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 = 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: <A
href= "http://www.orafaq.com " target=_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= "http://www.freelists.org/archives/oracle-l/ "
target=_blank >http://www.freelists.org/archives/oracle-l/ </A > FAQ is at <A
href= "http://www.freelists.org/help/fom-serve/cache/1.html "
target=_blank >http://www.freelists.org/help/fom-serve/cache/1.html </A > 
 -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- 
 
 "Bringing people together to advance their
lives. " </BLOCKQUOTE > </BODY > </HTML >

-- ---_=_NextPart_001_01C453EF.9D4488A0--
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --