One of most common uses of Oracle’s SQL*Plus is running text file scripts; either using START or, more commonly, the @ or @@ commands. Most of the time START and @ are interchangeable for initiating the execution of a script. @@ behaves similarly but with documented path differences.
So, while they appear to be mostly equivalent, the @ and @@ commands have a slightly different impact on the SQL*Plus input parser. That is, when you use START, the start command itself is parsed with the file name or url along with the script parameters as arguments to START.
When @ or @@ are used, the contents of the script are embedded into the input buffer as if they were typed. This has the interesting side effect of allowing you to send the contents of a file on your local machine to a remote database engine.
For example, let’s first look at the following simple pl/sql block and its output:
SQL> DECLARE 2 v_text VARCHAR2(32767) := ' 3 My sample text line 1 4 My sample text line 2 5 '; 6 BEGIN 7 DBMS_OUTPUT.put_line(LTRIM(RTRIM(v_text, CHR(10) || CHR(13)), CHR(10) || CHR(13))); 8 END; 9 / My sample text line 1 My sample text line 2 PL/SQL procedure successfully completed.
But, now, instead of embedding the 2 lines of text inside the pl/sql block directly, I’ll put text in a file on my pc (c:\temp\test.txt) and then I will use the @ command to include the file as part of the block.
SQL> DECLARE 2 v_text VARCHAR2(32767) := ' 3 @c:\temp\test.txt 5 '; 6 BEGIN 7 DBMS_OUTPUT.put_line(LTRIM(RTRIM(v_text, CHR(10) || CHR(13)), CHR(10) || CHR(13))); 8 END; 9 / My sample text line 1 (from test file) My sample text line 2 (from test file) PL/SQL procedure successfully completed.
In this example the usage was silly, I send the file contents to the database to process in a pl/sql block just to have them echoed back to me with DBMS_OUTPUT. Nevertheless, it demonstrates the functionality.
Next, let’s look at using the functionality to be a little more useful and load local data to a remote table. In this first pass, I’ll embed the expected contents of the SCOTT.EMP table directly within my pl/sql block as source csv text to populate the table. I’ll use a simple text splitter to first split the csv into lines, then again for each line into fields. With simple iteration I walk through the collections and populate the table.
SQL> TRUNCATE TABLE emp;
Table truncated.
SQL> DECLARE
2 v_csv VARCHAR2(32767) := '
3 7369,SMITH,CLERK,7902,1980-12-17,800,,20
4 7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
5 7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
6 7566,JONES,MANAGER,7839,1981-04-02,2975,,20
7 7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
8 7698,BLAKE,MANAGER,7839,1981-05-01,2850,,30
9 7782,CLARK,MANAGER,7839,1981-06-09,2450,,10
10 7788,SCOTT,ANALYST,7566,1987-04-19,3000,,20
11 7839,KING,PRESIDENT,,1981-11-17,5000,,10
12 7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
13 7876,ADAMS,CLERK,7788,1987-05-23,1100,,20
14 7900,JAMES,CLERK,7698,1981-12-03,950,,30
15 7902,FORD,ANALYST,7566,1981-12-03,3000,,20
16 7934,MILLER,CLERK,7782,1982-01-23,1300,,10
17 ';
18 v_lines DBMS_SQL.varchar2_table;
19 v_fields DBMS_SQL.varchar2_table;
20
21 FUNCTION split_text(p_text IN VARCHAR2, p_delimiter IN VARCHAR2)
22 RETURN DBMS_SQL.varchar2_table
23 IS
24 v_length NUMBER := LENGTH(p_text);
25 v_start NUMBER := 1;
26 v_index NUMBER;
27 v_results DBMS_SQL.varchar2_table;
28 BEGIN
29 WHILE (v_start <= v_length)
30 LOOP
31 v_index := INSTR(p_text, p_delimiter, v_start);
32
33 IF v_index = 0
34 THEN
35 v_results(v_results.COUNT + 1) := SUBSTR(p_text, v_start);
36 v_start := v_length + LENGTH(p_delimiter);
37 ELSE
38 v_results(v_results.COUNT + 1) := SUBSTR(p_text, v_start, v_inde
39 v_start := v_index + +LENGTH(p_delimiter);
40 END IF;
41 END LOOP;
42
43 RETURN v_results;
44 END;
45 BEGIN
46 v_lines := split_text(v_csv, CHR(10));
47
48 FOR i IN 1 .. v_lines.COUNT
49 LOOP
50 IF TRIM(v_lines(i)) IS NULL
51 THEN
52 CONTINUE;
53 END IF;
54
55 v_fields.delete;
56 v_fields := split_text(v_lines(i), ',');
57
58 INSERT INTO emp(empno,
59 ename,
60 job,
61 mgr,
62 hiredate,
63 sal,
64 comm,
65 deptno)
66 VALUES (TO_NUMBER(v_fields(1), '9999'),
67 v_fields(2),
68 v_fields(3),
69 TO_NUMBER(v_fields(4), '9999'),
70 TO_DATE(v_fields(5), 'yyyy-mm-dd'),
71 TO_NUMBER(v_fields(6), '9999'),
72 TO_NUMBER(v_fields(7), '9999'),
73 TO_NUMBER(v_fields(8), '9999'));
74 END LOOP;
75 END;
76 /
PL/SQL procedure successfully completed.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
I know the string parsing could be done within a single sql statement, either directly or using text manipulation functions I’ve posted before. And I’m aware the inserts would be more efficient as a bulk operation; but I wanted to demonstrate the process using loops to show how you can add your own special processing of your text data as you read through it. In this example I’m only doing type conversions, and every line uses the same ones; but each line of a more complicated source could be processed differently, possibly resulting in different target tables for each line or field; thus making bulk operations impossible.
Next we’ll do the same thing, except this time the data will be sourced from a file instead of embedded directly into the pl/sql block. Note the file referenced at line 3 instead of the data.
SQL> TRUNCATE TABLE emp;
Table truncated.
SQL> DECLARE
2 v_csv VARCHAR2(32767) := '
3 @c:\temp\emp.csv
17 ';
18 v_lines DBMS_SQL.varchar2_table;
19 v_fields DBMS_SQL.varchar2_table;
20
21 FUNCTION split_text(p_text IN VARCHAR2, p_delimiter IN VARCHAR2)
22 RETURN DBMS_SQL.varchar2_table
23 IS
24 v_length NUMBER := LENGTH(p_text);
25 v_start NUMBER := 1;
26 v_index NUMBER;
27 v_results DBMS_SQL.varchar2_table;
28 BEGIN
29 WHILE (v_start <= v_length)
30 LOOP
31 v_index := INSTR(p_text, p_delimiter, v_start);
32
33 IF v_index = 0
34 THEN
35 v_results(v_results.COUNT + 1) := SUBSTR(p_text, v_start);
36 v_start := v_length + LENGTH(p_delimiter);
37 ELSE
38 v_results(v_results.COUNT + 1) := SUBSTR(p_text, v_start, v_index - v_start);
39 v_start := v_index + +LENGTH(p_delimiter);
40 END IF;
41 END LOOP;
42
43 RETURN v_results;
44 END;
45 BEGIN
46 v_lines := split_text(v_csv, CHR(10));
47
48 FOR i IN 1 .. v_lines.COUNT
49 LOOP
50 IF TRIM(v_lines(i)) IS NULL
51 THEN
52 CONTINUE;
53 END IF;
54
55 v_fields.delete;
56 v_fields := split_text(v_lines(i), ',');
57
58 INSERT INTO emp(empno,
59 ename,
60 job,
61 mgr,
62 hiredate,
63 sal,
64 comm,
65 deptno)
66 VALUES (TO_NUMBER(v_fields(1), '9999'),
67 v_fields(2),
68 v_fields(3),
69 TO_NUMBER(v_fields(4), '9999'),
70 TO_DATE(v_fields(5), 'yyyy-mm-dd'),
71 TO_NUMBER(v_fields(6), '9999'),
72 TO_NUMBER(v_fields(7), '9999'),
73 TO_NUMBER(v_fields(8), '9999'));
74 END LOOP;
75 END;
76 /
PL/SQL procedure successfully completed.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Another feature of this is the URL processing of @ commands. In most cases I’ve seen, @ is used to run a local file, but START, @, and @@ are all capable of referencing a script at a remote URL from HTTP or FTP. The same embedding nature for @ and @@ works here as well. Thus you can use a simple pl/sql block to read a remote file and process the contents. This can be useful if your pc, with you logged in has different web permissions than those of the database server, database, and remote schema.
Here’s I’ll simply dump the contents of a remote file with DBMS_OUTPUT similar to what we did in the second code block above.
SQL> DECLARE
2 v_text VARCHAR2(32767) := '
3 @http://people.sc.fsu.edu/~jburkardt/data/csv/cities.csv
133 ';
134 BEGIN
135 DBMS_OUTPUT.put_line(LTRIM(RTRIM(v_text, CHR(10) || CHR(13)), CHR(10) || CHR(13)));
136 END;
137 /
"LatD", "LatM", "LatS", "NS", "LonD", "LonM", "LonS", "EW", "City", "State"
41, 5, 59, "N", 80, 39, 0, "W", "Youngstown", OH
42, 52, 48, "N", 97, 23, 23, "W", "Yankton", SD
46, 35, 59, "N", 120, 30, 36, "W", "Yakima", WA
42, 16, 12, "N", 71, 48, 0, "W", "Worcester", MA
43, 37, 48, "N", 89, 46, 11, "W", "Wisconsin Dells", WI
36, 5, 59, "N", 80, 15, 0, "W", "Winston-Salem", NC
49, 52, 48, "N", 97, 9, 0, "W", "Winnipeg", MB
39, 11, 23, "N", 78, 9, 36, "W", "Winchester", VA
34, 14, 24, "N", 77, 55, 11, "W", "Wilmington", NC
39, 45, 0, "N", 75, 33, 0, "W", "Wilmington", DE
48, 9, 0, "N", 103, 37, 12, "W", "Williston", ND
41, 15, 0, "N", 77, 0, 0, "W", "Williamsport", PA
37, 40, 48, "N", 82, 16, 47, "W", "Williamson", WV
33, 54, 0, "N", 98, 29, 23, "W", "Wichita Falls", TX
37, 41, 23, "N", 97, 20, 23, "W", "Wichita", KS
40, 4, 11, "N", 80, 43, 12, "W", "Wheeling", WV
26, 43, 11, "N", 80, 3, 0, "W", "West Palm Beach", FL
47, 25, 11, "N", 120, 19, 11, "W", "Wenatchee", WA
41, 25, 11, "N", 122, 23, 23, "W", "Weed", CA
31, 13, 11, "N", 82, 20, 59, "W", "Waycross", GA
44, 57, 35, "N", 89, 38, 23, "W", "Wausau", WI
42, 21, 36, "N", 87, 49, 48, "W", "Waukegan", IL
44, 54, 0, "N", 97, 6, 36, "W", "Watertown", SD
43, 58, 47, "N", 75, 55, 11, "W", "Watertown", NY
42, 30, 0, "N", 92, 20, 23, "W", "Waterloo", IA
41, 32, 59, "N", 73, 3, 0, "W", "Waterbury", CT
38, 53, 23, "N", 77, 1, 47, "W", "Washington", DC
41, 50, 59, "N", 79, 8, 23, "W", "Warren", PA
46, 4, 11, "N", 118, 19, 48, "W", "Walla Walla", WA
31, 32, 59, "N", 97, 8, 23, "W", "Waco", TX
38, 40, 48, "N", 87, 31, 47, "W", "Vincennes", IN
28, 48, 35, "N", 97, 0, 36, "W", "Victoria", TX
32, 20, 59, "N", 90, 52, 47, "W", "Vicksburg", MS
49, 16, 12, "N", 123, 7, 12, "W", "Vancouver", BC
46, 55, 11, "N", 98, 0, 36, "W", "Valley City", ND
30, 49, 47, "N", 83, 16, 47, "W", "Valdosta", GA
43, 6, 36, "N", 75, 13, 48, "W", "Utica", NY
39, 54, 0, "N", 79, 43, 48, "W", "Uniontown", PA
32, 20, 59, "N", 95, 18, 0, "W", "Tyler", TX
42, 33, 36, "N", 114, 28, 12, "W", "Twin Falls", ID
33, 12, 35, "N", 87, 34, 11, "W", "Tuscaloosa", AL
34, 15, 35, "N", 88, 42, 35, "W", "Tupelo", MS
36, 9, 35, "N", 95, 54, 36, "W", "Tulsa", OK
32, 13, 12, "N", 110, 58, 12, "W", "Tucson", AZ
37, 10, 11, "N", 104, 30, 36, "W", "Trinidad", CO
40, 13, 47, "N", 74, 46, 11, "W", "Trenton", NJ
44, 45, 35, "N", 85, 37, 47, "W", "Traverse City", MI
43, 39, 0, "N", 79, 22, 47, "W", "Toronto", ON
39, 2, 59, "N", 95, 40, 11, "W", "Topeka", KS
41, 39, 0, "N", 83, 32, 24, "W", "Toledo", OH
33, 25, 48, "N", 94, 3, 0, "W", "Texarkana", TX
39, 28, 12, "N", 87, 24, 36, "W", "Terre Haute", IN
27, 57, 0, "N", 82, 26, 59, "W", "Tampa", FL
30, 27, 0, "N", 84, 16, 47, "W", "Tallahassee", FL
47, 14, 24, "N", 122, 25, 48, "W", "Tacoma", WA
43, 2, 59, "N", 76, 9, 0, "W", "Syracuse", NY
32, 35, 59, "N", 82, 20, 23, "W", "Swainsboro", GA
33, 55, 11, "N", 80, 20, 59, "W", "Sumter", SC
40, 59, 24, "N", 75, 11, 24, "W", "Stroudsburg", PA
37, 57, 35, "N", 121, 17, 24, "W", "Stockton", CA
44, 31, 12, "N", 89, 34, 11, "W", "Stevens Point", WI
40, 21, 36, "N", 80, 37, 12, "W", "Steubenville", OH
40, 37, 11, "N", 103, 13, 12, "W", "Sterling", CO
38, 9, 0, "N", 79, 4, 11, "W", "Staunton", VA
39, 55, 11, "N", 83, 48, 35, "W", "Springfield", OH
37, 13, 12, "N", 93, 17, 24, "W", "Springfield", MO
42, 5, 59, "N", 72, 35, 23, "W", "Springfield", MA
39, 47, 59, "N", 89, 39, 0, "W", "Springfield", IL
47, 40, 11, "N", 117, 24, 36, "W", "Spokane", WA
41, 40, 48, "N", 86, 15, 0, "W", "South Bend", IN
43, 32, 24, "N", 96, 43, 48, "W", "Sioux Falls", SD
42, 29, 24, "N", 96, 23, 23, "W", "Sioux City", IA
32, 30, 35, "N", 93, 45, 0, "W", "Shreveport", LA
33, 38, 23, "N", 96, 36, 36, "W", "Sherman", TX
44, 47, 59, "N", 106, 57, 35, "W", "Sheridan", WY
35, 13, 47, "N", 96, 40, 48, "W", "Seminole", OK
32, 25, 11, "N", 87, 1, 11, "W", "Selma", AL
38, 42, 35, "N", 93, 13, 48, "W", "Sedalia", MO
47, 35, 59, "N", 122, 19, 48, "W", "Seattle", WA
41, 24, 35, "N", 75, 40, 11, "W", "Scranton", PA
41, 52, 11, "N", 103, 39, 36, "W", "Scottsbluff", NB
42, 49, 11, "N", 73, 56, 59, "W", "Schenectady", NY
32, 4, 48, "N", 81, 5, 23, "W", "Savannah", GA
46, 29, 24, "N", 84, 20, 59, "W", "Sault Sainte Marie", MI
27, 20, 24, "N", 82, 31, 47, "W", "Sarasota", FL
38, 26, 23, "N", 122, 43, 12, "W", "Santa Rosa", CA
35, 40, 48, "N", 105, 56, 59, "W", "Santa Fe", NM
34, 25, 11, "N", 119, 41, 59, "W", "Santa Barbara", CA
33, 45, 35, "N", 117, 52, 12, "W", "Santa Ana", CA
37, 20, 24, "N", 121, 52, 47, "W", "San Jose", CA
37, 46, 47, "N", 122, 25, 11, "W", "San Francisco", CA
41, 27, 0, "N", 82, 42, 35, "W", "Sandusky", OH
32, 42, 35, "N", 117, 9, 0, "W", "San Diego", CA
34, 6, 36, "N", 117, 18, 35, "W", "San Bernardino", CA
29, 25, 12, "N", 98, 30, 0, "W", "San Antonio", TX
31, 27, 35, "N", 100, 26, 24, "W", "San Angelo", TX
40, 45, 35, "N", 111, 52, 47, "W", "Salt Lake City", UT
38, 22, 11, "N", 75, 35, 59, "W", "Salisbury", MD
36, 40, 11, "N", 121, 39, 0, "W", "Salinas", CA
38, 50, 24, "N", 97, 36, 36, "W", "Salina", KS
38, 31, 47, "N", 106, 0, 0, "W", "Salida", CO
44, 56, 23, "N", 123, 1, 47, "W", "Salem", OR
44, 57, 0, "N", 93, 5, 59, "W", "Saint Paul", MN
38, 37, 11, "N", 90, 11, 24, "W", "Saint Louis", MO
39, 46, 12, "N", 94, 50, 23, "W", "Saint Joseph", MO
42, 5, 59, "N", 86, 28, 48, "W", "Saint Joseph", MI
44, 25, 11, "N", 72, 1, 11, "W", "Saint Johnsbury", VT
45, 34, 11, "N", 94, 10, 11, "W", "Saint Cloud", MN
29, 53, 23, "N", 81, 19, 11, "W", "Saint Augustine", FL
43, 25, 48, "N", 83, 56, 24, "W", "Saginaw", MI
38, 35, 24, "N", 121, 29, 23, "W", "Sacramento", CA
43, 36, 36, "N", 72, 58, 12, "W", "Rutland", VT
33, 24, 0, "N", 104, 31, 47, "W", "Roswell", NM
35, 56, 23, "N", 77, 48, 0, "W", "Rocky Mount", NC
41, 35, 24, "N", 109, 13, 48, "W", "Rock Springs", WY
42, 16, 12, "N", 89, 5, 59, "W", "Rockford", IL
43, 9, 35, "N", 77, 36, 36, "W", "Rochester", NY
44, 1, 12, "N", 92, 27, 35, "W", "Rochester", MN
37, 16, 12, "N", 79, 56, 24, "W", "Roanoke", VA
37, 32, 24, "N", 77, 26, 59, "W", "Richmond", VA
39, 49, 48, "N", 84, 53, 23, "W", "Richmond", IN
38, 46, 12, "N", 112, 5, 23, "W", "Richfield", UT
45, 38, 23, "N", 89, 25, 11, "W", "Rhinelander", WI
39, 31, 12, "N", 119, 48, 35, "W", "Reno", NV
50, 25, 11, "N", 104, 39, 0, "W", "Regina", SA
40, 10, 48, "N", 122, 14, 23, "W", "Red Bluff", CA
40, 19, 48, "N", 75, 55, 48, "W", "Reading", PA
41, 9, 35, "N", 81, 14, 23, "W", "Ravenna", OH
PL/SQL procedure successfully completed.
The automatic embedding is more obvious here when you look at the SQL*Plus line numbers, note how they jump from 3 to 133. The missing lines are from the remote file being parsed as part of the input, but not shown. The same line skipping occurred in the previous examples but was not as noticable, 3 jumped to 5 on the 2 line test file, and then 3 jumped to 17 on the EMP table data.
There are limitations of course. The @ command must be the first character of the line where you embed the data. This is why the examples above have to check for empty lines in the resulting text value.
Also, since the data becomes a string literal after being embedded within the block, you can’t load gigabytes of data, or even more 32K, you’ll be limited by:
PLS-00172: string literal too long
Or possibly VARCHAR2 limits if you declare your variable small. Even with the data volume limits, this has still been a useful technique for me with SQL*Plus clients 9i through 18c. My guess is it dates back even earlier but I don’t have any clients that old handy to confirm. While this has been a reliable feature for me, this usage is not (to my knowledge) documented anywhere. So it’s possible it will be desupported in some version. I have tested Oracle’s SQLcl with this usage, and it does not parse the @ and @@ commands as embedded text when placed within a text variable. Nor do any other tools I’ve tried, even if they claim to offer SQL*Plus compatibility.
So, for now, this is a SQL*Plus-only feature. I hope you find it helpful for as long as it is available.