Re: Dynamic SQL with DBMS_SQL Package

M,

     You need to have a space between the quote and the SET. You may also want to forgo the separate calls to BIND_VARIABLE and include password_in in the PARSE:   DBMS_SQL.PARSE(select_cursor,

       'UPDATE ' || tablename_in || ' SET password = ''' ||
        password_in || '''', DBMS_SQL.V7);

• dbms_sql.bind_variable (select_cursor, 'x', :password_in);

Jay!!!

mmellin_at_my-dejanews.com wrote:

> I am trying to create a procedure using Dynamic SQL. I havn'e used this
> package and need info. I am not sure how to build the string. Here is an
> example of the code and the error message. It appears that everything after
> the || tablename_in || is being ignored. Could someone please help.
>
> CREATE OR REPLACE PROCEDURE RESPONSENOW.test
> (tablename_in IN VARCHAR2,
> password_in IN VARCHAR2)
> is
> -- This procedure will select a record of a table. ID of type NUMBER,
> -- - NAME of type VARCHAR2(30),-- - BIRTHDATE of type DATE.
> select_cursor INTEGER;
> executed INTEGER;
> BEGIN
> -- prepare a cursor to update the table
> select_cursor := dbms_sql.open_cursor;
> DBMS_SQL.PARSE(select_cursor,
> 'UPDATE ' || tablename_in || 'SET password = :x', DBMS_SQL.V7);
> dbms_sql.bind_variable (select_cursor, ':x', password_in);
> executed := dbms_sql.execute(select_cursor);
> COMMIT;
> dbms_sql.close_cursor(select_cursor);
> END;
>
> Error messages
>
> ORA-00971: missing SET keyword
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "RESPONSENOW.X#X$TEST", line 13
> ORA-06512: at line 1
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Oct 30 1998 - 11:44:49 CST