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 Go to next message
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 #315109 is a reply to message #315108] Sat, 19 April 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Am I doing anything wrong?

yes, you can't use a variable for table name in a static SQL.
You have to use "dynamic SQL" (search for this).

Regards
Michel
Re: Problem when cursor returning a table name [message #315112 is a reply to message #315109] Sat, 19 April 2008 02:58 Go to previous messageGo to next message
mihir.kamdar
Messages: 7
Registered: April 2008
Location: Bangalore
Junior Member
Hi,

Thanks for the reply. But can you just help me a bit more specifically. I am unable to find anything in 'Dynamic SQL' relating to my issue.

Thanks,
Mihir
Re: Problem when cursor returning a table name [message #315116 is a reply to message #315112] Sat, 19 April 2008 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database PL/SQL User's Guide and Reference
Chapter 7 Performing SQL Operations with Native Dynamic SQL

Regards
Michel
Re: Problem when cursor returning a table name [message #315126 is a reply to message #315116] Sat, 19 April 2008 04:16 Go to previous messageGo to next message
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 #315133 is a reply to message #315108] Sat, 19 April 2008 07:00 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
It would help everyone if you'd read & follow posting guidelines as stated above.

we don't have your data.
We don't know your table.
So we can't explain why you get no rows.
Re: Problem when cursor returning a table name [message #315139 is a reply to message #315133] Sat, 19 April 2008 07:51 Go to previous messageGo to next message
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 #315143 is a reply to message #315108] Sat, 19 April 2008 08:17 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>The execute immediate statement is going wrong
My car is going wrong.
Please tell me how to make my car go.

We don't have your data.
We don't know your table.

We don't see your error, so we can not be sure what is wrong.
Re: Problem when cursor returning a table name [message #315149 is a reply to message #315143] Sat, 19 April 2008 08:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25040
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 Go to previous messageGo to next message
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 #315157 is a reply to message #315108] Sat, 19 April 2008 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>Only it is failing while 'EXECUTE IMMEDIATE'
What happens to or where do the result set go for the EXECUTE IMMEDIATE?

What additional clause is required by a SELECT embedded within PL/SQL?
Re: Problem when cursor returning a table name [message #315158 is a reply to message #315157] Sat, 19 April 2008 09:10 Go to previous messageGo to next message
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 #315160 is a reply to message #315158] Sat, 19 April 2008 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to:
1/ Add a dbms_output.put_line to display "rs_str"
2/ Execute the block within SQL*Plus and copy and paste this execution


But BEFORE, you have to carefully read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Problem when cursor returning a table name [message #315162 is a reply to message #315158] Sat, 19 April 2008 09:29 Go to previous messageGo to next message
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
Re: Problem when cursor returning a table name [message #315163 is a reply to message #315162] Sat, 19 April 2008 09:32 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good eyes, Raj!

In my answer:
Quote:
you can't use a variable for table name in a static SQL.

I should not add "in a static SQL" as it is also true in a dynamic SQL.
Without speaking about the join clauses on a non-existent table.

Regards
Michel

[Updated on: Sat, 19 April 2008 09:33]

Report message to a moderator

Previous Topic: How to replace the view with Previledges
Next Topic: Cannot see datatype size after using cast function
Goto Forum:
  


Current Time: Tue Dec 06 00:09:09 CST 2016

Total time taken to generate the page: 0.09114 seconds