Results 1 to 4 of 4

Thread: Dynamic SQL

  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Dynamic SQL

    I write a procedure in forms and this is a code:

    PROCEDURE P_EXEC_SQL (sql_text varchar2) IS

    cursor_create number;
    retour number;
    Begin
    cursor_create := dbms_sql.open_cursor;
    dbms_sql.parse (cursor_create,sql_text,dbms_sql.native);
    retour := dbms_sql.execute(cursor_create);
    dbms_sql.close_cursor(cursor_create);
    Exception
    When others then
    dbms_sql.close_cursor(cursor_create);
    dbms_output.put_line('Erreur:'||sqlerrm);

    END;

    But after execution we have some errors:

    Erreur 512 ligne 7,colonne 53
    Implementation Restriction:'DBMS_SQL.NATIVE':cannot directly access remote package variable or cursor
    Erreur 0 ligne 7,colonne 5
    Statement ignored

    I need an help to resolve this problem

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    That reminds me of Oracle 7. If you have 8i or 9i, then try the following:

    PROCEDURE P_EXEC_SQL (sql_text varchar2) IS
    -- no variables needed.
    null;
    BEGIN
    execute immeidate sql_text;
    EXCEPTION
    When others then dbms_output.put_line('Erreur:'||sqlerrm);
    END;

    * If SQL_TEXT is a SELECT statement, then it will fetch data and you must provide a variable to store the results in. For example, execute immeidate sql_text INTO my_variable;
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI,

    you can replace your dbms_sql with exec_sql, with the equivalent parse being exec_sql.parse(...,exec_sql.v7);
    SATHISH .

  4. #4
    Join Date
    Nov 2003
    Posts
    87
    PROCEDURE P_EXEC_SQL (sql_text varchar2) IS

    cursor_create number;
    retour number;
    Begin
    cursor_create := dbms_sql.open_cursor;
    exec_parse(cursor_create,sql_text,exec_sql.v7);
    retour := dbms_sql.execute(cursor_create);
    dbms_sql.close_cursor(cursor_create);
    Exception
    When others then
    dbms_sql.close_cursor(cursor_create);
    dbms_output.put_line('Erreur:'||sqlerrm);

    END;


    Warning: Procedure created with compilation errors.

    SQL> sho err
    Errors for PROCEDURE P_EXEC_SQL:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    6/1 PL/SQL: Statement ignored
    6/35 PLS-00201: identifier 'EXEC_SQL.V7' must be declared


    ????

Posting Permissions

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