Sitemap

Amicuk Programming Answers

How to Use the Procedures in a Sql Query

2015-10-11   Views:1

Advertisement

Hi Friends, Can anyone help me out whether can we use the procedure in the sql query.. if yes help me out with an example my requirement is i have one sql query .. in which i need to use the procedure which returns multiple values... how can i overco

Hi Friends,
Can anyone help me out whether can we use the procedure in the sql query..
if yes help me out with an example
my requirement is
i have one sql query .. in which i need to use the procedure which returns multiple values... how can i overcome it,can anyone help me out for this..
for your reference i am pasting the sql query
SELECT paf.person_id
FROM per_all_assignments_f paf START WITH paf.person_id = p_person_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type IN('E', 'C')
AND l_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
CONNECT BY PRIOR paf.supervisor_id = paf.person_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type IN('E', 'C')
AND l_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
and paf.person_id not in (>>>I HAVE TO USE THE PROCEDURE HERE<<<<);
Thanks in advance

The replay answer
Advertisement
We never saw your procedure, but maybe you could wrap it in a function
SQL> create or replace procedure get_members(in_something IN number, out_members OUT sys_refcursor)
is
begin
  open out_members for
    'select level member_id from dual connect by level <= :num' using in_something;
end get_members;
Procedure created.
SQL> create or replace type numbers as table of number;
Type created.
SQL> create or replace function members(in_something IN number)
return numbers
as
  member_cur sys_refcursor;
  members numbers;
begin
  get_members(in_something, member_cur);
  fetch member_cur bulk collect into members;
  close member_cur;
  return members;
end;
Function created.
SQL> select * from  table(members(4));
COLUMN_VALUE
           1
           2
           3
           4
4 rows selected.Variant on same using piplined function
SQL> create or replace function members_piped(in_something IN number)
return numbers pipelined
as
  member_cur sys_refcursor;
  rec number;
begin
  get_members(in_something, member_cur);
  loop
     fetch member_cur into rec;
     exit when member_cur%notfound;
     pipe row(rec);
  end loop;
  close member_cur;
  return;
end;
Function created.
SQL> select * from  table(members_piped(4));
COLUMN_VALUE
           1
           2
           3
           4
4 rows selected.
SQL> drop function members_piped;
Function dropped.
SQL> drop function members;
Function dropped.
SQL> drop type numbers;
Type dropped.
SQL> drop procedure get_members;
Procedure droppedEdit:
Sorry Blu, had not seen you already posted similar thing
Edited by: Peter on Jan 27, 2011 5:38 AM

Go to See the other 11 answers

How to Use the Procedures in a Sql Query

Category:DefaultRelease time:2015-10-11Views:130

Hi Friends, Can anyone help me out whether can we use the procedure in the sql query.. if yes help me out with an example my requirement is i have one sql query .. in which i need to use the procedure which returns multiple values... how can i overco[More]

SSRS - Stored procedure with Dynamic SQL Query

Category:DefaultRelease time:-0001-11-30Views:130

Am calling stored procedure in SSRS report.  I have used Dynamic SQL query in stored procedure as I don't know the column name and column count.  And I have used like below at end of the stored procedure "select * from ##temptable". As I have us[More]

Error while executing a procedure in pl/sql

Category:DefaultRelease time:2015-10-11Views:130

Hi, iam execvuting a procedure in pl/sql and i get the following error, proc Running: Wed Sep 7 06:13:20 IST 2005 Table truncated. BEGIN <procedure_name> ; END; ERROR at line 1: ORA-01555: snapshot too old: rollback segment number 75 with name "[More]

How to get resultset from procedure having dynamic select sql query ?

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have created a procedure, in which there is dynamic select query. The procedure has one out put parameter which gives error code. When I compile that procedure it compiles successufully. When I run it it executes successfully and gives output e[More]

How to build a report in web Intelligence using Store procedure under Microsoft SQL Server 2000

Category:DefaultRelease time:2015-10-11Views:130

Post Author: ltkin CA Forum: WebIntelligence Reporting Hi, How to build a report in web Intelligence using Store procedure under Microsoft SQL Server 2000 ? Regards,Hi ltkin, Unfortunately, it is not possible in Xir2 to create Webi reports from store[More]

Include Button that executes PL/SQL procedure to SQL query based region

Category:DefaultRelease time:2015-10-11Views:130

