I write when executed smoothly through the process of storage, call times lack expression, beg god to see what's going on?

The create or replace procedure get_form_data (p_date varchar2, str1 varchar2, str2 varchar2, str3 varchar2, str4 varchar2, str5 varchar2) IS

T_date date;
T_dept varchar2 (36);
T_identity varchar2 (36);
T_charge varchar2 (36);
T_operator varchar2 (36);
T_type varchar2 (36);
T_time varchar2 (36);
T_outp number (6, 2);
T_emer number (6, 2);
T_daytimeoutp number (6, 2);
T_daytimeemer number (6, 2);
T_night number (6, 2);
T_holidayoutp number (6, 2);
T_holidayemer number (6, 2);
T_export number (6, 2);
T_regist number (6, 2);
CUR1 SYS_REFCURSOR;
Sql_statement varchar2 (3000) :='select dim_date,' | | str1 | | ', '| | str2 | |', '| | str3 | |', '| | str4 | |', '| | str5 | |', the sum (t.o utp_nums) OUPT_NUMS, sum (emer_nums) emer_nums,
The sum (daytime_outp_nums daytime_outp_nums), sum (daytime_emer_nums) daytime_emer_nums,
The SUM (NIGHT_OUTP_NUMS NIGHT_OUTP_NUMS), SUM (HOLIDAY_OUTP_NUMS HOLIDAY_OUTP_NUMS), SUM (HOLIDAY_EMER_NUMS) HOLIDAY_EMER_NUMS, SUM (EXPORT_OUTP_NUMS) EXPORT_OUTP_NUMS,
The SUM (REGIST_CHARGES) REGIST_CHARGES
The from VDSS_CLINIC_MASTER_REC t where t.D IM_DATE=to_date (' | | p_date | | ', "yyyy/MM/dd") and rownum & lt;=2000 group by '| | str1 | |', '| | str2 | |', '| | str3 | |', '| | str4 | |', '| | str5 | |'. ';

The BEGIN
The delete from temp_form_data;
The open CUR1 for sql_statement;
Loop
The fetch cur1 into t_date, t_dept t_identity, t_charge, t_operator, t_type, t_time, t_outp, t_emer, t_daytimeoutp, t_daytimeemer, t_night, t_holidayoutp,
T_holidayemer, t_export t_regist;
Exit the when CUR1 % notfound;
Insert into temp_form_data values (t_date t_dept, t_identity, t_charge, t_operator, t_type, t_time, t_outp, t_emer, t_daytimeoutp, t_daytimeemer, t_night, t_holidayoutp,
T_holidayemer t_export, t_regist);
End loop;
The close CUR1;
commit;
END;

CodePudding user response:

Position error is the cursor the line
The open cur1 for sql_statement reported missing expression

CodePudding user response:

You write sql_statement strings behind the begin a try, their print sql_statement see SQL if you have any questions

CodePudding user response:

reference 1st floor caculus2 response:

position error is the cursor the line
The open cur1 for sql_statement reported missing expression

Should be the SQL you spell has a problem, print it out and have a look

CodePudding user response:

refer to the second floor sxq129601 response:

you try write sql_statement strings behind the begin, himself a print sql_statement see if there is something wrong with the SQL

SQL no problem, I write is to test out into a stored procedure

CodePudding user response:

reference sxq129601 reply: 3/f

Quote: refer to 1st floor caculus2 response:

Position error is the cursor the line
The open cur1 for sql_statement reported missing expression

Should be the SQL you spell has a problem, print it out and have a look

I also feel is a problem of SQL, but single yes, can you help me look at the connector does not have what problem, especially the date conversion that piece, but my other connection methods are tried compiled by this method

CodePudding user response:

Where is the great god, help me to look at dynamic SQL connector problem, before I CALL is want to look at the empty past can line, formula were reported missing, just write the whole parameters, quote me "" "/yyyy/MM/dd "identifier is invalid

CodePudding user response:

In the dynamic SQL string concatenation is three single quotes, you how is 3 double quotes, what the hell is

CodePudding user response:

I change the incoming date to the date type and submitted to the SQL didn't end correctly, SQL repeated test no problem

CodePudding user response:

Dynamic SQL can be done, you use the cursor efficiency could also end point

SQL> The create table t (id int, nn varchar2 (50));

The table is created,

SQL> Select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
7369 SMITH at 7902 1980-12-17 00:00:00 20 800
7499 ALLEN SALESMAN 7698 1600 300 1981-02-20 00:00:00 30
7521 WARD SALESMAN 7698 1250 500 1981-02-22 00:00:00 30
7566 JONES MANAGER 00:00:00 20 2975 1981-04-02 7839
7654 MARTIN SALESMAN 7698 1250 1400 1981-09-28 00:00:00 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 30 2850
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 00:00:00 20 3000 1987-04-19 7566
7839 KING PRESIDENT 00:00:00 5000 10 1981-11-17
7698 1981-09-08 7844 TURNER SALESMAN 00:00:00 1500 0 30
ADAMS at 7876 7788 1100 1987-05-23 00:00:00 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 30 950
7902 FORD ANALYST 00:00:00 20 3000 1981-12-03 7566
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull