SQL & PL/SQL :: Ref Cursor - Values To Be Passed Dynamically
Sep 26, 2011
I have used ref cursor to retrun output in cursor from procedure.I have used bind variables in the v_query and passing values with using clause as given in the following code. It is working but client want to pass values dynamically.
OPEN p_cur FOR v_query
USING p_ht_nm, p_ht_treat_source, var_cycle (1),
var_cycle (2),
var_cycle (3),
var_cycle (4),
var_cycle (5),
var_cycle (6),
var_cycle (7),
[code]...
We can also use execute immediate with the above code. But in both the cases we have to pass values in using clause.
QUERY:
/* Formatted on 2011/09/24 21:13 (Formatter Plus v4.8. */
INSERT INTO z_ca_get_lot_id
SELECT DISTINCT attrbts.lot_id
FROM (SELECT DISTINCT lot_id
FROM z_alv_cert_lot_attrbts_syn
WHERE NAME = 'Heat'
AND UPPER (text_value) = :p_ht_nm) attrbts,
[code]...
View 8 Replies
ADVERTISEMENT
May 7, 2010
I'm trying to figure out how to process a string of values that is being passed from Cold Fusion.
procedure test (Names in varchar2) is The Names variable from the Cold Fusion page would have values like Joe1,Joe2,Joe3,Joe4.
I need to loop through the Names variable (Joe1 then Joe2 then Joe3 and so on) and insert each one into a table. how to do that within the procedure?
View 5 Replies
View Related
Nov 16, 2010
I was looking for a way to see if a default value for a procedure was passed NULL or it got NULL by default. [URL]
View 11 Replies
View Related
Aug 5, 2013
I am trying a pl/sql block which which take a string and execute it dynamically. Suppose below is string
M_COL := Q'[(P_CODE=> ':DEPTNO',P_CODE_TYPE => 'STATE')]';
Now trying to execute it. using below
M_STR := 'SELECT CHK_DEPT' || M_COL || ' FROM EMP WHERE EMPNO=''7499''';
EXECUTE IMMEDIATE M_STR
INTO M_DATE;
Now what i want is M_STR vairable to executed as
SELECT CHK_DEPT(P_CODE=> DEPTNO,P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
instead of
SELECT CHK_DEPT(P_CODE=> 'DEPTNO',P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
other in other way in parameter P_CODE Column value of DEPTNO should be passed.Also note that DEPTNO column in string M_COL is Dynamic.i.e
M_COL := Q'[(P_CODE=> ':DEPTID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':EMP_ID',P_CODE_TYPE => 'STATE')]';
View 9 Replies
View Related
Aug 15, 2013
i need to get values from lookup table dynamically,i am getting the missing keyword error.
create or replace procedure xyz(cur1 OUT SYS_REFCURSOR)
AS
vsql varchar2(2000);
CURSOR CURSOR1 is SELECT DQS_SRC_COL_NM,LDIC_SEQUENCE FROM look_up WHERE LDIC_SOR ='friend';
BEGIN
[code]....
View 14 Replies
View Related
Nov 12, 2012
need to dynamically assign value of each column of a row type variable:The example is like that:
Table "Student_list" is as follows:
---------
COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50)
[code]....
Is there any work around where i can dynamically built my variable or table.column name and assign a value to it?
View 7 Replies
View Related
Jul 31, 2013
how to assign values from a "rule table" to a rowtype-variable. The ruletable contains values for different columns in different tables.Now i need to assign those given values for given columns out of that rule table to the equivalent column in a rowtype-variable.
CREATE TABLE TBRULES
(
TABLE VARCHAR2(50 BYTE)
, COLUMN VARCHAR2(50 BYTE)
, VALUE VARCHAR2(200 BYTE)
);
[code].....
View 15 Replies
View Related
Feb 17, 2011
My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
View 10 Replies
View Related
Jun 28, 2012
I'm trying to update a number field on an apex page by summing up the values of multiple page items. I've tried to follow this tutorial
[URL]........
especially the "Create a Set Value Dynamic Action Using PL/SQL" part.
In the tutorial example they return an page item value P3_SAL multiplied by a multiplier they've determined by another page item's case. What I want to do is much simpler in that I just want to add up multiple page item values and then display them in a number field at the bottom of the page.
so the code I tried to use based on the tutorial is in the tab: Home >Application Builder>Application 103>Page 3>Edit Dynamic Action>Create / Edit Action
Set Type: PL/SQL Function Body
PL/SQL Function Body:
BEGIN
return :P3_ITEM1+P3_ITEM2+P3_ITEM3;
END;
Page Items to Submit: P3_ITEM1,P3_ITEM2,P3_ITEM3
But it gives me an error that I need to declare the identifiers, yet it works if I only use one item and I can perform any arithmetic.
i.e.
BEGIN
return :P3_ITEM1+1000;
END;
View 2 Replies
View Related
Nov 19, 2011
I am having a scenario where i have a ref cursor opening and fetching though dynamic sql query. And those values which i get from ref cursor i want to use them for other parameter cursor in for loop.
for example
PROCEDURE script (
p_account_no IN VARCHAR2,
p_from_date IN DATE,
p_to_date IN DATE,
p_subledger_code IN VARCHAR2,
p_posted IN VARCHAR2,
v_alloc_unalloc IN OUT alloc_unalloc, -- ref cursor declared in package specification.
[code]..........
View 12 Replies
View Related
Apr 21, 2011
oracle 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 have a cursor in my procedure. When I OPEN, FETCH, it doesnt give me any values. But instead of cursor,if I use the sql , used in the same cursor, then i am getting the value.
DECLARE
l_vin CLM_MAIN.vin%TYPE;
l_part CLM_MAIN.vin%TYPE;
l_clm_id CLM_MAIN.vin%TYPE;
[Code]....
Clm_main has a UNIQUE constrains, of VIN, PART. So when i OPEN the cursor, its not finding clm_id , eventhough it exist in the table. so it takes it as claims_cur%NOTFOUND, an tries to INSERT in the table. But since the record with that VIN and PART already exists, it throws exception that ORA-00001: unique constraint (CLM_MAIN_UK) violated.
View 25 Replies
View Related
Aug 12, 2010
I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.
what parameter or code I should used ?
I have marked with color row as below.
DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;
[Code]....
View 6 Replies
View Related
Sep 21, 2010
i have a proc that is taking p_serial_number refsursor as in parameter. the structure of p_serial_number is required to be
mfg_prod_cdchar (3 byte)
mfg_prod_seq_no char (6 byte)
How do I need to define this ref cursor ? and when I use it in my Procedure how do I fetch the column values ?
View 6 Replies
View Related
Mar 22, 2010
Is it possible to pass a nested select statement as a parameter to a procedure? In other words, I am currently successfully doing this:
SELECT BASE_PAY INTO N FROM EMPLOYEES WHERE ID=p_ID;
SetPackageValueProc(PkgID,'YearlyBasePay',N);
And I'd like to do something along the lines of this:
SetPackageValueProc(PkgID,'YearlyBasePay',(SELECT BASE_PAY FROM EMPLOYEES WHERE ID=p_ID));
View 21 Replies
View Related
Jan 7, 2011
I keep on getting the Invalid cipher error whenever I do a call to DBMS_CRYPTO.Decrypt The flavor I am calling is the one taking the parameter list below:
(dst in out Blob,src in Blob,typ in pls_integer, key in raw,iv in raw)
So the issue happens on the third parameter: typ. It is a constant defined in the package representing the decryption algorithm I intend to use (AES256 in my case). So the call goes something like that in my pl/sql
dbms_crypto.Decrypt(vdecrypted, vcrypted,dbms_crypto.encrypt_aes256,vkey);
but it won't work. I also tried setting a variable like :
my_algo := dbms_crypto.encrypt_aes256;
and using it in the call but that gave the same result.
View 6 Replies
View Related
Jul 15, 2011
I have developed a RDF report which when linked to my application called OTM, it returns data which are not expected.
My report has two lexical parameters which are of date variables.
When i run the report from my application it asks for the parameter valeus and when i specify it, the report is not returnign the data within the date range i specified. it returns me all the data.
View 3 Replies
View Related
Sep 5, 2011
The below query i am getting error invalid arguments passed, but i have double checked the query for arguments.
SELECT SUM(DECODE
( flv.lookup_code,
NULL, DECODE ( SIGN ( NVL ( qlv.operand), 0 )
- 2 ) ,
[code]....
View 4 Replies
View Related
Oct 7, 2011
I have a requirement to extract data from an XML formatted string that is passed as an IN parameter to a database stored procedure. I do not have a table to store the XML, nor an XML schema, nor a namespace, just the XML string. While new to XML, I do understand the basics. Using XMLTable I am able to parse out the simplest of XML data. My problem is this; attempting to extract data from nested XML nodes results in the error
'ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence'.
Testing a solution example I found on this site by Barbara Boehmer, I still get the ORA-19279 error. I suspect it may be because I have version 10g while she has version 11g. Her solutions is from #msg_514154. Here is that code that gives me the error:
SCOTT@orcl_11gR2> SELECT x."deptno"
2 FROM (SELECT '<departments><deptno>1</deptno><deptno>2</deptno></departments>' col FROM dual) xml_data,
3 XMLTABLE
4 ('/departments/deptno'
5 PASSING XMLTYPE (xml_data.col)
6 COLUMNS
7 "deptno" number path '/deptno') x
8 /
deptno
----------
1
2
2 rows selected.
As I said, this example generates the ORA-19279 error. Need processing XML data from an IN parameter?
View 5 Replies
View Related
Mar 16, 2013
After a power failure, I have now dropped disks in my 2 diskgroups DG_RACDATA1 and OCRVD.
Disks has been renamed and prefixed by DROPPED...
Their state is now "FORCING"
Mount status = missing
header status = unknown
Mode status = Offline
These disks was belonging to a failure group with redundancy so the cluster still works ...but I have lost the redundancy.
How to reuse these offline disk or confirm that what I plan to do is good:
Shall I put them online ? ALTER DISKGROUP DG_RACDATA1 ONLINE DISK DROPPED0000_DG_RACDATA1
Shall I drop them ? ALTER DISKGROUP DG_RACDATA1 DROP DISK DROPPED0000_DG_RACDATA1 FORCE
and then: zeroing them and readd to failgroup ? but how ?
View 4 Replies
View Related
Jul 24, 2013
The OUI threw the subject error during installation of Grid Infrastructure 12.1.0.1 on OEL 6.4 with Job Role Separation. This occurred after successfully running root.sh when the OUI started the NETCA task . The solution was very simple: go to /u01/app/12.1.0 and "chmod g+w grid". At that point I was able to click "retry" and it worked perfectly. There were no other errors or issues during the entire installation. Just passing it along.
View 0 Replies
View Related
Sep 17, 2010
Table A
Id Country city
1 US
2 US Boston
3 Boston
4 US Newyork
5 London
6 Japan Tokyo
Im looking for a query which returns results based on both city and country passed.
If i pass country US and city Boston it should return row2 with US and Boston row
If i pass country null and city Boston it should return row3
If i pass country UK and city Boston it should return row3
If i pass country UK and city London it should return row5
i.e. If country/city combination exists in DB return that row Else city row should be returned.
View 5 Replies
View Related
Sep 7, 2007
I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR;
PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER);
END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid = 7
and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 4
opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 11 <----- 7+
opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors)
opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 53 <----- 17+
opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
View 1 Replies
View Related
Feb 25, 2011
Is it possible to:
-define a cursor with bind variables
-get a cursor record from these cursor
-and pass the bind variable in the OPEN clause
Did'nt succeed as shown in the example.
SET SERVEROUTPUT ON SIZE 900000;
DECLARE
--works fine
CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3;
--doesn't work
--CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1;
crec c1%rowtype;
BEGIN
--works fine
OPEN c1;
--isn't possible ?
--OPEN c1 USING 3;
[Code]....
View 3 Replies
View Related
Sep 11, 2011
just looking around to use the new feature available in oracle 11g to convert the dbms_sql numeric cursor to reference cursor, how to do it?
parse and execute the sql string first with dbms_sql and then convert it to ref cursor?
View 1 Replies
View Related
Nov 23, 2011
I want to return ref cursor based on explicit cursors
create table jumbo(id number, name varchar2(20));
insert into jumbo values(1,'jumbo');
create table mumbo(id number, name varchar2(20));
insert into mumbo values(1,'mumbo');
commit;
[Code].....
The above procedure has compilation errors when I am trying to open ref cursor
LINE/COL ERROR
-------- --------------------------------------------------------
20/24 PL/SQL: SQL Statement ignored
20/38 PL/SQL: ORA-00942: table or view does not exist
32/24 PL/SQL: SQL Statement ignored
32/38 PL/SQL: ORA-00942: table or view does not exist
SQL>
View 5 Replies
View Related
Aug 7, 2013
Can i user exist when cursor will using For Cursor .
View 15 Replies
View Related
Jul 25, 2013
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies
View Related
Dec 3, 2012
below SQL I'm pulling data manually(by entering specialist name) instead of that I want this query to be modified so that the sys is pulled dynamically, I.e. like select specialist from t.
CODESELECT
type, Hari,Rakesh,Santhosh, Total
FROM (
select
(type), max( decode( SPECIALIST, 'Hari', cnt, null ) ) Hari,
max( decode( SPECIALIST, 'Rakesh', cnt, null ) ) Rakesh,
max( decode( SPECIALIST, 'Santhosh', cnt, null ) ) Santhosh,
Sum(cnt) total
from (
select
[Code]...
View 1 Replies
View Related
Dec 3, 2010
I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868
View 3 Replies
View Related
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
