1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to give to_char expression in a string

Discussion in 'Oracle Forms and Reports' started by aakishore, Sep 12, 2013.

  1. aakishore

    aakishore Guest

    I am trying to create a table by calling a procedure. Table name is passed as an argument to proc and in runtime table need to be created.

    Below the sql snapshot.

    create or replace
    PROCEDURE TESTPROC12
    (
    P_ARG1 IN VARCHAR2,
    P_ARG2 IN VARCHAR2,
    P_FRM_DATE IN DATE,
    P_TO_DATE IN DATE,
    P_TMP_Table IN VARCHAR2
    )
    AUTHID CURRENT_USER
    IS
    V_SQL_STMT1 varchar2(1000);
    BEGIN

    V_SQL_STMT1 := 'CREATE TABLE '|| P_TMP_Table||' AS
    SELECT DISTINCT ESS.CUSTOMERID, TYPE_ID, PRODUCT_ID,
    to_char('Period_date','MM/DD/YYYY') FROM EDELIVERY_STATEMENT_SENT ESS WHERE
    ESS.PROCESSED_DATE BETWEEN ''' ||P_FRM_DATE|| ''' AND ''' || P_TO_DATE || '''
    AND ESS.REGION_ID = ''' || P_ARG1 ||'''
    AND ESS.COUNTRY_ID = ''' ||P_ARG2|| '''';

    EXECUTE IMMEDIATE V_SQL_STMT1;
    END;

    Please advise how to give to_char expression in a string. Thanks in advance.

    Getting the below error.
    Error(19,28): PLS-00103: Encountered the symbol "Period_date" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || member SUBMULTISET_
     
  2. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi Kishore...

    The problem i feel is in to_char try changing it like this to_char(Period_date,'MM/DD/YYYY'), remove the singe quotes on the column name period_date which is the issue.
    if not working let me know i will figure it out...

    Cheers
    Kamal(kamal.love@gmail.com)