I have the following function declared in my shell script:

write_to_stage_tables()
{
l_result=`sqlplus -s ${l_user_pwd} <<eof
whenever oserror exit sql.oscode rollback
whenever sqlerror exit sql.sqlcode rollback

set serverout on size 1000000;
set head off feedback off pages 0;
declare

l_ret_status varchar2(10);
l_ret_msg varchar2(2000);
l_event_text varchar2(2000);
l_event_details varchar2(2000);

e_write_to_stage exception;

begin

l_event_text := 'Call to xxwsh_ship_confirm.load_invoice_stg_tables';

xxwsh_ship_confirm.load_invoice_stg_tables
(
p_ret_status => l_ret_status
,p_ret_msg => l_ret_msg
);

if l_ret_status != 'S' then

l_event_details := l_ret_msg;
raise e_write_to_stage;

end if;

commit;

exception
when e_write_to_stage
then

rollback;

xxexcpt_log_pub.initialize ('XXWSH_INVOICE_FILE_LOAD');
xxexcpt_log_pub.write_exception
(
pi_exception_type => xxexcpt_log_pub.g_excpt_type_user_defined
,pi_exception_name => 'E_WRITE_TO_STAGE'
,pi_parameters => null
,pi_location => l_event_text
,pi_location_details => l_event_details
,pi_package_name => 'XXWSH_INVOICE_FILE_LOAD'
,pi_procedure_name => 'WRITE_TO_STAGE_TABLES'
,pi_table_name => null
,pi_column_name => null
,pi_column_value => null
,pi_error_message => 'See reqest log output for request $4'
);

when others
then

rollback;

l_event_details := sqlerrm;

xxexcpt_log_pub.initialize ('XXWSH_INVOICE_FILE_LOAD');
xxexcpt_log_pub.write_exception
(
pi_exception_type => xxexcpt_log_pub.g_excpt_type_system_explicit
,pi_exception_name => 'OTHERS'
,pi_parameters => null
,pi_location => l_event_text
,pi_location_details => l_event_details
,pi_package_name => 'XXWSH_INVOICE_FILE_LOAD'
,pi_procedure_name => 'WRITE_TO_STAGE_TABLES'
,pi_table_name => null
,pi_column_name => null
,pi_column_value => null
,pi_error_message => l_event_details
);

end;

/
commit;
exit
eof`

echo ${l_result}
}

In my shell script I call it by the following:

l_status=`write_to_stage_tables`

What I need to populate into the l_status variable is the value of l_result from my function. What am I missing here?

Thanks in adavance!

How does it not work? The use of backticks within backticks is redundant, though; perhaps you would get better results if you took out the backticks from inside write_to_stage_tables and simply let the backticks from the call of the function capture the output from SQL. (Using echo, especially without proper quoting, might lose significant whitespace from the string you are echoing.)

I was able to get the value passed back out of my pl/sql block by returning the wanted value using dbms_output.put_line.

write_to_stage_tables()
{
l_return=`sqlplus -s ${l_user_pwd} <<eof
whenever oserror exit sql.oscode rollback
whenever sqlerror exit sql.sqlcode rollback

set serverout on size 1000000;
set head off feedback off pages 0;
declare

l_ret_status varchar2(10);
l_ret_msg varchar2(2000);
l_event_text varchar2(2000);
l_event_details varchar2(2000);

e_write_to_stage exception;

begin

l_event_text := 'Call to xxwsh_ship_confirm.load_invoice_stg_tables';

xxwsh_ship_confirm.load_invoice_stg_tables
(
p_ret_status => l_ret_status
,p_ret_msg => l_ret_msg
);

if l_ret_status != 'S' then

l_event_details := l_ret_msg;
raise e_write_to_stage;

end if;

commit;

dbms_output.put_line(l_ret_status);

exception
when e_write_to_stage
then

rollback;

xxexcpt_log_pub.initialize ('XXWSH_INVOICE_FILE_LOAD');
xxexcpt_log_pub.write_exception
(
pi_exception_type => xxexcpt_log_pub.g_excpt_type_user_defined
,pi_exception_name => 'E_WRITE_TO_STAGE'
,pi_parameters => null
,pi_location => l_event_text
,pi_location_details => l_event_details
,pi_package_name => 'XXWSH_INVOICE_FILE_LOAD'
,pi_procedure_name => 'WRITE_TO_STAGE_TABLES'
,pi_table_name => null
,pi_column_name => null
,pi_column_value => null
,pi_error_message => 'See reqest log output for request $4'
);

when others
then

rollback;

l_event_details := sqlerrm;

xxexcpt_log_pub.initialize ('XXWSH_INVOICE_FILE_LOAD');
xxexcpt_log_pub.write_exception
(
pi_exception_type => xxexcpt_log_pub.g_excpt_type_system_explicit
,pi_exception_name => 'OTHERS'
,pi_parameters => null
,pi_location => l_event_text
,pi_location_details => l_event_details
,pi_package_name => 'XXWSH_INVOICE_FILE_LOAD'
,pi_procedure_name => 'WRITE_TO_STAGE_TABLES'
,pi_table_name => null
,pi_column_name => null
,pi_column_value => null
,pi_error_message => l_event_details
);

end;

/
commit;
exit
eof`
echo ${l_return}
}