My 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)
I have a user requirement where they want to be able to select multiple values for a single parameter that will act as a filter for a report. The possible values for a parameter can come from almost any master data Oracle eBusiness table. I don't want to just present the user with a LOV where they search down a list selecting what they want. That wouldn't be very practical for selecting part numbers from 1000's of rows. I also want the user to easily see what values they have selected. I thought about providing a button that would navigate to a multi-row form where they could record their values and use those values in the where clause of the SQL for the report, but that is a somewhat complex solution I have never tried before. how to do this. I'm working in the latest version 4 of APEX.
View 1 Replies View RelatedI have a doubt how to pass input parameter for nested table which is declared as input parameter in procedure.
CREATE TYPE t_example AS OBJECT(msg_text VARCHAR2(100), bundle_msg_text VARCHAR2(100), version NUMBER(10))
/
create type t_msg_details ia table of t_example
/CREATE TABLE table_nested_sample
(msg_codes NUMBER(10),
language_id NUMBER(10),
[Code]...
How to call this procedure I want to insert data like this
Msg_codes Language_id Language_name msg_text_details
101 1 ENG "msg_test" "Bundle_msg_text" 1
"msg_test_1" "Bundle_msg_text_2" 2
i need to pass table as input parameter in stored procedures. during the run time, i am getting error
CREATE OR replace TYPE emp_type IS OBJECT (
id NUMBER(4),
ename VARCHAR2(10));
CREATE OR replace TYPE emp_type_tab
IS TABLE OF EMP_TYPE;
CREATE OR replace PROCEDURE Test_proc (in_emp_type IN EMP_TYPE_TAB)
AS
BEGIN
FOR i IN 1.. in_emp_type.COUNT LOOP
dbms_output.Put_line(in_emp_type.Id(i));
END LOOP;
END;
/
i have a stored procedure whose input parameter is a varchar2 datatype.i created this procedure for an interface and tibco would be calling my procedure by passing input parameters.my problem is when there is a input string with & (ambersand) then its not working.
even i tried to pass the parameter with & in TOAD, it asks me to enter value for string.look at the sample code below which i wrote for testing purpose:
procedure is:
create or replace procedure testproc(p_in in varchar2)
is
begin
null;
end;
i pass parameter as given below:
begin
testproc('abc & def');
end;
if i run above script, it asks me to input some string value as it sees & in the string. attached is the image that shows up in TOAD. if i run below script it works. but i dont know how many &'s will be there in the input parameter. hence i cant do. and also TIBCO cant modify the input paramter while calling the procedure.
begin
testproc('abc &'||'def');
end;
I 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?
I need to get multiple code values and put it into a variable which later need to pass into the where clause of an sql. But i am not getting any results even i pass those values in the variable of an where clause: below is my Procedure:
declare
TYPE crMain_record is RECORD (
v_code dummy.v_code%type,
n_no dummy.n_no%type,
[Code].....
END;"lv_character" is going to hold the multiple code values which i need to pass into whare clause of the above SQL: the totlal number of these mulitipe codes can be more then 50..
And lv_character values are commung from a setup table
lv_character varchar2(32767):= '('||''''||'COMMIS'||''''||' , '||''''||'AGY BUILDING BENS'||''''||')';
--And lv_character values are commung from a setup table.where "lv_character" holdes multipe code values...
And lv_character values are commung from a setup table and upper(d.v_code)in lv_characterif the
Using pl/sql block , i tried to have a certain input values from Sql prompt but it doesn't work when i invoke it.
Here the simplest
declare
a number(4);
begin
for i in 1..10 loop
a := &a;
end loop;
end;
/
resolve issue while modified the user function code for returns the values as timestamps
---function code
create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin
[Code].....
Input:-
select fun_test_timestamp('5') from dual;
Output:-
SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;
Modified Fun Code:
create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);
[Code]...
Input:-
select fun_test_timestamp('5') from dual;
Output:-
ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
*Action:
I am trying to create one pipelined function but facing some errors as below
SQL> CREATE OR REPLACE
2 FUNCTION FUN_PIPELINED_EMP
3 RETURN org_typ Pipelined
4 IS
5 CURSOR c1 IS SELECT EMPLOYEE_ID,DESIGNATION,DEPT_ID,PROJECT_ID,MANAGER_ID FROM
6 EMPLOYEE_DETAILS1;
7 org_rec c1%rowtype;
8 begin
[code]....
In the follow code example, is it possible to save the seeds that I generated into a table when I call this table function without expliciting doinginsert into <some_table>select select * from table(pkg_seed.getSeed(200)); I try the automonous_transaction clause but it does not work.
--drop package pkg_seed--drop type seed_tab CREATE or replace TYPE seed_rec AS OBJECT( id number,seed number); CREATE or replace TYPE seed_tab AS TABLE OF seed_rec; CREATE or replace PACKAGE pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED;END pkg_seed;/ CREATE or replace PACKAGE BODY pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED IS cursor cur_seed(vmaxrow number) is select rownum id, floor(dbms_random.value(1,1000) ) seed from dual connect by level <= vmaxrow; l_seed cur_seed%rowtype; BEGIN open cur_seed(maxrow); LOOP FETCH cur_seed into l_seed; pipe row(seed_rec(l_seed.id,l_seed.seed)); END LOOP; RETURN; -- the function returns a single result END getSeed;END pkg_seed;/ select * from table(pkg_seed.getSeed(200));
For the below query:
SELECT id, pob,exp
FROM emp
where exp= (:exp)or @exp is null
O/P:
id pob exp
_____________________
1 CT 2
2 NJ 3
3 NY 2
It takes only one value of :exp but, I would like to give multiple values separated by ','. My problem is the parameter can be either single value or Blank or multiple values. My code do work for single value and Blank, Now how to implement multiple values .
Can we create a Pipelined function in A Package ? I know we can create it standalone function.
View 11 Replies View RelatedI need to create a function where in data from 5 rows is clubbed into one row. Like this I have around 425 rows which should be clubbed to 85 rows. Requirement is similar to pivot but not exactly like a pivot as different columns need to be taken from those 5 rows. This is for reporting purpose in order to get data in the desired report format.
SQL mentioned below works fine. It does return data.When below code is used as a normal procedure with OUT parameter as Index by table of Record type code works fine. It returns data. Functionality is met. But when used as a pipeline function, it returns no data.
Below code gets compiled but returns nothing. I didn't find anything on Google or any website for same.
CREATE TYPE r_report_mth_rec_obj AS OBJECT (
acct_num VARCHAR2 (20),
acct_name VARCHAR2 (80),
fund_group VARCHAR2 (80),
fund_type VARCHAR2 (80),
share_class_code VARCHAR2 (10),
share_class_description VARCHAR2 (20),
curr_code VARCHAR2 (10),
[code]...
CREATE TABLE prim_tbl
(id NUMBER,--- id is not primary key here
description VARCHAR2(30));
INSERT ALL
INTO prim_tbl VALUES (1,'aad')
INTO prim_tbl VALUES (1,'aads')
INTO prim_tbl VALUES (2,'bb')
INTO prim_tbl VALUES (2,'cc')
INTO prim_tbl VALUES (2,'dd')
SELECT * FROM dual;
I want to select the ids only one time, i.e my output will have only two rows: one row with id as 1 and other row with id 2 whatever be the description.
desired output sample:
Quote:1, aad
2, bb
I used:
select distinct(id),description from prim_tbl;
but it did not give the required result.How can I get it??
I have following tables with data as under:
table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9
I need to generate output as :
column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null
Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?
Updating multiple ROWS with different values using single statement. Requirement is to update one column in a table with the values in the other table.
Say we have 3 tables, CORPORATION,CORPORATE PROFILE and MEMBER.
Each MEMBER has CORPORATE PROFILE which in turn is associated with CORPORATION. Now I need to update MEMBER table with CORPORATION identifier for members who belong to corporations with identifiers say 'ABC' and 'DEF'.
MEMBER table contains column 'CORPIDENTIFIER '. CORPORATEPROFILE table contains MEMBERID and CORPORATIONID,this will associate a member with the corporation. CORPORATION table contains ID and CORPIDENTIFIER.
Using the below query I am getting error,ORA-01427:single-row subquery returns more than one row
UPDATE MEMBER M SET M.CORPIDENTIFIER=
(SELECT A.IDENTIFIER FROM CORPORATION A,CORPORATEPROFILE B
WHERE B.CORPORATIONID=A.ID AND B.MEMBERID=M.ID AND (A.IDENTIFIER LIKE 'ABC' OR A.IDENTIFIER LIKE 'DEF'))
Sub query in the above query returns multiple rows and hence it is throwing the error.More than one members are associated with Corporations ABC and DEF. Is there any way possible to update all the rows in single query with out iterating the result set of sub query.
The requirement I have is :
I have two tables eim_asset and eim_asset1.I want to update the table eim_asset1 using the following update SQL (Or Logic)
update eim_asset1
set emp_emp_login = (select login from s_user where row_id in
(select row_id from s_emp_per where row_id in
(select pr_emp_id from s_postn where row_id in
(select position_id from s_accnt_postn where ou_ext_id in
(select row_id from s_org_ext where row_id in
(select owner_accnt_id from s_asset where owner_accnt_id is not null)))))
It gives me the ORA error : ORA-01427:single-row subquery returns more than one row.know why I am getting it, because of the one-to-many relationship between owner accounts and their assets.
Version oracle 11g
Is there a way to avoid multiple UNION statements and get statitc values in single SQL?
select 'Week1' as term from dual
UNION
select 'Week2' as term from dual
UNION
....
....Some functionality like selecting from dynamic table column-rows.... like .. "select term from table(term(rowvalues('Week1', 'Week2'...'Week5')) from dual" or something like that
I want multiple values from a function. I want to use this function in a SQL query. Here i'm giving my try.
SQL> CREATE TABLE TEMP
2 (
3 ID NUMBER(1),
4 SAMPTYPE VARCHAR2(20 BYTE),
5 SALARY NUMBER(10)
6 )
7 /
Table created.
SQL> INSERT INTO TEMP VALUES(1,'ABC',10000);
1 row created.
SQL> INSERT INTO TEMP VALUES(2,'PQR',20000);
1 row created.
SQL> INSERT INTO TEMP VALUES(3,'JPD',5000);
1 row created.
SQL> COMMIT;
Commit complete.
[code]...
Here i get result as ABC*10000, but i want two separate values as ABC,10000. how can i do this via function.
I have a function that returns the total sum of an account. From reports I call the function passing the account code. The function sums the values for that specific account code and returns the value. In my function I have the following code :
where account_code = P_CODE.
Eg. The value of :P_CODE is 'CS'.
I now want to pass multiple account codes ('CS','TV',LJ') to the function. How do I change the IN clause in the function to accommodate multiple values.
I have tried using the instr function, but it does not work. eg. AND instr(o.ACCOUNT_CODES,','||P_CODE||',') > 0
I am using regexp_substr to break the pipe delimited string. Want to use the output as the NUMBER input to a cursor.Its not working and not getting any error also.
Here is an example.
initial input : '5545|4124|12456'
using the sql below to break the string into columns.
select regexp_substr('5545|4124|12456','[^|]+', 1, level)) from dual
connect by regexp_substr(, '[^|]+', 1, level) is not null;
But when i try to pass the output of the above query to a cursor with input defined as number, its not accepting. I tried using to_number for the output of above query.
I have search function that takes the input parameters and returns all the matching rows. this is straight forward only. My problem is having multiple types as a input parameter. that is the reason i am not able to pass the input value for these types.
My Input Type table looks like this.
CREATE OR REPLACE TYPE T_T_PARTY_REQUEST_CRITERIA
AS TABLE OF T_O_PARTY_REQUEST_CRITERIA;
/
CREATE OR REPLACE TYPE T_O_PARTY_REQUEST_CRITERIA
AS OBJECT
(
SYSTEM_IDENTIFER VARCHAR2(50),
PROCESS_TYPE VARCHAR2(50),
[code]........
Last two inputs are type again.my question here is how to pass the values for these two T_T_PARTY_KEY_IDENTIFIER and T_T_ADDTN_IDENTIFIER_INFO. I have defined the the last two types following.
CREATE OR REPLACE TYPE T_T_PARTY_KEY_IDENTIFIER
AS TABLE OF T_O_PARTY_KEY_IDENTIFIER;
/
CREATE OR REPLACE TYPE T_T_ADDTN_IDENTIFIER_INFO
AS TABLE OF T_O_ADDTN_IDENTIFIER_INFO;
/
[code].......
I have pasted the query here from my function. when i pass the null as part of input for these 2 types my query is working. otherwise it is saying invalid Identifier.First I tried with first Type.
I am passing the value as
(PRKYTP.PRTY_KEY_TYP_NM = ITTPRC.PARTY_KEY_IDENTIFIER.PARTY_KEY_TYP_NM OR ITTPRC.PARTY_KEY_IDENTIFIER.PARTY_KEY_TYP_NM = 'ALL' OR ITTPRC.PARTY_KEY_IDENTIFIER.PARTY_KEY_TYP_NM IS NULL);
Error is Error(34,147): PL/SQL: ORA-00904: "ITTPRC"."PARTY_KEY_IDENTIFIER"."PARTY_KEY_TYP_NM": invalid identifier
SELECT DISTINCT T_O_PARTY_REQUEST_IDENTIFIER(PR.PRTY_RQST_ID) BULK COLLECT INTO T_T_P_R_CRITERIA
FROM TABLE(CAST(I_T_T_PARTY_REQUEST_CRITERIA AS T_T_PARTY_REQUEST_CRITERIA)) ITTPRC,
PRTY_RQST PR
JOIN BUSN_APPLC BIAP ON BIAP.BUSN_APPLC_ID = PR.BUSN_APPLC_ID
[code]........
I have a record type and table of records as follows
type rec is record
(
empid employee.empid%type,
ename employee.ename%type
);
type tab_rec is table of rec;
Suppose data from employee table is fetched into this collection
Can you pls clarify if we can refer to all the rows of empid in the above collection ?
something like tab_rec.empid without using the subscript for referring to the nth row
My requirement isto pass this list as input parameters to a procedure(PL/SQL).
NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'
File :-mauri.export.ngf
NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc
CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......
find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.
CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
How to pass parameter in a view.
create or replace view spic_bags as
select distinct(a.item_code) Material_Code,a.uom,
(case when a.card_code = '60' then
case when a.ccn <> '091' then
[code]........
in this above view i have hardcoded dates, well how to pass parameter for this view without hardcoding? here i need to pass the date as a parameter from a select query to view the data from the above view!
In a procedure i need to pass date parameter, to check how to do this. I create a simple procedure, but it giving
PLS-00049: Bad Bind Variable 'FD'
PLS-00049: Bad Bind Variable 'TD'
How to resolve .......
CREATE PROCEDURE pass_date_parameter
AS
fd VARCHAR2 (10) DEFAULT '01-01-2010';
td VARCHAR2 (10) DEFAULT '01-06-2010';
ffd VARCHAR2 (10) DEFAULT '01-01-2010';
ttd VARCHAR2 (10) DEFAULT '01-06-2010';
[Code]....
I'm using Oracle 9i. How can i check for a string if it contains only numbers or letters? Otherwise i should return false.
View 6 Replies View Relatedi am trying to create a procedure that will take an input and based on that input it will delete or update a row, if the input is wrong we will dbs_ouput a line telling the user to enter the input again. can i have a input parameter in a procedure and insert multiple rows with a single insert on a procedure?
View 6 Replies View Related