Solved

pls-00320

Posted on 2004-10-15
7
929 Views
Last Modified: 2012-06-21
help me out here please

When i compile my proc it gives me error at this cursor

cursor cursor_insert is
select process_status,organization_code,plan_name,r2.emp_cd,r2.emp_nm,r2.docomo_date,r2.project_cd,r2.job_cd,r2.hours
from qa_result_initial r1 , dump_tran_initial r2
where r1.emp_cd = r2.emp_cd and
r1.docomo_date = r2.docomo_date and
to_char(r2.docomo_date,'MON-YYYY') = myear;

PLS-00320: the declaration of the type of this expression is incomplete or malformed
0
Comment
Question by:frinpd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 12323900
Is myear:
1) Exists?
2) A valid parameter or variable?
3) Is it VARCHAR2?


0
 

Author Comment

by:frinpd
ID: 12323944
All right let me tell you what i did

previously this query was like this

select process_status,organization_code,plan_name,emp_cd,emp_nm,character3,project_cd,job_cd,hours
from qa_results r1 , dump_tran_initial r2
where r1.character1 = r2.emp_cd and
r2.docomo_date = to_date(r1.character3,'YYYY-MM-DD') and
to_char(r2.docomo_date,'MON-YYYY') = 'AUG-2004'  and plan_id = 108;

Where character3 from Qa_result table has data type Varchar2(25) (This is Oracle Application's Default table) and it stores data in there.

But above query does work but below query doesn't work

select process_status,organization_code,plan_name,emp_nm,docomo_date,project_cd,job_cd,hours
from dump_tran_initial
where ( emp_cd,docomo_date) NOT IN
(select character1, to_date(character3,'yyyy-mm-dd')
from qa_results  q
where to_char(to_date(NVL(character3,'1900-01-01'),'yyyy-mm-dd'),'MON-YYYY') = 'AUG-2004'  and
plan_id = 108
);

so what i did i created qa_result_initial table and convert character3 field's data type from varchar2 to data and insert data using

insert into qa_result_initial
    (emp_cd,project_cd,docomo_date,hours,month_year,job_cd,
     emp_nm,overtime_flg
       )
       (
   select character1 as emp_cd,character2 as Project_cd,to_date(character3,'YYYY-MM-DD') as Docomo_date,character4 as Hours,
   character5 as Month_year,
   character6 as Job_cd,character7 as Emp_nm,character8 as Ot_flg from qa_results
   where plan_id = 108 and character5 = 'AUG-2004'
     );

i play around that field but couldn't find solution...
0
 

Author Comment

by:frinpd
ID: 12324036
Any idea....???
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:frinpd
ID: 12324063
Below are the tables...

SQL> desc qa_result_initial;
 Name                          
 ----------------------------
 EMP_CD                        
 EMP_NM                        
 PROJECT_CD                    
 JOB_CD                        
 DOCOMO_DATE                    
 HOURS                          
 MONTH_YEAR                    
 OVERTIME_FLG                  

SQL> desc dump_tran_initial;
 Name                          
 ----------------------------
 PROCESS_STATUS                
 ORGANIZATION_CODE              
 PLAN_NAME                      
 EMP_CD                        
 EMP_NM                        
 PROJECT_CD                    
 JOB_CD                        
 DOCOMO_DATE                    
 HOURS                          
 MONTH_YEAR                    
 OVERTIME_FLG                  
0
 

Author Comment

by:frinpd
ID: 12324083
Allright here is the procedure i am posting... any clue

CREATE OR REPLACE procedure initial_time_p(p_month varchar2, p_year varchar2) is
begin
declare

cursor CURemps is
select docomo_employee, emp_nm_ms,
project_cd,job_cd, Date_opened,Date_closed
from q_employee_master_v;


cursor cursor_insert is
select process_status,organization_code,plan_name,r2.emp_cd,r2.emp_nm,r2.docomo_date,r2.project_cd,r2.job_cd,r2.hours
from qa_result_initial r1 , dump_tran_initial r2
where r1.emp_cd = r2.emp_cd and
r1.docomo_date = r2.docomo_date and
to_char(r2.docomo_date,'MON-YYYY') = myear;



cursor cursor_update is
select process_status,organization_code,plan_name,emp_nm,docomo_date,project_cd,job_cd,hours
from dump_tran_initial
where ( emp_cd,docomo_date) NOT IN
(select emp_cd,docomo_date
from qa_result_initial  
where month_year = myear
) ;


v_end_date date;
day_count number;
cur_date date;

myear varchar2(150);


begin

myear := p_month ||'-'||p_year;


delete from dump_tran_initial;
delete from qa_result_initial;
commit;

insert into qa_result_initial
    (emp_cd,project_cd,docomo_date,hours,month_year,job_cd,
     emp_nm,overtime_flg
       )
       (
   select character1 as emp_cd,character2 as Project_cd,to_date(character3,'YYYY-MM-DD') as Docomo_date,character4 as Hours,
   character5 as Month_year,
   character6 as Job_cd,character7 as Emp_nm,character8 as Ot_flg from qa_results
   where plan_id = 108 and character5 = myear
     );


for r1 in CURemps loop

v_end_date := nvl(r1.Date_closed,last_day(to_date(p_month || '/01/' || p_year, 'MON/DD/YYYY')));

cur_date := to_date(p_month ||p_year,'MONYYYY');
day_count := v_end_date - (cur_date - 1);

for i in 1..day_count loop

if TO_CHAR(cur_date,'fmDY') NOT IN ('SAT','SUN') then
           
                                 insert into dump_tran_initial
                                 (
                                 process_status,organization_code,plan_name,
                                 emp_cd,emp_nm,project_cd,job_cd,
                                 Docomo_date,Hours,Month_year,Overtime_flg
                                 )
                                 values
                                 (
                                 1,'USA','INITIAL TIME PARAMETER',
                                 r1.docomo_employee, r1.emp_nm_ms,r1.project_cd,
                                 r1.job_cd, cur_date, 7,myear,'No'
                                 );

end if;
cur_date := cur_date + 1;
end loop;
end loop;

commit;


for i in cursor_insert LOOP
insert into q_initial_time_parameter_iv
 (
 process_status,organization_code,plan_name,insert_type,matching_elements,
 emp_cd,emp_nm,project_cd,job_cd,
 Docomo_date,Hours,month_year,Overtime_flg
 )
values(i.process_status,i.org_code,i.plan_name,2,'emp_cd,project_cd,job_cd,docomo_date',
 i.emp_cd,i.emp_nm,i.project_cd,i.job_cd,i.Docomo_date,i.hours,i.month_year,i.overtime_flg
);
END LOOP;

for j in cursor_update LOOP
insert into q_initial_time_parameter_iv
 (
 process_status,organization_code,plan_name,
 emp_cd,emp_nm,project_cd,job_cd,
 Docomo_date,Hours,month_year,Overtime_flg
 )
values(i.process_status,i.org_code,i.plan_name,
 i.emp_cd,i.emp_nm,i.project_cd,i.job_cd,i.Docomo_date,i.hours,i.month_year,i.overtime_flg
);
END LOOP;


end;
end;
/


Mike .... help me
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 12337832
Sorry, I was out for the weekend, I'll look into it...
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 12338302
Does it give compile error? Whart error,  on which statement?

Compile with SET ECHO ON and then SHOW ERROR

IF You are getting no compilation errors, then it may be some data error.
Your procedure seem's to be ok.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle DBLINKS From 11g to 8i 3 67
Oracle Insert not working 10 48
Oracle DB Slows After Datapump Until Next Reboot 27 133
oracle forms question 22 48
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

726 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