Calling Shell Script from Pl/Sql, But Shell Gets Executed as Grid User, Not Oracle

Calling shell script from PL/SQL, but shell gets executed as grid user, not oracle

The issue, as pointed out in the comments, is that Runtime.getRuntime().exec runs throught EXTPROC, and thus through the Grid Listener. Since we have OS user isolation between DB and GRID on our new configuration, this raised a permission problem on the FS.

The solution to this is one of the bellow:

• Fix FS permission to let grid user write the files and change umask to something like 774 or 664, so both grid and oracle users will be able to modify the files later;

• change sudoers file and allow grid to execute the commands needed as oracle without password and change shell script to include sudo;

• create a new listener on DB Home on another port and change TNSNAMES.ORA entry to point to the new port. Then extproc will be executed as OS user oracle. You will have to manually edit LISTENER.ORA on $OH and start it with lsnrctl, because listeners registered with srvctl will always be started by grid ;

• change main listener to the db home. I don't recommend that (see item above).

[EDIT]
As pointed out by @AlexPoole and @jonearles, there are two other options that weren't fit for my case, but might be for others:

• if you run the script locally on sqlplus, setting ORACLE_SID, the FS access will be made by the OS user running sqlplus. So you can run as oracle, or some other user and fix the FS permissions;
• if you schedule a job on dbms_job scheduler as SYS, the task will be executed by oracle (this behavior may be version dependent, so further testing is needed).

Regards,

Daniel Stolf

Shell script to automatically run PL/SQL

I was asked to make them run automatically using shell scripts

At first place, why do you worry about automation of the script at database level. You could simply schedule it as a cron job at the OS level.

You could also schedule it at database level using DBMS_SCHEDULER or DBMS_JOB, whichever is applicable to your database version.

How to call a stored procedure in oracle from shell

you can just do it like this

sqlplus login/pass @get_area.sql

where get_area.sql contains your SQL code

the last detail is to put an exit; at the end of the script oftherwise you will stay on the SQL > prompt

Oracle PL/SQL Analysing output of queries, writing to file

If you are selecting from different tables I would use the following - alternatively a cursor could be used if the same query was being used with only a parameter (like id) varying in which case you could use cursor parameters.

DECLARE
   v_count    PLS_INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO v_count 
    FROM sample_table 
    WHERE id=3;

    CASE v_count
        WHEN 0 THEN dbms_output.put_line('FALSE');
        ELSE dbms_output.put_line('TRUE');
    END CASE;

END;

It is important to use COUNT(*) not count on a field as COUNT(*) still returns 0 if no records exist.

Edit to add: The cursor equivalent is

DECLARE
    v_count    PLS_INTEGER;

    CURSOR count_cur(cp_id  sample_table.id%TYPE)
    IS
        SELECT COUNT(*)
        FROM sample_table
        WHERE id = cp_id;
BEGIN
    OPEN count_cur(3);
    FETCH count_cur INTO v_count;
    CLOSE count_cur;

    CASE v_count
        WHEN 0 THEN dbms_output.put_line('FALSE');
        ELSE dbms_output.put_line('TRUE');
    END CASE;

END;

There are also tests available on cursors such as cursor%FOUND or cursor%NOTFOUND that can be accessed once a cursor is opened. Although if all you are doing is testing whether a record exists that can be more verbose.

Also bear in mind if you are doing a SELECT INTO statement that unless you are doing COUNT(*) which is guaranteed to always return a record it is good form to catch the possible too many or no rows exceptions.

---

Related Topics