[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to assign query to a procedure call directly?

Posted on 2011-09-29
9
Medium Priority
?
217 Views
Last Modified: 2012-06-27
I have a function call like this in a program,

SMS.SETDETFORM(DEID,formula,P_DST_FVC_ID,P_DST_FVC_ID);
where deid, formula,p_dst_fvc_id,p_dst_fvc_id all are variables.

now In the above call, Instead of formula, I need to fetch a value from a query directly
I should not assign the result to a variable and then pass it to the query.

In place of formula , I need to use the query.

formula query =
'select abcid
from abc  
where abcid=123'

Anyway for this.

Thanks,
Sakthi.
0
Comment
Question by:sakthikumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36814979
I'm not understanding the question.

What is SMS.SETDETFORM?
What are you wanting in the form of results?
0
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 36815107

 >>>SMS.SETDETFORM(DEID,formula,P_DST_FVC_ID,P_DST_FVC_ID);
>>>>where deid, formula,p_dst_fvc_id,p_dst_fvc_id all are variables.

I understood this part.
Now you dont want the formula variable but in place of that you want to use a dynamic query ..


So you want to use like :

>>>SMS.SETDETFORM(DEID,<<select query >>,P_DST_FVC_ID,P_DST_FVC_ID);

If you want to use the dynamic query then 1st run the query and then pass the result to your formula variable.

This should solve your problem


0
 
LVL 48

Expert Comment

by:schwertner
ID: 36815324
You have to define the paramere 'formula' as VARCHAR2.

So you will be able to invoke the procedure so:

SMS.SETDETFORM(DEID,'query = select abcid from abc  where abcid=123',P_DST_FVC_ID,P_DST_FVC_ID);

In the procedure use a block of statements that:

1. Encounter the keyword 'query'
2. Place the character after '=' in a variable
3. run
          execute immediatelly  INTO variable
4. use 'variable' as value returned by the query
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 42

Expert Comment

by:dqmq
ID: 36815516
SMS.SETDETFORM(DEID,
(
'select abcid
from abc  
where abcid=123 group by abcid'  --<== critical query only returns 1 row (value)
),P_DST_FVC_ID,P_DST_FVC_ID);

0
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 36815545
For more information on dynamic queries you can follow this link hope this will help you:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36815581
Let me know if I understood correctly:-

create or replace function temp_dev_f1 ( a number, b number) return number as
begin
  if b < 50 then
    return 10;
  else
    return 5;
  end if;
end;
/


select temp_dev_f1(1,1) from dual;

TEMP_DEV_F1(1,1)
----------------
              10

                    
select temp_dev_f1(1, x) from ( select rownum x from dual connect by level < 100) ;

You will get dynamic value of X from lower query.
0
 

Author Comment

by:sakthikumar
ID: 36890041
Hi for those who didn't understand,

I want something like the below example
Update table B set employee = (select name from Table A where ID = 3) where ID = 3

For employee value, it uses a query to get the value directly. Like this for below,

SMS.SETDETFORM(DEID,formula,P_DST_FVC_ID,P_DST_FVC_ID);
where deid, formula,p_dst_fvc_id,p_dst_fvc_id all are variables.

SMS.SETDETFORM is procedure for which we need to pass these
DEID, formula,P_DST_FVC_ID,P_DST_FVC_ID
as parameters.

What I want is
for the parameter formula, I have a query, this should be used directly in procedure.
Is this possible by any means?

0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36891498
Try this:-

Update table B set employee = (select SMS.SETDETFORM(DEID, x, ...) from (select name, (select x from another_table t2.x = t1.x) from Table A where ID = 3)) where ID = 3

I have not tested above code.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 2000 total points
ID: 36906147
You cannot call a PL\SQL procedure directly in a query.
You can however call a PL\SQL function directly in a query (that is, if the function returns a datatype that the query can handle, like a varchar2, number or date).

So, if you re-write the procedure to be a function, you may be able to call the function from your query.  Or, if you write a "wrapper" function that in turn calls the procedure, you could call the "wrapper' function from your query, and that in turn, will call the procedure.

Or, are you asking about passing in a query to a procedure (or function) and having that query dynamically executed in the procedure (or function)?  That is possible, but remember that Oracle is optimized for static SQL statements (that can use bind variables supplied by input parameters).  If you choose to use dynamic SQL in PL\SQL (which is possible if you use the "execute immediate..." syntax, or the more complex procedures in the DBMS_SQL package) be aware that your performance will be slower, and there may be more security risks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question