Home » Infrastructure » Unix » Oracle code in Unix script (Unix,Oracle 10g)
Oracle code in Unix script [message #423275] Wed, 23 September 2009 03:35 Go to next message
Messages: 1
Registered: September 2009
Junior Member
I have compiled the following script in Oracle 10g. The script works fine but now i've been asked to put the follwing code in a Unix script. How do i do this? Any help appreciated. Thanks

sql_statement varchar2(200);
cursor_id number;
ret_val number;
dbms_output.put_line('Re-compilation of Invalid Objects');

for invalid in (select object_type, owner, object_name
from sys.dba_objects o
where o.status = 'INVALID'
and o.object_type in ('PACKAGE', 'PACKAGE BODY',
order by o.object_type) loop

if invalid.object_type = 'PACKAGE BODY' then
sql_statement := 'alter package '||invalid.owner||'.'||invalid.object_name||
' compile body';
sql_statement := 'alter '||invalid.object_type||' '||invalid.owner||'.'||
invalid.object_name||' compile';
end if;
/* now parse and execute the alter table statement */
cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(cursor_id, sql_statement, dbms_sql.native);
ret_val := dbms_sql.execute(cursor_id);

dbms_output.put_line(rpad(initcap(invalid.object_type)||' '||
invalid.object_name, 32)||' : compile
end loop;


Re: Oracle code in Unix script [message #423277 is a reply to message #423275] Wed, 23 September 2009 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please search BEFORE posting.
There are many examples on how to use SQL or PL/SQL in Unix scripts in this forum.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.


[Updated on: Wed, 23 September 2009 03:37]

Report message to a moderator

Re: Oracle code in Unix script [message #423497 is a reply to message #423277] Thu, 24 September 2009 15:31 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Your code seems to be based on this 1998 script http://www.orafaq.com/scripts/sql/compall2.txt

some comments:
1) Personally, I avoid tricks like printing chr(0) to get a blank line becuase I don't want to get bitten by an actual chr(0) in my output. I guess it must have been tested by the original author though...
2) I'd use the newer execute immediate rather than dbms_sql (easier to read & maintain).
3) I'd avoid the intcap stuff on object names - it'll just confuse everyone when you hit an object that genuinely CamelCase. Back in 1998 people didn't have much CamelCase in Oracle - that ofen comes from non-Oracle programmers.
4) many environments will have additional object types that are candidates for compiling (e.g. TYPE).
5) You seem to have removed the dependency part of the original query. One of the main tricks to avoid having to compile based on the dependency tree it just to complile all package specs before compiling any bodies. Even then, you may need to loop through more than once. Google for or see Oracle support (aka metalink) examples.
Re: Oracle code in Unix script [message #426590 is a reply to message #423275] Fri, 16 October 2009 15:07 Go to previous message
Messages: 6
Registered: October 2009
Location: India
Junior Member
To insert sql code in shell script

sqlplus -s user/password <<EOF
select sysdate from dual;

search google for "Connecting to Oracle database from unix shell script"

Previous Topic: Oracle patch for Solaris 10
Next Topic: cron job not getting executed
Goto Forum:

Current Time: Wed May 22 23:15:53 CDT 2024