Loads a column of the LIST OF BYTE VARYING data type from a text
or binary file without needing to use special application code.
The specified file is opened and each row is read and stored in
the LIST OF BYTE VARYING column specified by the list cursor.
1 – Environment
You can use the INSERT statement in interactive SQL only.
2 – Format
(B)0[m[1;4mINSERT[m [1;4mINTO[m [1;4mCURSOR[m [1mqqqqqq> <cursor-name> qqqqk [m [1m lqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqj [m [1m mqq> [1;4mFILENAME[m[1m qq> <file-spec> qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>[m [1m mq> [1;4mAS[m[1m qwq> [1;4mBINARY[m[1m qqqqqqqqqqqqqwj[m [1m tq> [1;4mTEXT[m[1m qqqqqqqqqqqqqqqu[m [1m mq> [1;4mCHARACTER[m[1m [1;4mVARYING[m[1m qqj[m
3 – Arguments
3.1 – AS
Syntax options:
AS BINARY | AS CHARACTER VARYING | AS TEXT
Specifies whether the file specified with the FILENAME clause
contains these types of data:
o BINARY
Used to load unformatted data such as images and audio files.
The contents are broken into 512 octet segments during INSERT.
o CHARACTER VARYING
Used to load text but with no terminator. The contents are
written one line to a segment.
o TEXT
Used to load text, a terminator is added to each segment
loaded. The contents are written one line to a segment with
trailing terminators carriage return (CR) and line feed (LF).
3.2 – FILENAME filespec
The specification for the file that you want to load into the
LIST OF BYTE VARYING column.
3.3 – INSERT INTO CURSOR cursor-name
The name of the target list cursor to which you want to add a
list segment.
4 – Example
Example 1: Adding a New Row Using Data from a Text File
SQL> -- Declare a table cursor.
SQL> DECLARE TABLE_CURSOR
cont> INSERT ONLY TABLE CURSOR
cont> FOR SELECT * FROM RESUMES;
SQL> -- Open table cursor and insert values.
SQL> OPEN TABLE_CURSOR;
cont> INSERT INTO CURSOR TABLE_CURSOR
cont> VALUES ('10065', NULL);
1 row inserted
SQL> -- Declare a list cursor.
SQL> DECLARE LIST_CURSOR
cont> INSERT ONLY LIST CURSOR
cont> FOR SELECT RESUME WHERE CURRENT OF TABLE_CURSOR;
SQL --Open list cursor.
SQL> OPEN LIST_CURSOR;
SQL> --Load text from file into LIST OF BYTE VARYING column.
SQL> INSERT INTO CURSOR LIST_CURSOR
cont> FILENAME 'resume_10065.sql' AS TEXT;
SQL> CLOSE LIST_CURSOR;
SQL> CLOSE TABLE_CURSOR;
SQL> COMMIT;