Thread: script error

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0

    script error


    i m keep getting this eror can some one plz help me with this script
    very urgent

    SQL>
    SQL> declare
    2 cursor cur_insertrow is
    3 select
    4 b.fyi_lab_report,
    5 a.fyi_reference_id,
    6 a.fyi_title,
    7 a.fyi_first_name,
    8 a.fyi_last_name,
    9 a.fyi_position,
    10 c.sys_author,
    11 a.fyi_address1,
    12 a.fyi_city,
    13 a.fyi_state,
    14 a.fyi_postal_code,
    15 a.fyi_phone,
    16 a.fyi_received_date,
    17 b.fyi_approval_date,
    18 b.fyi_un_ship_name,
    19 b.fyi_un_number,
    20 b.fyi_un_haz_class,
    21 b.fyi_product_desc,
    22 b.fyi_ship_name,
    23 b.fyi_haz_class,
    24 b.fyi_notes,
    25 b.fyi_expiration_date
    26 from fyiadm.Fyi_ohma_wf a,fyiadm.fyi_ohma_doc
    27 b,fyiadm.fyi_sysdata c;
    28
    29 where
    30 fyi_project_status not like '1032%'
    31 and
    32
    33 (b.sys_fkey = a.sys_fkey);
    34
    35 begin
    36
    37 --EXECUTE IMMEDIATE('TRUNCATE TABLE DATASET_EXPLO');
    38 --COMMIT;
    39
    40 for var_insertrow in cur_insertrow
    41 loop
    42
    43 insert into dataset_explo(
    44 select
    45 lab_report_no,
    46 reference_number,
    47 contact_title,
    48 contact_first_name,
    49 contact_last_name,
    50 contact_position,
    51 cotact_company,
    52 contact_address,
    53 contact_city,
    54 contact_state,
    55 contact_zip,
    56 contact_phone,
    57 received_date,
    58 national_stock_number,
    59 un_proper_shipping_name,
    60 un_serial_number,
    61 product_name,
    62 shipping_name,
    63 hazard_class,
    64 un_name_massage,
    65 expire_date
    66 from dataset_explo;
    67 )
    68 values(
    69 var_insertrow.fyi_lab_report,
    70 var_insertrow.fyi_reference_id,
    71 var_insertrow.fyi_title,
    72 var_insertrow.fyi_first_name,
    73 var_insertrow.fyi_last_name,
    74 var_insertrow.fyi_position,
    75 var_insertrow.sys_author,
    76 var_insertrow.fyi_address1,
    77 var_insertrow.fyi_city,
    78 var_insertrow.fyi_state,
    79 var_insertrow.fyi_postal_code,
    80 var_insertrow.fyi_phone,
    81 var_insertrow.fyi_received_date,
    82 var_insertrow.fyi_approval_date,
    83 var_insertrow.fyi_un_ship_name,
    84 var_insertrow.fyi_un_number,
    85 var_insertrow.fyi_un_haz_class,
    86 var_insertrow.fyi_product_desc,
    87 var_insertrow.fyi_ship_name,
    88 var_insertrow.fyi_haz_class,
    89 var_insertrow.fyi_notes,
    90 var_insertrow.fyi_expiration_date);
    91
    92
    93
    94
    95
    96 If MOD(Cur_insertrow%ROWCOUNT,5000) = 0
    97 THEN
    98 COMMIT;
    99 End if;
    100 End Loop;
    101 commit;
    102 end;
    103 /
    where
    *
    ERROR at line 29:
    ORA-06550: line 29, column 1:
    PLS-00103: Encountered the symbol "WHERE" when expecting one of the following:
    begin function package pragma procedure subtype type use
    <an identifier> <a double-quoted delimited-identifier> form
    current cursor
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    27 b,fyiadm.fyi_sysdata c;
    28
    29 where
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0
    b.fyi_haz_class,
    now i m getting these

    *
    ERROR at line 22:
    ORA-06550: line 65, column 19:
    PL/SQL: ORA-00907: missing right parenthesis
    ORA-06550: line 42, column 11:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 66, column 16:
    PLS-00103: Encountered the symbol ")" when expecting one of the following:
    begin case declare end exit for goto if loop mod null pragma
    raise return select update while with <an identifier>
    <a double-quoted delimited-identifier
  6. #4
  7. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    If the listing you copied is correct then on line 65 use comma separator at the end of expire_date.
  8. #5
  9. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    Sorry that was not a proper solution dont ue it.
  10. #6
  11. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    I think you dont need to use semi colone at the end of line 66
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    for your INSERT INTO part, you don't have to have a select in there to get the column names... it should be

    INSERT INTO dataset_explo ( column_name1, colum_name2, etc ) VALUES ( value1, value2 )

    I've used SQLPLUS a lot before, and I would do it the way I described. You may be able to do it the way you have written but I think it is clearer to do it the other way, your way seems to confuse the backend.

    you also do NOT need a semicolon at the end of line 27 I don' t think ,as you haven't even put in the where clause on the cursor yet.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0
    can you give me any advise now what shoud i do?

    1 declare
    2 cursor cur_insertrow is
    3 select
    4 b.fyi_lab_report,
    5 a.fyi_reference_id,
    6 a.fyi_title,
    7 a.fyi_first_name,
    8 a.fyi_last_name,
    9 a.fyi_position,
    10 a.fyi_alt_address,
    11 c.sys_author,
    12 a.fyi_address1,
    13 a.fyi_address2,
    14 a.fyi_city,
    15 a.fyi_state,
    16 a.fyi_postal_code,
    17 a.fyi_phone,
    18 b.fyi_hydrostatic_flag,
    19 b.fyi_acetylene_flag,
    20 a.fyi_received_date,
    21 b.fyi_approval_date,
    22 b.fyi_un_ship_name,
    23 b.fyi_un_number,
    24 b.fyi_un_haz_class,
    25 b.fyi_product_desc,
    26 b.fyi_ship_name,
    27 b.fyi_haz_class,
    28 b.fyi_notes,
    29 b.fyi_expiration_date,
    30 c.sys_flags
    31 from fyiadm.Fyi_ohma_wf a,fyiadm.fyi_ohma_doc
    32 b,fyiadm.fyi_sysdata c
    33 where
    34 fyi_project_status not like '1032%'
    35 and
    36 (b.sys_fkey = a.sys_fkey);
    37 begin
    38 --EXECUTE IMMEDIATE('TRUNCATE TABLE DATASET_EXPLO');
    39 --COMMIT;
    40 for var_insertrow in cur_insertrow
    41 loop
    42 insert into dataset_explo(
    43 lab_report_no,
    44 reference_number,
    45 contact_title,
    46 contact_first_name,
    47 contact_last_name,
    48 contact_position,
    49 cotact_company,
    50 contact_address,
    51 contact_city,
    52 contact_state,
    53 contact_zip,
    54 contact_phone,
    55 received_date,
    56 un_revision_date,
    57 revision_date,
    58 national_stock_number,
    59 un_proper_shipping_name,
    60 product_name,
    61 date_of_entry,
    62 user_of_change
    63 )
    64 values(
    65 var_insertrow.fyi_reference_id,
    66 var_insertrow.fyi_title,
    67 var_insertrow.fyi_first_name,
    68 var_insertrow.fyi_last_name,
    69 var_insertrow.fyi_position,
    70 var_insertrow.fyi_alt_address,
    71 var_insertrow.sys_author,
    72 var_insertrow.fyi_address1,
    73 var_insertrow.fyi_city,
    74 var_insertrow.fyi_state,
    75 var_insertrow.fyi_postal_code,
    76 var_insertrow.fyi_phone,
    77 var_insertrow.fyi_hydrostatic_flag,
    78 var_insertrow.fyi_acetylene_flag,
    79 var_insertrow.fyi_received_date,
    80 var_insertrow.fyi_approval_date,
    81 var_insertrow.fyi_un_ship_name,
    82 var_insertrow.fyi_un_number,
    83 var_insertrow.fyi_product_desc,
    84 var_insertrow.fyi_ship_name,
    85 var_insertrow.fyi_notes,
    86 var_insertrow.fyi_expiration_date,
    87 );
    88 If MOD(Cur_insertrow%ROWCOUNT,5000) = 0
    89 THEN
    90 COMMIT;
    91 End if;
    92 End Loop;
    93 commit;
    94* end;
    SQL> /
    contact_phone,
    *
    ERROR at line 54:
    ORA-06550: line 87, column 12:
    PL/SQL: ORA-00936: missing expression
    ORA-06550: line 42, column 10:
    PL/SQL: SQL Statement ignored
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    ok bud, 6550 means that you don't have the same parameters as you need between two expressions. First ,you should definitely get yourself a copy of what all the oracle errors mean to make your own life easier, and secondly, I counted 20 items of the relation you are inserting into, but 22 items are being inserted.... that is most likely what your problem is.
  18. #10
  19. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    1 declare
    2 cursor cur_insertrow is
    3 select
    4 b.fyi_lab_report,
    5 a.fyi_reference_id,
    6 a.fyi_title,
    7 a.fyi_first_name,
    8 a.fyi_last_name,
    9 a.fyi_position,
    10 a.fyi_alt_address,
    11 c.sys_author,
    12 a.fyi_address1,
    13 a.fyi_address2,
    14 a.fyi_city,
    15 a.fyi_state,
    16 a.fyi_postal_code,
    17 a.fyi_phone,
    18 b.fyi_hydrostatic_flag,
    19 b.fyi_acetylene_flag,
    20 a.fyi_received_date,
    21 b.fyi_approval_date,
    22 b.fyi_un_ship_name,
    23 b.fyi_un_number,
    24 b.fyi_un_haz_class,
    25 b.fyi_product_desc,
    26 b.fyi_ship_name,
    27 b.fyi_haz_class,
    28 b.fyi_notes,
    29 b.fyi_expiration_date,
    30 c.sys_flags
    31 from fyiadm.Fyi_ohma_wf a,fyiadm.fyi_ohma_doc
    32 b,fyiadm.fyi_sysdata c
    33 where
    34 fyi_project_status not like '1032%'
    35 and
    36 (b.sys_fkey = a.sys_fkey);
    37 begin
    38 --EXECUTE IMMEDIATE('TRUNCATE TABLE DATASET_EXPLO');
    39 --COMMIT;
    40 for var_insertrow in cur_insertrow
    41 loop
    42 insert into dataset_explo(
    43 lab_report_no, //1
    44 reference_number, //2
    45 contact_title, //3
    46 contact_first_name, //4
    47 contact_last_name, //5
    48 contact_position, //6
    49 cotact_company, //7
    50 contact_address, //8
    51 contact_city, //9
    52 contact_state, //10
    53 contact_zip, //11
    54 contact_phone, //12
    55 received_date, //13
    56 un_revision_date, //14
    57 revision_date, //15
    58 national_stock_number, //16
    59 un_proper_shipping_name, //17
    60 product_name, //18
    61 date_of_entry, //19
    62 user_of_change //20
    63 )
    64 values(
    65 var_insertrow.fyi_reference_id, //1
    66 var_insertrow.fyi_title, //2
    67 var_insertrow.fyi_first_name, //3
    68 var_insertrow.fyi_last_name, //4
    69 var_insertrow.fyi_position, //5
    70 var_insertrow.fyi_alt_address, //6
    71 var_insertrow.sys_author, //7
    72 var_insertrow.fyi_address1, //8
    73 var_insertrow.fyi_city, //9
    74 var_insertrow.fyi_state, //10
    75 var_insertrow.fyi_postal_code, //11
    76 var_insertrow.fyi_phone, //12
    77 var_insertrow.fyi_hydrostatic_flag, //13
    78 var_insertrow.fyi_acetylene_flag, //14
    79 var_insertrow.fyi_received_date, //15
    80 var_insertrow.fyi_approval_date, //16
    81 var_insertrow.fyi_un_ship_name, //17
    82 var_insertrow.fyi_un_number, //18
    83 var_insertrow.fyi_product_desc, //19
    84 var_insertrow.fyi_ship_name, //20
    85 var_insertrow.fyi_notes, //21
    86 var_insertrow.fyi_expiration_date, //22
    87 );
    88 If MOD(Cur_insertrow%ROWCOUNT,5000) = 0
    89 THEN
    90 COMMIT;
    91 End if;
    92 End Loop;
    93 commit;
    94* end;
    SQL> /

    you are selecting only 20 column from table but want to insert 22 columns (see those i commented out) , by the way what the comma separator is doing at the end of line 86 as you dont need it at the end of statement.
    You were making the same mistake in your first script also.

IMN logo majestic logo threadwatch logo seochat tools logo