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

    Unanswered: Creating function error occurs

    Hello!

    Creating the following function:

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

    CREATE OR REPLACE FUNCTION FNC_BLN_CHECK_EXISTS
    (
    pTable IN VARCHAR2(50),
    pId IN VARCHAR2(25),
    pParam IN VARCHAR2(50)
    )
    RETURN BOOLEAN IS
    BLN_EXISTS BOOLEAN;
    NUM_COUNT NUMBER;
    BEGIN

    -- SELECT COUNT(*) from server where ID_SERVER = 2;

    SELECT COUNT(*) INTO NUM_COUNT FROM pTable WHERE pId = pParam;

    IF NUM_COUNT > 0 THEN
    BLN_EXISTS = TRUE;
    ELSE
    BLN_EXISTS = FALSE;
    END IF;

    RETURN(BLN_EXISTS);

    END FNC_BLN_CHECK_EXISTS;



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


    i've gotten this error-message:

    NO CREATE OR REPLACE statemant found to execute

    (S2720) Expecting: ) , := CHARACTER DEFAULT



    Where is my mistake(-s)?
    Any idea?
    Regards,
    Julia

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

    I have corrected the FUNCTION that u made obvious mistake take a look here .

    --------------------------------------------------------------------
    CREATE OR REPLACE FUNCTION FNC_BLN_CHECK_EXISTS
    (
    pTable IN VARCHAR2,
    pId IN VARCHAR2,
    pParam IN VARCHAR2
    )
    RETURN BOOLEAN IS
    BLN_EXISTS BOOLEAN;
    NUM_COUNT NUMBER;
    BEGIN

    -- SELECT COUNT(*) from server where ID_SERVER = 2;

    SELECT COUNT(*) INTO NUM_COUNT FROM pTable WHERE pId = pParam;

    IF NUM_COUNT > 0 THEN
    BLN_EXISTS := TRUE;
    ELSE
    BLN_EXISTS := FALSE;
    END IF;

    RETURN(BLN_EXISTS);

    END FNC_BLN_CHECK_EXISTS;
    /


    ------------------------------------------------------------------

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

    error again!

    Thank You,
    But now the next error occurs:

    PLS-00201: identifier 'PTABLE' must be declared

    Why?

    Perhaps, i can use only column-variables, e.g. :
    CREATE OR REPLACE Function TEST
    ( pTable ALL_CONS_COLUMNS.TABLE_NAME%TYPE,
    pID ALL_CONS_COLUMNS.COLUMN_NAME %TYPE,
    ...
    ) AS ...????
    Last edited by julla27; 03-25-04 at 06:19.
    Regards,
    Julia

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

    now I have modified the SELECT STATMENT take look now it works

    ----------------------------------------------------------------

    CREATE OR REPLACE FUNCTION FNC_BLN_CHECK_EXISTS
    (
    pTable IN VARCHAR2,
    pId IN VARCHAR2,
    pParam IN VARCHAR2
    )
    RETURN BOOLEAN IS
    BLN_EXISTS BOOLEAN;
    NUM_COUNT NUMBER;
    BEGIN

    -- SELECT COUNT(*) from server where ID_SERVER = 2;

    EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO NUM_COUNT FROM ' || pTable ||' WHERE pId = pParam';

    IF NUM_COUNT > 0 THEN
    BLN_EXISTS := TRUE;
    ELSE
    BLN_EXISTS := FALSE;
    END IF;

    RETURN(BLN_EXISTS);

    END FNC_BLN_CHECK_EXISTS;
    /

    -------------------------------------------------------------

    Regards
    Saravanan.R

  5. #5
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Great, it functions!


    Thank You!
    Regards,
    Julia

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    BTW, you can simplify this:
    PHP Code:
    IF NUM_COUNT 0 THEN
      BLN_EXISTS 
    := TRUE;
    ELSE
      
    BLN_EXISTS := FALSE;
    END IF;

    RETURN(
    BLN_EXISTS); 
    to this:
    PHP Code:
    RETURN (NUM_COUNT 0); 
    Use the power of Booleans!

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

    May be i'm blind and cannot see a simple noticeable mistake, but
    why making this statement:
    #############################################
    BEGIN
    IF FNC_BLN_CHECK_EXISTS('LOCATION', 'ID_LOC', 'TST') THEN
    PRC_ALTER_LOCATION('TST', 'test', 'test', 'test');
    ELSE
    PRC_ADD_NEW_LOCATION('TST', 'test', 'test', 'test');
    END IF;
    END;
    #############################################

    i get this error-message:

    ORA-00905: missing keyword
    ORA-06512: at "FNC_BLN_CHECK_EXISTS", line 14 --> 'select ...'

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

    my function:

    CREATE OR REPLACE FUNCTION FNC_BLN_CHECK_EXISTS
    (
    pTable IN VARCHAR2,
    pId IN VARCHAR2,
    pParam IN VARCHAR2
    )
    RETURN BOOLEAN IS
    NUM_COUNT NUMBER;
    BEGIN

    EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO NUM_COUNT FROM '
    || pTable ||' WHERE ' || pId || ' = ' || pParam || ' ';


    RETURN (NUM_COUNT > 0);


    END FNC_BLN_CHECK_EXISTS;
    /
    ############################################

    PRC_ALTER_LOCATION and PRC_ADD_NEW_LOCATION are OK.



    Thanks in advance!
    Regards,
    Julia

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    this bit:
    PHP Code:
    EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO NUM_COUNT FROM ' 
    || pTable ||' WHERE ' || pId || ' = ' || pParam || ' '
    should be:
    PHP Code:
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' 
    || pTable ||' WHERE ' || pId || ' = ' || pParam
    INTO NUM_COUNT 


  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Actually, what you are trying to do is more usually done like this:
    PHP Code:
    CREATE OR REPLACE PROCEDURE ins_upd_location
    p_id_loc in location.id_loc%type
    p_col1 in location.col1%type
    p_col2 in location.col2%type
    p_col3 in location.col3%type
    )
    BEGIN
      update location
      set col1 
    p_col1col2 p_col2col3 p_col3
      where id_loc 
    p_id_loc;
      if 
    sql%rowcount 0 then
        insert into location
    id_loccol1col2col3 values (p_id_locp_col1p_col2p_col3 );
      
    end if;
    END;

    BEGIN
      ins_upd_loc
    'TST''test''test''test' );
    END

  10. #10
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Thanx andrew It works.

  11. #11
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Yes!

    Thanks to all,
    it functions!
    Regards,
    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
  •