Re: A problem about text file and oracle

[Ray Ontko <rayo@ONTKO.COM>]

I'm wondering if one of your variables might contain
a value that includes an apostrophe.  Instead of this:

>  let $itemnumx   =''''|| $equip ||''''
>  let $statx      =''''|| $loc ||''''
>  let $descx      =''''|| $desc ||''''
...
> ([$ITEMNUMX],' ',' ',' ',' ',[$STATX],[$DESCX],' ','

Try this:

> ($equip,' ',' ',' ',' ',$loc,$desc,' ','

It's better to simply use the variable in the insert statement
than to put quotes around the value and treat it as statement
text.

Ray

> Hi there to all sqr users,
>
> Hello... I am only a beginner programmer in sqr, using
> sqr reportmate 3.5 and Oracle7 database. So I have
> this problem about reading data from text file (in
> this case, I'm using Comma Separated Value file *or
> csv), then inserting them into a table in oracle
> database through sqr. When I running the programming
> lines, I get this error message..
> --------------------------------------------------
>
>      OEXEC
>
> Error on line 113:
>    (SQR 3735) Couldn't execute SQL.
>
>
> --------------------------------------------------
>
> I have include my whole programming lines as you can
> see below... Could anybody please tell me whats wrong
> with them?  Any help is very appreciated...
>
> Thank you
> Mohd Hafiz
>
>   !  EXMPLE.SQR Import from EXCEL text file and writes
> into the table in oracle database
> !-------------------------------------------------------------------------
> begin-setup
>
>         Begin-SQL
>         alter session set
> nls_date_format='DD-MON-YYYY'
>         End-SQL
>
> end-setup
>
> begin-report
>   Input $filename 'Enter File Name with extension
> (.csv) '
>
>
>   let $file = 'C:\UPLOAD\'
>   concat $filename with $file
>
>
>   open  $FILE as 1 for-reading record=5000
>   move ',' TO $sepchar          ! Column separator character
>   move 'Y' TO $ERRORFLAG
>
>   do read_stock
>
>   IF $ERRORFLAG = 'Y'
>      PRINT 'STOCK RECORDS SUCCESSFULLY UPLOADED'  (+3
> ,10) BOLD
>   ELSE
>      PRINT 'UPLOADED WITH ERROR. REFER TO REMARKS.'
> (+3 ,10) BOLD
>   END-IF
> end-report
>
>
> begin-procedure read_stock
> print 'Inserting records from file c:\upload into
> WORKORDER1 tables ...' (+3,5)
> move 0 to #tot-recs
> while 1 = 1!#cnt < 5000
>   read 1 into $x:5000
>   if #end-file = 1
>      break
>   end-if
>   unstring $x by $sepchar into $equip $loc $desc $a $b
> $c $d $e $f $g $h $i $j $k $date1 $date2 $l $m $n $o
> $p
>
>  do start_loading
>
>   add 1 to #cnt
>   add 1 to #inserts
>   if #inserts >= 10
>     commit
>     move 0 to #inserts
>   end-if
>
> end-while
>
> close 1
> display ''    ! leave a blank line
> print 'Total Stock Records written for this batch : '
> (+5,8)
> print #tot-recs (,50) edit 999,999
> end-procedure
>
>
> begin-procedure start_loading
>         do write_item
> end-procedure
>
>
> BEGIN-PROCEDURE WRITE_ITEM
>
>  let $itemnumx   =''''|| $equip ||''''
>  let $statx      =''''|| $loc ||''''
>  let $descx      =''''|| $desc ||''''
>  let $ax         =''''|| $a ||''''
>  let $bx         =''''|| $b ||''''
>  let $cx         =''''|| $c ||''''
>  let $dx         =''''|| $d ||''''
>  let $ex         =''''|| $e ||''''
>  let $fx         =''''|| $f ||''''
>  let $gx         =''''|| $g ||''''
>  let $hx         =''''|| $h ||''''
>  let $ix         =''''|| $i ||''''
>  let $jx         =''''|| $j ||''''
>  let $kx         =''''|| $k ||''''
>  let $date1x     =''''|| $date1 ||''''
>  let $date2x     =''''|| $date2 ||''''
>  let $lx         =''''|| $l ||''''
>  let $mx         =''''|| $m ||''''
>  let $nx         =''''|| $n ||''''
>  let $ox         =''''|| $o ||''''
>  let $px         =''''|| $p ||''''
>
>  move $ax to #ay
>  move $bx to #by
>  move $cx to #cy
>  move $dx to #dy
>  move $ex to #ey
>  move $fx to #fy
>  move $gx to #gy
>  move $ix to #iy
>  move $jx to #jy
>  move $kx to #ky
>  move $mx to #my
>  move $nx to #ny
>  move $ox to #oy
>
>  BEGIN-SQL
>
>
>   INSERT INTO MAXIMO.EQUIPMENT
>
> 
>(EQNUM,PARENT,SERIALNUM,ASSETNUM,COSTCENTER,LOCATION,DESCRIPTION,VENDOR,FAILURECODE,MANUFACTURER,
>
> 
>PURCHASEPRICE,REPLACECOST,INSTALLDATE,WARRANTYEXPDATE,METERREADING,AVGMETERUNIT,TOTALCOST,YTDCOST,BUDGETCOST,CLASSIFICATION,
>
> 
>CALNUM,ISRUNNING,ITEMNUM,READINGDATE,UNCHARGEDCOST,TOTUNCHARGEDCOST,TOTDOWNTIME,STATUSDATE,CHANGEDATE,CHANGEBY,
>   EQ1,EQ2,EQ3,EQ4,EQ5,EQ6,EQ7,EQ8,EQ9,EQ10,
>   EQ11,EQ12,EQ13,EQ14,EQ15,EQ16,EQ17,EQ18,EQ19,EQ20,
>
> 
>EQ21,EQ22,LDKEY,METERREADING2,AVGMETERUNIT2,METERLABEL1,METERLABEL2,METERUNIT1,METERUNIT2,READINGDATE2,
>
> EQ23,EQ24,PRIORITY,INVCOST,GLACCOUNT,ROTSUSPACCT,CHILDREN,BINNUM,DISABLED)
>
>   VALUES
> ([$ITEMNUMX],' ',' ',' ',' ',[$STATX],[$DESCX],' ','
> ',' ',#ay,#by,' ',' ',#cy,#dy,#ey,#fy,#gy,' ','
> ',$hx,' ','
> 
>',#iy,#jy,#ky,to_date('13-MAR-02','DD-MON-YY'),to_date('13-MAR-02','DD-MON-YY'),
> $lx,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','
> ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',#my,#ny,'
> ',' ',' ',' ',' ',' ',' ',' ',#oy,' ',' ',$px,' ',' ')
>
>
>  END-SQL
>  COMMIT
>  add 1 to #tot-recs
>
>
> END-PROCEDURE
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Movies - coverage of the 74th Academy Awards®
> http://movies.yahoo.com/
>

----------------------------------------------------------------------
Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/