Solved

pls-00320

Posted on 2004-10-15
7
938 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
Industry Leaders: 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!

 

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

Technology Partners: 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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

729 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