Home » RDBMS Server » Server Utilities » SQL*Loader: constraints and PL/SQL function (merged by LF)
SQL*Loader: constraints and PL/SQL function (merged by LF) [message #255512] Wed, 01 August 2007 01:51 Go to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

hi experts


i am new with sql loader ... and i am stuck in one problem please help me out

my table structure is like this
-------------------------------

com_cd varchar2(10),--- forign key con. with com_info
trans_date date,
descript varchar2(10),
amount number(14,2),
trans_cd varchar2(10),--- Primary key (com_Cd,trans_no)
--- fill with LPAD(ROWNUM, 10, '0')
dk_no varchar2(10),--- Unique


DATA is like
------------
"01",20091205,"machanics",700,100001,"fd-50"
"01",20070405,"tecnicals",500,100002,"df-56"
"01",20060205,"machanics",400,100003,"fd-46"
"01",20070905,"machanics",800,100004,"fd-55"
"01",20070304,"research",700,100005,"fd-54"
"01",20070501,"research",700,100006,"fd-79"
"01",20070604,"research",700,100007,"fd-15"
"01",20070909,"tecnicals",600,100008,"fd-65"

Script is like
--------------

load data
infile 'M:\dbf_only\ms_trans.TXT'
into table ms_trans
fields terminated by ","
optionally enclosed by '"'
trailing nullcols
(
com_Cd char(10) "01",
trans_Date date 'yyyymmdd'
descript,
amount,
trans_cd char(10) "LPAD(ROWNUM, 10, '0')",
dk_no
)


error shows
------------
Record 1: Rejected - Error on table MS_TRANS.
ORA-02291: integrity constraint (IGLOO.MS_TRANS_FK21185950593156) violated - parent key not found



my question is how to avoid this error .. while i am giving all the data in all rows and columns .. and nothing i left blank .. i know this is an error for forign key .. but u see i m putting data also .. i also trid this by removing fk constraint .. then it shows error on uniqe and when i tried to remove uniqe constraint is shows error on Pk.

Any solutions ..??? Please help me out .. in breif


regards
anwer

Re: sql loader and constraints [message #255516 is a reply to message #255512] Wed, 01 August 2007 01:55 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

ORA-02291: integrity constraint (IGLOO.MS_TRANS_FK21185950593156) violated - parent key not found


U should know what to do?
Re: sql loader and constraints [message #255518 is a reply to message #255516] Wed, 01 August 2007 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju,
Use YOU and not "u".
We already told you that several times.

Anwer,
Please read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.

It is a foreign key from what column to what column/table?

Regards
Michel
Re: sql loader and constraints [message #255520 is a reply to message #255516] Wed, 01 August 2007 02:05 Go to previous messageGo to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

respected seniour

give me any hint ..
Re: sql loader and constraints [message #255524 is a reply to message #255520] Wed, 01 August 2007 02:10 Go to previous messageGo to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

dear michal

if you see my first line of structure you found

com_cd varchar2(10),--- forign key con. with com_info

its a forign key connected with com_info table


Re: sql loader and constraints [message #255529 is a reply to message #255524] Wed, 01 August 2007 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If for you "forign key con. with com_info" is clear, it is not for me that this means that com_cd is a foreign key that references something in the com_info table.
You want an advice then insert the correct row ('01') in com_info table.

Regards
Michel
Re: sql loader and constraints [message #255535 is a reply to message #255529] Wed, 01 August 2007 02:31 Go to previous messageGo to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

dear michel



Data "01" already in com_info table ..

SQL> /

COM_CD COM_NM
---------- ---------------------------------------------
01 PAKISTAN DAIRY PRODUCTS PRIVATE LIMITED
02 AL-KARAM TEXTILE MILLS
Re: sql loader and constraints [message #255603 is a reply to message #255512] Wed, 01 August 2007 04:47 Go to previous messageGo to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

Hi experts

I have done all by my own .. I just add constant clause in the script . now i just stuck with the following error ..
COM_CD                                                    CONSTANT
    Value is '01'
DEALER_CD                            NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "LPAD(RECNUM, 10, '0')"
LOC_CD                                                    CONSTANT
    Value is '01'

Record 1: Rejected - Error on table DEALER_INFO, column DEALER_CD.
ORA-00984: column not allowed here


if i remove lpad it works fine .. but i need the data in that form .. any suggussions




sql loader data with plsql function [message #255647 is a reply to message #255512] Wed, 01 August 2007 06:57 Go to previous messageGo to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

hi experts


i just want to replace my one column with plsql function bt it give me error ..pleae help me out


load data
infile 'M:\dbf_only\ms_dtl.TXT'
into table ms_trans
replace
fields terminated by ","
optionally enclosed by '"'
trailing nullcols
(
com_Cd CONSTANT "01",
trans_Date      date 'yyyymmdd',
descript,
amount,
trans_cd char(10) "LPAD(ROWNUM, 10, '0')",
dk_no
)


it give me error
Record 01: Rejected - Error on table MS_DTL, column TRANS_CD.
ORA-00984: column not allowed here

what should i do for .. suggust me

Re: sql loader data with plsql function [message #255674 is a reply to message #255647] Wed, 01 August 2007 08:23 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
rownum is a pseudo-column, hence not allowed in a SQL*Loader control file, or at least I would think. I have never attempted to even think of using it there.
I'd create a sequence and use NEXTVAL instead.
Re: sql loader data with plsql function [message #255833 is a reply to message #255674] Thu, 02 August 2007 00:42 Go to previous messageGo to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

hi experts

in my routine i use if then else condition for this kind of situation for e.g.....

	SELECT COUNT(*) INTO M_dealer_CD FROM dealer_INFO where com_Cd=:global.com_Cd ;
			IF M_dealer_CD = 0 THEN
			M_dealer_CD:=1;
			M_dealer_CD:=LPAD(M_dealer_CD,10,'0');
 		ELSE
			SELECT MAX(dealer_CD)+1 iNTO M_dealer_CD FROM dealer_INFO where com_Cd=:global.com_Cd ;
			M_dealer_CD:=LPAD(M_dealer_CD,10,'0');		
		END IF;


now what to do if i would like to do the same in sql loader .. is there any solution .. also tell me what nullif and defaultif do in sql loader

column_name [POSITION({start | *[+offset]}[{: | -}end])]
[datatype] [PIECED]
[NULLIF condition [AND condition...]]
[DEFAULTIF condition [AND condition...]]
["sql_expression"]




any hint or example of coding would be appricaitable ..

regards

Re: sql loader data with plsql function [message #255838 is a reply to message #255833] Thu, 02 August 2007 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table then you have the power of SQL and PL/SQL to do what you want.

Regards
Michel
Re: sql loader data with plsql function [message #255857 is a reply to message #255838] Thu, 02 August 2007 02:51 Go to previous messageGo to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

hi experts


i write a function for this requerment
CREATE OR REPLACE  FUNCTION "IGLOO"."MAX_DEALER"  (p_com_CD 
    varchar2) RETURN varchar2 IS
m_dealer_CD varchar2(10);
BEGIN
SELECT COUNT(*) INTO M_dealer_CD FROM dealer_INFO where com_Cd=p_com_Cd;
	IF M_dealer_CD = 0 THEN
		M_dealer_CD:=1;
		M_dealer_CD:=LPAD(M_dealer_CD,10,'0');
    return (m_dealer_cd);
	ELSE
		SELECT MAX(dealer_CD)+1 iNTO M_dealer_CD FROM dealer_INFO where com_Cd=p_com_Cd;
		M_dealer_CD:=LPAD(M_dealer_CD,10,'0');		
    return (m_dealer_cd);
	END IF;
	



it shows error

Record 2: Rejected - Error on table DEALER_INFO, column DEALER_CD.
ORA-04091: table IGLOO.DEALER_INFO is mutating, trigger/function may not see it
ORA-06512: at "IGLOO.MAX_DEALER", line 4


any solution .. or tell me if i made commit after every record ..this problem will be solved or not ..and how can i do commit after every row??


regards

anwer
Re: sql loader data with plsql function [message #255860 is a reply to message #255857] Thu, 02 August 2007 03:04 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
any solution

Use external table.

Regards
Michel
Previous Topic: importing data
Next Topic: Why did the dump file extracted too large?
Goto Forum:
  


Current Time: Wed Dec 07 08:28:40 CST 2016

Total time taken to generate the page: 0.31771 seconds