x

Sql qyery to accept varianles and perform search

 DECLARE
     v_Batch_Cd VARCHAR2(15);
     v_Run_Status VARCHAR2(20);
     vv_Run_status VARCHAR2(20);
     v_Batch_Nbr VARCHAR2(20);
     v_Batch_Bus_Dt VARCHAR2(20);
     v_Rec_Err_Cnt VARCHAR2(20);
     v_Rec_Proc_Cnt VARCHAR2(20);
     v_Batch_List VARCHAR2(20);
     v_batch_job_id VARCHAR2(20);
     v_batch_start_dttm VARCHAR2(20);
 CURSOR C1 IS
        v_Batch_Cd:= '&v_Batch_Cd';
      select                            
        a.batch_cd as hambatchcd,   
        a.batch_nbr as hambatchnbr,   
        sum(a.rec_proc_cnt) as riggity,                            
        sum(a.rec_err_cnt) as wrecked,                             
        b.batch_bus_dt as bbdson,       
        b.run_status as bbdsonstatus  
      from ci_batch_inst a, ci_batch_run b
      where b.run_status in ('40')                  
      and a.batch_nbr = b.batch_nbr
      and a.batch_cd = b.batch_cd
      and a.batch_cd = &v_Batch_Cd
        group by b.batch_bus_dt, a.batch_nbr, a.batch_cd, b.run_status                            
      order by b.batch_bus_dt desc, a.batch_cd;
 BEGIN
      CASE 
      WHEN v_Batch_Cd = 'SAACT' THEN 
        BEGIN
         dbms_output.put_line('BATCH CODE'||'  '||'BATCH NUMBER'||'  '||'RECORDS PROCESSED'||'  '||'RECORDS ERRORED'||'  '||'BATCH BUSINESS DATE'||'  '||'STATUS');
         OPEN C1;
           LOOP
            FETCH C1 INTO v_Batch_Cd,v_Batch_Nbr,v_Rec_Proc_Cnt,v_Rec_Err_Cnt,v_Batch_Bus_Dt,v_Run_Status;
            EXIT WHEN C1%NOTFOUND;
            dbms_output.put_line(v_Batch_Cd||'     '||v_Batch_Nbr||'  '||v_Rec_Proc_Cnt||'  '||v_Rec_Err_Cnt||'  '||v_Batch_Bus_Dt||'  '||v_Run_Status);
           END LOOP;
         CLOSE C1;
      END;
      ELSE  
         dbms_output.put_line('PLEASE MAKE VALID SELCETION'); 
      END CASE;
 END;

Error report - ORA-06550: line 13, column 8: PLS-00103: Encountered the symbol "V_BATCH_CD" when expecting one of the following:

( select ORA-06550: line 44, column 4: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with

more ▼

asked Apr 20 at 02:16 PM in Default

avatar image

jedgal78
1

I know very little of Oracle, but you seem to have an extra "END" with what you have provided. Is there another BEGIN at the top we aren't seeing?

Apr 24 at 12:39 PM JohnM

@jedgal78 It looks like you are trying to assign the locally declared variable named v_Batch_Cd a value from the variable passed into the script from outside. In this case, why do you enclose the outside variable name in single quotes? Doesn't it make it a literal string, assigning the locally declared variable a hard-coded string literal value instead of the expected value of the outside parameter? Please try to remove the single quotes in line 13.

Apr 24 at 01:47 PM Oleg
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1080
x433

asked: Apr 20 at 02:16 PM

Seen: 63 times

Last Updated: Apr 24 at 01:47 PM

Copyright 2016 Redgate Software. Privacy Policy