Home » SQL & PL/SQL » SQL & PL/SQL » using a cursor to fetch data
using a cursor to fetch data [message #596592] Wed, 25 September 2013 02:20 Go to next message
hsen
Messages: 7
Registered: September 2013
Junior Member
when i run this code i get a invalid number error please help

create or replace
PROCEDURE BULK_REJECT(System_name VARCHAR2)
as
MDM_run_id VARCHAR2(14);
V_sql clob;
cursor Job_metric is
select rowid_job,system_name, table_display_name, run_status
from c_repos_job_control where trunc(last_update_date) = trunc(sysdate-2)
and object_function_type_code in ('C','L') and run_status in (0,1) order by system_name desc;

metric job_metric%ROWTYPE;

BEGIN

select max(run_id) into MDM_run_id from memc_etl_run;
open job_metric;
loop fetch Job_metric into metric;
exit when job_metric%NOTFOUND;


V_sql:= 'insert into TARGET_CUSTOM_REJECT_BO
select '||MDM_run_id||',
b.pkey_src_object,''mdm_err'',a.error_description,'||metric.system_name||',''ui''
from c_stg_sfa_party_rej a ,C_stg_sfa_party b
where
trunc(a.src_rowid)=trunc(b.src_rowid)
and a.rowid_job='''||metric.rowid_job||'''';
EXECUTE IMMEDIATE V_sql;
end loop;
commit;
close job_metric;
end BULK_reject;
Re: using a cursor to fetch data [message #596597 is a reply to message #596592] Wed, 25 September 2013 02:27 Go to previous messageGo to next message
Maaher
Messages: 7055
Registered: December 2001
Senior Member
I can't solve this for you because I don't have a clue what your tables look like. Please post table descriptions.

But I do have some questions:
1. why dynamic SQL?
2. why the input parameter? You don't do anything with it.
3. why dynamic SQL?


MHE
Re: using a cursor to fetch data [message #596599 is a reply to message #596597] Wed, 25 September 2013 02:44 Go to previous messageGo to next message
hsen
Messages: 7
Registered: September 2013
Junior Member
THE INPUT PARAMETER SHOULD NOT BE THERE ..

WE NEED TO MAKE A PROCEDURE THT RUN TO LOAD THE DATA FROM DIFFERENT TABLE IN TO A CUSTOM TABLE SAY

c-REPOS_JOB_CONTROL HAS ALL THE ROWID_JOB OF THE RESPECTIVE TABLE SO BASED ON THT ROWID_JOB WE NEED TO SEARCH THE REJECT TABLE

THE ONLY PROBLEM IS ( FRO EXAMPLE THE TABLE C_PARTY AND c_STG_PARTY WILL HAVE ENTRIES IN JOB CONTROL TABLE BUT THERE REJECTS WILL BE ON C_STG_PRT_REJ) TABLE . HOW COULD WE DO THAT IN THE RUNTIME
Re: using a cursor to fetch data [message #596601 is a reply to message #596599] Wed, 25 September 2013 02:48 Go to previous messageGo to next message
hsen
Messages: 7
Registered: September 2013
Junior Member
DESC C_REPOS_JOB_CONTROL
Name Null Type
------------------------------ -------- ------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------
ROWID_JOB NOT NULL CHAR(14 CHAR)
ROWID_TABLE NOT NULL CHAR(14 CHAR)
TABLE_DISPLAY_NAME NOT NULL VARCHAR2(100 CHAR)
SYSTEM_NAME VARCHAR2(50 CHAR)
START_RUN_DATE DATE
END_RUN_DATE DATE
RUN_STATUS NUMBER
RETURN_CODE NUMBER
STATUS_MESSAGE VARCHAR2(1024 CHAR)
ROWID_TABLE_OBJECT VARCHAR2(30 CHAR)
OBJECT_TYPE_CODE CHAR(1 CHAR)
OBJECT_DESC VARCHAR2(255 CHAR)
OBJECT_FUNCTION_TYPE_CODE VARCHAR2(2 CHAR)
CREATE_DATE NOT NULL DATE
CREATOR VARCHAR2(50 CHAR)
LAST_UPDATE_DATE DATE
UPDATED_BY VARCHAR2(50 CHAR)
ROWID_JOB_GROUP_CONTROL CHAR(14 CHAR)
ROWID_JOB_GROUP_ITEM CHAR(14 CHAR)
..... REJ TABLE DESC


DESC C_STG_SFA_PARTY_REJ
Name Null Type
------------------------------ -------- ------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------
ROWID_JOB NOT NULL CHAR(14 CHAR)
ERROR_DESCRIPTION VARCHAR2(1000 CHAR)
SRC_ROWID VARCHAR2(30 CHAR)
LAST_UPDATE_DATE DATE
MIDDLE_NAME VARCHAR2(50 CHAR)
FIRST_NAME VARCHAR2(50 CHAR)
PRTY_ID NUMBER(38,1)
LAST_NAME VARCHAR2(50 CHAR)
NAME VARCHAR2(50 CHAR)
PARTY_TYP VARCHAR2(50 CHAR)



TARGET TABLE DESC


DESC TARGET_CUSTOM_REJECT_BO
Name Null Type
------------------------------ -------- ------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------
RUN_ID NOT NULL VARCHAR2(14)
SRC_PKEY NOT NULL VARCHAR2(200)
ERR_CD NOT NULL VARCHAR2(20)
ERR_ATTR NOT NULL VARCHAR2(200)
SRS_SYS_CD NOT NULL VARCHAR2(10)
MAPNG_NM VARCHAR2(100
Re: using a cursor to fetch data [message #596603 is a reply to message #596599] Wed, 25 September 2013 02:52 Go to previous messageGo to next message
Maaher
Messages: 7055
Registered: December 2001
Senior Member
If you want an answer you will have to post what your tables look like. I also suggest you drop the dynamic SQL. There's no need for that, you can do it in a plain SQL statement. And does the insert statement work in SQL*Plus?

MHE

PS: Please, don't post with your caps lock on. It gives the impression you're shouting.

Edit: sorry, you've updated the thread while I was posting.

[Updated on: Wed, 25 September 2013 02:53]

Report message to a moderator

Re: using a cursor to fetch data [message #596604 is a reply to message #596601] Wed, 25 September 2013 02:53 Go to previous messageGo to next message
pablolee
Messages: 2658
Registered: May 2007
Location: Scotland
Senior Member
1. Don't post in uppercase, it is considered to be SHOUTING, I'm sure that you didn't mean to shout at Maaher Wink
2. Instead of posting that you get an invalid number error, post the actual error output, it contains information that can be useful to people trying to solve this for you.
3. When posting code, please put code tags around your code, like this:
[code]
Your code goes
in here
[/code]

That way any formatting that you have applied to your code will be preserved (indents etc)

icon13.gif  Re: using a cursor to fetch data [message #596605 is a reply to message #596601] Wed, 25 September 2013 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you want we debug a code that we have not from the code you post but is not the actual one... hmm... quite hard...

Re: using a cursor to fetch data [message #596608 is a reply to message #596605] Wed, 25 September 2013 03:14 Go to previous messageGo to next message
hsen
Messages: 7
Registered: September 2013
Junior Member
no i am not shouting and yes i have updated the table structure
Re: using a cursor to fetch data [message #596610 is a reply to message #596608] Wed, 25 September 2013 03:16 Go to previous messageGo to next message
hsen
Messages: 7
Registered: September 2013
Junior Member
/code/

create or replace
PROCEDURE BULK_REJECT(System_name VARCHAR2)
as
MDM_run_id VARCHAR2(14);
V_sql clob;
cursor Job_metric is
select rowid_job,system_name, table_display_name, run_status
from c_repos_job_control where trunc(last_update_date) = trunc(sysdate-2)
and object_function_type_code in ('C','L') and run_status in (0,1) order by system_name desc;

metric job_metric%ROWTYPE;

BEGIN

select max(to_number(run_id)) into MDM_run_id from memc_etl_run;
open job_metric;
loop fetch Job_metric into metric;
exit when job_metric%NOTFOUND;


V_sql:= 'insert into TARGET_CUSTOM_REJECT_BO
select ''||MDM_run_id||'',
b.pkey_src_object,''mdm_err'',a.error_description,'||metric.system_name||'
from (SELECT TABLE_NAME
where
trunc(a.src_rowid)=trunc(b.src_rowid)
and a.rowid_job='''||metric.rowid_job||'''';
EXECUTE IMMEDIATE V_sql;
end loop;
commit;
close job_metric;
end BULK_reject;


the error message :

Connecting to the database cmx_ors.
ORA-00904: "SFA": invalid identifier
ORA-06512: at "CMX_ORS.BULK_REJECT", line 27
ORA-06512: at line 6
Process exited.
Disconnecting from the database cmx_ors.
icon4.gif  Re: using a cursor to fetch data [message #596613 is a reply to message #596610] Wed, 25 September 2013 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Are you sure you have posted the correct code?
There is no SFA in your procedure.

If you use SQL*Plus and post your session we can see where is the problem.
Note that to post code this not /code/ but [code]



Re: using a cursor to fetch data [message #596615 is a reply to message #596613] Wed, 25 September 2013 03:25 Go to previous messageGo to next message
hsen
Messages: 7
Registered: September 2013
Junior Member
//'' this is the correct code ''//

create or replace
PROCEDURE BULK_REJECT(System_name VARCHAR2)
as
MDM_run_id VARCHAR2(14);
V_sql clob;
cursor Job_metric is
select rowid_job,system_name, table_display_name, run_status
from c_repos_job_control where trunc(last_update_date) = trunc(sysdate-2)
and object_function_type_code in ('C','L') and run_status in (0,1) order by system_name desc;

metric job_metric%ROWTYPE;

BEGIN

select max(run_id) into MDM_run_id from memc_etl_run;
open job_metric;
loop fetch Job_metric into metric;
exit when job_metric%NOTFOUND;


V_sql:= 'insert into TARGET_CUSTOM_REJECT_BO
select '||MDM_run_id||',
b.pkey_src_object,''mdm_err'',a.error_description,'||metric.system_name||',''ui''
from c_stg_sfa_party_rej a ,C_stg_sfa_party b
where
trunc(a.src_rowid)=trunc(b.src_rowid)
and a.rowid_job='''||metric.rowid_job||'''';
EXECUTE IMMEDIATE V_sql;
end loop;
commit;
close job_metric;
end BULK_reject;
Re: using a cursor to fetch data [message #596617 is a reply to message #596615] Wed, 25 September 2013 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 25 September 2013 10:23


Are you sure you have posted the correct code?
There is no SFA in your procedure.

If you use SQL*Plus and post your session we can see where is the problem.
Note that to post code this not /code/ but [code]


Re: using a cursor to fetch data [message #596620 is a reply to message #596610] Wed, 25 September 2013 03:39 Go to previous messageGo to next message
pablolee
Messages: 2658
Registered: May 2007
Location: Scotland
Senior Member
Your first post states that you are receiving an Invalid Number Error. Your post that includes the code implies that you are getting an invalid identifier
You then post more code, but do not post what error goes with that.
Can you please clarify your position.
Show us the actual code
Show us the actual error
Use code tags as I and Michel have described to you.

Cheers
Re: using a cursor to fetch data [message #596622 is a reply to message #596615] Wed, 25 September 2013 03:45 Go to previous messageGo to next message
hsen
Messages: 7
Registered: September 2013
Junior Member
this is the final code and this is the final error

create or replace
 PROCEDURE BULK_REJECT(System_name VARCHAR2)
 as
 MDM_run_id VARCHAR2(14);
 V_sql clob;
 cursor Job_metric is 
select rowid_job,system_name, table_display_name, run_status
 from c_repos_job_control where trunc(last_update_date) = trunc(sysdate-2)
 and object_function_type_code in ('C','L') and run_status in (0,1) order by system_name desc;

 metric job_metric%ROWTYPE;

 BEGIN 

select max(run_id) into MDM_run_id from memc_etl_run;
 open job_metric;
 loop fetch Job_metric into metric;
 exit when job_metric%NOTFOUND;

 
V_sql:= 'insert into TARGET_CUSTOM_REJECT_BO
 select '||MDM_run_id||',
 b.pkey_src_object,''mdm_err'',a.error_description,'||metric.system_name||',''ui''
 from c_stg_sfa_party_rej a ,C_stg_sfa_party b 
where
 trunc(a.src_rowid)=trunc(b.src_rowid) 
and a.rowid_job='''||metric.rowid_job||'''';
 EXECUTE IMMEDIATE V_sql;
 end loop;
 commit;
 close job_metric;
 end BULK_reject;//


this is the actual error
//Connecting to the database cmx_ors.
ORA-00904: "SFA": invalid identifier
ORA-06512: at "CMX_ORS.BULK_REJECT", line 27
ORA-06512: at line 6
Process exited.
Disconnecting from the database cmx_ors.//

[Updated on: Wed, 25 September 2013 03:48] by Moderator

Report message to a moderator

Re: using a cursor to fetch data [message #596624 is a reply to message #596622] Wed, 25 September 2013 03:51 Go to previous messageGo to next message
pablolee
Messages: 2658
Registered: May 2007
Location: Scotland
Senior Member
I've added code tags. Please learn to do this yourself. Personally, then next time you post any code without bothering to use code tags, I'll not be getting involved in your question(s) (However, I'm sure that there are dozens of others that will, it's just that I am not interested in helping out when you won't put in the effort, sorry)
As to the error, you are apparently listing a column or table that doesn't exist. Instead of using execute immediate on a piece of code that clearly is syntactically incorrect, output the variable v_sql (using dbms_output) and post it here
Re: using a cursor to fetch data [message #596625 is a reply to message #596624] Wed, 25 September 2013 03:58 Go to previous messageGo to next message
Maaher
Messages: 7055
Registered: December 2001
Senior Member
And I still don't see why you would want dynamic SQL in that procedure. What's wrong with
...
    Insert Into Target_Custom_Reject_Bo( run_id, src_pkey, err_cd, err_attr, srs_sys_cd, mapng_nm)
    Select mdm_run_id
         , b.pkey_src_object
         , 'mdm_err'
         , a.error_description
         , metric.system_name
         , 'ui'
    from   c_stg_sfa_party_rej a 
       ,   c_stg_sfa_party     b 
    Where trunc(a.src_rowid) = trunc(b.src_rowid) 
    and   a.rowid_job        = metric.rowid_job;
...


MHE
icon4.gif  Re: using a cursor to fetch data [message #596628 is a reply to message #596625] Wed, 25 September 2013 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MDM_run_id is a variable not a column name, and it is a variable that is set to "max(run_id)" which was in the previous post converted to a number (maybe the reason of the first "invalid number" error) and now it is likely set to "SFA" which does not exist in the tables as the error "invalid identifier" seems to indicate... or maybe this is "metric.system_name" that is set to "SFA" and still not exist in the tables...


[Updated on: Wed, 25 September 2013 04:26]

Report message to a moderator

Re: using a cursor to fetch data [message #596631 is a reply to message #596628] Wed, 25 September 2013 04:44 Go to previous message
Maaher
Messages: 7055
Registered: December 2001
Senior Member
Laughing You're right, Michel. I must admit I missed that last point. That's what dynamic SQL does: it obfuscates things. Exactly the reason why I advise to convert it to a plain and simple SQL statement.

MHE
Previous Topic: pl/sql date format
Next Topic: Start DB session using sh script with out the password
Goto Forum:
  


Current Time: Thu Dec 18 09:29:52 CST 2014

Total time taken to generate the page: 0.15895 seconds