Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Question Unanswered: Problem with CAST in store procedure

    This is an Oracle 8.1.7 database.

    I have a stored procedure that I have written that contains the following piece of code:

    INSERT INTO T2959_EZB_PO_DTL
    (
    T2958_po_nbr,
    T616_exp_vbu_nbr,
    T617_FNC_TYP_CD,
    T2959_po_lin_nbr,
    vnd_itm_id,
    itm_des_txt,
    itm_qty,
    itm_ord_unt_msr_id,
    itm_unt_prc_amt,
    itm_unt_tax_amt,
    add_dm,
    upd_id,
    upd_dm
    )
    SELECT DISTINCT
    tid.po_nbr,
    tid.vbu_nbr,
    14,
    tid.po_lin_nbr,
    tpd.itm_id,
    TRANSLATE(tpd.itm_desc USING CHAR_CS) itm_desc,
    tpd.itm_qty,
    tpd.itm_um,
    tpd.itm_prc,
    tpd.itm_tax,
    sysdate,
    'test',
    sysdate
    FROM temp_inv_data tid,
    temp_po_data tpd
    WHERE tid.po_nbr = tpd.po_nbr
    AND tid.po_lin_nbr = tpd.po_lin_nbr
    AND CAST(tid.vbu_nbr as VARCHAR2) = tpd.vbu_nbr
    AND tid.ivo_sub_lin = 1;

    This will compile. However, when I execute the procedure I get the following error:

    ORA-00906: missing left parenthesis

    This appears to be referencing the line:

    AND CAST(tid.vbu_nbr as VARCHAR2) = tpd.vbu_nbr

    I can get the SQL to run directly in SQL*Plus if I re-write the statement as:

    AND CAST(tid.vbu_nbr as VARCHAR2(12)) = tpd.vbu_nbr

    This will not compile. The following errors is displayed:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    537/36 PLS-00103: Encountered the symbol "(" when expecting one of the
    following:
    . ) @ %

    How do I get it to compile and still run?

    Thanks for your help.

    David

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This looks like the common issue where your version of Oracle supports a "new" feature (in this case CAST) in SQL but not in PL/SQL - because until 10G the PL/SQL engine has always lagged behind the SQL engine.

    Can you use TO_CHAR(tid.vbu_nbr) instead? If not, you would have to work around the restriction by using dynamic SQL (i.e. EXECUTE IMMEDIATE).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •