Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

pls-00320

Posted on 2004-10-15
7
Medium Priority
?
951 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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

722 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