The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into bso(l1, n1) values(A(i), i);
SELECT qc.fk_table_name,
qc.fk_table_short_name,
qc.fk_lookup_type,
qc.pk_id,
qc.fk_id,
qc.pk_id2,
qc.fk_id2,
qc.pk_id3,
qc.fk_id3,
qc.fk_meaning,
qc.fk_description,
qc.fk_add_where
FROM qa_chars qc
WHERE qc.char_id = x;
SELECT
/*
This is a batch program that needs to loop through
all collection plans to generate a global view.
Full table scan is expected. Bryan So 1/31/2006
*/
distinct qpc.char_id,
decode(qc.hardcoded_column, null, qpc.plan_id, 0) plan_id,
upper(qpc.result_column_name) result_column_name,
qc.name,
qc.hardcoded_column
FROM qa_plan_chars qpc, qa_chars qc
WHERE qc.char_id = qpc.char_id AND
qpc.enabled_flag = 1 AND
qc.enabled_flag = 1
ORDER BY qc.hardcoded_column, qc.name;
j INTEGER; -- following lists: select list (v_sql_table),
v_sql_table(2) := ' AS SELECT /*+ push_pred(PH) USE_NL(PH PR)*/ ';
v_sql_table(10) := ' qr.qa_last_update_date last_update_date,'; -- 8
v_sql_table(11) := ' qr.qa_last_updated_by last_update_by_id,'; -- 9
v_sql_table(12) := ' fu2.user_name last_updated_by,'; -- 10
v_sql_table(16) := ' qr.last_update_login'; -- 14
v_where_table(3) := 'qr.qa_last_updated_by = fu2.user_id';
SELECT char_id, name, hardcoded_column, datatype
INTO l_element_id, l_element_name, l_element_hardcoded_column,
l_element_datatype
FROM qa_chars
WHERE char_id = current_element;
SELECT grantee, privilege, grantable
BULK COLLECT INTO privs_rec_tab
FROM User_TAB_PRIVS
WHERE table_name = p_view_name
ORDER BY grantee;
v_select VARCHAR2(20000);
SELECT qc.char_id,
upper(translate(qc.name,' ''*{}','_____')) name,
qpc.result_column_name,
qc.hardcoded_column,
qc.FK_LOOKUP_TYPE,
qc.FK_TABLE_NAME,
qc.FK_TABLE_SHORT_NAME,
qc.PK_ID,
qc.FK_ID,
qc.PK_ID2,
qc.FK_ID2,
qc.PK_ID3,
qc.FK_ID3,
qc.FK_MEANING,
qc.FK_DESCRIPTION,
qc.FK_ADD_WHERE,
qc.DATATYPE
FROM qa_chars qc,
qa_plan_chars qpc
WHERE qc.char_id = qpc.char_id
AND qpc.plan_id = x_plan_id
ORDER BY qpc.prompt_sequence;
v_select := ' SELECT
qr.rowid row_id,
qr.plan_id,
qp'||g_period||'name plan_name,
qr.organization_id,
hou.name organization_name,
qr.collection_id,
qr.occurrence,
qr.qa_last_update_date last_update_date,
qr.qa_last_updated_by last_updated_by_id,
fu2.user_name last_updated_by,
qr.qa_creation_date creation_date,
qr.qa_created_by created_by_id,
fu.user_name created_by,
qr.last_update_login';
qr.qa_last_updated_by = fu2.user_id AND
qr.organization_id = hou.organization_id
AND (qr.status IS NULL OR qr.status = 2)';
v_select := v_select || ', qr.' || fk.hardcoded_column;
v_select := v_select ||
', qa_flex_util.sales_order(qr.so_header_id) "' || fk.name || '"';
v_select := v_select || ', qr.' || fk.hardcoded_column;
v_select := v_select ||
', qa_flex_util.rma_number(qr.rma_header_id) "' || fk.name || '"';
v_select := v_select || ', qr.' || fk.hardcoded_column;
v_select := v_select ||
', qa_flex_util.project_number(qr.project_id) "' || fk.name || '"';
v_select := v_select || ', qr.' || fk.hardcoded_column;
v_select := v_select || ', ' || fk.fk_table_short_name || '.' ||
fk.fk_meaning || ' "' || fk.name || '"';
v_select := v_select || ', qr.' || fk.hardcoded_column ||
' "' || fk.name || '"';
v_select := v_select || ', ' || temp || ' "' || fk.name || '"';
v_final := v_select || v_from || v_where;
v_select VARCHAR2(20000);
SELECT qc.char_id,
upper(translate(qc.name,' ''*{}','_____')) name,
qpc.result_column_name,
qc.hardcoded_column,
qc.FK_LOOKUP_TYPE,
qc.FK_TABLE_NAME,
qc.FK_TABLE_SHORT_NAME,
qc.PK_ID,
qc.FK_ID,
qc.PK_ID2,
qc.FK_ID2,
qc.PK_ID3,
qc.FK_ID3,
qc.FK_MEANING,
qc.FK_DESCRIPTION,
qc.FK_ADD_WHERE,
qc.DATATYPE
FROM qa_chars qc,
qa_plan_chars qpc
WHERE qc.char_id = qpc.char_id
AND qpc.plan_id = x_plan_id
ORDER BY qpc.prompt_sequence;
SELECT substr(import_view_name, 1, length(import_view_name)-2)||'DV'
INTO v_deref_view_name FROM qa_plans
WHERE plan_id = x_plan_id;
v_select := ' SELECT /*+ LEADING(qp) USE_NL(qp qr) push_pred(PH) USE_NL(PH PR)*/
qr.rowid row_id,
qr.plan_id,
qp'||g_period||'name plan_name,
qr.organization_id,
hou.name organization_name,
qr.collection_id,
qr.occurrence,
qr.qa_last_update_date last_update_date,
qr.qa_last_updated_by last_updated_by_id,
fu2.user_name last_updated_by,
qr.qa_creation_date creation_date,
qr.qa_created_by created_by_id,
fu.user_name created_by,
qr.last_update_login';
qr.qa_last_updated_by = fu2.user_id AND
qr.organization_id = hou.organization_id';
v_select := v_select || ', qr.' || fk.hardcoded_column;
v_select := v_select ||
', qa_flex_util.sales_order(qr.so_header_id) "' || fk.name || '"';
v_select := v_select || ', qr.' || fk.hardcoded_column;
v_select := v_select ||
', qa_flex_util.rma_number(qr.rma_header_id) "' || fk.name || '"';
v_select := v_select || ', qr.' || fk.hardcoded_column;
v_select := v_select ||
', qa_flex_util.project_number(qr.project_id) "' || fk.name || '"';
v_select := v_select || ', qr.' || fk.hardcoded_column;
v_select := v_select || ', ' || fk.fk_table_short_name || '.' ||
fk.fk_meaning || ' "' || fk.name || '"';
v_select := v_select || ', qr.' || fk.hardcoded_column ||
' "' || fk.name || '"';
v_select := v_select || ', ' || temp || ' "' || fk.name || '"';
v_final := v_plan_view_create || v_select || v_from || v_where;
v_deref_view_final := v_deref_view_create || v_select || v_from || v_deref_view_where;
UPDATE qa_plans set deref_view_name = v_deref_view_name
WHERE plan_id = x_plan_id;
v_select VARCHAR2(20000);
SELECT qpc.result_column_name,
upper(translate(qc.name,' ''*{}','_____')) name,
qc.hardcoded_column,
qc.developer_name
FROM qa_plan_chars qpc, qa_chars qc
WHERE qpc.char_id = qc.char_id AND qpc.plan_id = x_plan_id
ORDER BY prompt_sequence;
v_select := 'CREATE OR REPLACE FORCE VIEW "' || upper(x_view_name) ||
'" AS SELECT
transaction_interface_id,
qa_last_updated_by_name,
qa_created_by_name,
collection_id,
source_code,
source_line_id,
process_status,
organization_code,
operating_unit_id,
operating_unit,
plan_name,
insert_type,
matching_elements,
spec_name';
v_select := v_select || ', VERSION_NUMBER';
v_select := v_select || ', ' || prec.developer_name;
v_select := v_select || ', ' || prec.result_column_name ||
' "' || prec.name || '"';
v_select := v_select || ' FROM QA_RESULTS_INTERFACE';
ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, v_select,
x_view_name);