OraFAQ Forum: Unix » Oracle code in Unix script

Home » Infrastructure » Unix » Oracle code in Unix script (Unix,Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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;
/

Report message to a moderator

Re: Oracle code in Unix script [message #423277 is a reply to message #423275]

Wed, 23 September 2009 03:37

Michel Cadot
Messages: 68756
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.

Report message to a moderator

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

Code: [Select all] [Show/ hide]

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

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

Regards
Scripter

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 17 01:44:44 CDT 2025