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?

5 days ago 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.

5 days ago 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:

x1076
x432

asked: Apr 20 at 02:16 PM

Seen: 42 times

Last Updated: 5 days ago

Copyright 2016 Redgate Software. Privacy Policy