Home » SQL & PL/SQL » SQL & PL/SQL » need an idea
need an idea [message #358698] Wed, 12 November 2008 03:49 Go to next message
marcello_1
Messages: 10
Registered: July 2007
Junior Member
Hello you all,


I have this problem.

cursor c_myCursor is
select * from table_a a, table_b b
where a.id = b.id
and   not in ( , , , )



For r_myCursor in c_myCursor LOOP

update

end loop;





And here is the actual problem:
I have a file which I read (that's not the problem). The problem is that I have strings in the file which should be in the not in clause.

so if the file has
string 1 = me
string 2 = they
string 3 = who

then the cursor should be :


cursor c_myCursor is
select * from table_a a, table_b b
where a.id = b.id
and   not in ( 'he','they' ,'who' , )



I want to exclude in the select statement everyone I read from the file.

So, the cursor where clause should be build dynamically according to the content of the file.

Anyone got an Idea about how to solve this?


Hope someone comes up with an idea.

Thanks in advance
Re: need an idea [message #358702 is a reply to message #358698] Wed, 12 November 2008 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is "not in"?

Regards
Michel
Re: need an idea [message #358712 is a reply to message #358702] Wed, 12 November 2008 04:38 Go to previous messageGo to next message
marcello_1
Messages: 10
Registered: July 2007
Junior Member
Hi Michel,

I tried to write it in a kind of pseudo code to make it understandable, but I guess that just made things worse Smile.

what i tried to say is that de cursor will be dependent on what is provided.



cursor c_myCursor is
select * from table_a a, table_b b
where a.id = b.id
and   a.name <> 'he'
and   a.name <> 'they'
and   a.name <> 'who'




What I have at this moment is:


TYPE usernames_array is varray(20) of varchar2(400);
     names usernames_array;
-- I fill names with the content of the file and pass it to the cursor.

 cursor c_names(p_names in usernames_array%type) is
 
 SELECT a.name
    FROM table_a a, table_b b
  WHERE a.id = b.id
    and   a.name <>  
    and   a.name <> 
    and   a.name <>
-- here comes the problem. 
This should come from the array, which is read from the file. So, in a way dynamically, 
because I don't know how many names the file contains.
 r_myCursor c_myCursor%rowtype;





Hope that this makes it understandable.

With regards

Marcello

[Updated on: Wed, 12 November 2008 05:38] by Moderator

Report message to a moderator

Re: need an idea [message #358718 is a reply to message #358698] Wed, 12 November 2008 05:13 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Is it fixed that the string contain only three names ?

Anyway good option will be to use table functions


Refer Variable in List for the almost the same case as yours .

Smile
Rajuvan.
Re: need an idea [message #358721 is a reply to message #358718] Wed, 12 November 2008 05:21 Go to previous messageGo to next message
marcello_1
Messages: 10
Registered: July 2007
Junior Member
Hi Rajuvan,

No, it is not fixed. The file contains always only names, but it can be expanded in the future with more names.

With regards,

ps: i'm gonna read the documentation you mentioned. I might find something usefull.

Thanks

Re: need an idea [message #358726 is a reply to message #358712] Wed, 12 November 2008 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
and a.name not in (select name from external_table_that_points_to_the_file)

By the way, "name" is a reserved word, don't use it.

Regards
Michel
Re: need an idea [message #358744 is a reply to message #358726] Wed, 12 November 2008 06:09 Go to previous messageGo to next message
marcello_1
Messages: 10
Registered: July 2007
Junior Member
Hi Michel,

I'm aware of reserved words, as I said before, I just tried to make the piece of code readable. So that's why it looks like rubbish, but the real code is implemented according to pl/sql coding standards. Anyway, thanks for your advice.

with regards,

Marcello
Re: need an idea [message #358747 is a reply to message #358726] Wed, 12 November 2008 06:23 Go to previous messageGo to next message
marcello_1
Messages: 10
Registered: July 2007
Junior Member
Hi Michel (again Smile )

you said:
and a.name not in (select name from external_table_that_points_to_the_file)

My question is:

Is it possible to point with an external table to a file?
Would you be so kind to mention how you would get the data in the file in an external table?

Or should I read the file into an array of user-defined type and put the content of the file in an external table?

Any ideas?


With regards,

marc
Re: need an idea [message #358749 is a reply to message #358698] Wed, 12 November 2008 06:36 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Find One Good External table example here

Smile
Rajuvan
Previous Topic: Case and Decode
Next Topic: package specification compilation -> hangs; question about circular references and usage of subty
Goto Forum:
  


Current Time: Fri Dec 09 04:09:17 CST 2016

Total time taken to generate the page: 0.12168 seconds