0

Please refer to my sample, In oracle DB,these is below SP

CREATE OR REPLACE PROCEDURE SP_TEST_PUTLINE AS 
BEGIN
DBMS_OUTPUT.ENABLE;
  dbms_output.put_line('Hello world!');
END SP_TEST_PUTLINE;

I use the unix shell to call SP to get the dbms_output.put_line('Hello world!') from the SP, how should I do. I use sqlplus command to logon to DB ,seems can't get the result I want.

   output=$(IFS='';echo connect ${DBUSER}/${DBPASS}@${ORACLE_SID} execute SP_TEST_PUTLINE|sqlplus -s /nolog )

Can anyone help me? Thanks in advance...

------------------------------------split line 20180705 -------------------------------------

Thanks for the help of Alex and Kaushik Nayak,very helpful.

below are some findings for Kaushik Nayak,Please refer.

Hi Kaushik,It works, But at first, it was failed with below error

unknown command beginning "-e connect..." - rest of line ignored. SP2-0734:

So I change echo -e option to echo,then it works. So here are the questions 1)why I use the echo without -e option can get below result,Is it cause by IFS?

echo "abc\n def \nghi" 
abc
 def 
ghi

2) according to Alex's comments, whese two options need to write them to two lines? but you did not use \n between them

 set serveroutput on
set feedback off

and when I add \n between them as below .it encouterred error as below, but the 'hellow world!' has output,

SP2-0734: unknown command beginning "feedback o..." - rest of line ignored. Hello world! PL/SQL procedure successfully completed.

here is question, why did you not use \n between set serveroutput on and set feedback off, did this DB command (set feedback off) has run successfully?

Look forward to your reply. thanks in advance!

Tom
  • 121
  • 2
  • 14
  • 1
    Please don't edit your question to ask additional questions; you can ask for clarification in comments on the answers, but this goes a bit beyond that - in general it should be a new question (or two). But as these are minor points. they can probably be addressed via comments. – Alex Poole Jul 05 '18 at 09:07
  • Ok,Alex, need i fall back and raise another question? – Tom Jul 05 '18 at 09:17
  • only if the comment I added to Kaushik's answer doesn't explain it enough. But note for the future *8-) – Alex Poole Jul 05 '18 at 09:19
  • Ok, Got it..... – Tom Jul 05 '18 at 09:23

2 Answers2

3

The command you are using will fail to even connect because you need a line break between the connect and the execute.

But you also need to set serveroutput on, and you probably want to set feedback off too (and maybe other options.

I'd use a heredoc to make it easier to read and maintain:

output=$(
sqlplus -s /nolog <<!EOF
connect ${DBUSER}/${DBPASS}@${ORACLE_SID}
set serveroutput on
set feedback off
execute SP_TEST_PUTLINE
!EOF
)

# then do whatever you want with the output
echo ${output}

You might also want to do some error checking...

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
2

If you are using a single line echo to pass to sqlplus, you should put newlines using the -e option of echo

output=$(IFS='';echo -e "connect  ${DBUSER}/${DBPASS}@${ORACLE_SID}\nset serveroutput on feedback off\n execute SP_TEST_PUTLINE" |sqlplus -s /nolog )

You also have to specify set serveroutput on and feedback off to see only the output.

A better option would thus be to use a here document like Alex pointed out.

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • Hi Kaushik, after I removed '-e ' ,it works well,BTW, I have some questions and updated them under the ------split line 20180705-----, please find them from the question, Thanks – Tom Jul 05 '18 at 09:02
  • @Tom - .regarding `-e` I believe that indicates you're using a different version of ksh and/or a different OS; regarding the SP2-0734 my version has two separate `set` commands, while Kaushik combined them into a single `set`. If you add `\n` you need to add another `set` before `feedback`. – Alex Poole Jul 05 '18 at 09:12