Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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

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
carlino70
Asked:
carlino70
  • 13
  • 11
  • 5
  • +2
4 Solutions
 
johnsoneSenior Oracle DBACommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
I think you need to better explain what you are trying to do.
Agreed, sounds quite weird... Please clarify!
0
 
carlino70Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
slightwv (䄆 Netminder) Commented:
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
 
carlino70Author Commented:
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
 
carlino70Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
carlino70Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
carlino70Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
flow01Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
carlino70Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
carlino70Author Commented:
yes, XAJTDB is the owner of all objects on the test.
0
 
slightwv (䄆 Netminder) Commented:
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
 
carlino70Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
Post the results of:
select count(*) from A_5MIN_001;
select count(*) from A_5MIN_002;
select count(*) from A_5MIN_003;
0
 
carlino70Author Commented:
all tables have the same rows number:

535200
0
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
flow01Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>, 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
 
flow01Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
flow01Commented:
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
 
carlino70Author Commented:
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
 
flow01Commented:
Good !
But why ???
And if you use  hisrfrequency again ?
0
 
carlino70Author Commented:
Thanks again.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 13
  • 11
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now