Results 1 to 3 of 3

Thread: Logic issue

  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Unanswered: Logic issue

    Hi

    I would like to pass value to field based on the incoming value of specific input field. And i wrote procedure but could not able to execute as there were compilation errors . These are syntax related erors.
    1 CREATE OR replace PROCEDURE Insert_update(p_empno IN OUT NUMBER,
    2 p_dept IN NUMBER,
    3 p_sal IN OUT NUMBER)
    4 IS
    5 v_temp_empno NUMBER(4);
    6 v_dept_col VARCHAR2(64) := 'Dept_'
    7 ||p_dept
    8 || '_Sal';
    9 CURSOR c1 IS
    10 SELECT DISTINCT empno
    11 FROM target_table
    12 WHERE empno = p_empno;
    13 BEGIN
    14 OPEN c1;
    15 FETCH c1 INTO v_temp_empno;
    16 IF c1%notfound THEN
    17 INSERT INTO target_table
    18 (empno,
    19 v_dept_col)
    20 VALUES (p_empno,
    21 p_sal);
    22 ELSE
    23 UPDATE target_table
    24 SET v_dept_col = p_sal
    25 WHERE empno = p_empno;
    26 CLOSE c1;
    27 END IF;
    28* END insert_update;
    SQL> /

    Warning: Procedure created with compilation errors.

    SQL> show errors
    Errors for PROCEDURE INSERT_UPDATE:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    17/5 PL/SQL: SQL Statement ignored
    19/18 PL/SQL: ORA-01733: virtual column not allowed here
    23/5 PL/SQL: SQL Statement ignored
    24/12 PL/SQL: ORA-01733: virtual column not allowed here

  2. #2
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    execute ur dmls with exec immediate. u r deciding the col name dynamically!
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is what Oracle says:

    Quote Originally Posted by Oracle
    ORA-01733: virtual column not allowed here

    Cause: An attempt was made to use an INSERT, UPDATE, or DELETE statement on an expression in a view.

    Action: INSERT, UPDATE, or DELETE data in the base tables, instead of the view.

Posting Permissions

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