Oracle stored procedure inside can use | | connect the insert statement and the query? . Such as: VSQLS:=str_1 | | tab_record VSQL (str_1 is an insert statement, but tab_record VSQL is a query, the query results for multiple fields multiline value),

CodePudding user response:

Didn't understand what's this demand, say in detail,

CodePudding user response:

Str_1:="INSERT INTO RULE_WT (SYS_NAME, TABLE_NAME, COL_NAME, ABBR, INTEGRITY_SIZE, REGULAR_SIZE) ';

Tab_record VSQL is a query, for example: select 'program plan', 'YJ_GJLC_SJKB_XXHXMLC', 'comprehensive plan issued time, pr_abbr_name, count (*) from YJ_GJLC_SJKB_XXHXMLC where (to_char (" comprehensive plan issued time, "' - dd yyyy - mm) not between '1950-01-01' and to_char (sysdate, 'yyyy - mm - dd)) group by pr_abbr_name, the result of the execution of the corresponding str_1 several fields, now I want to VSQLS:=str_1 | | tab_record. VSQL, then execute immediate VSQLS can insert into the contents of the VSQL str_1, before it is possible, try to call a stored procedure times today's fault, an invalid relational operator is very depressed,

CodePudding user response:

Insert into table1 select * from table2 is legal

Give the correct specific statement posted and have a look

CodePudding user response:

Call a stored procedure times wrong, is invalid relational operator | | make a mistake, but | | not relational operators

CodePudding user response:

The error of the string concatenation VSQLS complete showing what

Picture is not complete

CodePudding user response:

Figure one article VSQL statement query results, figure 2 is an error line
VSQLS:="INSERT INTO RULE_WT (SYS_NAME, TABLE_NAME, COL_NAME, ABBR, INTEGRITY_SIZE, REGULAR_SIZE) '| |' select 'PMS', 'yj_gjlc_sjkb_scjglc', 'organization', pr_abbr_name, count (*), from 0 yj_gjlc_sjkb_scjglc where the organization is null group by pr_abbr_name '

CodePudding user response:

On the statement of it will be a problem, this statement alone will perform error?

CodePudding user response:

In the program are not dbms_output put_line? Look at the returned VSQLS value is what?
You write is not the actual value, but his writing

CodePudding user response:

You write statement has a problem, there is no conversion quotes

CodePudding user response:

Call a stored procedure is submitted to the fault, and no output VSQLS, before this is not wrong, single quotation marks don't need to turn the migration at the library last week, began to appear the error, but also should not ah, the data are in good condition, it is impossible to put in ah,


This picture is inserted data earlier this month, the stored procedure has not changed, only the net error,

CodePudding user response:

So you need to see in the program VSQLS what is the actual value of the

Your 'select' PMS ', 'yj_gjlc_sjkb_scjglc', 'organization' this is the right writing clear

The output window will be displayed, program is not have dbms_output. Put_line (VSQLS)?

CodePudding user response:

Select 'PMS', 'yj_gjlc_sjkb_scjglc', 'organization' is to be written, so is their own values, as for VSQLS is no output to print, there is only one error, worry about me losing my hair

CodePudding user response:

If you can provide a set of table statements and test data to reproduce your problem now, should soon be able to solve,

CodePudding user response:

The question now is: 'select' PMS ', 'yj_gjlc_sjkb_scjglc', 'organization' this is not a string, there is a number of single quotes without joining together, so you need to know the real VSQLS,
If not the OUTPUT, you can create a table, and inserted the VSQLS and submit, then query this table, the results posted

CodePudding user response:

The CREATE
TABLE
DSQLS AS SELECT
T.s ystem,
T.t able_name,
Tc ol_abbr,
'1' RULE,
'select' | | '"' | | t.s ystem | | ' ' ', '| | "" | | t.t able_name | |' ' ', '| |" "| | tc ol_name | |' ' ', '| | tc ol_abbr | |', the count (*), '| |' 0 from '| | t.t able_name | |' where '| | t.i ntegrity_rule_context | |' group by '| | tc ol_abbr | |' VSQL
The FROM
Rule_database t
WHERE
T.i ntegrity_rule_context IS NOT NULL;


INSERT
INTO
DSQLS (system,
TABLE_NAME,
COL_ABBR,
RULE,
VSQL) (
SELECT
T.s ystem,
T.t able_name,
Tc ol_abbr,
'2',
'select' | | '"' | | t.s ystem | | ' ' ', '| | "" | | t.t able_name | |' ' ', '| |" "| | tc ol_name | |' ' ', '| | tc ol_abbr | |' 0 '| |', the count (*) from '| | t.t able_name | |' where '| | t.R EGULAR_RULE_CONTEXT | |' group by '| | tc ol_abbr | |'
The FROM
Rule_database t
WHERE
T.R EGULAR_RULE_CONTEXT IS NOT NULL);


DSQLS table data below

CodePudding user response:

INSERT INTO RULE_WT (SYS_NAME, TABLE_NAME, COL_NAME, ABBR, INTEGRITY_SIZE, REGULAR_SIZE) select 'ERP', 'yj_gjlc_sjkb_scjglc', 'proj_create_date' pr_abbr_name, count (*), from 0 yj_gjlc_sjkb_scjglc where ((pur_app_date is not null or cont_sign_date is not null or mat_arri_date is not null or proj_oper_date is not null or proj_settlements_date is not null or proj_final_date is not null or proj_close_date is not null) and proj_create_date is null) group by pr_abbr_name

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull