Home » Developer & Programmer » Forms » foms 6i - getting ora-00928 missing select keyword ora-06512 at"package.procedure_name" at line (forms 6i package.procedure error)
foms 6i - getting ora-00928 missing select keyword ora-06512 at"package.procedure_name" at line [message #583687] Fri, 03 May 2013 00:29 Go to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Hello,
I am importing data from excel to database table with column mapping.
I have created package with a procedure. I am calling this procedure from oracle forms 6i. while executing the package procedure,I am getting following error ora-00928 missing select keyword ora-06512 at"abcd.pk_excel_to_db" line 26 i.e befor forms_ddl.
I search on net for this problem but I am not getting perfect solution for this.
there is no problem in code and my form gets compiled but at run time i am getting this error.
I also want to check, array is populating with anything or not.
PACKAGE PK_EXCEL_TO_DB IS
  TYPE tKeyValue IS RECORD (
    CROUTE         VARCHAR2(255),
    VROUTE         VARCHAR2(1000),
    CTRNDATE       VARCHAR2(255),
    VTRNDATE       VARCHAR2(1000),
    CTTIME	        VARCHAR2(255),
    VTTIME         VARCHAR2(1000),
    CTID           VARCHAR2(255),
    VTID           VARCHAR2(1000));
  TYPE tDataList IS TABLE OF tKeyValue
  index by binary_integer;
  
  PROCEDURE PR_DO_INSERT(i_lData IN tDataList);	  
END;


PACKAGE BODY PK_EXCEL_TO_DB IS
    PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
    CC_ROUTE         VARCHAR2(255);
    VV_ROUTE         VARCHAR2(1000);
    CC_TRNDATE       VARCHAR2(255);
    VV_TRNDATE       VARCHAR2(1000);
    CC_TTIME	        VARCHAR2(255);
    VV_TTIME         VARCHAR2(1000);
    CC_TID           VARCHAR2(255);
    VV_TID           VARCHAR2(1000);
 BEGIN
    FOR i IN 1..i_lData.count 
    LOOP
      CC_ROUTE:=CC_ROUTE || ',' || i_ldata(i).CROUTE;
      VV_ROUTE:=VV_ROUTE|| ',''' || i_ldata(i).VROUTE || '''';
      
      CC_TRNDATE :=CC_TRNDATE  || ',' || i_ldata(i).CTRNDATE ;
      VV_TRNDATE :=VV_TRNDATE || ',''' || i_ldata(i).VTRNDATE  || '''';
      
      CC_TTIME :=CC_TTIME  || ',' || i_ldata(i).CTTIME ;
      VV_TTIME :=VV_TTIME || ',''' || i_ldata(i).VTTIME  || '''';
      
      CC_TID :=CC_TID  || ',' || i_ldata(i).CTID ;
      VV_TID :=VV_TID || ',''' || i_ldata(i).VTID|| '''';
    END LOOP;
    --EXECUTE IMMEDIATE 
    FORMS_DDL('INSERT INTO TEMP2 (' || SUBSTR(CC_ROUTE, 2) || ' ' || 
				       SUBSTR(CC_TRNDATE, 2) || ' ' || 
				       SUBSTR(CC_TTIME, 2) || '' || 
				       SUBSTR(CC_TID, 2) || ') 
                         VALUES (' ||  SUBSTR(VV_ROUTE,2) || '  ' || 
				       SUBSTR(VV_TRNDATE,2) || '' || 
				       SUBSTR(VV_TTIME,2) || ' ' || 
				       SUBSTR(VV_TID,2)|| ')');                                   
 commit;                                            
  END;
END;


Please help me regarding the same, its urgent.
Thank you
Sameer

[Updated on: Fri, 03 May 2013 01:04]

Report message to a moderator

Re: foms 6i - getting ora-00928 missing select keyword ora-06512 at"package.procedure_name" at line [message #583697 is a reply to message #583687] Fri, 03 May 2013 02:38 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
When debugging dynamic sql you should always assign the dynamic sql to a string variable and then display it.
Then you can be sure it's correct.
Re: foms 6i - getting ora-00928 missing select keyword ora-06512 at"package.procedure_name" at line [message #583716 is a reply to message #583697] Fri, 03 May 2013 06:18 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Thanks cookiemonster,
But sorry I am not getting what u say? I never used oracle records and collection before.
Thanks again
Sameer.
Re: foms 6i - getting ora-00928 missing select keyword ora-06512 at"package.procedure_name" at line [message #583718 is a reply to message #583716] Fri, 03 May 2013 06:36 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
The records and collections are irrelevant to what I'm saying
DECLARE

  l_sql varchar2(32767);
.....
BEGIN

......
l_sql := 'INSERT INTO TEMP2 (' || SUBSTR(CC_ROUTE, 2) || ' ' || 
				       SUBSTR(CC_TRNDATE, 2) || ' ' || 
				       SUBSTR(CC_TTIME, 2) || '' || 
				       SUBSTR(CC_TID, 2) || ') 
                         VALUES (' ||  SUBSTR(VV_ROUTE,2) || '  ' || 
				       SUBSTR(VV_TRNDATE,2) || '' || 
				       SUBSTR(VV_TTIME,2) || ' ' || 
				       SUBSTR(VV_TID,2)|| ')';

message(l_sql);

end;
Re: foms 6i - getting ora-00928 missing select keyword ora-06512 at"package.procedure_name" at line [message #583725 is a reply to message #583718] Fri, 03 May 2013 06:54 Go to previous message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Thanks for quick reply cookiemonster

I am working on it.

Thanks a lot.
Regards
Sameer
Previous Topic: problem in print record at block level
Next Topic: Get Total number of records
Goto Forum:
  


Current Time: Fri Sep 19 12:55:05 CDT 2014

Total time taken to generate the page: 0.11034 seconds