Solved

Help on creating dynamic insert update and delete statement

Posted on 2006-07-10
48
1,421 Views
Last Modified: 2008-01-09
Hello All,

I need some help in fixing the procedure. I am trying to create the dynamic insert, update and delete statements.

When I comment on the delete and update statement I get the error as
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification

If I comment the insert statement then I get the error as
ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1.

The syntax I used to execute the procedure is

SET SERVEROUTPUT ON SIZE 100000
EXEC CRT_DYNAMIC_CARDS ('YM');

I will definitely increase the points accordingly

Below is my complete procedure
***************************************************************************
CREATE OR REPLACE PROCEDURE  CRT_DYNAMIC_CARDS (IN_STG_TYPE in varchar2)IS

v_rej_stmt                  VARCHAR2(4000);
v_del_stmt                  VARCHAR2(4000);
v_upd_stmt                  VARCHAR2(4000);
v_column1                   VARCHAR2(100);
v_column_list_ins           VARCHAR2(4000);
v_column_list_sel           VARCHAR2(4000);
insert_list                 VARCHAR2(4000);
select_list                 VARCHAR2(4000);
from_list                   VARCHAR2(4000);

v_stg_table_name            VARCHAR2(30) ;
v_rej_table_name            VARCHAR2(30) ;
v_tgt_output_data           VARCHAR2(10) ;
i                           NUMBER(10) := 1;

Cursor rej_cur is
SELECT DISTINCT STG_COL_NM
FROM CUST_DEF
WHERE STG_TYP_CD = IN_STG_TYPE
AND STG_TBL_NM IS NOT NULL;

BEGIN

FOR rec in rej_cur
LOOP
  v_column1 := rec.STG_COL_NM;
  v_column_list_ins := v_column_list_ins||v_column1||',' ;
  v_column_list_sel := v_column_list_sel||'a.'||v_column1||',' ;
  i := i + 1;

END LOOP;

v_column_list_ins  := RTRIM(v_column_list_ins,',') ;
v_column_list_sel  := RTRIM(v_column_list_sel,',') ;

SELECT DISTINCT STG_TBL_NM, REJ_TBL_NM
INTO v_stg_table_name,v_rej_table_name
FROM CUST_DEF
WHERE STG_TYP_CD =IN_STG_TYPE;

SELECT TRGT_OPUT_DATA_TXT
INTO v_tgt_output_data
FROM CUST_DEF
WHERE STG_TYP_CD =IN_STG_TYPE
AND TRGT_COL_NM ='STATUS';

insert_list := 'INSERT INTO '||v_rej_table_name||'('||v_column_list_ins||',ACCT_NBR,CUST_CD) ';
select_list := 'SELECT '||v_column_list_sel||',b.ACCT_NBR,b.CUST_CD  ';
from_list   := 'FROM  '||v_stg_table_name||' a,
                CUST_MATCH_NBR b
                WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM
                AND (TRIM(MBR_NBR)=''0'' OR TRIM(MBR_NBR) IS NULL)
                AND TRIM(a.CLAIM_TYPE) <> '||v_tgt_output_data ;
v_rej_stmt  := insert_list||select_list||from_list;


v_del_stmt  := 'DELETE
FROM   CUST_MATCH_NBR
WHERE  ROW_SEQ_NUM IN
       (SELECT b.ROW_SEQ_NUM
        FROM '||v_stg_table_name||' a, CUST_MATCH_NBR b
       WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM AND
              (TRIM(b.MBR_NBR)=''0'' OR TRIM(MBR_NBR) IS NULL) AND
             TRIM(a.CLAIM_TYPE) <> '||v_tgt_output_data||')' ;


v_upd_stmt  := 'UPDATE CUST_MATCH_NBR
   SET SRC_REC_CD = ''CM''
WHERE  ROW_SEQ_NUM IN
       (SELECT b.ROW_SEQ_NUM
        FROM '||v_stg_table_name||' a, CUST_MATCH_NBR b
       WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM AND
              TRIM(b.MBR_NBR)=''0'' AND
             TRIM(a.CLAIM_TYPE) = ' ||v_tgt_output_data || ')';  

