SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PL/SQL error PLS-00103

654774Sep 19 2008 — edited Sep 25 2008

Hi Everyone,

This is a piece of PL/SQL that i have been working on. It is designed to update consignment figures by a percentage. the user will define wether the change is to be a plus or a minus.

BEGIN
IF :p6_maths=- then
update concount_forecast_data_copy
set MANUAL_ENTRY_CONS=MANUAL_ENTRY_CONS - (MANUAL_ENTRY_CONS/100)*:p2_percent, CITEDI_CONS=CITEDI_CONS - (CITEDI_CONS/100)*:p2_percent where country_name=:p2_country and week_no =:p2_week_no and year =:p2_year;
ELSE
update concount_forecast_data_copy
set MANUAL_ENTRY_CONS=MANUAL_ENTRY_CONS - (MANUAL_ENTRY_CONS/100)*:p2_percent, CITEDI_CONS=CITEDI_CONS - (CITEDI_CONS/100)*:p2_percent where country_name=:p2_country and week_no =:p2_week_no and year =:p2_year;
ENDIF;
END;

However it does not compile. The error i get is:

ORA-06550: line 2, column 17: PLS-00103: Encountered the symbol "THEN" when expecting one of the following: ( - + case mod new null avg count current max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

1. BEGIN
2. if :p6_maths=- then
3. update concount_forecast_data_copy
4. set MANUAL_ENTRY_CONS=MANUAL_ENTRY_CONS - (MANUAL_ENTRY_CONS/100)*:p2_percent, CITEDI_CONS=CITEDI_CONS - (CITEDI_CONS/100)*:p2_percent where country_name=:p2_country and week_no =:p2_week_no and year =:p2_year;

I am not that experienced in PL/SQL and any help as to where i am going wrong would be appritiated :)

-N.S.N.O.

Solomon Yakobson

Answer

Well, since CLOB in general is multi-line text each clob needs to be stored as a separate file. So we prepare CTL file:

LOAD DATA
INFILE 'C:\TEMP\TMP_LOAD_CLOB_DATA.TXT'
  INTO TABLE TMP_LOAD_CLOB_DATA
  FIELDS TERMINATED BY X'09'
  (
   CUSTOMER_ID,
   FIRSTNAME,
   LASTNAME,
   CUSTOMER_DETAILS_CLOB_FILE FILLER,
   CUSTOMER_DETAILS LOBFILE(CUSTOMER_DETAILS_CLOB_FILE) TERMINATED BY EOF
  )

Data file C:\TEMP\TMP_LOAD_CLOB_DATA.TXT:

123	ABC	XYZ	C:\TEMP\TMP_LOAD_CLOB1.TXT
456	DEF	ZZZ	C:\TEMP\TMP_LOAD_CLOB2.TXT

Clob file C:\TEMP\TMP_LOAD_CLOB1.TXT:

Lot Of Information 
about customer ABC XYZ
which should be loaded
into a clob

Clob file C:\TEMP\TMP_LOAD_CLOB2.TXT:

Lot Of Information 
about customer DEF ZZZ
which should be loaded
into a clob

Now:

SQL> SELECT  *
  2    FROM  TMP_LOAD_CLOB_DATA
  3  /

no rows selected

SQL> host
Microsoft Windows [Version 10.0.17763.1637]
(c) 2018 Microsoft Corporation. All rights reserved.

I:\>sqlldr scott@pdb1sol122/tiger control=C:\TEMP\TMP_LOAD_CLOB_DATA.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Feb 5 07:25:12 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 2

Table TMP_LOAD_CLOB_DATA:
  2 Rows successfully loaded.

Check the log file:
  TMP_LOAD_CLOB_DATA.log
for more information about the load.

I:\>exit

SQL> SELECT  *
  2    FROM  TMP_LOAD_CLOB_DATA
  3  /


CUSTOMER_ID FIRSTNAME LASTNAME CUSTOMER_DETAILS
----------- --------- -------- ------------------------------
123         ABC       XYZ      Lot Of Information
                               about customer ABC XYZ
                               which should be loaded
                               into a clob

456         DEF       ZZZ      Lot Of Information
                               about customer DEF ZZZ
                               which should be loaded
                               into a clob

SQL>

SY.

Marked as Answer by Nirvan · Feb 22 2021

1 person found this helpful

Nirvan

Hi Solomon, Thank you for your response..

User_ZZAIV

Hi,
For above is there any way to load CLOB data without having separate files ??

User_H3J7U

To load lobs, use the insert, update, or merge statement.

User_48D4R

Solomon Yakobson

I'll assume CLOB doesn't start with new line. I will also assume, based on your data sample, vertical pipe can't appear in CLOB - otherwise you simply can't use it as field separator. If so, control file TMP_LOAD_CLOB_DATA.CTL:

LOAD DATA
  INFILE 'C:\MY_TEMP\TMP_LOAD_CLOB_DATA.TXT'
  "str X'7C0D0A'"
  INTO TABLE TMP_LOAD_CLOB_DATA
  FIELDS TERMINATED BY "|"
  (
   ID,
   VALUE,
   DATA
  )

7C is hex ascii code for vertical pipe. 0D0A is new line (CRLF - carriage return, line feed). Now we need to add vertical pipe at the end of each line in data file TMP_LOAD_CLOB_DATA.TXT:

1 | Dr4wQQ | it
 is 498,780
 characters
 long......|
2 | WQWQ3 | it 
is 
925,342
 characters long......|
3 | DERRQQ | it 
is 
478,998
 characters
 long
 line1
 line2
 many
 more
 lines|

Now we load:

I:\>sqlldr scott@xxx/yyy control=c:\my_temp\tmp_load_clob_data.ctl log=c:\my_temp\tmp_load_clob_data.log

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Aug 12 06:55:18 2022

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 2
Commit point reached - logical record count 3

Table TMP_LOAD_CLOB_DATA:
  3 Rows successfully loaded.

Check the log file:
  c:\my_temp\tmp_load_clob_data.log
for more information about the load.

I:\>

Now we check what was loaded:

SQL> select * from tmp_load_clob_data;

        ID VALUE      DATA
---------- ---------- ----------------------------------------
         1  Dr4wQQ     it
                       is 498,780
                       characters
                       long......

         2  WQWQ3      it
                      is
                      925,342
                       characters long......

         3  DERRQQ     it
                      is
                      478,998
                       characters
                       long
                       line1
                       line2
                       many
                       more
                       lines

SQL>

SY.

Gor_Mahia

Hi
Did you get a solid solution to this issue? It seems splitting CLOB field isn't that helpful since you still need a unique key to merger them back?

1 - 8

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 23 2008

Added on Sep 19 2008

5 comments

289 views

SQL & PL/SQL

PL/SQL error PLS-00103

Comments

Post Details