-2

I have to call oracle SQL statement inside my project.

All connection related stuff is done, but my tool does not capture the output parameter executed by oracle.

Hence I need alter this query to return p_num value in a select statement.

i.e. the table which has 1 column ('p_num') with column name called 'Result' and which has only one row which is p_num value.

Following is the sql statement which currently gives output value with help of dbms_output.put_line

DECLARE
p_num varchar2(4000);
message varchar2(4000) ; 
BEGIN
p_num := MyFunction();

dbms_output.put_line('Message : ' || p_num) ;
    END;

What I want is p_num value in a SELECT statement so that I can capture specific column inside my bpm tool.

Thomas G
  • 9,886
  • 7
  • 28
  • 41
user3815413
  • 385
  • 2
  • 20
  • 1
    Which SELECT statement? There is no select statement in your question –  May 11 '16 at 06:59
  • i want value of p_num shoudl be in resultset, so that my bpm tool captures output result value, i want some thing like select p_num from temp_table – user3815413 May 11 '16 at 07:10
  • Which result? Show us the query you want to change –  May 11 '16 at 07:11
  • Not sure, but may be you are looking for this :- http://stackoverflow.com/questions/5127482/executing-a-function-in-sql-plus – Pravin Satav May 11 '16 at 07:16
  • in above sql statment i have value in p_num, which i want to be in the form of select statments table result – user3815413 May 11 '16 at 08:16
  • when i put select * from dual i am getting a result as 'X' in dummy column, i want similar kind of result where in stood of 'X' it should be value from p_num – user3815413 May 11 '16 at 08:34

1 Answers1

1

You can directly call the function in the SELECT statement.

1) First way is to do it VIA plain SQL

SELECT MyFunction FROM DUAL;

2) Second way is PLSQL but i will not recommend it unless its unavoidable

set serveroutput on;
declare
lv_var VARCHAR2(100);
lv_out_param VARCHAR2(100);
BEGIN
lv_var:=MyFunction(lv_out_param);
dbms_output.put_line(lv_var||' '||lv_out_param);
END;
/
Avrajit Roy
  • 3,233
  • 1
  • 13
  • 25
  • I am new to oracle , Wat is this DUAL, Keyword? When i execute select Myfunction('APPR') from DUAL it says ORA-06550: line 9, column 5: PLS-00428: an INTO clause is expected in this SELECT statement – user3815413 May 11 '16 at 07:03
  • DUAL is a built-in table with a single row and column. It is used when you just want to evaluate an expression and aren't really interested in the table data. – Codo May 11 '16 at 07:09
  • Regarding PLS-00428: Do no use PL/SQL, i.e. remove DECLARE, BEGIN, END and just execute the SELECT statement without anything else. – Codo May 11 '16 at 07:10
  • @user3815413 : I have added both the ways of achieving the solution. Let me know if this helps. And explained above DUAL is a dummy table which ORACLE has provided. – Avrajit Roy May 11 '16 at 07:21
  • Thank you so much Avrajit,Thanks a alot for quick responce Now it is saying, Function MyFunction has out arguments, How can i passs out argumenst without declare? – user3815413 May 11 '16 at 07:23
  • Then it cant be called via plain SQL. You need to do it via PLSQL only – Avrajit Roy May 11 '16 at 07:24
  • I have modified the answer as per your comment. Could you plz check – Avrajit Roy May 11 '16 at 07:26
  • This was giving the result but not as table, which cannot be captured in bpm tool. ithink select statement result it will capture since it was table like result – user3815413 May 11 '16 at 08:00
  • when i write select * from dual i am getting a result as 'X' in dummy column, i want similar kind of result where in stood of 'X' it should be value from p_num – user3815413 May 11 '16 at 08:35