CODEWITHSUNDEEP
oracle-databaseplsql
Teysz
Teysz

Reputation: 741

INSERT Statement in PL/SQL fails in Oracle database

When executing an SQL statement in PLSQL with 

DBMS_SQL.EXECUTE('insert into tablename VALUES(aNumber)');

It gives an ASCII character error. And when inserting it directly it succeeds and inserts it.

Upvotes: 1

Views: 3643

Answers (1)

hol
hol

Reputation: 8421

In PL/SQL you could write the INSERT statement directly.

DECLARE
  tablevalue      varchar2(200);
BEGIN
  tablevalue := 'Hello World!';

  INSERT   INTO tablename
         VALUES (tablevalue);
END;

Your statement fails because that is not the way DBMS_SQL.EXECUTE works. Check out the documentation and the example: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#BABBFFFJ

According to the example given in the reference documentation you should do it like this (first you prepare the statement, then bind the variable and then run it).

CREATE OR REPLACE PROCEDURE demo(tablevalue IN varchar2) AS
    cursor_name INTEGER;
    rows_processed INTEGER;
BEGIN
    cursor_name := dbms_sql.open_cursor;
    DBMS_SQL.PARSE(cursor_name, 'INSERT INTO tablename VALUES(:x)',
                   DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', tablevalue);
    rows_processed := DBMS_SQL.EXECUTE(cursor_name);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
    raise;
END;

You use it then like this

 exec demo('something');

Hope it helps

Upvotes: 7

Related Questions