Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    56

    Exclamation Unanswered: how to use tablename.columnname in procedure

    Hello Friends,

    I have a procedure that have table name and column name as incoming parameters - I want to declare a variable of type %type taking the paramenters into consideration .. example

    PROCEDURE UPDATE_AUDIT_TABLE( TABLE_NAME VARCHAR2, COL_NAME VARCHAR2, ) IS
    v_table_name varchar2(20) := TABLE_NAME;
    v_col_name varchar2(20) := COL_NAME ;
    v_temp_old_value v_table_name||'.'||v_col_name||'%TYPE' ;.
    .
    .

    END UPDATE_AUDIT_TABLE;

    I am getting error ..on the statment in BOLD

    can you pls advice ?

    thanks/kumar

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am getting error ..on the statment in BOLD
    ERROR?
    What Error?
    I don't see any error.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2009
    Posts
    56

    Exclamation how to use tablename.columnname in procedure

    Dear sir ,

    Have u run the sample or just seeing the code u r telling u r views..

    The error is basically syntax error .. and is most common happens if we don't put semicolon ;or open bracket ex - (

    just take an example with tablename and column as parameters and try to use the same way i did .. u will find the error ..

    thanks/mike

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am sorry to see that COPY & PASTE are broken for you
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2009
    Posts
    56
    Here's the complete procedure ..

    PROCEDURE Proc_Update_Audit_Table( TABLE_NAME VARCHAR2, FAMID NUMBER, MEMBNO NUMBER, COL_NAME VARCHAR2, NEW_VALUE VARCHAR2) IS
    v_temp_old_value TABLE_NAME ||'.'|| COL_NAME ||'%'||'type' ;
    BEGIN
    v_location := 0;

    SELECT USER INTO gv_user_name FROM DUAL;
    gv_date_time := to_char(sysdate, 'DD-MON-YYYY HH:MIS');

    IF ( MEMBNO IS NULL ) THEN
    SELECT COL_NAME INTO v_temp_old_value FROM TABLE_NAME WHERE FAMID = FAMID ;
    ELSE
    SELECT COL_NAME INTO v_temp_old_value FROM TABLE_NAME WHERE FAMID = FID AND MEMBNO = MEMBNO ;
    END IF;
    INSERT INTO AUDIT_TABLE_RECORDS VALUES ( TABLE_NAME , FAMID , MEMBNO, COL_NAME ,v_temp_old_value, NEW_VALUE , gv_user_name, gv_date_time );
    EXCEPTION
    WHEN OTHERS THEN
    v_err_code := SQLCODE;
    v_err_msg := substr(SQLERRM, 1, 200);
    INSERT INTO audit_table (error_number, error_message) VALUES (v_err_code, v_err_msg);
    error_logging(p_error_code => substr(sqlerrm,1,9), p_error_message => substr(sqlerrm,12), p_package =>'PKG_FCI_APP',p_procedure => 'UPDATE_AUDIT_TABLE ' , p_location => v_location);
    END Proc_Update_Audit_Table;


    The error is ..due to line in Bold

    Error(20,29): PLS-00103: Encountered the symbol "|" when expecting one of the following: := . ( @ % ; not null range default character

    Error(20,60): PLS-00103: Encountered the symbol ";" 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

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by MIKELALA View Post
    I have a procedure that have table name and column name as incoming parameters - I want to declare a variable of type %type taking the paramenters into consideration
    I wonder, why do you want to do this. As the only usage of that variable is storing it to some AUDIT_TABLE_RECORDS column with static data type (hopefully VARCHAR2 with sufficient length), there is no reason for giving it different data type. Oracle will convert it implicitly in that SELECT statement. You would be able to perform only a few (if any) 'generic' actions on that column anyway.

    If 'COL_NAME' and 'TABLE_NAME' are not actual column/table names, you will have to run that SELECTs dynamically. Have a look at EXECUTE IMMEDIATE statement. If you do not want to kill the performance, do not forget binding.

Posting Permissions

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