剧本:
DECLARE
obj_exists NUMBER (38, 0);
faqqestion VARCHAR2 (4000 BYTE);
targetobjid NUMBER (38, 0);
translatedtemplate BOOLEAN;
BEGIN -- prints header
DBMS_OUTPUT.Put_line (
'"'
|| 'TemplateID'
|| '";"'
|| 'Templatetext'
|| '";"'
|| 'Sprache'
|| '";');
-- iterates over the latest version of all 'de' templates
FOR latestgermanobjects
IN ( SELECT vs.sl_vs_id vsId,
vs.sl_obj_id vsObjId,
TRANSM.sl_obj_id transmObjId,
TRANSM.lang,
OBJ.sl_obj_id objObjId,
faq.faq_template_id faqTemplateId
FROM sl_versionset vs
INNER JOIN sl_pp01_trans_meta transM
ON VS.sl_obj_id = TRANSM.sl_obj_id
INNER JOIN sl_pp01_obj obj
ON TRANSM.sl_obj_id = OBJ.sl_obj_id
INNER JOIN sl_pp01_faq01 faq
ON OBJ.sl_obj_id = FAQ.sl_obj_id
WHERE VS.sl_vs_next IS NULL
AND VS.sl_vs_deleted_flag = 0
AND VS.sl_vs_state <> 'Deprecated'
AND transm.lang = 'de'
AND obj.sl_obj_objtyp_id IN (334)
AND obj.sl_obj_deleted_flag = 0
ORDER BY faq.faq_template_id ASC)
LOOP
-- iterate over all languages except 'de' to check translation
FOR singlelanguagecursor
IN (SELECT sl_metaval_varchar
FROM slma_meta_values
WHERE sl_metaval_varchar <> 'de' AND slma_metatype_id = 5)
LOOP
translatedtemplate := FALSE;
-- iterate over all versions of versionset of the german template to find old tranlsations
FOR templates
IN ( SELECT sl_obj_id
FROM sl_versionset
WHERE sl_vs_id = latestgermanobjects.vsid
AND sl_vs_deleted_flag = 0
ORDER BY sl_obj_id DESC)
LOOP
SELECT COUNT (1)
INTO obj_exists
FROM sl_pp01_trans_meta
WHERE source_io = templates.sl_obj_id
AND lang = singlelanguagecursor.sl_metaval_varchar;
-- checks, if a translation of the chosen language exists
IF obj_exists >= 1
THEN
translatedtemplate := TRUE;
-- if only a tranlsation of a older version of the source object exists
IF templates.sl_obj_id <> latestgermanobjects.objobjid
THEN
SELECT sl_obj_id
INTO targetobjid
FROM sl_pp01_trans_meta
WHERE source_io = templates.sl_obj_id
AND lang =
singlelanguagecursor.sl_metaval_varchar;
SELECT faq_question
INTO faqqestion
FROM sl_pp01_templ01
WHERE sl_obj_id = targetobjid;
-- prints 'Template Id', tranlsation of templatetext, language of translation of a transation of a older source object
DBMS_OUTPUT.Put_line (
'"'
|| latestgermanobjects.faqtemplateid
|| '";"'
|| faqqestion
|| '";"'
|| singlelanguagecursor.sl_metaval_varchar
|| '";');
END IF;
-- ends loop, after translation has found
EXIT;
END IF;
END LOOP;
-- if no translation is available
IF NOT translatedtemplate
THEN
DBMS_OUTPUT.Put_line (
'"'
|| latestgermanobjects.faqtemplateid
|| '";"'
|| 'translation n/a'
|| '";"'
|| singlelanguagecursor.sl_metaval_varchar
|| '";');
END IF;
END LOOP;
END LOOP;
END;
新的SQL和PL/SQL,所以我将感谢任何帮助。
下面是错误的一些示例-解释-代码:
declare
v_text VARCHAR2(4000);
begin
-- this will work fine..
SELECT * INTO v_text FROM
( -- this is a fake-table
SELECT 'some text' FROM DUAL
);
dbms_output.put_line(v_text);
-- this will get you an no data found exception
BEGIN
SELECT * INTO v_text FROM
( -- this is a fake-table
SELECT 'some text' FROM DUAL
)
WHERE 1 = 2;
dbms_output.put_line(v_text);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('there is no data..');
END;
-- this will get you your exception 'too many rows'
BEGIN
SELECT * INTO v_text FROM
( -- this is a fake-table
SELECT 'some text' FROM DUAL UNION ALL
SELECT 'some text' FROM DUAL
);
dbms_output.put_line(v_text);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('there is too much data..');
END;
end;
你的具体问题是第69排,我猜:
SELECT sl_obj_id
INTO targetobjid
FROM sl_pp01_trans_meta
WHERE source_io = templates.sl_obj_id
AND lang =
singlelanguagecursor.sl_metaval_varchar;
如果获得多行,则不允许将输入
。修正选择(where ROWNUM=1
!!!仅当结果正确时使用!!!)或处理异常。
在第1行声明 * 错误: ORA-01422:精确提取返回的行数超过请求的行数 ORA-06512:在第11行 这是我的代码。
ORA-06512:在“sys.dbms_sql”处,第1721行 我确信这个函数的结果只有一行,我不想使用。
Select语句在Resault中返回多行。是否可以用此函数获得resault中的所有行? ORA-01422:精确提取返回的行数多于请求的行数
我很难理解PL/SQL触发器的问题所在。错误为:错误报告-SQL错误:ORA-01422:精确提取返回的行数超过请求的行数ORA-06512:在“System.trg_late_return”处,第6行ORA-04088:触发器“System.trg_late_return”执行过程中的错误01422。00000-“精确提取返回的行数超过请求的行数”*原因:精确提取中指定的行数小于返回的行数。*操