Home » Developer & Programmer » Forms » Table insert using form
Table insert using form [message #292112] Mon, 07 January 2008 21:20 Go to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Hi everybody

We have to copy selected rows of a table regularly. This is how I do it.

1. Select the rows
2. Save using table insert into an SQL file.
3. Then copy the SQL file onto a CD and give it to the required user. The required user uses this on another server on another location. Both the servers are not connected physically.

What I want to do now is to allow the end user to do this task using a form.

I am using Oracle 8.1.7 and forms 5.0.6.8.

Can anyone please tell me how to do this.

Alister

Report message to a moderator

Re: Table insert using form [message #292154 is a reply to message #292112] Tue, 08 January 2008 01:35 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood you correctly, maybe the simplest way would be to create a PUSH BUTTON and call existing scripts (the ones you use now) using the HOST built-in. That's how you'd create a file; user will, however, need to burn it himself.

Report message to a moderator

Re: Table insert using form [message #292155 is a reply to message #292112] Tue, 08 January 2008 01:39 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
I am using the save as table insert of the SQL Navigator. I do not want to give other end users access to SQL Navigator.

Alister

Report message to a moderator

Re: Table insert using form [message #292167 is a reply to message #292155] Tue, 08 January 2008 02:11 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
Will the user be able to access both the servers from the forms application ?

If yes, you can write a query to select data from one server and insert it into the second one.

Minto

Report message to a moderator

Re: Table insert using form [message #292173 is a reply to message #292167] Tue, 08 January 2008 02:23 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Alister said that there's no connection between two servers.

Alister, if you use SQL Navigator, it doesn't mean that it can't be done in SQL*Plus. Create a script which will do the same job as this action you perform using this GUI tool and run it from the form; something like
sqlplus -s scott/tiger@ora10 @prepare_data.sql
"prepare_data.sql" is a script you should write, and which will create a file that looks exactly like the one SQL Navigator produces.

Report message to a moderator

Re: Table insert using form [message #292395 is a reply to message #292112] Tue, 08 January 2008 21:08 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Thank you littlefoot.

I shall try out your idea.

Alister

Report message to a moderator

Re: Table insert using form [message #292475 is a reply to message #292112] Wed, 09 January 2008 00:47 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
I tried what Mr.Littlefoot said but am unable to get the desired output.
I am attaching the table as a dmp file and requirements below. Please try and help me.

Thank you.


Alister

For the following query
select * from afpenmst where pponumber between '0607216532' and '0607216556'

I want an output as below as an SQL file:

INSERT INTO &&table_name
VALUES
('02679474','0607216532','SANTHANARAMAN.G','SBV','7000802','13515','23-AUG-2007','04','24-AUG-2007',21,NULL,3950,'EF','1',NULL,1913,2 963,NULL,0,0,2963,175434,'N','2','C/O,G.SARALA,','7,P & T, NAGAR, POLICE STATION ROAD,','MAYILADUTHURAI.',2429,NULL,2,6,15,'Y',24,NULL,NULL,'13-DEC-1950',NULL,NULL,'Y',NULL,'05','5',NULL,NULL,NULL,NULL,NULL,NULL,NU LL,NULL,NULL,NULL,'TS1817,NEELA SOUTH STREET','NAGAPATTINAM',NULL,'Y','24-AUG-2007',NULL,'S',NULL,0,'YES',NULL,'1975','N19350')
/
INSERT INTO &&table_name
VALUES
('07837045','0607216533','CHELLADURAI.H','426','4701111','3225119','15-AUG-2007','04','16-AUG-2007',29,NULL,8475,'EF','1',NULL,3814,6 357,NULL,0,0,6357,350000,'N','2','NO.1-1/21, MARAVAR STREET,','VADUGAPATTY,','PERIYAKULAM-625602 THENI DT.',5212,NULL,NULL,NULL,NULL,'N',28,NULL,NULL,'16-MAY-1956',NULL,NULL,'Y',NULL,'10','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,'SANGAM TOWERS','7-A WEST VELI STREET','MADURAI 625 001','Y','16-AUG-2007',NULL,'S',NULL,0,'YES',NULL,'4238','N19351')
/
INSERT INTO &&table_name
VALUES
('10581819','0607216534','DURAIRAJ.M.K','R09','5701101','20005637933','14-OCT-2007','04','15-OCT-2007',20.5,NULL,4305,'EF','1',NULL,1 938,3229,NULL,0,0,3229,186673,'N','2','NO.224,VINAYAKA STREET,','CHINNASALEMPATTI, KANNANUR PO','THURAIYUR.',2648,NULL,NULL,NULL,NULL,'N',8,1,NULL,'1-JUL-1967',NULL,NULL,'Y',NULL,'06','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU LL,NULL,NULL,'P.B.NO.21','TIRUCHIRAPPALLI',NULL,'Y','15-OCT-2007',NULL,'S',NULL,0,'NO',NULL,'2153','N19352')
/
INSERT INTO &&table_name
VALUES
('04690308','0607216535','SIVASUBRAMANIAN.N','R07','5701402','63622210003658','14-JUL-2007','04','15-JUL-2007',26,NULL,4600,'EF','1', NULL,2070,3450,NULL,0,0,3450,252954,'N','2','NO.16/4/3-B, LAKSHMI BHAVANAM,','PERALI ROAD, L.B.S. NAGAR,','VIRUDUNAGAR-626001',2829,NULL,NULL,NULL,NULL,'N',12,NULL,NULL,'19-MAR-1956',NULL,NULL,'Y',NULL,'07','5',NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,'146,WEST BOULEWARD ROAD','P.B.319','TIRUCHIRAPPALLI','Y','15-JUL-2007',NULL,'S',NULL,0,'NO',NULL,'2300','N19353')
/
INSERT INTO &&table_name
VALUES
('04174331','0607216536','KUPPUSAMY.D','Y08','9000805','9881','30-OCT-2007','07','30-OCT-2007',24.5,6082.43,4100,'RP','0',2258,1913,2 258,'20-DEC-2007',903,113345,1355,106232,'Y','2','3/361-A KULATHUKARAI STREET,','NATHANKADUVETTI (PO)','TIRUKOILUR TK-605755',2522,NULL,3,8,23,'Y',9,NULL,NULL,'14-JUL-1949',NULL,NULL,'Y',NULL,'04','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU LL,'AVS TOWERS','1 THIRUVIKA ROAD',NULL,'N',NULL,NULL,'S',NULL,40,'YES',NULL,'2050','N19354')
/
INSERT INTO &&table_name
VALUES
('04174987','0607216537','CHITTRAMBALAM.E','Y08','3800709','748274030','20-OCT-2007','07','20-OCT-2007',25,6045.09,4100,'RP','0',2290 ,1913,2290,'20-DEC-2007',916,114977,1374,108400,'Y','2','3/152, KATTU VALAIVU,','KARIPPATTI (PO) 636106','VALAPPADI TK SALEM DT.',2522,NULL,3,0,2,'Y',30,NULL,NULL,'3-JUN-1949',NULL,NULL,'Y',NULL,'04','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'P B NO 55','101A ARUNACHALA ASARI ST','SALEM','Y','20-OCT-2007',NULL,'S',NULL,40,'YES',NULL,'2050','N19355')
/
INSERT INTO &&table_name
VALUES
('03417797','0607216538','GANESAN.R','620','4800779','541080865/5','16-OCT-2007','07','16-OCT-2007',39.5,16001.81,6950,'RP','0',8001, 4848,8001,'20-DEC-2007',3200,438528,4801,350000,'Y','2','NO.20,NORTH AGRAGARAM,','VALAVANUR (PO)605108','VILLUPURAM DT.',5557,NULL,NULL,NULL,NULL,'N',2,NULL,NULL,'3-DEC-1951',NULL,NULL,'Y',NULL,'09','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N ULL,'27,BHARATHI ROAD,','CUDDALORE',NULL,'Y','16-OCT-2007',NULL,'S',NULL,40,'NO',NULL,'3475','N19356')
/
INSERT INTO &&table_name
VALUES
('03367228','0607216539','PERIYASAMY.P','632','5701101','20006452769','1-NOV-2007','07','1-NOV-2007',38.5,12694.5,5600,'RP','0',6348, 3906,6348,'20-DEC-2007',2539,328446,3809,288701,'Y','2','NO.9 JAMBUKESWARAN NAGAR,','T.V.KOVIL.','TRICHY-5',4477,NULL,NULL,NULL,NULL,'N',2,2,NULL,'24-AUG-1950',NULL,NULL,'Y',NULL,'07','5',NULL,NULL,NULL,NULL,N ULL,NULL,NULL,NULL,NULL,NULL,'P.B.NO.21','TIRUCHIRAPPALLI',NULL,'N',NULL,NULL,'S',NULL,40,'YES',NULL,'2800','N19357')
/
INSERT INTO &&table_name
VALUES
('03402988','0607216540','JEEVARAJ.P','A27','5700871','10497','1-OCT-2007','07','1-OCT-2007',38,14767.5,10150,'RP','0',7384,4568,7384 ,'20-DEC-2007',2953,404680,4431,350000,'Y','2','46/A, DORAISAMYPURAM,','PALAKARAI,','TRICHY-620001',6242,NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,'20-OCT-1951',NULL,NULL,'Y',NULL,'13','5',N ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'144 WEST BOULEWARD ROAD','TIRUCHIRAPPALLI','TIRUCHIRAPPALLI','Y','1-OCT-2007',NULL,'S',NULL,40,'YES',NULL,'5075','N19358')
/
INSERT INTO &&table_name
VALUES
('03371712','0607216541','BALRAJ.R','633','5601102','10349492605','1-DEC-2007','07','1-DEC-2007',34.5,13891.88,6050,'RP','0',6946,422 0,6946,'20-DEC-2007',0,0,6946,311904,'N','2','1/334, SWAMINATHA PILLAI NAGAR,','GORIKULAM, THANJAVUR-1',NULL,4837,NULL,NULL,NULL,NULL,'N',3,NULL,NULL,'1-SEP-1953',NULL,NULL,'Y',NULL,'09','5',NULL,NULL,NULL,NULL,NULL,NULL,NUL L,NULL,NULL,NULL,'P.B.NO.45','HOSPITAL ROAD','THANJAVUR','N',NULL,NULL,'S',NULL,0,'YES',NULL,'3025','N19359')
/
INSERT INTO &&table_name
VALUES
('03358460','0607216542','LOURDURAJ.P','Z73','5701402','40290','15-MAR-2007','07','15-MAR-2007',30,5805.4,3930,'RP','0',2639,1913,263 9,'20-DEC-2007',1055,136475,1584,119370,'Y','2','26/A, THULASINGA NAGAR,','RAMACHANDRA NAGAR,','EDAMALAIPPATTI PUTHUR-TRICHY-2',2063,NULL,NULL,NULL,NULL,'N',26,125,NULL,'20-JAN-1950',NULL,NULL,'Y',NULL,'03','5',NULL,NULL,NULL,NULL,NULL,NULL,NUL L,NULL,NULL,NULL,'146,WEST BOULEWARD ROAD','P.B.319','TIRUCHIRAPPALLI','Y','15-MAR-2007',NULL,'S',NULL,40,'YES',NULL,'1965','N19360')
/
INSERT INTO &&table_name
VALUES
('03436937','0607216543','RAJENDRAN.G','204','9000801','375','24-AUG-2007','04','25-AUG-2007',24,NULL,4030,'EF','1',NULL,1913,3023,NU LL,0,0,3023,204552,'N','2','NO.5, MARIAMMAN KOIL STREET,','BEEMANAICKENTHOPPU,','VILLUPURAM-605602',2478,NULL,2,10,12,'Y',28,NULL,NULL,'28-FEB-1951',NULL,NULL,'Y',NULL,'05','5',NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'AVS TOWERS','1 THIRUVIKA ROAD',NULL,'N',NULL,NULL,'S',NULL,0,'YES',NULL,'2015','N19361')
/
INSERT INTO &&table_name
VALUES
('03449609','0607216544','WILLIAM.K','570','5900501','20847','31-DEC-2007','06','1-JAN-2008',31,6000,4000,'SP','0',2819,1913,2819,'1- JAN-2008',1127,132671,1692,131130,'Y','2','76(34)NEW JANDA STREET,','R.N.PALAYAM,VELLORE-632001',NULL,2460,NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,'9-DEC-1947',NULL,NULL,'Y',NULL,'03','5',NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SARATHY MISSION NORTHWING','P.B.NO.408','VELLORE','N',NULL,NULL,'S',NULL,40,'YES',NULL,'2000','N19362')
/
INSERT INTO &&table_name
VALUES
('03418182','0607216545','RAMASAMY.A','425','5800846','14256','31-DEC-2007','06','1-JAN-2008',37,14250,9500,'SP','0',7125,4275,7125,' 1-JAN-2008',2850,335502,4275,331535,'Y','2','NO.54, Vth CROSS STREET,','BALAJI AVENUE, THACHA NALLUR,','TIRUNELVELI-627358',5843,NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,'6-DEC-1947',NULL,NULL,'Y',NULL,'12','5',NULL,NULL,NULL,NUL L,NULL,NULL,NULL,NULL,NULL,NULL,'25C-2, MADURAI ROAD','TIRUNELVELI JUNCTION','TIRUNELVELI','N',NULL,NULL,'S',NULL,40,'YES',NULL,'4750','N19363')
/
INSERT INTO &&table_name
VALUES
('03418157','0607216546','THAYUMANAVAN.R','568','5701101','1084859440-5','31-DEC-2007','06','1-JAN-2008',39.5,7845.48,5300,'SP','0',3 923,2385,3923,'1-JAN-2008',1569,184703,2354,184965,'Y','2','NO.31, UPSTAIRS,','OLD KAJAPET, TRICHY-1',NULL,3260,NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,'12-DEC-1947',NULL,NULL,'Y',NULL,'09','5',NULL,NULL,NULL,NULL,NULL,NULL,NU LL,NULL,NULL,NULL,'P.B.NO.21','TIRUCHIRAPPALLI',NULL,'Y','1-JAN-2008',NULL,'S',NULL,40,'NO',NULL,'2650','N19364')
/
INSERT INTO &&table_name
VALUES
('04171123','0607216547','AMALRAJ.G','Z75','5700740','405658074','31-DEC-2007','06','1-JAN-2008',23,5505,3670,'SP','0',1919,1913,1919 ,'1-JAN-2008',767,90292,1152,89263,'Y','2','4/314, ANNA NAGAR,','M.K.KOTTAI, KILAKURICHI(PO)','TRICHY-11',2257,NULL,4,3,17,'Y',2,NULL,NULL,'19-DEC-1947',NULL,NULL,'Y',NULL,'02','5',NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,'P.B.NO.4','128,BIG BAZAR SREET','TIRUCHIRAPPALLI','N',NULL,NULL,'S',NULL,40,'YES',NULL,'1835','N19365')
/
INSERT INTO &&table_name
VALUES
('03419540','0607216548','KARUPPASAMY.C','424','5701101','010848559539','31-DEC-2007','06','1-JAN-2008',35,16069,10825,'SP','0',8035, 4872,8035,'1-JAN-2008',3214,378353,4821,350000,'Y','2','NO.3, RAMAKRISHNA NAGAR, Ist STREET,','KARUMANDAPAM, TRICHY-1',NULL,6658,NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,'25-DEC-1947',NULL,NULL,'Y',NULL,'13','5',NULL,NULL,NULL,NULL,NULL,NULL,NU LL,NULL,NULL,NULL,'P.B.NO.21','TIRUCHIRAPPALLI',NULL,'Y','1-JAN-2008',NULL,'S',NULL,40,'NO',NULL,'5413','N19366')
/
INSERT INTO &&table_name
VALUES
('04180392','0607216549','GOVINDARAJ.G','Y08','9000805','9906','31-DEC-2007','06','1-JAN-2008',22.5,5992.8,4025,'SP','0',2043,1913,20 43,'1-JAN-2008',817,96178,1226,95783,'Y','2','52/1, MANTHAI KARAI STREET,','THANIKALAMPATTU, KADAKANUR (PO)','TIRUKOILUR TK-605755',2476,NULL,3,2,27,'Y',10,NULL,NULL,'1-JAN-1948',NULL,NULL,'Y',NULL,'04','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU LL,'AVS TOWERS','1 THIRUVIKA ROAD',NULL,'Y','1-JAN-2008',NULL,'S',NULL,40,'YES',NULL,'2013','n19367')
/
INSERT INTO &&table_name
VALUES
('03442202','0607216550','NATESAN.R','406','5701101','20006452066','31-DEC-2007','06','1-JAN-2008',25,10219,6900,'SP','0',3871,3105,3 871,'1-JAN-2008',1548,182231,2323,182425,'Y','2','NALLAVUR, PALAYUR (PO)','KUMBAKONAM RMS',NULL,4244,NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,'18-DEC-1947',NULL,NULL,'Y',NULL,'10','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU LL,NULL,NULL,'P.B.NO.21','TIRUCHIRAPPALLI',NULL,'Y','1-JAN-2008',NULL,'S',NULL,40,'YES',NULL,'3450','N19368')
/
INSERT INTO &&table_name
VALUES
('02506890','0607216551','THANGAVEL.V','Y06','5900605','3326','31-DEC-2007','06','1-JAN-2008',16,5275.5,3580,'SP','0',1913,1913,1913, '1-JAN-2008',765,90056,1148,60576,'Y','2','7/211,SELVI KOTTAI,','ANDAMPALLAM(PO)606804','TANDARAI VIA TIRUVANNAMALAI DT',2202,NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,'7-DEC-1947',NULL,NULL,'Y',NULL,'03','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,'73 ARCOT ROAD','VELLORE',NULL,'N',NULL,NULL,'S',NULL,40,'NO',NULL,'1790','n19369')
/
INSERT INTO &&table_name
VALUES
('03449749','0607216552','MUTHAMMAL.K','205','5700502','000044642','31-DEC-2007','06','1-JAN-2008',27.5,6021,4030,'SP','0',2509,NULL, NULL,'1-JAN-2008',1003,118074,1506,117192,'Y','2','NO.21,BHARATHIAR STREET,','ULAGANATHAPURAM,KALLUKUZHI,','TRICHY-620001',2478,NULL,NULL,NULL,NULL,'N',23,46,NULL,'10-DEC-1947',NULL,NULL,'Y',NULL,'05', '5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'25 NANDIKOIL STREET','P.B.354 TEPPAKULAM','TIRUCHIRAPPALLI','Y','1-JAN-2008',NULL,'S',NULL,40,'YES',NULL,'2015','N19370')
/
INSERT INTO &&table_name
VALUES
('04183654','0607216553','GOVINDAN.R','Y06','3800525','12920','31-DEC-2007','06','1-JAN-2008',21,5947.6,4025,'SP','0',1913,1913,1913, '1-JAN-2008',765,90056,1148,89397,'Y','2','129-B, MUTHU GOUNDAN STREET,','ALLIKUTTAI (PO)','SALEM TK & DT',2476,NULL,2,7,3,'Y',29,5,NULL,'12-DEC-1947',NULL,NULL,'Y',NULL,'04','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'A/C SECTION,P.B.NO 10','SRI SARADA VIDYALAYA','FAIRLANDS SALEM','Y','1-JAN-2008',NULL,'S',NULL,40,'YES',NULL,'2013','N19371')
/
INSERT INTO &&table_name
VALUES
('03390792','0607216554','JAYAVEL.C','519','4801119','11426044397','31-DEC-2007','06','1-JAN-2008',36,6885,4590,'SP','0',3443,2066,34 43,'1-JAN-2008',1377,162101,2066,160182,'Y','2','SETHUVARAYANKUPPAM (PO)','NAGALUR (VIA),VIRDDHACHALAM-TK.','CUDDALORE-DT. 606203',2823,NULL,1,4,29,'Y',11,NULL,NULL,'1-JAN-1948',NULL,NULL,'Y',NULL,'05','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '68,NETHAJI ROAD','MANJAKUPPAM','CUDDALORE','Y','1-JAN-2008',NULL,'S',NULL,40,'NO',NULL,'2295','N19372')
/
INSERT INTO &&table_name
VALUES
('01085130','0607216555','SONAIYAN.T','Z79','6000801','18493','31-DEC-2007','06','1-JAN-2008',33,7350,4900,'SP','0',3675,2205,3675,'1 -JAN-2008',1470,173049,2205,171006,'Y','2','NO.4/3885, EAST STREET,','SATHANKULAM (PO)','RAMANAD TK & DT. 623535',3014,NULL,NULL,NULL,NULL,'N',5,NULL,NULL,'3-DEC-1947',NULL,NULL,'Y',NULL,'07','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL L,NULL,'77V RAILWAY FEEDER ROAD','RAMANATHAPURAM',NULL,'Y','1-JAN-2008',NULL,'S',NULL,40,'YES',NULL,'2450','N19373')
/
INSERT INTO &&table_name
VALUES
('02699084','0607216556','MUNIAN.I','TO4','6000301','11404','31-DEC-2007','06','1-JAN-2008',25.5,5266.03,3540,'SP','0',2035,1913,2035 ,'1-JAN-2008',814,95825,1221,95460,'Y','2','3/29, BHARATHI NAGAR,','MANDAPAM -623518','RAMANATHAPURAM DT.',2177,NULL,NULL,NULL,NULL,'N',1,71,NULL,'1-JAN-1948',NULL,NULL,'Y',NULL,'02','5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL L,'MARAKAYAR STREET','MANDAPAM','RAMANATHAPURAM DISTRICT','Y','1-JAN-2008',NULL,'S',NULL,40,'NO',NULL,'1770','N19374')
/
  • Attachment: afpenmst.dmp
    (Size: 1.17MB, Downloaded 2139 times)

Report message to a moderator

Re: Table insert using form [message #292536 is a reply to message #292475] Wed, 09 January 2008 03:00 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
hi,

i think you can write a procedure something like this:

DECLARE
   sqlfile   text_io.file_type;
BEGIN
   sqlfile := text_io.fopen ('c:\insert.sql', 'w');

   FOR c_table_data IN (SELECT col1, col2, col3
                          FROM table1)
   LOOP
      text_io.put_line (sqlfile,
                           'INSERT INTO TABLE1 (COL1,COL2,COL3) VALUES ('
                        || c_table_data.col1
                        || ','
                        || c_table_data.col2
                        || ','
                        || c_table_data.col3
                        || ');= '
                       );
   END LOOP;

   text_io.put_line ('/');
   text_io.fclose (sqlfile);
END;


Minto

Report message to a moderator

Re: Table insert using form [message #292571 is a reply to message #292475] Wed, 09 January 2008 04:22 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Instead of bunch of INSERT INTO statements, you'd perhaps do it easier using Oracle's EXPORT and IMPORT utilities. Unfortunately, your database version doesn't support a QUERY option. If it did, a simple export on one side and even easier import on the other might do the job. Here's an example (in a case you decide to do the upgrade): instead of 
select * from afpenmst where pponumber between '0607216532' and '0607216556';
you'd
EXP username/password@database TABLES=afpenmst -
 QUERY=\"WHERE pponumber BETWEEN '0607216532' AND '0607216556'\" -
 FILE=export_file.dmp
The other side would import it as
IMP username/password@another_database FILE=export_file.dmp
and that would be all.

Another suggestion might be using the SQL*Loader. It is available in your database as well, so - why not give it a try? Here's an example based on Scott's schema (which is, I believe, available to you and you could test it, perhaps with slight modifications).

First, let us create a data file using SPOOL command in SQL*Plus. We'll create an SQL file and call it from an operating system batch script. As I use MS WIndows XP, this example is based on it:
REM test.sql
set echo off
set termout off
set heading off
set colsep ';'
set linesize 200
spool test.txt
select * from emp where empno between 7500 and 7700;
spool off
exit
REM test.bat

sqlplus -s scott/tiger @test.sql
exit

Running 'test.bat', we'd create a 'test.txt' file which contains data for employees whose numbers are between 7500 and 7700:
      7521;WARD      ;         ;      7698;22.02.81;      1250;          ;        30                     
      7566;JONES     ;MANAGER  ;      7839;02.04.81;      2975;          ;        20                     
      7654;MARTIN    ;SALESMAN ;      7698;28.09.81;      1250;      1400;        40                     
      7698;BLAKE     ;MANAGER  ;      7839;01.05.81;      2850;          ;        40
Now you'd transfer this 'test.txt' file to another location and, once it gets there, load it using SQL*Loader. This is a control file ('test.ctl') which does the job:
load data
infile 'test.txt'
replace
into table test
fields terminated by ";"
trailing nullcols
 (empno, 
  ename, 
  job, 
  mgr, 
  hiredate "to_date(:hiredate, 'dd.mm.yy')", 
  sal, 
  comm "trim(:comm)", 
  deptno
 )
Another batch script which is just to be run looks like this:
REM load.bat

sqlldr mike/lion control=test.ctl log=test.log
exit
When the other user runs 'load.bat', records will be inserted into the (precreated) table 'test' and that's all.

Now, I'll remove 'exit' commands from all .BAT files to avoid exiting command prompt and show you how it works; quite simple, actually: user on your location runs 'test.bat', and user on another location runs 'load.bat':

Your location
>test

>sqlplus -s scott/tiger @test.sql
Transfer the file, and on another location do
>load

>sqlldr mike/lion control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sri Sij 9 11:15:49 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 6

Just to see what we've done:
>sqlplus mike/lion

SQL*Plus: Release 10.2.0.1.0 - Production on Sri Sij 9 11:15:54 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select ename, job, sal from test;

ENAME      JOB              SAL
---------- --------- ----------
WARD                       1250
JONES      MANAGER         2975
MARTIN     SALESMAN        1250
BLAKE      MANAGER         2850

SQL> exit

Now you have several options; I hope one of them will be helpful.

[Updated on: Wed, 09 January 2008 19:52] by Moderator

Report message to a moderator

Re: Table insert using form [message #292811 is a reply to message #292112] Wed, 09 January 2008 22:55 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Thank you Mr.Littlefoot and Mr.Minto

I shall try out what you have said and give you a feed back.


Alister

Report message to a moderator

Re: Table insert using form [message #294920 is a reply to message #292112] Sun, 20 January 2008 21:01 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear Mr. Littlefoot and Mr. Minto

I tried both the options that you have given me. The one in which the control file is created is a nice option but the personnel at the other end is only an end user and his privileges are limited and he prefers the bunch insert.
The procedure that Mr. Minto gave gives a very similar output to the one that was generated earlier except that the characters are in double quotes instead of single quote and the Null values are represented as "".
Can either of you please tell me how to get the characters within Single quote and to have the word Null printed instead of "".
Once again I thank both of you very much.

Alister

Report message to a moderator

Re: Table insert using form [message #294970 is a reply to message #294920] Mon, 21 January 2008 01:05 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
just modify the procedure like:

text_io.put_line (sqlfile,
                           'INSERT INTO TABLE1 (COL1,COL2,COL3) VALUES ('
                        || NVL(to_char(c_table_data.col1),'NULL')
                        || ','
                        || NVL(to_char(c_table_data.col2),'NULL')
                        || ','
                        || NVL(to_char(c_table_data.col3),'NULL')
                        || ');= '
                       );


Minto

Report message to a moderator

Re: Table insert using form [message #294985 is a reply to message #292112] Mon, 21 January 2008 01:43 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Thank you very much, this takes care of one of the problems,but I want the characters in Single quotes and not double quotes. Can you please tell me how to do that.


Alister.

Report message to a moderator

Re: Table insert using form [message #294996 is a reply to message #294985] Mon, 21 January 2008 02:10 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
try this:

text_io.put_line (sqlfile,
                           'INSERT INTO TABLE1 (COL1,COL2,COL3) VALUES ('''
                        || NVL(to_char(c_table_data.col1),'NULL')
                        || ''','''
                        || NVL(to_char(c_table_data.col2),'NULL')
                        || ''','''
                        || NVL(to_char(c_table_data.col3),'NULL')
                        || ''');= '
                       );

Minto

Report message to a moderator

Re: Table insert using form [message #295319 is a reply to message #292112] Tue, 22 January 2008 02:11 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Thank you Mr. Minto It is working fine just as required.


Alister

Report message to a moderator

Re: Table insert using form [message #295603 is a reply to message #292112] Tue, 22 January 2008 21:15 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
The procedure that you gave is working fine. Thank you for it. But the output that I get gives me 'NULL' within quotes. It is taking it as a character whos value is NULL. I want just NULL as the output. Now what I am doing is to open the SQL file and use find and replace all. Since the end user has to do this task and at different times different persons may be doing it, I want to include it in the query using some procedure or so. I want the end user to do very little. Can the find and replace be included in an oracle query if it cannot be accomodated in the original procedure.
Sorry for the trouble and Thank you once again.

Alister.

Report message to a moderator

Re: Table insert using form [message #295908 is a reply to message #295603] Wed, 23 January 2008 22:01 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
hi,

you can use the replace function for this purpose:

text_io.put_line
            (sqlfile,
             REPLACE (   'INSERT INTO TABLE1 (COL1,COL2,COL3) VALUES ('''
                      || NVL (TO_CHAR (c_table_data.col1), 'NULL')
                      || ''','''
                      || NVL (TO_CHAR (c_table_data.col2), 'NULL')
                      || ''','''
                      || NVL (TO_CHAR (c_table_data.col3), 'NULL')
                      || '''); ',
                      '''NULL''',
                      'NULL'
                     )
            );

Minto

Report message to a moderator

Re: Table insert using form [message #295918 is a reply to message #292112] Wed, 23 January 2008 22:34 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Thank you Mr. Minto I shall try the replace procedure and give you my feedback.

Alister

Report message to a moderator

Re: Table insert using form [message #295923 is a reply to message #292112] Wed, 23 January 2008 23:05 Go to previous message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Thank you very much Mr.Minto. The replace command has servered my requirement perfectly.

Alister.

Report message to a moderator

Previous Topic: forms10g on Oracle9i Application server
Next Topic: hw to change value of one field s soon as other field changes
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Mar 10 19:11:24 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.