Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    6

    Angry Unanswered: Using Dynamic SQL To call Stored Procedures

    Hi,

    Is it possible to use the package DBMS_SQL to call dynamically STORED PROCEDURES? I got an exception in the DBMS_SQL.PARSE method when I execute the following in TOAD:

    declare
    sqlstr VARCHAR2(100);
    cid INTEGER;

    procedure myproc( value CHAR )
    is
    begin
    dbms_output.put_line( ' Inside Out, boy u turn me !! - ' || value );
    end myproc;

    begin
    cid := DBMS_SQL.OPEN_CURSOR;
    sqlstr := 'myproc( :v )';
    DBMS_SQL.BIND_VARIABLE_CHAR( cid, 'v', 'Hello World!' );
    DBMS_SQL.PARSE( cid, sql_str, dbms_sql.v7 );
    DBMS_SQL.CLOSE_CURSOR( cid );
    end;

    Any Idea?

    Thanks

  2. #2
    Join Date
    Aug 2002
    Posts
    6

    Correction

    Due to error when typing the code,
    in the example The DBMS_SQL.PARSE(...) call IS BEFORE The DBMS_SQL.BIND_VARIABLE_CHAR(...)...

  3. #3
    Join Date
    Aug 2002
    Posts
    6

    Talking I FOUND THE ANSWER

    I got the reply from another forum. The following should work for Oracle 7.x.x and above :

    declare
    sqlstr VARCHAR2(100);
    cid INTEGER;

    procedure myproc( value CHAR )
    is
    begin
    dbms_output.put_line( ' Inside Out, boy u turn me !! - ' || value );
    end myproc;

    begin
    cid := DBMS_SQL.OPEN_CURSOR;
    sqlstr := 'begin myproc( :v ); end;';
    DBMS_SQL.PARSE( cid, sql_str, dbms_sql.v7 );
    DBMS_SQL.BIND_VARIABLE_CHAR( cid, 'v', 'Hello World!' );
    DBMS_SQL.EXECUTE( cid );
    DBMS_SQL.CLOSE_CURSOR( cid );
    end;

    The myproc should be declared and created in a package, i provided here as an example.

    enjoy

  4. #4
    Join Date
    Mar 2002
    Posts
    19

    Re: Using Dynamic SQL To call Stored Procedures

    sorry but
    these so called packages
    must be bought ?

  5. #5
    Join Date
    Sep 2002
    Location
    Quebec
    Posts
    5

    Re: Using Dynamic SQL To call Stored Procedures

    Originally posted by townscream
    sorry but
    these so called packages
    must be bought ?
    which one DBMS_SQL?? No it's part of the DBMS

Posting Permissions

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