Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    Unanswered: Problem using variables in the function

    Hello!

    I have problem using variable in the DML-statements in my function in this way:

    1) instead of :

    UPDATE TEXT SET ... WHERE LANGUAGE_1 = STR_LANG;

    2) this form is my goal:
    EXECUTE IMMEDIATE 'UPDATE TEXT ... WHERE LANGUAGE_'|| NUM_LANG || '= STR_LANG ';


    But i get this error:

    ORA-00904: invalid column name ORA-06512 ....

    (because of LANGUAGE_'|| NUM_LANG || ' )


    I can compile it without errors. But not use!

    #########################################

    My whole function:

    CREATE OR REPLACE FUNCTION FNC_SAVE_TEXT
    (
    pText IN VARCHAR2,
    pClass IN VARCHAR2
    )
    RETURN NUMBER IS
    NUM_LANG NUMBER;
    STR_LANG VARCHAR2(250);
    STR_CLASS VARCHAR2(25);
    NUM_ID NUMBER;
    BEGIN

    NUM_LANG := 1;
    --NUM_LANG := PKG_GLOBAL.G_LANGUAGE;

    STR_CLASS := pClass;
    STR_LANG := pText;

    --UPDATE TEXT SET CHANGED_ON = SYSDATE, CHANGED_BY = USER, CLASSIFICATION = STR_CLASS WHERE LANGUAGE_1 = STR_LANG;
    EXECUTE IMMEDIATE 'UPDATE TEXT SET CHANGED_ON = SYSDATE, CHANGED_BY = USER, CLASSIFICATION = STR_CLASS WHERE LANGUAGE_'|| NUM_LANG || '= STR_LANG ';

    IF SQL%ROWCOUNT = 0 THEN
    EXECUTE IMMEDIATE'INSERT INTO TEXT(LANGUAGE_' || NUM_LANG || ' , CLASSIFICATION) VALUES(STR_LANG, STR_CLASS)';
    EXECUTE IMMEDIATE'SELECT ID_TEXT INTO NUM_ID FROM TEXT WHERE LANGUAGE_' || NUM_LANG || ' = STR_LANG';
    ELSE
    EXECUTE IMMEDIATE'SELECT ID_TEXT INTO NUM_ID FROM TEXT WHERE LANGUAGE_' || NUM_LANG || ' = STR_LANG';
    UPDATE TEXT SET CHANGED_ON = SYSDATE, CHANGED_BY = USER, CLASSIFICATION = STR_CLASS WHERE ID_TEXT = NUM_ID;
    END IF;

    RETURN (NUM_ID);
    END FNC_SAVE_TEXT;
    /


    It would be great if anybody could help me.

    Thanks in advance,
    Julia

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi,

    Plz Find the the Language_? COLUMN is Exists in the TEXT TABLE

  3. #3
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hi,

    i have this structure:

    CREATE TABLE TEXT
    (
    ID_TEXT INTEGER,
    CREATED_ON DATE,
    CREATED_BY VARCHAR2(20),
    CHANGED_ON DATE,
    CHANGED_BY VARCHAR2(20),
    LANGUAGE_1 VARCHAR2(100),
    LANGUAGE_2 VARCHAR2(100),
    ...
    CLASSIFICATION VARCHAR2(25)
    );

    I tested it without using the variable, e.g. LANGUAGE_1,.. - it works.

    The problem is really only at

    LANGUAGE_'|| NUM_LANG || '.

    Any idea?

    Thanks in advance,
    Julia

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try setting column_name to a variable ....

    NUM_LANG NUMBER;
    STR_LANG VARCHAR2(250);
    STR_CLASS VARCHAR2(25);
    NUM_ID NUMBER;
    v_column_name varchar2(50); -- <==

    BEGIN

    NUM_LANG := 1;
    --NUM_LANG := PKG_GLOBAL.G_LANGUAGE;

    STR_CLASS := pClass;
    STR_LANG := pText;

    --UPDATE TEXT SET CHANGED_ON = SYSDATE, CHANGED_BY = USER, CLASSIFICATION = STR_CLASS WHERE LANGUAGE_1 = STR_LANG;
    v_column_name := 'LANGUAGE_'||NUM_LANG; -- <==

    EXECUTE IMMEDIATE 'UPDATE TEXT SET CHANGED_ON = SYSDATE, CHANGED_BY = USER, CLASSIFICATION = STR_CLASS WHERE '||v_column_name|| '= STR_LANG '; -- <==


    HTH
    Gregg

  5. #5
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    yes It works.

  6. #6
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello All!

    Thanks for your help!

    The solution if Gbrabham functions,
    but only for UPDATE-Statements. INSERT cannot do it!
    May be here is something wrong?:

    #########################################

    CREATE OR REPLACE FUNCTION FNC_GET_ID_FROM_TEXT
    (
    pText IN VARCHAR2,
    pClass IN VARCHAR2
    )
    RETURN NUMBER IS
    NUM_LANG NUMBER;
    STR_LANG VARCHAR2(250);
    STR_CLASS VARCHAR2(25);
    NUM_ID NUMBER;

    V_COL VARCHAR2(50); -- gbrabham
    BEGIN
    NUM_LANG := 1;
    STR_CLASS := pClass;
    STR_LANG := pText;
    V_COL := 'LANGUAGE_' || NUM_LANG; -- gbrabham

    UPDATE TEXT SET CHANGED_ON = SYSDATE, CHANGED_BY = USER, CLASSIFICATION = STR_CLASS WHERE V_COL = STR_LANG;

    IF SQL%ROWCOUNT = 0 THEN
    --INSERT INTO TEXT( V_COL , CLASSIFICATION) VALUES(STR_LANG, STR_CLASS);
    EXECUTE IMMEDIATE 'INSERT INTO TEXT( ' || V_COL || ' , CLASSIFICATION) VALUES(STR_LANG, STR_CLASS)';
    SELECT ID_TEXT INTO NUM_ID FROM TEXT WHERE V_COL = STR_LANG;
    ELSE
    EXECUTE IMMEDIATE 'SELECT ID_TEXT INTO NUM_ID FROM TEXT WHERE ' || V_COL || ' = STR_LANG';
    UPDATE TEXT SET CHANGED_ON = SYSDATE, CHANGED_BY = USER, CLASSIFICATION = STR_CLASS WHERE ID_TEXT = NUM_ID;
    END IF;

    RETURN (NUM_ID);
    END;
    /

    ######################################

    Error_message:
    ORA-00984: column not allowed here

    ######################################

    If i use instead of V_COl --> LANGUAGE_1 ==> it works!

    ######################################


    Any idea?

    Thanks in advance for your help!

  7. #7
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi Julla,

    Check it our ur assign variable. I tried out this code. It works.

  8. #8
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hi Saravanan.R,

    i can compile this function, but i cannot call it, e.g.:

    BEGIN

    PRC_INS_UPD_TEST('Hello', FNC_GET_ID_FROM_TEXT('Hello', 'test'), FNC_GET_ID_FROM_TEXT('Hello', 'test'));

    END;

    #################

    ORA-00984: column not allowed here
    ORA-06512: at "FNC_GET_ID_FROM_TEXT", line 21
    ORA-06512: at line 2

    Thank you!
    Last edited by julla27; 04-05-04 at 05:38.

  9. #9
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hi SaravananR.,

    Here, in the attachment are tables/../functions the function FNC_GET_ID_FROM_TEXT depends on.

    Thank you for your help.
    Attached Files Attached Files

  10. #10
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi Julia,

    Here the ERROR DESCRIPTION that u occured. check this.

    ORA-00984 column not allowed here
    Cause: A column name was used in an expression where it is not permitted,
    such as in the VALUES clause of an INSERT statement.
    Action: Check the syntax of the statement and use column names only where
    appropriate.

  11. #11
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello,

    thank you very much for your help!

    The Problem occurs because of Column_type = VARCHAR2!

    Can i make a conversion

    V_COL VARCHAR2(50); -- gbrabham
    V_COL_NAME COLS.COLUMN_NAME%TYPE;

    BEGIN
    V_COL := 'LANGUAGE_1'; -- gbrabham
    ??

    ...

    Or is it a completely wrong solution?
    I try to do it, but get no positive results.


    Regards,
    Julia

  12. #12
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hi!

    It works!
    But on an other way: i just implemented a parameter LanguageId, that allows me to distinct in the function:


    CREATE OR REPLACE FUNCTION FNC_GET_ID_FROM_TEXT
    (
    pText IN VARCHAR2,
    pClass IN VARCHAR2,
    pLanguage IN INTEGER ------------ <--
    )
    RETURN NUMBER IS
    ....
    NUM_LANG NUMBER; ------------ <--
    NUM_ID NUMBER;
    BEGIN

    NUM_LANG := pLanguage; ------------ <--
    STR_CLASS := pClass;
    STR_LANG := pText;

    IF NUM_LANG = 1 THEN ------------ <--
    UPDATE TEXT SET CHANGED_ON = SYSDATE, CHANGED_BY = USER, CLASSIFICATION = STR_CLASS
    WHERE LANGUAGE_1 = STR_LANG ;
    .................................................
    ELSIF NUM_LANG = 2 THEN ------------ <--
    UPDATE TEXT SET CHANGED_ON = SYSDATE, CHANGED_BY = USER, CLASSIFICATION = STR_CLASS
    WHERE LANGUAGE_2 = STR_LANG ;
    ....................................
    ELSE
    RAISE_APPLICATION_ERROR(-20000, 'test_err_msg');
    END IF;


    RETURN (NUM_ID);
    END FNC_GET_ID_FROM_TEXT;
    /




    #####################################


    Anyway:

    Thanks you all for your help!

    Julia

Posting Permissions

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