I would like to add two columns to a SQL query region. These columns would not be sourced from the query, but rather would be used to execute a PL/SQL procedure. For example, I would like to have a manager approve or deny adding an additional employe[More]

How to execute stored procedure in the SQL Commands page

Category:DefaultRelease time:2015-10-11Views:130

i'm oracle express edition newbie :) i have this stored procedure. it not belong to a package. create or replace procedure "LIST_MEMBERS" ("MEMBER_CUR" OUT SYS_REFCURSOR) is begin OPEN MEMBER_CUR FOR SELECT * FROM members; end; now, in[More]

Running a Stored Procedure in Oqacle SQL Developer

Category:DefaultRelease time:2015-10-11Views:130

Hi All, Could anybody elaborate on the procedure to execute a Stored Procedure in Oracle SQL Developer. I feel we need to configure the output for the DBMS_OUTPUT Cheers, Bellon the procedure to execute a Stored Procedure in Oracle SQL Developer.Sele[More]

Creation of DB Adaptert for calling stored procedure in MS SQL server

Category:DefaultRelease time:2015-10-11Views:130

Hi, I need to create a DB adapter to call a stored procedure in MS SQL Server. I have gone thru the thread MS SQL Server database connection It mentions that we need to use a command line utility for generating the wsdl and xsd for calling stored pro[More]

In JDBC Sender Adapter , the server is Microsoft SQL .I need to pass current date as the input column while Executing stored procedure, which will get me 10 Output Columns. Kindly suggest me the SQL Query String

Category:DefaultRelease time:2015-10-11Views:130

In JDBC Sender Adapter , the server is Microsoft SQL .I need to pass current date as the input column while Executing stored procedure, which will get me 10 Output Columns. Kindly suggest me the SQL Query String , for executing the Stored Procedure w[More]

SQL query statement  for stored procedure / function listing ...

Category:DefaultRelease time:2015-10-11Views:130

Hi everyone, Is there a SQL query to list all the stored procedures and functions of an user in an Oracle 8 database? I have this idea: select * from USER_SOURCE where TYPE = 'PROCEDURE' or TYPE = 'FUNCTION' but I am not too sure whether this is corr[More]

Hot
Whenever I play a song it starts from where I ast left off, which to me is getting kind of annoying, since I'm used to it playing from the beginning. I also switch songs either in the middle or towards the end..So it's a hassle to go switch the time [More]
My children remove my iphone 4 in Icloud - find my phone and now i don't see my device in this services. I want to return it. Help me please!Welcome to the Apple Community. You can turn Find my Phone on/off in settings > iCloud.Read other 5 answers [More]
Hi All, I have created a screen with 5input fields from dictionary, and one sub screen to hold a table control. Now my problem with the requirement is user wants to enter values in input fields as header and values in table control as items and saves [More]
I want to simulate the functionality of some MSS global variable in PL/SQL. I know that @@SPID may based on the v$session. I can just list all current users id and don't know how to get the id of my current user. How can @@SPID, @@SERVERNAME and @@ID [More]
Hi All, I have a list report which lists all the opportunities in Siebel 8.1. This report works fine when I run the report without querying anything. But when I query for some thing in the UI (ex: records which has country as UK only), and try to run [More]
hi, Can anyone explain the sqlldr command please. in the command last DIRECT = TRUE means what, DISCARD.TXT means what? ThanksHi user584291! 1.) DIRECT = TRUE means that sqlldr tries to use direct path loading. 2.) If you have records in your inputfi [More]
Hi, I am working with a daabase that has multiple datafiles for each tablespace. The datafiles for each tablespace are located on a single disk. I believe no stripping is present in this design. Presently, it is recomended that the datafiles for each [More]
I am facing problem regarding graphical user interface. I am using text box for editing files. I want to show the line numbers and graphical breakpoints along with text box. Can anybody help me in this? Thanks.Thanks for you reply. But actually I don [More]
Is there a simple way with ADDT to get the NUMBER of rows in a table that contain a particular ID or other value? Would QueryBuilder be the place to do this, or is there a snippet of code? For example, to find out how many students have registered fo [More]
Data Center with new Internet pipe. Current ExternalURL is blank and InternalURL is set to local server https://dns-name/Microsoft-Server-ActiveSync. If we modify these entries to point to a load balanced published DNS name is a restart required for [More]