Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unanswered: Stored Procedure to_char error

    hi!

    i'm using oracle 9.2.0.4

    i have the following stored procedure:

    PROCEDURE GET_BEHAVIORS
    ( PARM_PREFIX IN VARCHAR2,
    PARM_MARKET IN OUT NUMBER,
    PARM_IMSI IN OUT VARCHAR2,
    PARM_PRIORITY IN OUT NUMBER,
    PARM_TATYPE IN NUMBER,
    PARM_DATE IN OUT VARCHAR2
    )
    AS
    PROC_EQUIPMENT VARCHAR2(32);
    PROC_FHD DATE;
    PROC_LHD DATE;
    PROC_OVS NUMBER(4) := 0;


    PROC_TOLL NUMBER(4) := 0;
    PROC_ROAM NUMBER(4) := 0;
    PROC_CC VARCHAR2(4);
    PROC_NUM VARCHAR2(32);
    PROC_DATE VARCHAR2(32) := '';
    PROC_SUBPRO VARCHAR2(32) := '';
    TYPE RefCurTyp IS REF CURSOR;
    cv1 RefCurTyp;
    BEGIN


    -- makes use of cursor for dynamic sql since we don't want to hard code the market/table name
    OPEN cv1 FOR 'SELECT EQUIPMENT, FHD, LHD, SUB_COUNTRY, SUB_NUM FROM ' || PARM_PREFIX || '_SUBINFO
    where market = '|| PARM_MARKET ||' and imsi = (to_char(' || PARM_IMSI || '))';

    LOOP
    FETCH cv1 INTO PROC_EQUIPMENT, PROC_FHD, PROC_LHD, PROC_CC, PROC_NUM;
    EXIT WHEN cv1%NOTFOUND;
    END LOOP;

    .
    .
    .
    .
    END;

    and when i run the following, an error appears

    SQL> DECLARE
    2 PARM_MARKET NUMBER;
    3 PARM_IMSI VARCHAR2(200);
    4 PARM_PRIORITY NUMBER;
    5 PARM_DATE VARCHAR2(200);
    6
    7 BEGIN
    8 PARM_MARKET := 1;
    9 PARM_IMSI := '6391893750C';
    10 PARM_PRIORITY := 0;
    11 PARM_DATE := '20040807';
    12
    13 SMART.GET_BEHAVIORS ( 'M01', PARM_MARKET, PARM_IMSI, PARM_PRIORITY, NULL, PARM_DATE );
    14 END;
    15 /
    DECLARE
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis
    ORA-06512: at "SMART.GET_BEHAVIORS", line 28
    ORA-06512: at line 13


    SQL>


    but if i remove the 'C' from imsi, the procedure runs, any ideas?

  2. #2
    Join Date
    Sep 2004
    Posts
    17
    PARAM_IMSI already is a character. Why you use to_char to convert that parameter? Tha actuall Statement is: to_char (6391893750C) and that wont work - because 6391893750C is not a numeric value. Without the C it is a numeric value and the to_char will work.

    Alex

  3. #3
    Join Date
    Oct 2004
    Posts
    2

    got it!

    i got it! thanks anyway.... i just changed

    (to_char(' || PARM_IMSI || '))'; to

    (to_char(''' || PARM_IMSI || '''))';


  4. #4
    Join Date
    Sep 2004
    Posts
    16
    The procedure is creating problems while building the query for dynamic execution.
    The problem lies in the interpretation of the varchar2 string PARM_IMSI. Try the following:
    OPEN cv1 FOR 'SELECT EQUIPMENT, FHD, LHD, SUB_COUNTRY, SUB_NUM FROM '|| PARM_PREFIX ||' WHERE IMSI = '||'TO_CHAR(''PARM_IMSI'')';

    Also note that PARM_IMSI is Not enclosed in double quotes - They are 2 single quotes. Hope this would help.

    --Vinita

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Do yourself, and your users, a favour and learn how to do this properly, using bind variables:
    Code:
    OPEN cv1 FOR 'SELECT EQUIPMENT, FHD, LHD, SUB_COUNTRY, SUB_NUM FROM ' || PARM_PREFIX || '_SUBINFO
    where market = :p1 and imsi = to_char(:p2)'
    USING PARM_MARKET, PARM_IMSI;
    Apart from all the benefits of performance, scaling, etc., don't you just find that so much easier to read? And there is no messy doubling of apostrophes!

Posting Permissions

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