Solved

Passing parameter to sentence with the results of cursor - Oracle - Pl-sql

Posted on 2014-12-17
31
258 Views
Last Modified: 2014-12-27
Hi experts,
I have:
select distinct tablename from hisrfrequency;
output:
a_5min_001
a_5min_002
a_5min_003

Open in new window

And I need use this table list, in other sentence:
select distinct pointnumber from <hisrfrequency.tablename>;

Open in new window

output, for tablename=a_5min_001
10001
10002
10003
...

Open in new window

Then for each pointnumbers, execute a delete, for example
The idea is to use a pl-sql block. With errors, something like this;
DECLARE

cursor c1 
is
select * from hisrfrequency;
r1 c1%ROWTYPE;


cursor c2 (r1 c1%ROWTYPE) 
is
select distinct pointnumber from r1.tablename;
r2 c2%ROWTYPE;

str_del VARCHAR2(300);

begin

open 

open c1;
      loop
              fetch c1 into r1;

                exit when c1%notfound;
                    dbms_output.put_line(chr(10)||r1.tablename);
                begin
                
                    open c2(r1.tablename);
                    loop
                            fetch c2 into r2;
                            exit when c2%notfound;
                    dbms_output.put_line(chr(10)||r2.pointnumber);    
                    str_del := 'delete <r1.tablename> where pointnumber = <r2.pointnumber>';
                    execute immediate str_del;
                    end loop;
                    close c2;
                end;
      end loop;
 close c1;
 end;
/

Open in new window

They could send me some example to follow?
Thankyou in advanced
Regards
0
Comment
Question by:carlino70
  • 13
  • 11
  • 5
  • +2
31 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 40505651
I think you need to better explain what you are trying to do.

From what I figured out, you have a list of tables (in HISRFREQUENCY).
For each of those tables you want to get a list of all the unique POINTNUMBERs in that table.
Then delete the list of POINTNUMBERs from the table specified.

Essentially this will delete all records in each table listed with the exception of any where POINTNUMBER IS NULL.  You can do that a lot easier that what you are doing.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40505693
I think you need to better explain what you are trying to do.
Agreed, sounds quite weird... Please clarify!
0
 

Author Comment

by:carlino70
ID: 40505707
I'm sorry johnsone, I´ll to send some more detail:
From what I figured out, you have a list of tables (in HISRFREQUENCY).
For each of those tables you want to get a list of all the unique POINTNUMBERs in that table.

