Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2005
    Posts
    17

    Red face Unanswered: call stored procedure retrieved from variable

    Hi,

    I have a list of stored procedure that are stored in a table

    Table1
    name varchar(30) --> stored procedure names stored here

    I have a query that does this

    v_name varchar(30)

    select name into
    v_name
    from Table1

    then I want to call the stored procedure my referencing the variable

    v_name 'xyz', 'abc'


    I get the error v_name is not a procedure or is undefined

    Please help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    While this could be done via EXECUTE IMMEDIATE, I contend that this "design" is daft and should be discarded.
    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
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Why exactly is the design daft, depending on how its used it can be a very powerful technique. A number of years ago I created a system where depending on the customer type coming in it would authenticate the user using a procedure where the procedure was listed in a table. This allowed us to change the authentication logic without taking the system down. It also got round the problem that because we had somewhere between 100-500 users authenticating every second (v.large ISP) the stored procedure was always running thus you could never change the procedure as it was always 'locked'. By having the procedure call defined in a table you could create a new procedure, put the call in the table and then system would switch automatically. The other advantage is that it can make your code a lot cleaner cutting out lots of if..then logic sometimes.

    Alan

  4. #4
    Join Date
    Aug 2005
    Posts
    17

    Question

    Can someone shed some light on how to do this please.

    Thank you.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Look it up in the PLSQL manual under native dynamic sql

    plsql_block := 'BEGIN calc_proc(:x, :Y); END;'
    EXECUTE IMMEDIATE plsql_block USING a, b;

    Alan

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Another approach I've heard about (though haven't tested myself) is to define object subtypes which have a common interface, and have a column of the parent type in the control table (instead of a VARCHAR2 column containing the name of an arbitrary procedure). Then in your code you can retrieve the object and execute its method without the need for dynamic code.

    I don't know whether that is any more efficient, though. Possibly it is just same thing but with a funkier syntax.

    I've just joined a project where the batch logging function can be configured to use different packages, and it is implemented as dynamic PL/SQL (EXECUTE IMMEDIATE 'BEGIN ' || v_logging_proc || '(:arg1, :arg2); END;' ...). I can't say I'm crazy about the approach.

  7. #7
    Join Date
    Aug 2005
    Posts
    17

    Question

    Can someone please tell me why this errors out.

    execute immediate 'begin '|| v_scriptname || '( :a, :b ); end; '
    using v_id, v_tracking;


    Error message below

    ORA-06550: line 1, column 7:
    PLS-00103: Encountered the symbol "(" when expecting one of the following:

    begin case declare exit for goto if loop mod null pragma
    raise return select update while with <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> <<
    close current delete fetch lock insert open rollback
    savepoint set sql execute commit forall merge pipe
    The symbol "case" was substituted for "(" to continue.
    ORA-06550: line 1, column 17:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:

    * & = - + < / > at in is mod remainder not rem when
    <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
    LIKE4_ LIKEC_ between overlaps || multi
    ORA-06512: at "F.SP_MASTER", line 30
    ORA-06512: at line 2
    Process exited.
    Disconnecting from the database FR - TEST DATABASE.

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Maybe v_striptname is null, or has some invalid value. Display the generated string before executing it.

Posting Permissions

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