Stored procedure is as follows:
The CREATE OR REPLACE PROCEDURE tr_TEST1 IS
V_SQL VARCHAR2 (20000);
CURSOR CURSOR_1 IS the SELECT DISTINCT t. booking date FROM tr_tmp T ORDER BY booking date;
The BEGIN
V_SQL:='SELECT area, center, the repair order quantity;

FOR V_ booking date IN CURSOR_1
LOOP
V_SQL:=V_SQL | | ', '| |' SUM (DECODE (date of appointment, "' | | v_ booking date. An appointment date | |
"', order quantity, 0)) AS '| | V_ booking date. An appointment date;
END LOOP;

V_SQL:=V_SQL | | 'FROM tr_test1 GROUP BY area, center, the repair ORDER quantity ORDER BY area, center, the repair ORDER quantity'.
V_SQL:='CREATE OR REPLACE the VIEW RESULT AS' | | V_SQL;
The EXECUTE IMMEDIATE V_SQL;
COMMIT;
END tr_TEST1;

Compilation is successful, but when the execution tip can't find the FROM keyword, what went wrong?

CodePudding user response:

 V_SQL:='SELECT area, center, the repair order quantity; 


Add a comma 

V_SQL:='SELECT area, center, order quantity,'; 

CodePudding user response:

pass by!

CodePudding user response:

reference 1st floor wmxcn2000 response:

 V_SQL:='SELECT area, center, the repair order quantity; 


Add a comma 

V_SQL:='SELECT area, center, order quantity,'; 

Or make a mistake,,,

CodePudding user response:

Tr_tmp, tr_test1 two table structure, and data post;

reference iris_tr reply: 3/f

Quote: refer to 1st floor wmxcn2000 response:

 V_SQL:='SELECT area, center, the repair order quantity; 


Add a comma 

V_SQL:='SELECT area, center, order quantity,'; 

Or make a mistake,,,

What's wrong, you have to say,,,

CodePudding user response:

How to preach not pictures, execution: the begin
Tr_test1;
end;
Lack of prompt expression

CodePudding user response:

The

refer to the original poster iris_tr response:

stored procedure is as follows:
The CREATE OR REPLACE PROCEDURE tr_TEST1 IS
V_SQL VARCHAR2 (20000);
CURSOR CURSOR_1 IS the SELECT DISTINCT t. booking date FROM tr_tmp T ORDER BY booking date;
The BEGIN
V_SQL:='SELECT area, center, the repair order quantity;

FOR V_ booking date IN CURSOR_1
LOOP
V_SQL:=V_SQL | | ', '| |' SUM (DECODE (date of appointment, "' | | v_ booking date. An appointment date | |
"', order quantity, 0)) AS '| | V_ booking date. An appointment date;
END LOOP;

V_SQL:=V_SQL | | 'FROM tr_test1 GROUP BY area, center, the repair ORDER quantity ORDER BY area, center, the repair ORDER quantity'.
V_SQL:='CREATE OR REPLACE the VIEW RESULT AS' | | V_SQL;
The EXECUTE IMMEDIATE V_SQL;
COMMIT;
END tr_TEST1;

Compilation is successful, but when the execution tip can't find the FROM keyword, what went wrong?

 
Should be a column alias problem 
- select hiredate as 20161201 from emp a; - no 
- select hiredate as' 20161201 'the from emp a; - no 
The select hiredate as "20161201" the from emp a; - 
- "', order quantity, 0)) AS '| | V_ booking date. An appointment date; 
- change into the following add double quotation marks try 
"', order quantity, 0)) AS" '| | V_ booking date. An appointment date | |' "'; 

CodePudding user response:

Revised:
The CREATE OR REPLACE PROCEDURE tr_TEST1 IS
V_SQL VARCHAR2 (20000);
CURSOR CURSOR_1 IS the SELECT DISTINCT t. booking date FROM tr_tmp T ORDER BY booking date;
The BEGIN
V_SQL:='SELECT area, the center;

FOR V_ booking date IN CURSOR_1
LOOP
V_SQL:=V_SQL | | ', '| |' SUM (DECODE (date of appointment, "' | | v_ booking date. An appointment date | |
"', order quantity, 0)) AS" '| | V_ booking date. An appointment date | |' "';
END LOOP;

V_SQL:=V_SQL | | 'FROM tr_test1 GROUP BY area, center the ORDER BY area, center';
V_SQL:='CREATE OR REPLACE the VIEW RESULT AS' | | V_SQL;
The EXECUTE IMMEDIATE V_SQL;
END tr_test1;

Compilation is successful, but to perform prompt
ORA - 04044: here are not allowed to process, function, package or type
ORA - 06512: in line15
Ora - 06512: : in line2

CodePudding user response:

refer to 7th floor iris_tr response:

modified:
The CREATE OR REPLACE PROCEDURE tr_TEST1 IS
V_SQL VARCHAR2 (20000);
CURSOR CURSOR_1 IS the SELECT DISTINCT t. booking date FROM tr_tmp T ORDER BY booking date;
The BEGIN
V_SQL:='SELECT area, the center;

FOR V_ booking date IN CURSOR_1
LOOP
V_SQL:=V_SQL | | ', '| |' SUM (DECODE (date of appointment, "' | | v_ booking date. An appointment date | |
"', order quantity, 0)) AS" '| | V_ booking date. An appointment date | |' "';
END LOOP;

V_SQL:=V_SQL | | 'FROM tr_test1 GROUP BY area, center the ORDER BY area, center';
V_SQL:='CREATE OR REPLACE the VIEW RESULT AS' | | V_SQL;
The EXECUTE IMMEDIATE V_SQL;
END tr_test1;

Compilation is successful, but to perform prompt
ORA - 04044: here are not allowed to process, function, package or type
ORA - 06512: in line15
Ora - 06512: : in line2

 
, print statements, 
- the EXECUTE IMMEDIATE V_SQL; 
Dbms_output. Put_line (v_sql); 

CodePudding user response:

refer to the eighth floor dd215130268 response:

Quote: refer to 7th floor iris_tr response:

Revised:
The CREATE OR REPLACE PROCEDURE tr_TEST1 IS
V_SQL VARCHAR2 (20000);
CURSOR CURSOR_1 IS the SELECT DISTINCT t. booking date FROM tr_tmp T ORDER BY booking date;
The BEGIN
V_SQL:='SELECT area, the center;

FOR V_ booking date IN CURSOR_1
LOOP
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull