When you create a table, by default, the number of bytes used to represent a character in a VARCHAR2 type is 1, irrespective of the characterset of the DB. This may be fine if the characterset is such that it uses just one byte per character. However, if the characterset of the DB is such that there might take more than one byte to represent a character (for example for characterset = AL32UTF8) you might run into troubles, such as the error: ORA-01401: inserted value too large for column.
Sometimes you need to execute dynamic SQL statements. Starting from Oracle8i you can accomplish this task using the EXECUTE IMMEDIATE statement. Here are three examples of how you can take advantage of this great statement.
sql_select := 'SELECT * FROM your_table WHERE field1 = :1'; EXECUTE IMMEDIATE sql_select INTO your_cursor USING your_parameter_for_field1; In this first example I showed how you can use EXECUTE IMMEDIATE to execute the query and put the result into a cursor.
Here are some snippets of code showing how to accomplish this:
CREATE TABLE SampleTable (id number primary key, person XMLType) This first example creates a table with only two columns: id and person. The first is the PK of the table and the second is of XMLType type. The latter is going to contain our XML data.
Now let’s insert one row in the table.
INSERT INTO SampleTable VALUES (1, XMLType('XMLString')) Where you must replace XMLString with any string representing XML.