unix in Pl/sql blocks [message #268161] |
Mon, 17 September 2007 18:20  |
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 #268183 is a reply to message #268163] |
Mon, 17 September 2007 21:53   |
rleishman
Messages: 3728 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   |
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   |
Frank
Messages: 7901 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 #275244 is a reply to message #268161] |
Thu, 18 October 2007 23:26  |
 |
Kevin Meade
Messages: 2103 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
|
|
|