OraFAQ Forum: SQL & PL/SQL » Error(17,5): PL/SQL: ORA-00905: missing keyword

Home » SQL & PL/SQL » SQL & PL/SQL » Error(17,5): PL/SQL: ORA-00905: missing keyword (oracle 10.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593308]

Thu, 15 August 2013 10:50

candace
Messages: 6
Registered: August 2013
Location: USA

Junior Member

Can any one of you please help me with this
i need to get values from lookup table dynamically,i am getting the missing keyword error please let me know what I am missing
Appreciate your help.

Code: [Select all] [Show/ hide]

create or replace procedure xyz(cur1 OUT SYS_REFCURSOR)
     AS
       vsql varchar2(2000);
     CURSOR  CURSOR1 is SELECT DQS_SRC_COL_NM,LDIC_SEQUENCE FROM look_up WHERE LDIC_SOR ='friend';
     BEGIN
     
     OPEN cur1 FOR 
select  exc_val.source_sys ,exc_val.excptn_row_seq_num,  
(CURSOR1(
case when CURSOR1.LDIC_SEQUENCE = 1 then 
vsql:= vsql+'case when exc_val.COL_NM='' '||CURSOR1.DQS_SRC_COL_NM||' then exc_val.COL_DATA_VAL else null end ',
 when CURSOR1.LDIC_SEQUENCE = 2 then 
vsql := vsql+'case when exc_val.COL_NM='''+CURSOR1.DQS_SRC_COL_NM+''' then exc_val.COL_DATA_VAL else null end ',
 when CURSOR1.LDIC_SEQUENCE = 3 then 
vsql := vsql+'case when exc_val.COL_NM='''+CURSOR1.DQS_SRC_COL_NM+''' then exc_val.COL_DATA_VAL else null end ',
 when CURSOR1.LDIC_SEQUENCE = 4 then 
vsql := vsql+'case when exc_val.COL_NM='''+CURSOR1.DQS_SRC_COL_NM+''' then exc_val.COL_DATA_VAL else null end ',
END)
)
exc.EXCPTN_STAT_CD ,
exc_val.COL_DATA_VAL,
br.TITLE,
exc_val.CREATED_ON ,
exc_val.MODIFIED_ON 
from T_one exc
INNER JOIN T_two exc_val 
ON exc.excptn_row_seq_num = exc_val.excptn_row_seq_num 
inner join T_three br on br.bus_rule_key = exc_val.bus_rule_key
where exc_val.ofndg_col_ind = 'N' 
  and exc_val.source_sys = 'friend'
order by exc_val.excptn_row_seq_num;
end;

*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Thu, 15 August 2013 10:56] by Moderator

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593312 is a reply to message #593308]

Thu, 15 August 2013 10:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593313 is a reply to message #593312]

Thu, 15 August 2013 11:05

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

You appear to be doing pl/sql variable assignments inside a select statement. That's never going to work.

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593314 is a reply to message #593313]

Thu, 15 August 2013 11:06

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Also + isn't a valid concatenation operator in sql or pl/sql

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593315 is a reply to message #593312]

Thu, 15 August 2013 11:08

candace
Messages: 6
Registered: August 2013
Location: USA

Junior Member

Code: [Select all] [Show/ hide]

 CREATE TABLE look_up 
   (	"LDIC_SOR" VARCHAR2(10 BYTE), 
	"LDIC_SYS_ID" VARCHAR2(50 BYTE), 
	"DQS_SRC_TAB_NM" VARCHAR2(50 BYTE), 
	"DQS_SRC_COL_NM" VARCHAR2(50 BYTE), 
	"LDIC_SEQUENCE" VARCHAR2(50 BYTE), 
	"LDIC_SRC_TAB_NM" VARCHAR2(50 BYTE), 
	"LDIC_SRC_COL_NM" VARCHAR2(50 BYTE), 
	"ADTNL_PROCESSING_FLAG" VARCHAR2(1 BYTE), 
	"DQS_SOR" VARCHAR2(50 BYTE), 
	"DQS_SYS_ID" VARCHAR2(50 BYTE), 
	"LDIC_LOOKUP_TAB_NM" VARCHAR2(50 BYTE), 
	"LDIC_LOOKUP_COL_NM" VARCHAR2(50 BYTE)
   ) 


SET DEFINE OFF;
Insert into look_up  (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_S YS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('AFS','AFS3','AFS_COMBINED_REC','OBGN_NUM','4','T_LDIC_STAGE_LOAN_INFO','OBGN_NUM','N','AFS','N/A',null,null);
Insert into look_up  (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_S YS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('AFS','AFS3','AFS_COMBINED_REC','CUST_FAC','1','T_LDIC_STAGE_LOAN_INFO','CUSTOMER_FACILITY','N','AFS','N/A',null,null);
Insert into look_up  (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_S YS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('AFS','AFS3','AFS_COMBINED_REC','FAC_NUM','2','T_LDIC_STAGE_LOAN_INFO','FACILITY_NUM','N','AFS','N/A',null,null);
Insert into look_up  (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_S YS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('AFS','AFS3','AFS_COMBINED_REC','CUST_NUM','3','T_LDIC_STAGE_LOAN_INFO','OBLIGOR_NUMBER','N','AFS','N/A',null,null);
Insert into look_up  (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_S YS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('friend','friend','LIQ_CUSTOMER','COD_CID_CUST_ID','4','T_LDIC_STAGE_LOAN_INFO','OBLIGOR_NUMBER','N','friend','N/A',null,null);
Insert into look_up  (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_S YS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values  ('friend','friend','LIQ_FACILITY','FOD_PID_FACILITY','2','T_LDIC_STAGE_LOAN_INFO','FACILITY_NUM','Y','friend','N/A','V_IHUB_LIQ_LN_ST G_FACILITY',null);
Insert into look_up  (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_S YS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values  ('friend','friend','LIQ_OUTSTANDING','OOD_RID_OUTSTANDNG','3','T_LDIC_STAGE_LOAN_INFO','OBGN_NUM','Y','friend','N/A','V_IHUB_LIQ_LN_S TG_FACILITY',null);
Insert into look_up  (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_S YS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('friend','friend','LIQ_CUSTOMER','COD_CID_CUST_ID','1','T_LDIC_STAGE_LOAN_INFO','CUSTOMER_FACILITY','N','friend','N/A',null,null);

[Updated on: Thu, 15 August 2013 11:10]

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593316 is a reply to message #593315]

Thu, 15 August 2013 11:11

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Please read and follow How to use [code] tags and make your code easier to read?

An explanation of the logic the sql needs to apply would also be idea.

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593317 is a reply to message #593308]

Thu, 15 August 2013 11:15

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Welcome to the forum.

Follow the following basic forum principles:

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593319 is a reply to message #593317]

Thu, 15 August 2013 11:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Code: [Select all] [Show/ hide]

SQL> Insert into look_up  (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_S YS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('AFS','AFS3','AFS_COMBINED_REC','OBGN_NUM','4','T_LDIC_STAGE_LOAN_INFO','OBGN_NUM','N','AFS','N/A',null,null);
Insert into look_up  (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_S YS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('AFS','AFS3','AFS_COMBINED_REC','OBGN_NUM','4','T_LDIC_STAGE_LOAN_INFO','OBGN_NUM','N','AFS','N/A',null,null)
                                                                                                                                                           *
ERROR at line 1:
ORA-00917: missing comma

it appears that line break messed up the INSERT statements

[Updated on: Thu, 15 August 2013 11:22]

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593321 is a reply to message #593319]

Thu, 15 August 2013 11:29

candace
Messages: 6
Registered: August 2013
Location: USA

Junior Member

Code: [Select all] [Show/ hide]

SET DEFINE OFF;
Insert into look_up (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_SYS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('AFS','AFS3','AFS_COMBINED_REC','OBGN_NUM','4','T_LDIC_STAGE_LOAN_INFO','OBGN_NUM','N','AFS','N/A',null,null);
Insert into look_up (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_SYS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('AFS','AFS3','AFS_COMBINED_REC','CUST_FAC','1','T_LDIC_STAGE_LOAN_INFO','CUSTOMER_FACILITY','N','AFS','N/A',null,null);
Insert into look_up (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_SYS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('AFS','AFS3','AFS_COMBINED_REC','FAC_NUM','2','T_LDIC_STAGE_LOAN_INFO','FACILITY_NUM','N','AFS','N/A',null,null);
Insert into look_up (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_SYS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('AFS','AFS3','AFS_COMBINED_REC','CUST_NUM','3','T_LDIC_STAGE_LOAN_INFO','OBLIGOR_NUMBER','N','AFS','N/A',null,null);
Insert into look_up (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_SYS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('friend','friend','LIQ_CUSTOMER','COD_CID_CUST_ID','4','T_LDIC_STAGE_LOAN_INFO','OBLIGOR_NUMBER','N','friend','N/A',null,null);
Insert into look_up (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_SYS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('friend','friend','LIQ_FACILITY','FOD_PID_FACILITY','2','T_LDIC_STAGE_LOAN_INFO','FACILITY_NUM','Y','friend','N/A','V_IHUB_LIQ_LN_STG_FACILITY',null);
Insert into look_up (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_SYS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('friend','friend','LIQ_OUTSTANDING','OOD_RID_OUTSTANDNG','3','T_LDIC_STAGE_LOAN_INFO','OBGN_NUM','Y','friend','N/A','V_IHUB_LIQ_LN_STG_FACILITY',null);
Insert into look_up (LDIC_SOR,LDIC_SYS_ID,DQS_SRC_TAB_NM,DQS_SRC_COL_NM,LDIC_SEQUENCE,LDIC_SRC_TAB_NM,LDIC_SRC_COL_NM,ADTNL_PROCESSING_FLAG,DQS_SOR,DQS_SYS_ID,LDIC_LOOKUP_TAB_NM,LDIC_LOOKUP_COL_NM) values ('friend','friend','LIQ_CUSTOMER','COD_CID_CUST_ID','1','T_LDIC_STAGE_LOAN_INFO','CUSTOMER_FACILITY','N','friend','N/A',null,null);

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593322 is a reply to message #593321]

Thu, 15 August 2013 11:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

OK, we now have a table & rows of data in the table.
explain in words the requirements for the code.

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593323 is a reply to message #593322]

Thu, 15 August 2013 11:37

candace
Messages: 6
Registered: August 2013
Location: USA

Junior Member

for vsql ,i need to get values from lookup table dynamically,from CURSOR1
so I am storing look_up values in cursor and tried to call in the case statement

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593324 is a reply to message #593323]

Thu, 15 August 2013 11:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>for vsql ,i need to get values from lookup table dynamically,from CURSOR1

I see the words, but they make no sense to me.
Why "dynamically"? What is "dynamic"?

>so I am storing look_up values in cursor and tried to call in the case statement
cursor does NOT contain or "store" any value; EVER!

what is expected & desired results & why those results & not other possibilities.

How will you, I or anyone recognized when a correct solution has been posted?

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593325 is a reply to message #593324]

Thu, 15 August 2013 11:57

candace
Messages: 6
Registered: August 2013
Location: USA

Junior Member

if LDIC_SOR = 'AFS' then i need particular DQS_SRC_COL_NM column values
if LDIC_SOR = 'friend' then i need friend DQS_SRC_COL_NM column values

Code: [Select all] [Show/ hide]

SELECT LDIC_SOR,DQS_SRC_COL_NM,LDIC_SEQUENCE FROM look_up WHERE LDIC_SOR ='friend'

the DQS_SRC_COL_NM values returned in above sql statement should be substitued in place of vsql statements

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593328 is a reply to message #593325]

Thu, 15 August 2013 12:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I see words, but do not understand what you mean by them.

Report message to a moderator

Re: Error(17,5): PL/SQL: ORA-00905: missing keyword [message #593332 is a reply to message #593328]

Thu, 15 August 2013 12:23

candace
Messages: 6
Registered: August 2013
Location: USA

Junior Member

please see the procedure with ****
DQS_SRC_COL_NM column values for vsql

Code: [Select all] [Show/ hide]

if SELECT LDIC_SOR,DQS_SRC_COL_NM,LDIC_SEQUENCE FROM look_up WHERE LDIC_SOR ='friend'

then


create or replace procedure xyz(cur1 OUT SYS_REFCURSOR)
     AS
       vsql varchar2(2000);
     CURSOR  CURSOR1 is SELECT DQS_SRC_COL_NM,LDIC_SEQUENCE FROM look_up WHERE LDIC_SOR ='friend';
     BEGIN
     
     OPEN cur1 FOR 
select  exc_val.source_sys ,exc_val.excptn_row_seq_num,  
(CURSOR1(
case when CURSOR1.LDIC_SEQUENCE = 1 then 
*****vsql:= COD_CID_CUST_ID,
vsql:= FOD_PID_FACILITY,,
vsql:= OOD_RID_OUTSTANDNG,
vsql:= COD_CID_CUST_ID,*****
 when CURSOR1.LDIC_SEQUENCE = 2 then 
vsql := vsql+'case when exc_val.COL_NM='''+CURSOR1.DQS_SRC_COL_NM+''' then exc_val.COL_DATA_VAL else null end ',
 when CURSOR1.LDIC_SEQUENCE = 3 then 
vsql := vsql+'case when exc_val.COL_NM='''+CURSOR1.DQS_SRC_COL_NM+''' then exc_val.COL_DATA_VAL else null end ',
 when CURSOR1.LDIC_SEQUENCE = 4 then 
vsql := vsql+'case when exc_val.COL_NM='''+CURSOR1.DQS_SRC_COL_NM+''' then exc_val.COL_DATA_VAL else null end ',
END)
)
exc.EXCPTN_STAT_CD ,
exc_val.COL_DATA_VAL,
br.TITLE,
exc_val.CREATED_ON ,
exc_val.MODIFIED_ON 
from T_one exc
INNER JOIN T_two exc_val 
ON exc.excptn_row_seq_num = exc_val.excptn_row_seq_num 
inner join T_three br on br.bus_rule_key = exc_val.bus_rule_key
where exc_val.ofndg_col_ind = 'N' 
  and exc_val.source_sys = 'friend'
order by exc_val.excptn_row_seq_num;
end;

Report message to a moderator

Previous Topic:	Between Operations
Next Topic:	Syntax for cursors in PL/SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 20 21:42:46 CDT 2025