DBMS_OUTPUT.PUT_LINE('REJECT :'||v_rej_stmt);
DBMS_OUTPUT.PUT_LINE('DELETE :'||v_del_stmt);
DBMS_OUTPUT.PUT_LINE('UPDATE :'||v_upd_stmt);

EXECUTE IMMEDIATE v_rej_stmt ;
EXECUTE IMMEDIATE v_del_stmt ;
EXECUTE IMMEDIATE v_upd_stmt ;

DBMS_OUTPUT.PUT_LINE(‘COMPLETE SUCCESSFULLY');

END CRT_DYNAMIC_CARDS;

Thanks in advance for all the help.
0
Comment
Question by:cutie_smily
  • 21
  • 20
  • 5
  • +1
48 Comments
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
What type of data is MBR_NBR?  Also, CLAIM_TYPE.  Since you are trimming them, I assume they're character data.  If so, your from_list needs to be:

from_list   := 'FROM  '||v_stg_table_name||' a,
                CUST_MATCH_NBR b
                WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM
                AND (TRIM(MBR_NBR)=''0'' OR TRIM(MBR_NBR) IS NULL)
                AND TRIM(a.CLAIM_TYPE) <> '''||v_tgt_output_data ||'''';

and the update:

v_upd_stmt  := 'UPDATE CUST_MATCH_NBR
   SET SRC_REC_CD = ''CM''
WHERE  ROW_SEQ_NUM IN
       (SELECT b.ROW_SEQ_NUM
       FROM '||v_stg_table_name||' a, CUST_MATCH_NBR b
      WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM AND
            TRIM(b.MBR_NBR)=''0'' AND
           TRIM(a.CLAIM_TYPE) = ''' ||v_tgt_output_data || ''')';
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Change
v_column_list_sel := v_column_list_sel||'a.'||v_column1||',' ;

to
v_column_list_sel := 'a.'||v_column_list_sel||v_column1||',' ;

Also post the o/p of the dbms_output statements
0
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
also, you are going to end up with carriage returns in your strings.  I prefer:

from_list   := 'FROM  '||v_stg_table_name||' a, ' ||
                'CUST_MATCH_NBR b ' ||
                'WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM ' ||
                'AND (TRIM(MBR_NBR)=''0'' OR TRIM(MBR_NBR) IS NULL) ' ||
                'AND TRIM(a.CLAIM_TYPE) <> '''||v_tgt_output_data ||'''';
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Oops ignore my post. Didn't notice what you were doing.
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Comment out insert,update and delete and just do a DBMS_OUTPUT of all 3 statement. If DBMS_OUTPUT gives a buffer size error, try creating a temp table and insert these 3 statements in there and see if there's any syntax error with your statements
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Also after  running the stored procedure run this
select * from user_errors;

Should show you the errors from execute immediate statements.


ORA-06502: PL/SQL: numeric or value error: host bind array too small
Comment out the dbms_output statements and see if this error occurs
0
 

Author Comment

by:cutie_smily
Comment Utility
Thanks for the very quick replies. I will make the changes as per the suggestions and get back to you.

Hi Jrb1,

You are right the MBR_NBR and CLAIM_TYPE are characters.

thanks

0
 

Author Comment

by:cutie_smily
Comment Utility
Hi Jrb1,
I commented out update and delete statements and tried the code you provided for the from_list

I got the error below
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification
ORA-06512: at "ODNM.CRT_DYNAMIC_CARDS ", line 83
ORA-06512: at line 1

Hi Sathyagiri,

I commented out all the statements and executed the procedure

Below is the result:
REJECT :
DELETE :
UPDATE :
COMPLETE SUCCESSFULLY

PL/SQL procedure successfully completed.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Are you aware that PL\SQL is *NOT* optimized for dynamic SQL statements and that they will perform much more slowly than procedures with standard (static) SQL commands?

Do you want this application to handle more than just a few simultaneous users?  If so, you may need to use procedures with static SQL statements.

I also see some "select disctinct..." queries in your procedure.  Is the "distinct" keyword required in each of these places, or is that just included out of habit from someone who is more familiar with SQL Server than with Oracle?  The "distinct" keyword can be a big performance penalty in Oracle if you don't need it.
0
 

Author Comment

by:cutie_smily
Comment Utility
Cursor rej_cur is
SELECT DISTINCT STG_COL_NM
FROM CUST_DEF
WHERE STG_TYP_CD = IN_STG_TYPE
AND STG_TBL_NM IS NOT NULL;

I am using ‘distinct’ on a small control table which I think will not affect the performance as it contains not more that 1000 records.

thanks for looking into this.
0
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
CREATE OR REPLACE PROCEDURE  CRT_DYNAMIC_CARDS (IN_STG_TYPE in varchar2) IS

v_rej_stmt                  VARCHAR2(4000);
v_del_stmt                  VARCHAR2(4000);
v_upd_stmt                  VARCHAR2(4000);
v_column1                   VARCHAR2(100);
v_column_list_ins           VARCHAR2(4000);
v_column_list_sel           VARCHAR2(4000);
insert_list                 VARCHAR2(4000);
select_list                 VARCHAR2(4000);
from_list                   VARCHAR2(4000);

v_stg_table_name            VARCHAR2(30) ;
v_rej_table_name            VARCHAR2(30) ;
v_tgt_output_data           VARCHAR2(10) ;
i                           NUMBER(10) := 1;

Cursor rej_cur is
SELECT DISTINCT STG_COL_NM
FROM CUST_DEF
WHERE STG_TYP_CD = IN_STG_TYPE
AND STG_TBL_NM IS NOT NULL;

BEGIN

FOR rec in rej_cur
LOOP
  v_column1 := rec.STG_COL_NM;
  v_column_list_ins := v_column_list_ins||v_column1||',' ;
  v_column_list_sel := v_column_list_sel||'a.'||v_column1||',' ;
  i := i + 1;

END LOOP;

SELECT DISTINCT STG_TBL_NM, REJ_TBL_NM
INTO v_stg_table_name,v_rej_table_name
FROM CUST_DEF
WHERE STG_TYP_CD =IN_STG_TYPE;

SELECT TRGT_OPUT_DATA_TXT
INTO v_tgt_output_data
FROM CUST_DEF
WHERE STG_TYP_CD =IN_STG_TYPE
AND TRGT_COL_NM ='STATUS';

insert_list := 'INSERT INTO '||v_rej_table_name||'('||v_column_list_ins||'ACCT_NBR,CUST_CD) ';
select_list := 'SELECT '||v_column_list_sel||'b.ACCT_NBR,b.CUST_CD  ';
from_list   := 'FROM  '||v_stg_table_name||' a, ' ||
                'CUST_MATCH_NBR b ' ||
                'WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM ' ||
                'AND (TRIM(MBR_NBR)=''0'' OR TRIM(MBR_NBR) IS NULL) ' ||
                'AND TRIM(a.CLAIM_TYPE) <> '''||v_tgt_output_data||'''';
v_rej_stmt  := insert_list||select_list||from_list;


v_del_stmt  := 'DELETE ' ||
'FROM   CUST_MATCH_NBR ' ||
'WHERE  ROW_SEQ_NUM IN ' ||
       '(SELECT b.ROW_SEQ_NUM ' ||
       'FROM '||v_stg_table_name||' a, CUST_MATCH_NBR b ' ||
      'WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM AND ' ||
            '(TRIM(b.MBR_NBR)=''0'' OR TRIM(MBR_NBR) IS NULL) AND ' ||
           'TRIM(a.CLAIM_TYPE) <> '''||v_tgt_output_data||''')' ;


v_upd_stmt  := 'UPDATE CUST_MATCH_NBR '
   'SET SRC_REC_CD = ''CM'' ' ||
'WHERE  ROW_SEQ_NUM IN ' ||
       '(SELECT b.ROW_SEQ_NUM ' ||
       'FROM '||v_stg_table_name||' a, CUST_MATCH_NBR b ' ||
      'WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM AND ' ||
            'TRIM(b.MBR_NBR)=''0'' AND ' ||
           'TRIM(a.CLAIM_TYPE) = ''' ||v_tgt_output_data || ''')';  

DBMS_OUTPUT.PUT_LINE('REJECT :'||v_rej_stmt);
DBMS_OUTPUT.PUT_LINE('DELETE :'||v_del_stmt);
DBMS_OUTPUT.PUT_LINE('UPDATE :'||v_upd_stmt);

EXECUTE IMMEDIATE v_rej_stmt ;
EXECUTE IMMEDIATE v_del_stmt ;
EXECUTE IMMEDIATE v_upd_stmt ;

DBMS_OUTPUT.PUT_LINE(‘COMPLETE SUCCESSFULLY');

END CRT_DYNAMIC_CARDS;
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Can you run this query and just post the o/p

SELECT DISTINCT STG_COL_NM
FROM CUST_DEF
WHERE STG_TYP_CD = 'YM'
AND STG_TBL_NM IS NOT NULL;
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Is your "small control table which ... contains not more that 1000 records" a mostly static table, or do these records get deleted, and re-inserted frequently?

What are the advantages you see for dynamic SQL statements vs. static SQL statements?
0
 

Author Comment

by:cutie_smily
Comment Utility
Hi Markgeer,

It is a static table. The control table is basically a kind of mapping table. We are using the dynamic sql because we have multiple sources loading into one target table. We do not want to create an individual procedure or function to process each of the source records. So, we created a control table where we have all the source columns mapped to the target columns.

I would really appreciate if you have any other solutions.
***********************************************************************************
Hi Sathaygiri,

When I run the above sql statement I got back 89 rows where the 10th row is a blank and I am not sure why. These 89 rows are the column names of the stage tape 'YM'.

*************************************************************************************
Hi Jrb1,

Thanks very much for posting the complete procedure. I got 1 error when I compiled and 3 errors when I ran the procedure.

I compilation error was on the line
'SET SRC_REC_CD = ''CM'' ' ||
I added a pipe before it and compiled ok

When I ran the procedure the errors I got were:

For update and delete I got the missing parenthesis error and for the insert I got the same old error ORA-01747.

All these errors are at the line execute immediate.

Thanks for all the help. I raised the points for all the efforts and I will definitely raise more.
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Ok try removing the 10th row which was a blank from your table or try to filter it out from your select.

Also are you sure if the dynamic statements will fit in 4000 characters?

0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
One more way to debug is to check the validity of your dynamic statement.

Try this

create table test (query_string varchar2(4000));

And in your procedure add this just before the end statement.

exception
when others then
insert into test values (v_rej_stmt);

Then run your procedure and do a select * from test and post the o/p here.

0
 

Author Comment

by:cutie_smily
Comment Utility
Hi Sathyagiri,

I am not sure whether the dynamic statements will fit in 4000 characters. I don't have much knowledge in there procedures.
I am not sure how to take out that blank line using the SQL

I tried this but didn't work. Meaning it didn't removed the blank line.

SELECT DISTINCT STG_COL_NM
FROM CUST_DEF
WHERE STG_TYP_CD = 'YM'
AND STG_TBL_NM IS NOT NULL
and STG_TBL_NM <> ' ';


Thanks
0
 

Author Comment

by:cutie_smily
Comment Utility
This is error after the execution of the procedure

ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1

PL/SQL procedure successfully completed.

*************************************************************

Below is the output from the temp table.

QUERY_STRING

OORDINATION_OF_BENEFITS_IND,a.GENERAL_PURPOSE_2,a.ORIGINAL_QUANTITY,a.INGREDIENT_COST_PAID,a.COPAY_USED,a.AFTER_MAX_AMOUNT,a.AMOUNT_PAID,a.NEW_REFILL,a.FORMULARY_COMPLIANCE_CODE,a.RECAP_PROCESS_DATE,a.INGREDIENT_COST_CLAIMED,a.FILLER2,a.MANAGED_ACC_CLAIM_LCK_IN_CD,a.CARRIER_NUMBER,a.RECAP_PHARMACY_INDICATOR,a.CARDHOLDER_ID_NUMBER,a.PATIENT_BIRTHDATE,a.MAX_ALLOWA_COST_MAC_REDUCD_IND,a.SALES_TAX_PAID,a.VALID_PAID_DENIAL_CODE,a.POLICY_NUMBER,b.ACCT_NBR,b.CUST_CD  FROM  MARK_STG_TBL a, CUST_MATCH_NBR  b WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM AND (TRIM(MBR_NBR)='0' OR TRIM(MBR_NBR) IS NULL) AND TRIM(a.CLAIM_TYPE) <> ''AA''


1 row selected.
0
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 100 total points
Comment Utility
I tried to create tables like yours, and was able to get this to run:

CREATE OR REPLACE PROCEDURE  CRT_DYNAMIC_CARDS (IN_STG_TYPE in varchar2) IS
v_rej_stmt                  VARCHAR2(4000);
v_del_stmt                  VARCHAR2(4000);
v_upd_stmt                  VARCHAR2(4000);
v_column1                   VARCHAR2(100);
v_column_list_ins           VARCHAR2(4000);
v_column_list_sel           VARCHAR2(4000);
insert_list                 VARCHAR2(4000);
select_list                 VARCHAR2(4000);
from_list                   VARCHAR2(4000);

v_stg_table_name            VARCHAR2(30) ;
v_rej_table_name            VARCHAR2(30) ;
v_tgt_output_data           VARCHAR2(10) ;
i                           NUMBER(10) := 1;

Cursor rej_cur is
SELECT DISTINCT STG_COL_NM
FROM CUST_DEF
WHERE STG_TYP_CD = IN_STG_TYPE
AND STG_TBL_NM IS NOT NULL;

BEGIN

FOR rec in rej_cur
LOOP
  v_column1 := rec.STG_COL_NM;
  v_column_list_ins := v_column_list_ins||v_column1||',' ;
  v_column_list_sel := v_column_list_sel||'a.'||v_column1||',' ;
  i := i + 1;
END LOOP;

SELECT DISTINCT STG_TBL_NM, REJ_TBL_NM
INTO v_stg_table_name,v_rej_table_name
FROM CUST_DEF
WHERE STG_TYP_CD =IN_STG_TYPE;

SELECT TRGT_OPUT_DATA_TXT
INTO v_tgt_output_data
FROM CUST_DEF
WHERE STG_TYP_CD =IN_STG_TYPE
AND TRGT_COL_NM ='STATUS';

insert_list := 'INSERT INTO '||v_rej_table_name||'('||v_column_list_ins||'ACCT_NBR,CUST_CD) ';
select_list := 'SELECT '||v_column_list_sel||'b.ACCT_NBR,b.CUST_CD  ';
from_list   := 'FROM  '||v_stg_table_name||' a, ' ||
                'CUST_MATCH_NBR b ' ||
                'WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM ' ||
                'AND (TRIM(MBR_NBR)=''0'' OR TRIM(MBR_NBR) IS NULL) ' ||
                'AND TRIM(a.CLAIM_TYPE) <> '''||v_tgt_output_data||''' ';
v_rej_stmt  := insert_list||select_list||from_list;
v_del_stmt  := 'DELETE ' ||
'FROM   CUST_MATCH_NBR ' ||
'WHERE  ROW_SEQ_NUM IN ' ||
       '(SELECT b.ROW_SEQ_NUM ' ||
       'FROM '||v_stg_table_name||' a, CUST_MATCH_NBR b ' ||
      'WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM AND ' ||
            '(TRIM(b.MBR_NBR)=''0'' OR TRIM(MBR_NBR) IS NULL) AND ' ||
           'TRIM(a.CLAIM_TYPE) <> '''||v_tgt_output_data||''')' ;
v_upd_stmt  := 'UPDATE CUST_MATCH_NBR ' ||
   'SET SRC_REC_CD = ''CM'' ' ||
'WHERE  ROW_SEQ_NUM IN ' ||
       '(SELECT b.ROW_SEQ_NUM ' ||
       'FROM '||v_stg_table_name||' a, CUST_MATCH_NBR b ' ||
      'WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM AND ' ||
            'TRIM(b.MBR_NBR)=''0'' AND ' ||
           'TRIM(a.CLAIM_TYPE) = ''' ||v_tgt_output_data || ''')';

DBMS_OUTPUT.PUT_LINE('REJECT :'||v_rej_stmt);
DBMS_OUTPUT.PUT_LINE('DELETE :'||v_del_stmt);
DBMS_OUTPUT.PUT_LINE('UPDATE :'||v_upd_stmt);
EXECUTE IMMEDIATE v_rej_stmt ;
EXECUTE IMMEDIATE v_del_stmt ;
EXECUTE IMMEDIATE v_upd_stmt ;
DBMS_OUTPUT.PUT_LINE('COMPLETE SUCCESSFULLY');
END CRT_DYNAMIC_CARDS;
0
 

Author Comment

by:cutie_smily
Comment Utility
I deleted all my code and copied your and it compiled ok but when I executed the procedure I got the below errors

ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1


BEGIN CRT_DYNAMIC_CARDS( 'YM'); END;

*
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification
ORA-06512: at "ODNM.CRT_DYNAMIC_CARDS", line 75
ORA-06512: at line 1
**************************************************************
If I comment the below line
DBMS_OUTPUT.PUT_LINE('REJECT :'||v_rej_stmt);

I get missing parenthesis error for update and delete.

thanks,
0
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
OK...instead of this:

DBMS_OUTPUT.PUT_LINE('REJECT :'||v_rej_stmt);

Try:

DBMS_OUTPUT.PUT_LINE('insert :'||insert_list);
DBMS_OUTPUT.PUT_LINE('select :'||select_list);
DBMS_OUTPUT.PUT_LINE('from :'||from_list);
0
 

Author Comment

by:cutie_smily
Comment Utility
After removing some quotes and commenting on the insert statement I got the below errors

'TRIM(a.CLAIM_TYPE) <> '||v_tgt_output_data||')' ;

ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1

PL/SQL procedure successfully completed.
0
 

Author Comment

by:cutie_smily
Comment Utility
Is there a way to split the insert statements and then concatinate ? Because when I quiried the temp table as per Sathyagiri's suggestions I didn't see all the column names in the table.

Thanks
0
 

Author Comment

by:cutie_smily
Comment Utility
When I tried the below statements

DBMS_OUTPUT.PUT_LINE('insert :'||insert_list);
DBMS_OUTPUT.PUT_LINE('select :'||select_list);
DBMS_OUTPUT.PUT_LINE('from :'||from_list);

I got the error as

ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1
*****************************************************

When I commented all the DBMS_OUTPUT_LINE statments the procedure executed fine

COMPLETE SUCCESSFULLY
PL/SQL procedure successfully completed.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
The above error is because of dbms_output limitations.

Can you try this

replace EXECUTE IMMEDIATE v_rej_stmt with

EXECUTE IMMEDIATE insert_list||select_list||from_list;

0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Also to remove the blank record use this
SELECT DISTINCT STG_COL_NM
FROM CUST_DEF
WHERE STG_TYP_CD = IN_STG_TYPE
AND STG_TBL_NM IS NOT NULL AND REPALCE(STG_COL_NM,' ','') = '';
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
oops the query should be

SELECT DISTINCT STG_COL_NM
FROM CUST_DEF
WHERE STG_TYP_CD = IN_STG_TYPE
AND STG_TBL_NM IS NOT NULL AND REPALCE(STG_COL_NM,' ','') <> '';
0
 

Author Comment

by:cutie_smily
Comment Utility
Thanks very much for all the help. You guys are life savers

The procedure execute succefully after commenting the execute and dbms statements. I am not sure why the 'v_column_list_ins' is not displaying any of the columns in the creation of the insert statements.

Below are some of the warning messages
------------------------------------------------
Error: Hint: Value assigned to 'v_del_stmt' never used in 'CRT_DYNAMIC_CARDS'
Line: 55
Text: v_del_stmt  := 'DELETE ' ||

Error: Hint: Value assigned to 'v_upd_stmt' never used in 'CRT_DYNAMIC_CARDS'
Line: 64
Text: v_upd_stmt  := 'UPDATE XPBM_TRILLIUM_MATCH_RTN ' ||

Error: Hint: Comparison with NULL in 'CRT_DYNAMIC_CARDS'
Line: 22
Text: AND STG_TBL_NM IS NOT NULL AND REPLACE(STG_COL_NM,' ','') <> '';

Thanks
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
you can comment out the declarartion of v_del_stmt,v_upd_stmt in your proc to avoid the first 2 messages.

ALSO change this to
SELECT DISTINCT STG_COL_NM
FROM CUST_DEF
WHERE STG_TYP_CD = IN_STG_TYPE
AND STG_TBL_NM IS NOT NULL AND REPALCE(STG_COL_NM,' ','') <> '';

SELECT DISTINCT STG_COL_NM
FROM CUST_DEF
WHERE STG_TYP_CD = IN_STG_TYPE
AND STG_TBL_NM IS NOT NULL AND REPALCE(STG_COL_NM,' ','') is not null ;
0
 

Author Comment

by:cutie_smily
Comment Utility
Thanks very much the query worked this time. If I comment out the declaration of the two variables I am getting error at the update and delete statements as I am using those variables.

The procedure compiled ok and ran successfully after ignoring the warning and commenting out the dbms statements. I want to know where I can look at the insert statements this procedure created? I looked at the temp table and it didn’t created any.

Thanks,
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
I am assuming you have added code for inserting into temp table in the exception block, so if you don't have any exceptions you will not see that.

If you still want to see it.

Move the insert logic into temp table to the main block (ie) before the exception block
0
 

Author Comment

by:cutie_smily
Comment Utility
I have the below statements after the insert, delete and update

EXECUTE IMMEDIATE insert_list||select_list||from_list;

DBMS_OUTPUT.PUT_LINE('COMPLETE SUCCESSFULLY');

EXCEPTION
WHEN OTHERS THEN
INSERT INTO TEST_EXC VALUES (v_rej_stmt);

END CRT_DYNAMIC_CARDS;

******************************************************
When I did a query on the test_exc table I didn't get any rows.

Can you please help me how to do the below if that helps?
"Move the insert logic into temp table to the main block (ie) before the exception block"
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
This should do this
EXECUTE IMMEDIATE insert_list||select_list||from_list;

INSERT INTO TEST_EXC VALUES (insert_list||select_list||from_list);

-- If the above doesn't work
INSERT INTO TEST_EXC VALUES (v_rej_stmt);


DBMS_OUTPUT.PUT_LINE('COMPLETE SUCCESSFULLY');

EXCEPTION
WHEN OTHERS THEN
INSERT INTO TEST_EXC VALUES (v_rej_stmt);
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Also since your dynamic sql stmt exceeds 4000 chararachter, change your temp table column definition to  CLOB instead of varchar2(4000);
0
 

Author Comment

by:cutie_smily
Comment Utility
Thanks Satya,

The statement is created in the temp table but not the full statement. It created like below

OF_BENEFITS_IND,a.GENERAL_PURPOSE_2,a.ORIGINAL_QUANTITY,a.INGREDIENT_COST_PAID,a.COPAY_USED,a.AFTER_MAX_AMOUNT,a.AMOUNT_PAID,a.NEW_REFILL,a.FORMULARY_COMPLIANCE_CODE,a.RECAP_PROCESS_DATE,a.INGREDIENT_COST_CLAIMED,a.FILLER2,a.MANAGED_ACC_CLAIM_LCK_IN_CD,a.CARRIER_NUMBER,a.RECAP_PHARMACY_INDICATOR,a.CARDHOLDER_ID_NUMBER,a.PATIENT_BIRTHDATE,a.MAX_ALLOWA_COST_MAC_REDUCD_IND,a.SALES_TAX_PAID,a.VALID_PAID_DENIAL_CODE,a.POLICY_NUMBER,b.ACCT_NBR,b.CUST_CD  FROM  MARK_STG_TBL a, CUST_MATCH_NBR  b WHERE   a.ROW_SEQ_NUM=b.ROW_SEQ_NUM AND (TRIM(MBR_NBR)='0' OR TRIM(MBR_NBR) IS NULL) AND TRIM(a.CLAIM_TYPE) <> 'AA'

Can please let me know how do I fix this?

Thanks
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 400 total points
Comment Utility
Ok change the table definition of your TEMP TABLE

DROP TABLE TEST_EXC;

CREATE TABLE TEST_EXC(qry_string clob);

Now  in your peocedure use

EXECUTE IMMEDIATE insert_list||select_list||from_list;

INSERT INTO TEST_EXC VALUES (insert_list||select_list||from_list);

Also change the following in your procedure
v_rej_stmt                  VARCHAR2(4000);
v_del_stmt                  VARCHAR2(4000);
v_upd_stmt                  VARCHAR2(4000);

to
v_rej_stmt                  CLOB;
v_del_stmt                  CLOB;
v_upd_stmt                  CLOB;


0
 

Author Comment

by:cutie_smily
Comment Utility
I made the necessary changes and as mentioned above. The result from the temp table is

INSERT INTO Cust_REJ(DRUG_NAME,DRUG_DOSAGE_FORM,BILLING_ACCOUNT_NUMBER,

I think it printed only the first part of the insert statement and ignored the rest.

******************************************************

I used the below code

EXECUTE IMMEDIATE insert_list||select_list||from_list;
INSERT INTO RCIL_TEST VALUES (insert_list||select_list||from_list);

DBMS_OUTPUT.PUT_LINE('COMPLETE SUCCESSFULLY');
-----DBMS_OUTPUT.PUT_LINE('DELETE :'||v_del_stmt);

EXCEPTION
WHEN OTHERS THEN
INSERT INTO TEST_EXEC VALUES (v_rej_stmt);

END CRT_DYNAMIC_CARDS;
0
 

Author Comment

by:cutie_smily
Comment Utility
Please ignore RCIL_TEST table in the insert statement I use the TEST_EXEC.

thanks
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
before doing a select * from your temp table

type set long 9999 in your sqplus prompt, that will display the whole statement
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
You might want to type
set long 999999
0
 

Author Comment

by:cutie_smily
Comment Utility
You are the man !!!!!!!!!!!!!!!!!!

I will close this question soon and increase the points to full 500.

Please let me know how should I execute the update and delete statements and how to store them in the temp table.

Is there any otherway to display these statement without using the temp tables?

thanks very much.
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
If you have changed your v_upd_stmt and v_del_stmt variables to CLOB as I mentioned earlier you should be able to run those also.

Use the same logic for inserting them into temp tables also

For example
EXECUTE IMMEDIATE v_del_stmt ;
INSERT INTO TEST_EX VALUES(v_del_stmt);
EXECUTE IMMEDIATE v_upd_stmt ;
INSERT INTO TEST_EX VALUES(v_upd_stmt);
0
 

Author Comment

by:cutie_smily
Comment Utility
I am getting the error for these statements

EXECUTE IMMEDIATE v_del_stmt ;
EXECUTE IMMEDIATE v_upd_stmt ;

Error:
------
Error: PLS-00382: expression is of wrong type
Line: 79
Text: EXECUTE IMMEDIATE v_del_stmt ;

Error: PL/SQL: Statement ignored
Line: 79
Text: EXECUTE IMMEDIATE v_del_stmt ;

Error: PLS-00382: expression is of wrong type
Line: 82
Text: EXECUTE IMMEDIATE v_upd_stmt ;
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
Sorry just change your v_del_stmt and v_upd_stmt back to varchar2(4000). That should work.
0
 

Author Comment

by:cutie_smily
Comment Utility
Thanks very much worked like a charm.

Is there any otherway to display these statement without using the temp tables? I am asking this becuase my DBA may create problems to create a temp table in production.

If I use the dbms statements it is giving me errors.

Thanks very much and I really appreciate your time.
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
DBMS_OUTPUT has a limit of 255 chars per line.

What you could do is create a procedure like this
procedure print_sp( p_str in varchar2 )
is
   l_string long default p_str;
begin
   loop
     exit when l_string is null;
     dbms_output.put_line( substr( l_string, 1, 250 ) );
     l_string := substr( l_string, 251 );
   end loop;
end;

Then call this procedure p in your stored procedure like this

EXECUTE IMMEDIATE insert_list||select_list||from_list;
print_sp(insert_list||select_list||from_list;);

EXECUTE IMMEDIATE v_del_stmt ;
print_sp(v_del_stmt);

EXECUTE IMMEDIATE v_upd_stmt ;
print_sp(v_upd_stmt);

Or you can use utl_file to write the strings to a file.

refer to this link for more info
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:146412348066





0
 

Author Comment

by:cutie_smily
Comment Utility
Thank you very much for all the help and explanations. I am closing the question now and really appreciate all the help.

I am leaving the exception as you defined. If it is ok then please ignore it and if it is not give some kind of instructions.

Anyways I am closing the question and thanks very much.
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
I think you should be ok with the exception handler.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now