| Oracle code in Unix script [message #423275] |
Wed, 23 September 2009 03:35  |
ramyamakam
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
declare
sql_statement varchar2(200);
cursor_id number;
ret_val number;
begin
dbms_output.put_line(chr(0));
dbms_output.put_line('Re-compilation of Invalid Objects');
dbms_output.put_line('---------------------------------');
dbms_output.put_line(chr(0));
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',
'FUNCTION',
'PROCEDURE', 'TRIGGER',
'VIEW')
order by o.object_type) loop
if invalid.object_type = 'PACKAGE BODY' then
sql_statement := 'alter package '||invalid.owner||'.'||invalid.object_name||
' compile body';
else
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_sql.close_cursor(cursor_id);
dbms_output.put_line(rpad(initcap(invalid.object_type)||' '||
invalid.object_name, 32)||' : compile
d')
;
end loop;
end;
/
|
|
|
|
| Re: Oracle code in Unix script [message #423277 is a reply to message #423275] |
Wed, 23 September 2009 03:37  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
Regards
Michel
[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 |
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 |
scripter
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;
EOF
search google for "Connecting to Oracle database from unix shell script"
Regards
Scripter
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
