oracle 如何使用 CONCAT 在 SQL Plus 中解析 SP2-0734？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26595716/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to resolve SP2-0734 in SQL Plus using CONCAT?
提问by billabrian6
I have created a script that generates this error, but it still executes perfectly. What can I do to resolve this error? I have tried some of the common fixes like SET SQLBLANKLINES ON and it didn't work.
我已经创建了一个生成此错误的脚本，但它仍然可以完美执行。我该怎么做才能解决此错误？我尝试了一些常见的修复方法，例如 SET SQLBLANKLINES ON，但没有奏效。
The error: SP2-0734: unknown command beginning "CONCAT('IN..." - rest of line ignored.
错误：SP2-0734：未知命令开头“CONCAT('IN...” - 其余行被忽略。
This is the part of the script that generates the error:
这是生成错误的脚本部分：
PROMPT 'ENTER THE PERCENTAGE YOU WISH TO INCREASE THE PRICE BY: '
ACCEPT V_PCT_INCREASE NUMBER
PROMPT
PROMPT 'ENTER THE PRODUCT SEARCH CRITERIA: '
ACCEPT V_PRODUCT_DESC
PROMPT
PROMPT 'YOU HAVE CHOSEN TO UPDATE PRODUCTS WHOSE DESCRIPTION BEGINS WITH &V_PRODUCT_DESC WITH A PERCENTAGE INCREASE OF &V_PCT_INCREASE PERCENT.'
PROMPT
SET TERMOUT OFF;
SET VERIFY OFF;
SET SQLBLANKLINES ON;
SPOOL c:\runscripts\insert_prices.sql;
SELECT CONCAT('INSERT INTO TAR_TEMP_PRICE (PRODUCT_ID, START_DATE, LIST_PRICE, MIN_PRICE, END_DATE)
VALUES (', P.PRODUCT_ID||', '''||PR.START_DATE||''', '||PR.LIST_PRICE * (1+(&V_PCT_INCREASE/100))||', '||PR.MIN_PRICE * (1+(&V_PCT_INCREASE/100))||', CURRENT_DATE'||');')
FROM TAR_PROD P
JOIN TAR_PRICE PR
ON P.PRODUCT_ID = PR.PRODUCT_ID
WHERE UPPER(P.DESCRIPTION) LIKE UPPER('&V_PRODUCT_DESC%');
SPOOL OFF;
SET FEEDBACK ON;
SET TERMOUT ON;
PROMPT 'A SCRIPT insert_prices.sql HAS BEEN CREATED AT c:\runscripts\insert_prices.sql'
PROMPT
PROMPT 'THE FOLLOWING PRODUCTS WERE ADDED TO THE INSERT SCRIPT WITH THE ADJUSTED PRICES: '
SELECT P.PRODUCT_ID, P.DESCRIPTION, PR.LIST_PRICE, PR.MIN_PRICE,
PR.LIST_PRICE * (1+(&V_PCT_INCREASE/100)) AS "ADJ_LIST_PRICE",
PR.MIN_PRICE * (1+(&V_PCT_INCREASE/100)) AS "ADJ_MIN_PRICE"
FROM TAR_PROD P
JOIN TAR_PRICE PR
ON P.PRODUCT_ID = PR.PRODUCT_ID
WHERE UPPER(P.DESCRIPTION) LIKE UPPER('&V_PRODUCT_DESC%');
@C:\runscripts\insert_prices.sql
PROMPT 'INSERT SCRIPT IS COMPLETE.'
PROMPT 'SEE BELOW FOR RESULT VALIDATION.'
SELECT *
FROM TAR_TEMP_PRICE;
ROLLBACK;
It still works, but I would like the suppress the error.
它仍然有效，但我想抑制错误。
The insert statements generated by the code above:
上面代码生成的插入语句：
CONCAT('INSERTINTOTAR_TEMP_PRICE(PRODUCT_ID,START_DATE,LIST_PRICE,MIN_PRICE,END_DATE)VALUES(',P.PRODUCT_ID||','''||PR.START_DATE||''','||PR.LIST_PRICE
------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO TAR_TEMP_PRICE (PRODUCT_ID, START_DATE, LIST_PRICE, MIN_PRICE, END_DATE)
VALUES (100860, '01-JUN-90', 39.2, 31.36, CURRENT_DATE);
INSERT INTO TAR_TEMP_PRICE (PRODUCT_ID, START_DATE, LIST_PRICE, MIN_PRICE, END_DATE)
VALUES (100860, '01-JAN-90', 35.84, 28.672, CURRENT_DATE);
INSERT INTO TAR_TEMP_PRICE (PRODUCT_ID, START_DATE, LIST_PRICE, MIN_PRICE, END_DATE)
VALUES (100860, '01-JAN-89', 33.6, 26.88, CURRENT_DATE);
INSERT INTO TAR_TEMP_PRICE (PRODUCT_ID, START_DATE, LIST_PRICE, MIN_PRICE, END_DATE)
VALUES (100861, '01-JUN-90', 50.4, 40.32, CURRENT_DATE);
INSERT INTO TAR_TEMP_PRICE (PRODUCT_ID, START_DATE, LIST_PRICE, MIN_PRICE, END_DATE)
VALUES (100861, '01-JAN-90', 47.04, 37.632, CURRENT_DATE);
INSERT INTO TAR_TEMP_PRICE (PRODUCT_ID, START_DATE, LIST_PRICE, MIN_PRICE, END_DATE)
VALUES (100861, '01-JAN-89', 43.68, 34.944, CURRENT_DATE);
INSERT INTO TAR_TEMP_PRICE (PRODUCT_ID, START_DATE, LIST_PRICE, MIN_PRICE, END_DATE)
VALUES (100870, '01-JAN-90', 3.136, 2.688, CURRENT_DATE);
INSERT INTO TAR_TEMP_PRICE (PRODUCT_ID, START_DATE, LIST_PRICE, MIN_PRICE, END_DATE)
VALUES (100870, '01-JAN-89', 2.688, 2.128, CURRENT_DATE);
For some reason it is adding part of the select statement from the code above.
出于某种原因，它从上面的代码中添加了部分 select 语句。
Here is the output:
这是输出：
'ENTER THE PERCENTAGE YOU WISH TO INCREASE THE PRICE BY: '
12
'ENTER THE PRODUCT SEARCH CRITERIA: '
DYN
'YOU HAVE CHOSEN TO UPDATE PRODUCTS WHOSE DESCRIPTION BEGINS WITH DYN WITH A PERCENTAGE INCREASE OF 12 PERCENT.'
'A SCRIPT insert_prices.sql HAS BEEN CREATED AT c:\runscripts\insert_prices.sql'
'THE FOLLOWING PRODUCTS WERE ADDED TO THE INSERT SCRIPT WITH THE ADJUSTED PRICES: '
PRODUCT_ID DESCRIPTION LIST_PRICE MIN_PRICE ADJ_LIST_PRICE ADJ_MIN_PRICE
---------- ------------------------------ -------------- -------------- -------------- --------------
100860 DynaBlaster 400 .00 .00 .20 .36
100860 DynaBlaster 400 .00 .60 .84 .67
100860 DynaBlaster 400 .00 .00 .60 .88
100861 Dynablaster 600 .00 .00 .40 .32
100861 Dynablaster 600 .00 .60 .04 .63
100861 Dynablaster 600 .00 .20 .68 .94
100870 Dynablaster xl60 .80 .40 .14 .69
100870 Dynablaster xl60 .40 .90 .69 .13
8 rows selected.
SP2-0734: unknown command beginning "CONCAT('IN..." - rest of line ignored.
回答by Maheswaran Ravisankar
Add this to your SQL*Plus prompts. (SET HEAD OFF)
将此添加到您的 SQL*Plus 提示中。( SET HEAD OFF)
SET TERMOUT OFF;
SET VERIFY OFF;
SET SQLBLANKLINES ON;
SET HEAD OFF /* This turns of the headers in result */
SET FEEDBACK OFF /* Turns off the result feedback */
Issue is the result column of your SELECThad the default name starting with CONCAT(..as alias.
问题是您的结果列SELECT的默认名称以CONCAT(..别名开头。
So it is like..
所以就像..
CONCAT('INSERTINTOTAR_TEMP_PRICE(PRODUCT_ID,START_DATE,LIST_PRICE,MIN_PRICE,END_DATE)VALUES(',P.PRODUCT_ID||','''||PR.START_DATE||''','||PR.LIST_PRICE
------------------------------------------------------------------------------------------------------------------------------------------------------
And as you execute your INSERTscript, even tht gets executed along with your other inserts. So, we have to turn off the headers.
当您执行您的INSERT脚本时，即使是 tht 也会与您的其他插入一起执行。所以，我们必须关闭标题。
