Home » SQL & PL/SQL » SQL & PL/SQL » Problem when cursor returning a table name
Problem when cursor returning a table name [message #315108] |
Sat, 19 April 2008 02:33  |
mihir.kamdar
Messages: 7 Registered: April 2008 Location: Bangalore
|
Junior Member |
|
|
Hi,
I have a table called record_configs which has a column 'tname'. It returns table names. My cursor is like this:
CURSOR DATA_STREAM is
select distinct tname, ID from record_configs where id in (select record_config_id from record_configs_rules) ;
When I do something like below, it throws error:-
FOR datastream IN DATA_STREAM
LOOP
Select xyz from datastream.tname where ph_no = 'xxx' ;
END LOOP ;
It says that 'table or view does not exist'. It is not able to evaluate the cursor.
Am I doing anything wrong? Will be grateful if I get a quick response.
Thanks,
Mihir
|
|
|
|
|
|
Re: Problem when cursor returning a table name [message #315126 is a reply to message #315116] |
Sat, 19 April 2008 04:16   |
mihir.kamdar
Messages: 7 Registered: April 2008 Location: Bangalore
|
Junior Member |
|
|
Hi,
Thanks...I tried with Dynamic SQL, but no rows are getting retrieved. Please check the code below:
FOR subscriber IN Black_Listed_Subscribers (Previous_BlackList_Close_Date)
LOOP
AvgSubscriberUsage := 0 ;
FOR datastream IN DATA_STREAM
LOOP
BEGIN
dbms_output.put_line('tname '|| datastream.tname) ;
rs_str :='SELECT trunc(SUM(Value)/(COUNT(DISTINCT Day_of_Year)),4) FROM (SELECT day_of_year AS Day_of_Year,SUM(value) AS Value FROM '||datastream.tname||' WHERE phone_number = subscriber.Phone_Number AND subscriber_id = subscriber.subscriber_id GROUP BY day_of_year)' ;
EXECUTE IMMEDIATE rs_str INTO Total_value ;
dbms_output.put_line('Total_value' ||Total_value) ;
AvgSubscriberUsage := AvgSubscriberUsage + Total_Value ;
INSERT INTO FRAUDULENT_INFO (ACCOUNT_NAME, PHONE_NUMBER, NETWORK_ID , FRAUD_TYPE, ALARM_MODIFIED_DATE, AVERAGE_USAGE_PER_DAY, FIRST_CDR_TIME, ALARM_CREATED_DATE, ALARM_VALUE) VALUES (subscriber.Account_name, subscriber.Phone_number, subscriber.network_id , subscriber.Fraud_Type, subscriber.modified_date, AvgSubscriberUsage, sysdate, subscriber.Created_Date, 0) ;
________________________________
Please tell me what's wrong?
Thanks,
Mihir
|
|
|
|
Re: Problem when cursor returning a table name [message #315139 is a reply to message #315133] |
Sat, 19 April 2008 07:51   |
mihir.kamdar
Messages: 7 Registered: April 2008 Location: Bangalore
|
Junior Member |
|
|
Hi,
please find the script attached. It has to insert one row into table named fraudulent_info, but it is failing to insert. The execute immediate statement is going wrong. Some formatting issue i guess. Can someone please point out and tell me.
Any more info required, plz. let me know.
Thanks,
Mihir
|
|
|
|
Re: Problem when cursor returning a table name [message #315149 is a reply to message #315143] |
Sat, 19 April 2008 08:36   |
mihir.kamdar
Messages: 7 Registered: April 2008 Location: Bangalore
|
Junior Member |
|
|
Hi,
Following is the table where I want to insert:-
SQL> desc fraudulent_info ;
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCOUNT_NAME NOT NULL VARCHAR2(40)
PHONE_NUMBER NOT NULL VARCHAR2(40)
NETWORK_ID NOT NULL NUMBER(20)
FRAUD_TYPE NOT NULL VARCHAR2(80)
AVERAGE_USAGE_PER_DAY NOT NULL NUMBER(16,6)
ALARM_CREATED_DATE NOT NULL DATE
ALARM_MODIFIED_DATE NOT NULL DATE
ALARM_VALUE NOT NULL NUMBER(16,6)
FIRST_CDR_TIME NOT NULL DATE
This is the data when I execute the following cursor:-
SELECT S.ACCOUNT_NAME, S.PHONE_NUMBER, S.NETWORK_ID, A.VALUE, A.CREATED_DATE, A.MODIFIED_DATE, A.REFERENCE_ID AS SUBSCRIBER_ID, A.ID, A.DISPLAY_VALUE AS FRAUD_TYPE
FROM SUBSCRIBER S, ALARMS A
WHERE S.SUBSCRIBER_TYPE = 1
AND S.ID = A.REFERENCE_ID
AND A.REFERENCE_TYPE=1
AND A.MODIFIED_DATE >= to_date(to_char(Last_processed_BlackList,'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss')
ORDER BY MODIFIED_DATE ASC;
ACCOUNT_NAME
----------------------------------------
PHONE_NUMBER NETWORK_ID VALUE CREATED_D
---------------------------------------- ---------- ---------- ---------
MODIFIED_ SUBSCRIBER_ID ID
--------- ------------- ----------
FRAUD_TYPE
--------------------------------------------------------------------------------
2222222222
+919820535222 1025 10 18-APR-08
19-APR-08 4200 1025
Subscription Fraud
Attaching my code again. Please see if you can help.
Thanks,
Mihir
|
|
|
Re: Problem when cursor returning a table name [message #315152 is a reply to message #315149] |
Sat, 19 April 2008 08:48   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
mihir.kamdar wrote on Sat, 19 April 2008 06:36 | Hi,
SELECT S.ACCOUNT_NAME, S.PHONE_NUMBER, S.NETWORK_ID, A.VALUE, A.CREATED_DATE, A.MODIFIED_DATE, A.REFERENCE_ID AS SUBSCRIBER_ID, A.ID, A.DISPLAY_VALUE AS FRAUD_TYPE
FROM SUBSCRIBER S, ALARMS A
WHERE S.SUBSCRIBER_TYPE = 1
AND S.ID = A.REFERENCE_ID
AND A.REFERENCE_TYPE=1
AND A.MODIFIED_DATE >= to_date(to_char(Last_processed_BlackList,'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss')
ORDER BY MODIFIED_DATE ASC;
Mihir
|
The SELECT above looks nothing close to
>rs_str :='SELECT trunc(SUM(Value)/(COUNT(DISTINCT Day_of_Year)),4) FROM (SELECT day_of_year AS Day_of_Year,SUM(value) AS Value FROM '||datastream.tname||' WHERE phone_number = subscriber.Phone_Number AND subscriber_id = subscriber.subscriber_id GROUP BY day_of_year)' ;
So I am confused as how they are part of the same problem.
The best way to debug EXECUTE IMMEDIATE is to print out the SQL
statement before executing it, CUT & PASTE in SQL*Plus & see what happens.
You need to create a valid SQL statement before EXECUTE IMMEDIATE will succeed.
|
|
|
Re: Problem when cursor returning a table name [message #315155 is a reply to message #315152] |
Sat, 19 April 2008 08:55   |
mihir.kamdar
Messages: 7 Registered: April 2008 Location: Bangalore
|
Junior Member |
|
|
Hi,
Thanks for the prompt reply. I cut and pasted it earlier only. Please see below:
SQL> SELECT trunc(SUM(Value)/(COUNT(DISTINCT Day_of_Year)),4) FROM (SELECT day_of_year AS Day_of_Year,SUM(value) AS Value FROM CDR WHERE phone_number = '+919820535222' AND subscriber_id = 4200 GROUP BY day_of_year) ;
TRUNC(SUM(VALUE)/(COUNT(DISTINCTDAY_OF_YEAR)),4)
------------------------------------------------
10
It gives the result correctly. Only it is failing while 'EXECUTE IMMEDIATE'
Thanks,
Mihir
|
|
|
|
Re: Problem when cursor returning a table name [message #315158 is a reply to message #315157] |
Sat, 19 April 2008 09:10   |
mihir.kamdar
Messages: 7 Registered: April 2008 Location: Bangalore
|
Junior Member |
|
|
Hi,
There is no additional where clause required. please look at the script that I attached. I have a cursor called DATA_STREAM to get all the table names.
I have an outer cursor called Black_Listed_Subscribers. I loop through that cursor, and for each of the rows of that cursor, I loop through the inner cursor DATA_STREAM, get the table name and pass it to 'EXECUTE IMMEDIATE'.
I have a feeling that formatting of the EXECUTE IMMEDIATE is going wrong. Can you please check that?
Thanks,
Mihir
|
|
|
|
Re: Problem when cursor returning a table name [message #315162 is a reply to message #315158] |
Sat, 19 April 2008 09:29   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Quote: |
EXECUTE IMMEDIATE 'SELECT trunc(SUM(Value)/(COUNT(DISTINCT Day_of_Year)),4) FROM
(SELECT day_of_year AS Day_of_Year,SUM(value) AS Value FROM :1
WHERE phone_number = subscriber.Phone_Number AND subscriber_id =
subscriber.subscriber_id GROUP BY day_of_year)' into Total_value using datastream.tname ;
|
Quote: |
SELECT trunc(SUM(Value)/(COUNT(DISTINCT Day_of_Year)),4) FROM
(SELECT day_of_year AS Day_of_Year,SUM(value) AS Value FROM CDR
WHERE phone_number = '+919820535222' AND subscriber_id = 4200
GROUP BY day_of_year) ;
|
Above two statements are not the same.
Regards
Raj
|
|
|
|
Goto Forum:
Current Time: Tue Feb 18 13:45:09 CST 2025
|