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!

Error for a Function to check if a Userimput SQL-Statement is Working or not

Discussion in 'SQL PL/SQL' started by mainman, Apr 19, 2013.

  1. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hey guys,
    i try to compile a function to check if a Userimput sql-statement is runnable.
    the userstatement is compared to a other statement and then i want to look up if the ouput is the same. there is the Minus statement which provides this. but i got following error when i try to compile the function

    Error at line 0: PL/SQL: Compilation unit analysis terminated

    is there a mistake in the syntax?

    Code (Text):
    CREATE OR REPLACE FUNCTION fc_ueberpruefen (fragenID IN tb_FRAGE.FRAGEID%Rowtype, v_statement IN VARCHAR)

    RETURN NUMBER IS v_erg NUMBER;

      v_frage VARCHAR(1000);
      v_ID NUMBER(4);
      v_Statement VARCHAR(1000);
      v_Themekreis VARCHAR(20);
      v_Userergebnis NUMBER(3);
     
    BEGIN

      v_ID := fragenID;
     
      --import of right command from a table
      SELECT FRAGE into v_frage
      FROM tb_frage
      WHERE v_ID = FRAGEID;

     
            -- looking up if the statement is runable
        EXECUTE IMMEDIATE '||v_frage||';
           
    --Minus the userstatenet and the right statement from the dba
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ('||v_frage||' MINUS '||v_statement||')'
    INTO v_erg;



    RETURN v_erg;


    END;
    best wishes.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    1. fragenID IN tb_FRAGE.FRAGEID%Rowtype
    2. v_ID NUMBER(4);

    v_ID := fragenID;

    I am worrying about how v_id := fragenID; Is it possible.
     
  3. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Code (Text):
    SELECT FRAGE into v_frage
      FROM tb_frage
      WHERE fragenID = FRAGEID;
    so i tried this and removed the
    v_ID := fragenID;

    but still get the same Error.

    Code (Text):
    CREATE OR REPLACE FUNCTION fc_ueberpruefen (fragenID IN NUMBER, v_statement IN VARCHAR)
     
    RETURN NUMBER IS v_erg NUMBER;
     
      v_frage VARCHAR(1000);
      v_ID NUMBER(4);
      v_Statement VARCHAR(1000);
      v_Themekreis VARCHAR(20);
      v_Userergebnis NUMBER(3);
     
    BEGIN
     

     
      --import of right command from a table
      SELECT FRAGE into v_frage
      FROM tb_frage
      WHERE fragenID = FRAGEID;
     
     
            -- looking up if the statement is runable
        EXECUTE IMMEDIATE '||v_frage||';
     
    --Minus the userstatenet and the right statement from the dba
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ('||v_frage||' MINUS '||v_statement||')'
    INTO v_erg;
     
     
     
    RETURN v_erg;
     
     
    END;
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hey please share your tb_frage table structure

    fragenID is not a column of table as seen in code.
     
  5. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    [​IMG]

    so here it is as picture from apex object browser
     
  6. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Is this function a part of a package or autonomous function? .. If it is part of a package, then plz provide the pkg spec.
     
  7. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    its an autonomous function, which will be placed in an application
    best wishes
     
  8. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Whats the Oracle DB Version? ... Try and replace all the VARCHAR with VARCHAR2, and also define the variable V_FRAGE as TB_FRAGE.FRAGE%ROWTYPE.
     
  9. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    i did, still got the same error
    "Error at line 0: PL/SQL: Compilation unit analysis terminated"

    working on Application Express 4.1.1.00.23
    best wishes
     
  10. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Share your latest code Here...
     
  11. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Code (Text):
    CREATE OR REPLACE FUNCTION fc_ueberpruefen (fragenID IN NUMBER, v_statement IN VARCHAR)

    RETURN NUMBER IS v_erg NUMBER;

      v_frage TB_FRAGE.FRAGE%ROWTYPE;
      v_ID NUMBER(4);
      v_Statement VARCHAR2(1000);
      v_Themekreis VARCHAR2(20);
      v_Userergebnis NUMBER(3);
     
    BEGIN

       
      --import of right command from a table
      SELECT FRAGE into v_frage
      FROM tb_frage
      WHERE fragenID = FRAGEID;


            -- looking up if the statement is runable
            EXECUTE IMMEDIATE '||v_frage||';
           
    --Minus the userstatenet and the right statement from the dba
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ('||v_frage||' MINUS '||v_statement||')'
    INTO v_erg;


    RETURN v_erg;


    END;
     
  12. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    problem solved by another member of my team
    thx for helping
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I believe the problem is here:

    EXECUTE IMMEDIATE '||v_frage||';

    v_frage is a varchar2 variable so the statement should be:

    EXECUTE IMMEDIATE v_frage;