Home » SQL & PL/SQL » SQL & PL/SQL » unix in Pl/sql blocks
unix in Pl/sql blocks [message #268161] Mon, 17 September 2007 18:20 Go to next message
praveenbajaj
Messages: 2
Registered: September 2007
Junior Member
Hi ,

i need to find to status of some 25k records which are stored in DB.I have the input data in flat text files.
Is there any way of finding the required o/p without creating the table and using only select command or pl/sql blocks.
I have only read permission on the DB.


Is there any way where in we can embed unix command in pl/sql blocks.For example my logic :



declare


cursor asd is
select <output> from <table> where column in ( < input data from flat file or read the file by running unix command>);

begin

for rec in asd
loop
....
...

end loop;
end;

/


Please let me know the possible solutions.Thanks in advance
Re: unix in Pl/sql blocks [message #268163 is a reply to message #268161] Mon, 17 September 2007 18:24 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Please let me know the possible solutions
I don't understand the problem or how to recognize any "correct" answer,
>Is there any way where in we can embed unix command in pl/sql blocks
NO!

What problem are you really trying to solve & how would an independent observer be able to conclude which possible solutions gives a correct answer?

Are you using the right tool for this job (PL/SQL)?
Re: unix in Pl/sql blocks [message #268183 is a reply to message #268163] Mon, 17 September 2007 21:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Technically it is possible, but it's very bad design so I'm not going to go into the answer.

PL/SQL is a database programming language. If you need to interact a lot with the operating system and then call SQL statements based on what you find, then you're better off using a language designed for that purpose. Pro*C and Perl::DBI come to mind as two amongst many.

Ross Leishman
Re: unix in Pl/sql blocks [message #268479 is a reply to message #268161] Tue, 18 September 2007 16:54 Go to previous messageGo to next message
praveenbajaj
Messages: 2
Registered: September 2007
Junior Member
What am i planning to do is write a shell script which would in turn create a sql script(echo whole of the Pl/sql block in aaa.sql file) then run the sql script using sqlplus command .

But the problem i am facing is when reading the data from the input file and using in the where clause of select statement of the cursor as shown below :

where column in ( < input data from flat file or read the file by running unix command>);

This can also be taken care by splitting the echo statement near 'where' clause but this clause does not take more than 1000 input values.
Please help me in solving my problem.


Also please could anyone suggest me someother way of getting the output from the database
Re: unix in Pl/sql blocks [message #268481 is a reply to message #268479] Tue, 18 September 2007 16:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Can't you read the file as an external table and join that with the desired tables?
I know that this would require a (one-time only) privilege to create this external table, but after that select will suffice.
Re: unix in Pl/sql blocks [message #268483 is a reply to message #268161] Tue, 18 September 2007 16:59 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
It might work better for you if you treated the OS data files as EXTERNAL TABLEs; then you could simply do

where column in (select key from external_table)
Re: unix in Pl/sql blocks [message #275244 is a reply to message #268161] Thu, 18 October 2007 23:26 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
external table is good suggestion.

But if you determined (or stuck) with having only select privileges on the database you intend to query, and you wish to use pl/sql, then most obvious choice is using UTL_FILE database supplied utility package. The input file would need to be accessible from the database server machine.

Since you only have read access to the database, you cannot create stored procedures, objects, etc. so you are very limited. The only solution that pops into my head under these circumstances is: to create an anonymous pl/sql block that lets you read data from a table and compare it to your file. Basically you need to read a record from the file, and then check the database.

Since you are using an anonymous pl/sql block, you won't be able to return your results anywhere other than to DBMS_OUTPUT or alternatively, write results to a second file.

Maybe someone more creative than me can think of something else.

In the meantime, you need to get your administrators to up your privs. I suggest you find out what kind of chocolate your DBA's like. And I am not talking the cheap crap, don't go to the vending machine for a Hershey bar, you need real juice if you want results. There is a chocolateer that comes to my work place every fourth Wednesday, and his chocolate covered peanut kiss pretzels (hand dipped etc. etc.) have just the right mix of sweet and salty flavor. They give me quit an edge over the other consultants in the building. My fellow contractors are constantly puzzling over why my jobs always get run first, and my change requests are always at the top of the inbox. I can't understand why they have not figured out how the game is played yet. Maybe its a cultural thing; most of them being from over seas, they don't realize, its not a bribe, its just how the good-old-boys do business.

Good luck, Kevin
Previous Topic: identify table associated with a blob.
Next Topic: Help needed
Goto Forum:
  


Current Time: Mon Dec 05 06:41:39 CST 2016

Total time taken to generate the page: 0.04527 seconds