Such as topic, the database is recorded, but there is no return value, Montana consult everybody out of the question, is there

 DECLARE 
Lv_str NUMBER:=146; 
Lv_rowid ROWID. 
The BEGIN 
FOR lv_rec IN (SELECT OWNER | | '. '| | TABLE_NAME TABLE_NAME, listagg (column_name,', ') within group (order by TABLE_NAME) COL 
The FROM all_TAB_COLUMNS 
WHERE DATA_TYPE='NUMBER' 
And table_name like 'S_PROMO %' 
GROUP BY the OWNER, TABLE_NAME 
) 
LOOP 
The BEGIN 
The EXECUTE IMMEDIATE 'SELECT ROWID FROM' | | lv_rec. Table_name | | 'WHERE' | | lv_str | | 'IN (' | | lv_rec. Col | |') AND ROWNUM=1 '
INTO lv_rowid; 
The EXCEPTION 
The WHEN NO_DATA_FOUND THEN 
Lv_rowid:=NULL; 
The END; 

IF lv_rowid IS NOT NULL THEN 
DBMS_OUTPUT. PUT_LINE (' TABLE NAME='| | lv_rec. Table_name | |' ROWID='| | lv_rowid); 
The EXIT; 
END the IF; 
END LOOP; 
The END; 

CodePudding user response:

The EXECUTE IMMEDIATE 'SELECT ROWID

This sentence, generated dynamic statement, you run, should make a mistake;

CodePudding user response:

On the surface, listagg (column_name, ', ') formed in the condition of using
Normal, should be in (' a ', 'b', 'c'), but the content of the variables inside there is no single quotation marks

CodePudding user response:

As long as it has value, there is a return value

 
SELECT the OWNER | | '| | TABLE_NAME TABLE_NAME, listagg (column_name,', ') within group (order by TABLE_NAME) COL 
The FROM all_TAB_COLUMNS 
WHERE DATA_TYPE='NUMBER' 
And table_name like 'S_PROMO %' 
GROUP BY the OWNER, TABLE_NAME 

After the operation, in the OUTPUT to print the results

CodePudding user response:

I this is 10 g, run in its own statement and results of

 
DECLARE 
Lv_str NUMBER:=146; 
Lv_rowid ROWID. 
The BEGIN 
FOR lv_rec IN (SELECT OWNER | | '. '| | TABLE_NAME TABLE_NAME, 
COL WM_CONCAT (COLUMN_NAME) 
The FROM all_TAB_COLUMNS 
WHERE DATA_TYPE='NUMBER' 
GROUP BY the OWNER, TABLE_NAME 
) LOOP 
The BEGIN 
The EXECUTE IMMEDIATE 'SELECT ROWID FROM' | | lv_rec. Table_name | | 
'WHERE' | | lv_str | | '(' IN | | lv_rec. Col | | 
AND ROWNUM=1 ' '
INTO lv_rowid; 
The EXCEPTION 
The WHEN NO_DATA_FOUND THEN 
Lv_rowid:=NULL; 
The END; 

IF lv_rowid IS NOT NULL THEN 
DBMS_OUTPUT. PUT_LINE (' TABLE NAME='| | lv_rec. Table_name | |'; The ROWID='| | 
Lv_rowid | | '; '| | lv_str | |' : '| | lv_rec. COL); 
The EXIT; 
END the IF; 
END LOOP; 
The END; 

The TABLE NAME=SYS. T; The ROWID=AFx6OLAD4AAAQ2YAAt; 146: SNAP_ID, DBID SAMPLE_ID, INSTANCE_NUMBER, USER_ID, SQL_PLAN_HASH_VALUE, SQL_OPCODE, PLSQL_ENTRY_SUBPROGRAM_ID, PLSQL_SUBPROGRAM_ID, P2, P1, SEQ#, EVENT_ID, BLOCKING_SESSION_SERIAL #, BLOCKING_SESSION, QC_INSTANCE_ID, QC_SESSION_ID, SERVICE_HASH, FLAGS, CURRENT_BLOCK #, CURRENT_FILE #, # CURRENT_OBJ, TIME_WAITED, WAIT_TIME, WAIT_CLASS_ID, P3, PLSQL_OBJECT_ID, PLSQL_ENTRY_OBJECT_ID, FORCE_MATCHING_SIGNATURE, SQL_CHILD_NUMBER, SESSION_SERIAL #, SESSION_ID