PL/SQL :: An Extra Parameter For Summary Function
Dec 19, 2012Why cant oracle give an extra parameter for summary function like MAX and MIN...For example
select max(salary,2)
from empWill give the second max salary.
ADVERTISEMENT
PL/SQL :: Trim Extra Space In Function Parameter List
May 9, 2013I am calling a function in front end, from front end i am passing space, now i want to trim space in parameter itself, it's psssible, assume the below is the function defination, i want to trim the p_region_name parameter like this trim(p_region_name), is this possible?
FUNCTION add_country_region_column (
p_s_country_code_iso_2 IN varchar2_table_type,
p_type IN d_country.c1_type%TYPE,
p_name IN d_country.c1_name%TYPE,
p_desc IN d_country.c1_desc%TYPE,
[Code]....
SQL & PL/SQL :: Function Without Parameter?
Aug 5, 2010if function having retrun statement, why we need to use out or inout parameters.
View 16 Replies View RelatedSQL & PL/SQL :: Use Cursor As Parameter For Function?
May 4, 2011I was wondering if it's possible to use the cursor as a parameter for a function. Something like this is what I'm trying to do:
set serverouput on
declare
cursor csv_file
[Code]....
PL/SQL :: Call Function Without Parameter?
Feb 27, 2013i am trying to call a function from Sql statement and i am getting this error ORA-06572: Function XX has out arguments.
View 2 Replies View RelatedShow Parameter Audit_trail And Function
Dec 16, 2010Is there any way to get audit_trail.VALUE? I know the statement: show parameter audit_trail; can show that value. But, i want to build a function which will return a string that contain audit_trail.VALUE
View 1 Replies View RelatedSQL & PL/SQL :: Cursor With Parameter Inside Function
Mar 7, 2010I have code inside function
.....
cursor cur1 is
select *
from sarchkler
where sarchkler_appl_no = in_appl_no
begin
select max(saradap_appl_no) into in_appl_no from saradap;
for rec1 in cur1 loop
......
my question I get variable for cursor after cursor declaration
PL/SQL :: How To Pass Value From A Table Into Parameter Of A Function
Nov 8, 2012I have written as function :
create FUNCTION Cov(p_str1_x IN VARCHAR2,p_str2_x IN VARCHAR2, p_str1_y IN VARCHAR2,p_str2_y IN VARCHAR2) RETURN NUMBER is
TYPE ty_varchar_assoc IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
v_x ty_varchar_assoc;
v_y ty_varchar_assoc;
v_inter ty_varchar_assoc;
[Code]....
Now i need to pass the values to the parameter from a table? How can i do that?
Forms :: Call Function With Parameter In Oracle
Jul 17, 2012i have this function
create function xxx_sal (p_number in number)
return number is
v_sal number;
begin
select sum(sal)
into v_sal
from emp
where empno = p_number;
return v_sal;
end;
how can called it in oracle forms
SQL & PL/SQL :: Function Invoked By Passing Date Parameter
Oct 18, 2011get rid of the below error
CREATE OR REPLACE FUNCTION fn (
p_salesrep_id IN jtf_rs_salesreps.salesrep_id%TYPE,
p_org_id IN jtf_rs_salesreps.org_id%TYPE,
p_cnf_date IN emcint_ord_headers_all.creation_date%TYPE
[Code]....
Invoking Functions
select fn(-3,293,'1/1/1952'), resource_id from jtf_rs_salesreps
where rownum < 5
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
SQL & PL/SQL :: Dynamic Calling Function With Type Record Parameter?
Jul 23, 2010I'm trying to execute a dynamic sql that calls a function. But that function has inserts and deletes inside and this way it can't be called through a select statement. And to be worst, it has an other problem, my function uses a record type as parameter.
My code (sample):
-----------------
DECLARE
type r_parameters is record
(cd_query cons_query_param.cd_query%type,
cd_usuario cons_query_user.cd_usuario%type,
nr_param cons_query_param.nr_param%type,
vl_param varchar2(2000),
[code].....
SQL & PL/SQL :: Write A Function That Receive As Parameter A Datatype Varchar2?
Jul 18, 2012here is the situation, I have to write a function that receive as parameter a datatype varchar2 and validate if the letter is less than "A" or greater than "Z", it must print do not correct, or correct depending on the situation, no problem so far, the only problem I've got is: If the symbol is ";" is valid, but i do not know how to write it, It does not depend on the PL/SQl languaje but depends on the logic itself.
DECLARE
v_nombre varchar2(20):='john;%';
vv varchar2(1);
begin
for i in 1..length(v_nombre) loop
[code]...
Server Utilities :: Export Illegal Parameter Value In OCI Lob Function?
Jan 21, 2011We are trying to export our production data .We got this error
. . exporting table EA_BLOB
EXP-00056: ORACLE error 24801 encountered
ORA-24801: illegal parameter value in OCI lob function
how to overcome this error ?
SQL & PL/SQL :: Pass Multiple Values As Single Input Parameter Into Pipeline Function
Dec 23, 2012My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":
with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)
SQL & PL/SQL :: Summary (Row To Column)
Jun 1, 2011 SQL summary for below data:
Table: temp_user_access
NAME Null? Type
------------------------------- --------- -----
USERGROUP VARCHAR2(255)
USERNAME NOT NULL VARCHAR2(40)
NICKNAME VARCHAR2(40)
AAACC VARCHAR2(70)
SYSTEM VARCHAR2(10)
I need the expected result as per attached photo
Retrieving Extra Rows
Dec 14, 2011I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:\NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
CODEFile_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.
I use Oracle 10.
SQL & PL/SQL :: Retrieving Extra Rows
Dec 14, 2011I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
File_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are. Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.I use Oracle 10.
PL/SQL :: Timestamps - Extra Zero Are Adding?
Jul 8, 2013view the below select statement..why it's adding extra zero's...
select to_timestamp('2001-05-22 12:00:18.600','YYYY-MM-DD HH:MI:SS.ff3AM') from dual
output: 5/22/2001 12:00:18.600000000 PM ---why it's adding extra zeors's
my output should be as " 5/22/2001 12:00:18.600 PM"
How To Find Summary Value By Range
Jul 22, 2011this is my table
create table sequence ( id int not null primary key);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?
SQL & PL/SQL :: How To Find Summary Value By Range
Jul 22, 2011this is my table
create table sequence (
id int not null primary key
);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?
SQL & PL/SQL :: Summary Column To Details?
Sep 25, 2011I have one table which has two columns name,qty and it has data like arif,3 pcs i want to display it in 3 lines if the qty is 3 and in 2 lines if the qty is 2 using sql query
View 6 Replies View RelatedSQL & PL/SQL :: Insert Into Table And Add Extra Field Value
Sep 19, 2011I need to copy records from a working table to a history table. I have the following sql statement
insert into test.history
(equip_ID, state, manufacturer, install_year, capacity,
group_ID, Test_status)
select (equip_ID, state, manufacturer, install_year, capacity,
group_ID, Test_status
from test.info_AP
Table test.history has one more field in it called test_year. I need to fill this field when I do the insert. Can't use an after update trigger as the field is currently set to not allow nulls.
SQL & PL/SQL :: Linking Two Tables With Extra Field
Oct 1, 2011Once I add an extra column to link the two tables the number of rows is reduced by one, however when I try to get this extra record I find none matching the same criteria that led to reducing the records.
SQL> SELECT count(*)
2 FROM N_CONTRACT NC, N_WITHDRAWAL_REQUEST NWR
3 WHERE NC.fk_temP_withdrawal_req_serial = NWR.SERIAL_NUMBER;
COUNT(*)
----------
2243
[code]...
SQL & PL/SQL :: Extra Characters While Utl_file.get_line()
Jul 21, 2010Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
i am trying to read the file, for which i m using the following:
l_utlfile_hdr := utl_file.fopen(p_dir,'header_evnt.txt', 'R');
utl_file.get_line(l_utlfile_hdr, l_hdr_evnt);
it gets these three characters at the start of the line
l_hdr_evnt = 
So i saved header_evnt.txt as UTF-8, and used utl_file.fopen_nchar, utl_file.get_line_nchar, which got rid of first two characters, but still has ¿. How to get rid of that?
Database - Summary Of Sales By Quarter?
Nov 30, 2010I brought in the Northwind database 2003 in to SQL Developer. I was looking at the view 'Summary of Sales by Quarter' But this report brings in everything. It doesent give me summary of sales for Qtr1, Qtr4 etc
These are the columns:
ShippedDate
OrderId
Subtotal
CREATE OR REPLACE FORCE VIEW "NORTHWIND"."SUMMARY_OF_SALES_BY_QUARTER" ("SHIPPEDDATE", "ORDERID", "SUBTOTAL")
AS
[code]....
How can i recreate this view to show me summary data for qtr1, qtr2, qtr3 and qtr 4 for the year 1997??
SQL & PL/SQL :: Summary Report From Detail Table
Mar 26, 2012I have been sweating on this since the weekend.
My source table
SELECT
'182383' EMPLOYEE_NO
, 'ABACUS' FULL_NAME
, 'ADMIN' DEPARTMENT
, 'ADMIN' PREV_DEPARTMENT
, TO_DATE(20090104,'YYYYMMDD') EMP_START_DATE
[Code]....
EFFECTIVE_START_DATE: SCD field, Date ABACUS moves from one department to another.
EFFECTIVE_END_DATE: SCD field, Date ABACUS left the department from another. To keep history of movement.
The sample report shows possible scenario on my data.
I want to build monthly report to cater from departmental headcount on monthly basis, starting from from ABACUS start date.
see sample report below
MONTH | DEPARTMENT |NEW_HIRES | RELOCATION (OUT) | RELOCATION (IN) | NET TOTAL PREV MONTH | NET TOTAL PRESENT MONTH |
WHERE
MONTH: REPORT MONTH
DEPARTMENT: THE DEPARTMENTS IN THE ORGANIZATION
NEW_HIRES: NUMBER OF NEW STAFFS HIRED THAT MONTH BASE ON EMP_START_DATE
RELOCATION_OUT: NUMBER OF STAFFS MOVEMENT TO ANOTHER DEPARTMENT (EMP_END_DATE IS NULL, EFFECTIVE_START_DATE AND
[Code]....
SQL & PL/SQL :: Procedure To Calculate Summary Session?
Aug 13, 2013I have a below requirement from client.
Create a procedure generate_daily_session_summary that will read the pcm_session_summary (pk sess_id) table and create a per day summary in the pcm_session_summary_daily table.
1. Summarize all records from pcm_session_summary for session start timestamp (START_TS) greater than the max existing SESS_DT from pcm_session_summary_daily table and less than the start of current day. Insert the records into the pcm_session_summary_daily table.
2. Populate the fields in pcm_session_summary_daily as follows :
sess_dt - truncated date from pcm_session_summary.start_ts unique_user_cnt - count of unique cws ids encountered in that day, sess_cnt number - count of total sessions in that day , max_concurrent_sess_cnt number - maximum number of concurrent sessions at any point of time in that day. Concurrent sessions are defined as those that have overlapping start_ts or end_ts - sessions that were active at the same moment in time would be concurrent with each other.
3. The procedure will be scheduled to run every day in a batch job and will summarize session data until the end of the previous day
I have the table with below details.
desc PCM_SESSION_SUMMARY
Name Null Type
---------- -------- ------------
SESS_ID NOT NULL NUMBER
USER_CUPID VARCHAR2(10)
START_TS TIMESTAMP(6)
END_TS TIMESTAMP(6)
ACT_CNT NUMBER
[code]....
I have written the below query to get the result for question no:1
select trunc(start_ts,'DDD') start_ts,count(distinct(user_cupid)) user_cnt,count(SESS_ID) Sess_cnt
from PCM_SESSION_SUMMARY
where START_TS < sysdate or START_TS > (select max(sess_dt) from pcm_session_summary_daily where sess_dt < sysdate)
group by trunc(start_ts,'DDD')
order by start_ts;
However i tried a lot but not sure how to get maximum concurrent session count for a day.One of my friend comment below.
The Pcm_session_summary_daily is empty so you need to handle nulls when doing max. That will fix the issue of no results for the Greater Than condition.
You need to get concurrent count of sessions with respect to one another, the query that you have will not work. Do it in PL/SQL using procedural logic. The queries would return multiple rows, you need to handle that.
Extra Long Stored Procedure Inputs
Mar 20, 2007I am modifying a stored proc that i wrote a couple of years ago. The change is that i want certain inputs to accept strings of characters (comma delimited) so they can be parsed and processed in the proc. The problem is, when i pass very little data (about 50 characters) It fails saying the character sting buffer is to small.
I have setup the input as a type of a column that is a clob and i have also tried using just vahrchar2 (with no size set).
Extra Space In File_location For Alter Command
Nov 7, 2010I do below alter command
alter tablespace RCA_MB_IDX add datafile '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M
But there is extra space in the file location '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbfX'
I mark X for that space ...
The command is commit .
But there is problem when do backup .
SQL & PL/SQL :: Procedure To Remove Extra Spaces In Strings
Feb 17, 2011I would like to create a procedure in oracle to remove extra spaces coming as blank spaces(squared ones)in strings.
View 32 Replies View Related