Results 1 to 2 of 2

Thread: Please Help

  1. #1
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185

    Angry Unanswered: Please Help

    I'm trying to run the query below to do some testing and I'm getting the error:

    ORA-06502: PL/SQL: numeric or value error: character to number conversion error

    CREATE OR REPLACE function "SCOTT"."ATTACK_FUNC" return VARCHAR2
    authid current_user as
    pragma autonomous_transaction;
    DECLARE TEST number;
    BEGIN
    EXECUTE IMMEDIATE 'GRANT DBA TO SCOTT';
    COMMIT;
    RETURN '';
    END;
    /

    BEGIN
    SYS.DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION('''|| SCOTT.ATTACKER_FUNC()||''');
    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Procedure DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION requires a numeric input parameter:
    Code:
    SQL> desc DBMS_CDC_SUBSCRIBE
    PROCEDURE ACTIVATE_SUBSCRIPTION
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     SUBSCRIPTION_HANDLE            NUMBER                  IN
    PROCEDURE DROP_SUBSCRIBER_VIEW
    ...
    You appear to be trying to call it with a string parameter. At the moment what you are passing in is literally this:
    Code:
    SQL> select '''|| SCOTT.ATTACKER_FUNC()||''' as param from dual;
    
    PARAM
    ----------------------------
    '|| SCOTT.ATTACKER_FUNC()||'
    (i.e. an apostrophe, two pipe symbols, a space, ...etc.)
    It looks like you intended to pass in the return value of that function enclosed in quotes, i.e. a string of 2 apostrophes (since the function always returns an empty string):
    Code:
    SQL> select ''''|| SCOTT.ATTACKER_FUNC()||'''' as param from dual;
    
    PA
    --
    ''
    (Note that apostrophes need to be doubled up inside the string.)

    Then again, maybe you intended to pass in the name of the function?
    Code:
    SQL> select 'SCOTT.ATTACKER_FUNC' as param from dual;
    
    PARAM
    -------------------
    SCOTT.ATTACKER_FUNC

Posting Permissions

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