is ok.
The delete sentence complete is:
delete <hisrfrequency.tablename> where utctime < sysdate and pointnumber = (select distinct pointnumber from <hisrfrequency.tablename>;

Open in new window

For every pointnumber of each of the tables, do the delete.
I attached scripts to create the objects.
thankyou again
HIHSRFREQUENCY.SQL
A-5MIN-001.SQL
A-5MIN-002.SQL
A-5MIN-003.SQL
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 334 total points
ID: 40505831
I'm not sure I full understand either but all that matters is it makes sense to you.

From what I think you want, here is a quick example that shows the basics.

Hopefully you can take it and apply it to what you want.

drop table tab1 purge;
create table tab1(col1 varchar2(10));

insert into tab1 values('tab2');
insert into tab1 values('tab3');
commit;

drop table tab2 purge;
create table tab2(col1 char(1));

insert into tab2 values('a');
insert into tab2 values('b');
commit;

drop table tab3 purge;
create table tab3(col1 char(1));

insert into tab3 values('c');
commit;

declare
	mycur sys_refcursor;
	myval tab2.col1%type;
begin
	for tab_name in (select * from tab1) loop
		
		open mycur for 'select col1 from ' || tab_name.col1;
		loop
			fetch mycur into myval;
			exit when mycur%notfound;

			dbms_output.put_line('delete goes here value: ' || myval);
		end loop;
	end loop;
end;
/

Open in new window

0
 

Author Comment

by:carlino70
ID: 40506873
slightwv, thanks for you attention.
I tried with your idea, and it works. But when I do replacement with my tables I see:
ORA-00942: table or view does not exist
ORA-06512: line 12
declare

    mycur sys_refcursor;
    myval a_5min_001.pointnumber%type;
    
begin
    for tab_name in (select distinct tablename from hisrfrequency) 
    
    loop
        
         open mycur for 'select distinct pointnumber from ' || tab_name.tablename;  -----Line 12
            loop
                fetch mycur into myval;
                exit when mycur%notfound;

                dbms_output.put_line('delete goes here value: ' || myval);
            
            end loop;
            
    end loop;
end;
/

Open in new window

0
 

Author Comment

by:carlino70
ID: 40506877
Hi, I'll try to be clearer. Apologies for the bad explanation:
I have a tabla HISRFREQUENCY, and when doing this:
select distinct tablename from HISRFREQUENCY;

Open in new window

Output:
A_5MIN_001
A_5MIN_002
A_5MIN_003

Open in new window

...
and doing:
select distinct pointnumber from a_5min_001;

Open in new window

Output:
1901
1902
1903 
...

Open in new window

select distinct pointnumber from a_5min_002;

Open in new window

Output:
2091
2092
2093
...

Open in new window

The problem is how to match the list of pointnumbers, with the correct tablename, in dynamic way.
Then with tablename = a_5min_001,
                 and list of pointnumber for her, I must do:
delete from a_5min_001 where utctime < sysdate and pointnumber = 1901;
delete from a_5min_001 where utctime < sysdate and pointnumber = 1902;
delete from a_5min_001 where utctime < sysdate and pointnumber = 1903;

Open in new window

Then with tablename = a_5min_002,
                 and list of pointnumber for her, I must do:
delete from a_5min_002 where utctime < sysdate and pointnumber = 2901;
delete from a_5min_002 where utctime < sysdate and pointnumber = 2902;
delete from a_5min_002 where utctime < sysdate and pointnumber = 2903;

Open in new window

And so with all tables found.
I hope I have been clearer
Thanks for all
Regards
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40506890
Just for grins, add some debugging to see where the problem is.

Output the select that is being used for the cursor.  Hopefully it will tell you where the problem is.

declare

    mycur sys_refcursor;
    myval a_5min_001.pointnumber%type;
    
begin
    for tab_name in (select distinct tablename from hisrfrequency) 
    
    loop
         dbms_output.put_line('select distinct pointnumber from ' || tab_name.tablename);
        
         open mycur for 'select distinct pointnumber from ' || tab_name.tablename;  -----Line 12
            loop
                fetch mycur into myval;
                exit when mycur%notfound;

                dbms_output.put_line('delete goes here value: ' || myval);
            
            end loop;
            
    end loop;
end;
/

Open in new window

0
 

Accepted Solution

by:
carlino70 earned 0 total points
ID: 40507165
slightwv, whit the debug line I see the same error:
ORA-00942: table or view does not exist
ORA-06512: line 12

Open in new window

and the debug says:
select distinct pointnumber from a_5min_001

Open in new window

I can tell you that table a_5min_001, is the first occurrence when I do:
select tablename from hisrfrequency;

Open in new window


I guess that the first table is found but not pass the first step.
Thankyou
Regards
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40507201
>>whit the debug line I see the same error:

The line number should have changed since you added lines.

>>and the debug says:

That select looks valid to me since it is the same one you posted in the original question.  It might be a permissions or synonym issue but I don't see how with a pl/sql block.

If that code was part of a procedure then a permission issue is likely.
0
 

Author Comment

by:carlino70
ID: 40507302
I inserted the lines of debug, taking same numbers of line, therefore the error line is the same.
Here the code.
declare

    mycur sys_refcursor;
    myval a_5min_001.pointnumber%type;
    
begin
    for tab_name in (select distinct tablename from hisrfrequency) 
    
    loop
         dbms_output.put_line('select distinct pointnumber from ' || tab_name.tablename);
       
         open mycur for 'select distinct pointnumber from ' || tab_name.tablename;  -----Line 12
            loop
                fetch mycur into myval;
                exit when mycur%notfound;

                dbms_output.put_line('delete goes here value: ' || myval);
            
            end loop;
            
    end loop;
end;
/

Open in new window

look what happens commenting after the open my cur and second loop
declare

    mycur sys_refcursor;
    myval a_5min_001.pointnumber%type;
    
begin
    for tab_name in (select distinct tablename from hisrfrequency) 
    
    loop
         dbms_output.put_line('select distinct pointnumber from ' || tab_name.tablename);
       /*
         open mycur for 'select distinct pointnumber from ' || tab_name.tablename;  -----Line 12
            loop
                fetch mycur into myval;
                exit when mycur%notfound;

                dbms_output.put_line('delete goes here value: ' || myval);
            
            end loop;
            */
    end loop;
end;
/

Open in new window

Output:
select distinct pointnumber from a_5min_001
select distinct pointnumber from a_5min_002
select distinct pointnumber from a_5min_003

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40507323
What is your database version (all 4 numbers please)?

I created the complete test case using your table scripts you posted above and ran it against an 11.2.0.2 database.

I fixed one typo in HIHSRFREQUENCY.SQL

changed: Values   ('A_5MIN_033');
to: Values   ('A_5MIN_003');


Using the sql above here is my output:
select distinct pointnumber from A_5MIN_003
delete goes here value: 3902
delete goes here value: 3901
select distinct pointnumber from A_5MIN_002
delete goes here value: 2902
delete goes here value: 2901
select distinct pointnumber from A_5MIN_001
delete goes here value: 1901
delete goes here value: 1902

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40507328
Here is the run with echo on:
SQL> declare
  2
  3      mycur sys_refcursor;
  4      myval a_5min_001.pointnumber%type;
  5
  6  begin
  7      for tab_name in (select distinct tablename from hisrfrequency)
  8
  9      loop
 10           dbms_output.put_line('select distinct pointnumber from ' || tab_name.tablename);
 11
 12           open mycur for 'select distinct pointnumber from ' || tab_name.tablename;  -----Line 12
 13              loop
 14                  fetch mycur into myval;
 15                  exit when mycur%notfound;
 16
 17                  dbms_output.put_line('delete goes here value: ' || myval);
 18
 19              end loop;
 20
 21      end loop;
 22  end;
 23  /
select distinct pointnumber from A_5MIN_003
delete goes here value: 3902
delete goes here value: 3901
select distinct pointnumber from A_5MIN_002
delete goes here value: 2902
delete goes here value: 2901
select distinct pointnumber from A_5MIN_001
delete goes here value: 1901
delete goes here value: 1902

PL/SQL procedure successfully completed.

Open in new window

0
 
LVL 20

Expert Comment

by:flow01
ID: 40507353
Just to verify : I see  a difference  in output results from hisrfrequency (is the first row a_5min_001 or A_5MIN_001 ?)

check
select distinct tablename from HISRFREQUENCY;
select distinct tablename from hisrfrequency;
select * from all_objects where UPPER(object_name) = 'HISRFREQUENCY';
select * from all_objects where UPPER(object_name) LIKE  '%A%5MIN%001%';
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40507363
>>I see  a difference  in output results from hisrfrequency

Shouldn't matter unless the tables were created case sensitive.  I don't believe that is the problem since the query posted in http:#a40506877 returned results without double quotes around the table name.
0
 

Author Comment

by:carlino70
ID: 40507379
The difference may be because I do not copied the results, I wrote him
select distinct tablename from HISRFREQUENCY;
A_5MIN_001
A_5MIN_002
A_5MIN_003

Open in new window

select distinct tablename from hisrfrequency;
A_5MIN_001
A_5MIN_002
A_5MIN_003

Open in new window

select * from all_objects where UPPER(object_name) = 'HISRFREQUENCY'; 
OWNER|OBJECT_NAME|SUBOBJECT_NAME|OBJECT_ID|DATA_OBJECT_ID|OBJECT_TYPE|CREATED|LAST_DDL_TIME|TIMESTAMP|STATUS|TEMPORARY|GENERATED|SECONDARY|NAMESPACE|EDITION_NAME
XAJTDB|HISRFREQUENCY||155219|155219|TABLE|07/01/2013 03:48:04 p.m.|04/12/2014 07:57:11 p.m.|2013-01-07:15:48:04|VALID|N|N|N|1|

Open in new window

select * from all_objects where UPPER(object_name) LIKE  '%A%5MIN%001%'; 
OWNER|OBJECT_NAME|SUBOBJECT_NAME|OBJECT_ID|DATA_OBJECT_ID|OBJECT_TYPE|CREATED|LAST_DDL_TIME|TIMESTAMP|STATUS|TEMPORARY|GENERATED|SECONDARY|NAMESPACE|EDITION_NAME
XAJTDB|I_A_5MIN_001_UTCTIME||899912|899912|INDEX|18/12/2014 03:40:17 p.m.|18/12/2014 03:40:17 p.m.|2014-12-18:15:40:17|VALID|N|N|N|4|
XAJTDB|V_A_5MIN_001||653323||VIEW|06/06/2014 11:25:07 a.m.|06/06/2014 11:39:41 a.m.|2014-06-06:11:25:07|VALID|N|N|N|1|
XAJTDB|A_5MIN_001_A||653377|899143|TABLE|06/06/2014 12:20:05 p.m.|18/12/2014 12:10:04 a.m.|2014-06-06:12:20:05|VALID|N|N|N|1|
XAJTDB|A_5MIN_001||652188|899907|TABLE|05/06/2014 03:05:57 p.m.|18/12/2014 03:40:31 p.m.|2014-06-05:15:05:57|VALID|N|N|N|1|
XAJTDB|A_5MIN_001_VALOR_INST||652191|899904|INDEX|05/06/2014 03:07:30 p.m.|18/12/2014 03:36:31 p.m.|2014-06-05:15:07:30|VALID|N|N|N|4|
XAJTDB|A_5MIN_001_PK||652189|899906|INDEX|05/06/2014 03:05:57 p.m.|18/12/2014 03:36:31 p.m.|2014-06-05:15:05:57|VALID|N|N|N|4|
XAJTDB|I_A_5MIN_001||652190|899905|INDEX|05/06/2014 03:07:14 p.m.|18/12/2014 03:36:31 p.m.|2014-06-05:15:07:14|VALID|N|N|N|4|

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40507392
>>The difference may be because I do not copied the results, I wrote him

I don't think it is a case sensitive issue.

Are you running the pl/sql block while logged into the database as XAJTDB?
0
 

Author Comment

by:carlino70
ID: 40507589
yes, XAJTDB is the owner of all objects on the test.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40507598
If you are logged in as XAJTDB when you ran the pl/sql block I posted then there is something different between what you posted and what is in your setup.

I used your SQL scripts to create the tables and data and ran the pl/sql block without any error.

I also asked for this and you never posted it:
What is your database version (all 4 numbers please)?
0
 

Author Comment

by:carlino70
ID: 40507833
Oracle 11.2.0.3.0 Standard Edition.

SQL> conn xajtdb/xxxxxxx@xxxx
Connected.
SQL>
SQL> show user
USER is "XAJTDB"
SQL>
SQL> declare
  2
  3      mycur sys_refcursor;
  4      myval a_5min_001.pointnumber%type;
  5
  6  begin
  7      for tab_name in (select distinct tablename from hisrfrequency)
  8
  9      loop
 10
 11
 12          open mycur for 'select distinct pointnumber from ' || tab_name.tablename;
 13              loop
 14                  fetch mycur into myval;
 15                  exit when mycur%notfound;
 16
 17                  dbms_output.put_line('delete goes here value: ' || myval);
 18
 19              end loop;
 20
 21      end loop;
 22  end;
 23  /
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 12

SQL>
SQL> select distinct tablename from hisrfrequency;

A_5MIN_001
A_5MIN_002
A_5MIN_003

3 rows selected.

SQL>
SQL>
SQL> select distinct pointnumber from a_5min_001;
       1935
       1918
       1923
       1914
       1920
       1929
       1917
       1922
       1930
       1932
       1901
       1911
       1924
       1921
       1925
       1934
       1928
       1936
       1926
       2000
       1910
       1933
       1912
       1902
       1915
       1931
       1913
       1927
       1919
       1916

30 rows selected.
SQL>

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40507839
Post the results of:
select count(*) from A_5MIN_001;
select count(*) from A_5MIN_002;
select count(*) from A_5MIN_003;
0
 

Author Comment

by:carlino70
ID: 40507856
all tables have the same rows number:

535200
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40507862
I didn't really want to know how many rows they had.

I wanted to make sure all the queries ran and didn't produce am ORA-00942.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 334 total points
ID: 40507896
Post the results from this one:
declare
      myval number;
      curr_tab varchar2(30);
begin
      for tab_name in (select distinct tablename from hisrfrequency) loop
      	curr_tab := tab_name.tablename;

          execute immediate 'select count(*) from ' || tab_name.tablename into myval;
          dbms_output.put_line('Records from ' || tab_name.tablename || ': ' || myval);

      end loop;

      exception when others then
          dbms_output.put_line('Bad table:' || curr_tab || ':');
end;
/

Open in new window

0
 
LVL 20

Expert Comment

by:flow01
ID: 40508187
and  check if there are  non visible characters (for example chr(13))  in your tablenames in hisrfrequency , that will prevent to find the  table in the execute


select distinct tablename , length(tablename) from hisrfrequency
union
select 'A_5MIN_xxx'  , length('A_5MIN_xxx') from dual
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40508208
>>, that will prevent to find the  table in the execute

chr(13) won't cause a problem.  To be honest, I can't find a non-printable character that causes a problem.

If you have an example of one please post it.

Here is my example using chr(13):
drop table tab1 purge;
create table tab1(col1 char(1));
insert into tab1 values('a');
commit;

create table hisrfrequency(tablename varchar2(20));

insert into hisrfrequency values('tab1' || chr(13));
commit;

declare
      myval number;
      curr_tab varchar2(30);
begin
      for tab_name in (select distinct tablename from hisrfrequency) loop
      	curr_tab := tab_name.tablename;

          execute immediate 'select count(*) from ' || tab_name.tablename into myval;
          dbms_output.put_line('Records from ' || tab_name.tablename || ': ' || myval);

      end loop;

      exception when others then
          dbms_output.put_line('Bad table:' || curr_tab || ':');
end;
/
                                          

Open in new window

0
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 166 total points
ID: 40508346
depends where the chr(13)  is  

SQL> insert into hisrfrequency values('ta' || chr(13) || 'b1');

1 rij is aangemaakt.

SQL> commit;

Commit is voltooid.

SQL> declare
  2        myval number;
  3        curr_tab varchar2(30);
  4  begin
  5        for tab_name in (select distinct tablename from hisrfrequency) loop
  6         curr_tab := tab_name.tablename;
  7            dbms_output.put_line('select count(*) from ' || tab_name.tablename);
  8            execute immediate 'select count(*) from ' || tab_name.tablename into myval;
  9            dbms_output.put_line('Records from ' || tab_name.tablename || ': ' || myval);
 10        end loop;
 11        exception when others then
 12            dbms_output.put_line('Bad table:' || curr_tab || ':');
 13            dbms_output.put_line(sqlerrm);
 14  end;
 15  /
select count(*) from ta
b1
Bad table:ta
b1:
ORA-00942: table or view does not exist

PL/SQL-procedure is geslaagd.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

 insert into hisrfrequency values('tab' || chr(13) || '1');  -- results in another error : probably because the alias 1 for tab is not permitted
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40508381
But then "select * from hisrfrequency;" would not produce the results shown in any of the posts that show that output.

When I select from the table in your example I get:
SQL> select * from hisrfrequency;

TABLENAME
--------------------
ta b1

Open in new window


In the original question:
a_5min_001
a_5min_002
a_5min_003

Open in new window


Again in http:#a40507379
select distinct tablename from HISRFREQUENCY;
A_5MIN_001
A_5MIN_002
A_5MIN_003

Open in new window

0
 
LVL 20

Expert Comment

by:flow01
ID: 40508831
In the sqlplus version I use (or its settings) the difference is not visible on the screen. (zie attachment
However cut and paste gives:
TABLENAME
--------------------
ta
b1

However , this is only a discussion if such a cause is possible, but does not give a solution for Carlino.
Carlino : please run the tests we suggested and share the results.
chr13-notvisible.jpg
0
 

Author Comment

by:carlino70
ID: 40509077
People, thanks for your ideas.
I did:
create table hisrfrequency_bkp
    as select * from hisrfrequency;

Open in new window

then:
select distinct tablename from hisrfrequency_bkp;

Open in new window

output:
A_5MIN_003
A_5MIN_002
A_5MIN_001

Open in new window

then I executed the test block ( slightwv idea ):
declare
      myval number;
      curr_tab varchar2(30);
begin
      for tab_name in (select distinct tablename from hisrfrequency_bkp) loop
          curr_tab := tab_name.tablename;

          execute immediate 'select count(*) from ' || tab_name.tablename into myval;
          dbms_output.put_line('Records from ' || tab_name.tablename || ': ' || myval);

      end loop;

      exception when others then
          dbms_output.put_line('Bad table:' || curr_tab || ':');

end;
/

Open in new window

output:
Records from A_5MIN_003: 535200
Records from A_5MIN_002: 535200
Records from A_5MIN_001: 535200

Open in new window

I think it works now.
0
 
LVL 20

Expert Comment

by:flow01
ID: 40509279
Good !
But why ???
And if you use  hisrfrequency again ?
0
 

Author Closing Comment

by:carlino70
ID: 40519489
Thanks again.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now