Sign Up to our social questions and Answers to ask questions, answer people’s questions, and connect with other people.
Login to our social questions & Answers to ask questions, answer people’s questions & connect with other people.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
Return more than one row from stored procedure in pl/sql
hello aya, as you know the procedure you can't use inside query so if you need to create procedure to return multiple row or values you can use : create or replace procedure myprocedure(retval in out sys_refcursor) is begin open retval for select employee_id,last_name from employees; end myprocedureRead more
hello aya,
as you know the procedure you can’t use inside query so if you need to create procedure to return multiple row or values you can use :
create or replace procedure myprocedure(retval in out sys_refcursor) isbegin
open retval for
select employee_id,last_name from employees;
end myprocedure;
and here you can call procedure inside anonymous pl/sql block :
[code]
DECLARE
myrefcur SYS_REFCURSOR;
employee_id employees.employee_id%TYPE;
last_name employees.last_name%TYPE;
BEGIN
myprocedure (myrefcur);
LOOP
FETCH myrefcur INTO employee_id,last_name;
EXIT WHEN myrefcur%NOTFOUND;
DBMS_OUTPUT.put_line (‘Employee Id: ‘ || employee_id || ‘ Last Name: ‘|| last_name);
END LOOP;
CLOSE myrefcur;
END;
[/code]
hope this help.
See lessHow to Create the TKPROF Trace File in oracle ?
Hello , Solution : For the TKPROF a) tkprof rawtrace.trc output_file explain=apps/apps/sort=(exeela,fchela) sys=no b) rawtrace.trc: Name of trace file output_file: tkprof out file explain: This option provides the explain plan for the SQL statements sort: This provides the sort criteria in which allRead more
Hello ,
Solution :
a) tkprof rawtrace.trc output_file explain=apps/apps/sort=(exeela,fchela) sys=no
b) rawtrace.trc: Name of trace file
output_file: tkprof out file
explain: This option provides the explain plan for the SQL statements
sort: This provides the sort criteria in which all SQL statements will be sorted. This will bring the bad SQL at the top of the outputfile.
sys=no: Disables SQL statements issued by user SYS
Regards ,
Mahmoud Morsy
See lessHow to see lock on table and query?
Hi Mina , Try this query : [code] SELECT c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine FROM v$locked_object a, v$session b, dba_objects c WHERE b.sid = a.session_id AND a.object_id = c.object_id AND xidsqn != 0; [/code] Regards , Mahmoud Morsy
Hi Mina ,
Try this query :
[code]
SELECT c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id AND xidsqn != 0;
[/code]
Regards ,
Mahmoud Morsy
See lessWhat is the difference between schema and user ?
SCOTT is a schema that includes the EMP, DEPT and BONUS tables with various grants, and other stuff. SYS is a schema that includes tons of tables, views, grants, etc etc etc. SYSTEM is a schema..... Technically -- A schema is the set of metadata (data dictionary) used by the database, typicalRead more
SCOTT is a schema that includes the EMP, DEPT and BONUS tables with various grants, and other stuff.
SYS is a schema that includes tons of tables, views, grants, etc etc etc.
SYSTEM is a schema…..
Technically — A schema is the set of metadata (data dictionary) used by the database, typically generated using DDL. A schema defines attributes of the database, such as tables, columns, and properties. A database schema is a description of the data in a database.
See lessI need query to get Apps_user_name , Date , SID , SERIAL#, oracle process , application process , Module , responsibility name in EBS R12.
Hello , The below query help you : select usr.user_name apps_username ,ses.osuser ,ses.machine ,i.first_connect "First Connect Date" ,ses.sid ,ses.serial# ,ses.module ,v.spid "Oracle Server Process" ,ses.process "Application Server Process" ,rsp.responsibility_name "Responsibility Name" ,null "RespoRead more
Hello ,
The below query help you :
select usr.user_name apps_username,ses.osuser
,ses.machine
,i.first_connect "First Connect Date"
,ses.sid
,ses.serial#
,ses.module
,v.spid "Oracle Server Process"
,ses.process "Application Server Process"
,rsp.responsibility_name "Responsibility Name"
,null "Responsibility Start Time"
,fuc.function_name "Function Name"
,i.function_type "Function Type"
,i.last_connect function_start_time
from icx_sessions i
,fnd_logins l
,fnd_appl_sessions a
,fnd_user usr
,fnd_responsibility_tl rsp
,fnd_form_functions fuc
,gv$process v
,gv$session ses
where i.disabled_flag = 'N'
and i.login_id = l.login_id
and l.end_time is null
and i.user_id = usr.user_id
and l.login_id = a.login_id
and a.audsid = ses.audsid
and l.pid = v.pid
and l.serial# = v.serial#
and i.responsibility_application_id = rsp.application_id(+)
and i.responsibility_id = rsp.responsibility_id(+)
and i.function_id = fuc.function_id(+)
and i.responsibility_id not in (select t1.responsibility_id
from fnd_login_responsibilities t1
where t1.login_id = l.login_id)
and rsp.language(+) = 'US'
union
select usr.user_name apps_username
,ses.osuser
,ses.machine
,l.start_time
,ses.sid
,ses.serial#
,ses.module
,v.spid
,ses.process
,rsp.responsibility_name
,r.start_time
,null
,null
,null function_start_time
from fnd_logins l
,fnd_login_responsibilities r
,fnd_user usr
,fnd_responsibility_tl rsp
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = r.login_id(+)
and r.end_time is null
and r.responsibility_id = rsp.responsibility_id(+)
and r.resp_appl_id = rsp.application_id(+)
and rsp.language(+) = 'US'
and r.audsid = ses.audsid
union
select usr.user_name
,ses.osuser
,ses.machine
,l.start_time
,ses.sid
,ses.serial#
,ses.module
,v.spid
,ses.process
,null
,null
,frm.user_form_name
,ff.type
,f.start_time function_start_time
from fnd_logins l
,fnd_login_resp_forms f
,fnd_user usr
,fnd_form_tl frm
,fnd_form_functions ff
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = f.login_id(+)
and f.end_time is null
and f.form_id = frm.form_id(+)
and f.form_appl_id = frm.application_id(+)
and frm.language(+) = 'US'
and f.audsid = ses.audsid
and ff.form_id = frm.form_id
order by function_start_time
Regards,
Mahmoud Morsy
See lessHow to check Archive log sequence in Oracle DB 11g?
Hi, Use the below query : [code] SELECT * FROM v$archived_log ORDER BY stamp DESC [/code] Regards, Mahmoud Morsy
Hi,
Use the below query :
[code]
SELECT *
FROM v$archived_log
ORDER BY stamp DESC
[/code]
Regards,
Mahmoud Morsy
See lessHow to check Workflow Mailer Status By query in EBS R12?
Hi , You can use the below query : SELECT component_name AS Component, component_status AS Status FROM apps.fnd_svc_components WHERE component_type = 'WF_MAILER' Regards, Mahmoud Morsy
Hi , You can use the below query :
Regards,
See lessMahmoud Morsy
How to query Gather schema statistic For Last week NonApps schemas in EBS R12?
Hello , Check the below Query: [code] SELECT * FROM apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r WHERE r.concurrent_program_id = p.concurrent_program_id AND r.program_application_id = p.application_id AND p.user_concurrent_program_name IN ('Gather Statistics for NonApps Schema')Read more
Hello ,
Check the below Query:
[code]
SELECT *
FROM apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
WHERE r.concurrent_program_id = p.concurrent_program_id
AND r.program_application_id = p.application_id
AND p.user_concurrent_program_name IN (‘Gather Statistics for NonApps
Schema’)
AND ( r.phase_code = ‘C’
AND r.status_code = ‘C’
AND r.actual_start_date >= SYSDATE)
[/code]
Regards
Mahmoud Morsy
See lessHow to query Gather schema statistic For Last week For Apps schema in EBS R12?
Hi, Try the below Query: [code] SELECT * FROM apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r WHERE r.concurrent_program_id = p.concurrent_program_id AND r.program_application_id = p.application_id AND p.user_concurrent_program_name IN ('OnDemand Gather Schema Statistics', 'GatherRead more
Hi,
Try the below Query:
[code]
SELECT *
FROM apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
WHERE r.concurrent_program_id = p.concurrent_program_id
AND r.program_application_id = p.application_id
AND p.user_concurrent_program_name IN (‘OnDemand Gather Schema Statistics’,
‘Gather Schema Statistics’,
‘Gather Schema Statistics (IT_ANALYZE)’)
AND ( r.phase_code = ‘C’
AND r.status_code = ‘C’
AND r.actual_start_date >= sysdate)
[/code]
Regards,
Mahmoud Morsy.
See lessHow to kill current session in Oracle (ORA-00027 ) ?
You can use this SQL statement to get the SID, SERIAL# of the current session: SELECT s.sid,s.serial#,spid,TRIM (s.machine) machine,TRIM (s.module) module,statusFROM v$session s, v$process pWHERE paddr = addr AND module IS NOT NULLORDER BY 1, 2 Then with sqlplus Run ALTER SYSTEM KILL SESSION 'sid,seRead more
You can use this SQL statement to get the SID, SERIAL# of the current session:
Then with sqlplus Run
Or by shell commnad:
Regards,
Mahmoud Morsy.
See less