SQL & PL/SQL :: Check If Element Present In Defined Column Of Cursor?

Apr 29, 2010

i want to produce a function who return XmlType element if she checks that a parameter (that i passed to here is contained in a row in a defined table)

for example

create table sec_form(
form_code VARCHAR2(8)
, sec_code VARCHAR2(5)
) ;
insert into sec_form values ('CSA','section1');
insert into sec_form values ('CSA','section2');
insert into sec_form values ('CSA','section3');

then i got my function

create or replace function checkIfSectionEnabled (elem XmlType,name_section varchar2)
return XmlType
is v XmlType;
CURSOR myCursor IS select * from sec_form where form_code = 'CSA';
begin
open myCursor;
loop

[code]....

now i'm gonna call the function (elem XmlType,name_section varchar2) and pass here "section1" for example ; and she must return me my xmltype element if she find "section1" finds section1 present;

when i try t compile i got:

Error(18,13): PL/SQL: Statement ignored
Error(18,26): PLS-00225: subprogram or cursor 'MYCURSOR' reference is out of scope

View 3 Replies


ADVERTISEMENT

Forms :: 10g - Debug And Check Values Of Defined Variables

Sep 22, 2010

I am currently working in form 10g and i m newbie to that technology...I want to debug my form and also check step step by values of variables which i defined.

View 1 Replies View Related

SQL & PL/SQL :: Cursor Defined To Retrieve Row By Row

May 26, 2011

In SQL server the cursor is defined to retrieve row by row and process it. We can fetch records as FIRST, LAST, NEXT, PREVIOUS and SCROLL like that. Can we do the above in oracle. Else what is the use of cursor in Oracle. I know that it is a private area in which the records are processed.

View 6 Replies View Related

XML DB :: Insert Element Into Data Stored In CLOB Column?

Dec 14, 2012

My ORACLE DB version is:

('Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production');
('PL/SQL Release 11.2.0.2.0 - Production');
('CORE 11.2.0.2.0 Production');
('TNS for Linux: Version 11.2.0.2.0 - Production');
('NLSRTL Version 11.2.0.2.0 - Production');

I have this XML data stored in a CLOB column:

<Activity>
<Changes>     
</Changes>
<Inserts>     
</Inserts>
<Definition>     

[code]....

I need creating an update that will insert another <Activity>SomeGUID</Activity> into the <Spawned> parent.

View 3 Replies View Related

SQL & PL/SQL :: Values Which Are Not Present In Column

Jun 2, 2011

I am trying to get only those values which are not present in the column. I have tried but i think that i am wrong in logic.

SELECT dummy
FROM DUAL
WHERE dummy IN
('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12',
'13')
AND NOT EXISTS (
SELECT pay_list_serial
FROM per_requests_master
WHERE request_status IN ('4', '5')
AND pay_list_no = :list_no)

View 12 Replies View Related

SQL & PL/SQL :: Mask A Column Value Present In Production

Nov 27, 2012

How we can mask a column value present in the production which is very sesitive like bank a/c no,Credit card nos etc while replicating to the test/development envirnoments in PL/SQL.Things that need to be considered is referential integrity,data types,length of the column and performace while execuing the code.

View 2 Replies View Related

JDeveloper, Java & XML :: How To Add Some Value In Already Present Node In Clob Column

Jan 31, 2012

adding some value in xml type clob column, for ex: xml_tab table has more than 100 rows, below i have created some rows

Quote:create table xml_tab(id number, ex_xml clob);

select * from xml_tab;

1 <CData CI="1111" <Dist> <value desc="25">25 </value> <value desc="30">30 </value> <value desc="35">35 </value> <value desc="40">40 </value> </Dist> <Year IsReadOnly="Y"> <value descr="Old"><1995 </value> <value>1995 </value> <value>1996 </value> <value>1997 </value> <value>1998 </value> <value>1999 </value> <value>2000 </value> <value>2001 </value> <value>2002 </value> <value>2003 </value> <value>2004 </value> <value>2005 </value> <value>2006 </value> <value>2007 </value> <value>2008 </value> <value>2009 </value> <value>2010 </value> </Year> </CData>

[Code]....

View 5 Replies View Related

Reports & Discoverer :: How To Present Report In XLS Format With Proper Column Headings

Dec 12, 2011

I have a report and require the output in xls/xlsx (Excel) format. Currently following properties under System Parameters in Report Builder 6i have been made

Desname = The path for e.g: \C:sz-serverc$ihelp_workingxls
pt_sample_report.xls
Desformat = Delimited
Destype = File

The rest parameters has default settings I have not changed any of the rest User parameter.The report comes up in excel format but totally in distorted manner. i.e; All the column names of report are displayed first in vertical format followed by actual rows for the report.

The requirement is like this

[Select all] [Show/ hide]
Student ID Student Name Fathers Name Guardian Name
1 sample1 sample2 sample3
2 sample4 sample5 sample6

And it is displaying like this

Student ID Student Name Fathers Name Guardian Name 1 sample1 sample2 sample3
Student ID Student Name Fathers Name Guardian Name 2 sample4 sample5 sample6

View 2 Replies View Related

PL/SQL :: Getting Column Defined Error?

Oct 30, 2012

I dont know where was the mistake, getting column ambiguously defined error

CREATE OR REPLACE PROCEDURE FLXUSER.testgg
AS
BEGIN
/*----------------------------------------------------------------------------------------------------------     
----------------------------------------------------------------------------------------------------------*/       
    BEGIN                              
    
[code]....

View 7 Replies View Related

How To Check Cursor Stats At Run Time

Sep 24, 2011

I have a stored procedure running in which there is a cursor which fetches around 1500000 records and then query another table using the fetched record values.

I cannot modify the procedure as its on production. I want to know which cursor record is currently being processed by the procedure, and how many are still remaining ? How to check the cursor stats at runtime. I want to check up to which record the cursor has been fetched and how many are still remaining. I have cursor name. Is there some dynamic view to check cursor stats at runtime ?

View 1 Replies View Related

SQL & PL/SQL :: How To Check If Result Of Cursor Is Empty

May 20, 2010

How can i check if the result of a defined cursor is empty?

View 6 Replies View Related

SQL & PL/SQL :: How To Check Cursor Having Record Or Null

Jun 22, 2010

i want to check cursor having record or null/ if having records then count of records?

View 5 Replies View Related

PL/SQL :: Getting ORA-00918 Column Ambiguously Defined Error

Jun 20, 2012

I am getting ORA-00918: column ambiguously defined error when running the below SQL query. I am gettig this error at the last line of code where v1.objectid=v2.parent_object_id

I have not written this query so i find difficult to debug it. Also i am not that well verse with SQL

select distinct v1.UNIQUE_DOC_NAME,v1.UNIQUE_DOC_NAME_1,v1.vendor_object_name,v1.display_name,v1.document_description,

[Code].....

View 10 Replies View Related

SQL & PL/SQL :: ORA-30556 - Functional Index Is Defined On Column To Be Modified

Feb 2, 2012

I'm altering a column length to increase the size and getting "ORA-30556: functional index is defined on the column to be modified".

On searching more about this error, it seems like the function index must be dropped before altering the column.The table I'm dealing with is huge.

Question 1:In case of dropping and recreating the index, should the following steps be done:

- Drop Index
- Alter the column to increase the size
- Recreate the index with NOLOGGING and NOPARALLEL clause
- ALTER INDEX to turn on LOGGING
- Gather Statistics on that index

Question 2:Is there anything else that should be done when the index is dropped and re-created?

Question 3:What are the side-effects of carrying out the above steps in a huge table with around 15 million rows?

Question 4:Would it work if I disable the index, alter the column and reenable the index?Do I have to rebuild the index and gather Stats upon reenabling it?

View 2 Replies View Related

Reports & Discoverer :: Error Code ORA-00918 Column Ambiguously Defined

Jul 18, 2013

I have written this code below in Disco Admin 10g but get Ora-00918 column ambiguously defined error even though I have used alias in the table name.

Select

porh.segment1
,Porh.creation_date
,Porh.last_update_date
,porh.closed_code
,porh.authorization_status
,porh.description
,porh.note_to_authorizer
,porh.cancel_flag
,porh.enabled_flag
,porl.line_num
[code]....

View 4 Replies View Related

SQL & PL/SQL :: How To Do Spell Check Of A Column

Nov 3, 2010

how to run a spell check on a column (contains desriptions) of a table and find out if any spelling mistakes are there in the text of that column. I want to run this in pl/sql.

View 8 Replies View Related

SQL & PL/SQL :: Check If Multiple Column Values Are Same?

Sep 16, 2011

I have a table as follows

create table teststr (indname varchar2(20),
counter1 number,counter2 number,counter3 number,counter4 number);
insert into teststr values('a',10,20,30,30);
insert into teststr values('b',10,20,5,3);
insert into teststr values('c',2,4,5,2);
insert into teststr values('d',1,2,3,4);
insert into teststr values('e',4,5,4,4);

Now i need the output if any of the column values are same.

output should be

select indname from teststr where counter1=counter2
or counter1=counter3 or counter1=counter4
or counter2=counter3 or counter2=counter4
or counter3=counter4

a
c
e

Is ther any other way to write the query instead of the numerous or conditions if i want to compare the column values in a table.

View 6 Replies View Related

SQL & PL/SQL :: Date Check On A Number Column

May 16, 2011

I have a column defined as Number( 8 ) which is supposed to have date values. I would like to check if all the rows in that table have valid dates. We could use to_date(coulmn_name, 'YYYYMMDD') and catch the rownums for error conditions using pl/sql. I would like to know if we could just do it using sql only and return the row numbers for those that are invalid dates?

View 5 Replies View Related

SQL & PL/SQL :: Is NOT NULL Needed If A CHECK Constraint Is On Column

Dec 13, 2010

In the below code, do I need the 'NOT NULL' after the 'state char(2)'? I am guessing that I do not need it since I have the CHECK constraint on the column.

CREATE TABLE employee(
id PRIMARY KEY,
first varchar(20) NOT NULL,
middle varchar(20),
[code]....

View 10 Replies View Related

PL/SQL :: Check For Any Duplicate Values In COUNT() Column

May 13, 2013

I'm going to do some testing, and for that I require to retrieve some data based on a single column e.g test_data_col, which -

1. Has 3 or more count(test_data_col) for a given set of group by columns e.g grp_col1, grp_col2, grp_col3
2. Within the set of rows retrieved, that particular column holds some duplicate values. I don't need the duplicates displayed, just know if duplicates exist or not.

This might explain what I'm trying to do -

grp_col1, grp_col2, grp_col3, test_data_col

1, A, xyz, HELLO
1, A, xyz, HELLO
1, A, xyz, BYE
1, A, xyz, GOODBYE

2, C, pqr, WELCOME
2, C, pqr, GOOD MORNING
2, C, pqr, BAD MORNING

So for condition 1, I do something like this -

SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;

In this same query, I want to do something that will tell me if the aggregate COUNT(test_data_col) has any duplicate values within it. Again, displaying the duplicates is not important here.

SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3,
/*some logic*/ dup_val
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;With the proper coding to replace /*some logic*/, I get following values -

cnt, grp_col_1, grp_col2, grp_col3, dup_val

4, 1, A, xyz, Y
3, 2, C, pqr, N

I just gave dup_val column to explain what I'm trying to achieve.. any other way to know the existence of duplicates in the count aggregate will be fine.My Oracle version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0

View 2 Replies View Related

SQL & PL/SQL :: Check Constraint To Disallow Number In Varchar2 Column

Apr 21, 2010

I have table customer which contains a column CUSTOMER_FIRST_NAME

CUSTOMER_FIRST_NAME VARCHAR2(50)

What will be sql statement to add a constraint on the CUSTOMER_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers ?

View 34 Replies View Related

PL/SQL :: Check If Data Stored In Varchar2 Type Column Is Date Or Not

Jun 29, 2012

Is there a seeded function by which I can check all the rows which stored dates in varchar column.

I have a table say test (test_data varchar2(100));

Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only

INSERT INTO test(1);
INSERT INTO test('ABC');
INSERT INTO test(SYSDATE);
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY'));
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test('15/01/2012');
...
commit;

View 17 Replies View Related

SQL & PL/SQL :: How To Get Column Info From Ref Cursor

Feb 20, 2013

i know the procedure "DBMS_SQL.DESCRIBE_COLUMNS" and the example,the example uses the "dbms_sql.open_cursor",not the ref cursor. so how to get the information of ref cursor by using describe_columns? or how to get the information of ref cursor by other?

View 4 Replies View Related

PL/SQL :: Dynamic Column In A Cursor

Sep 1, 2012

So, for lack of a better way to explain it, I need to do the following.

I need a EXECUTE IMMEDIATE to be able to use a CURSOR but its saying its not declared. Is their a way to pass the cursor so a Execute Immediate can use it...

V_SQL := 'REPLACE(V_DATA,'TEST',CUR_DATA.'||V_FIELD||');';
EXECUTE IMMEDIATE (V_SQL);

I know thats just a small part... but it shows the end goal. I need to append a VARCHAR that has the COLUMN NAME I need from the CURSOR. CUR_DATA is the open cursor. V_DATA is just a VARCHAR with text in it.

View 6 Replies View Related

SQL & PL/SQL :: Cursor To Fetch Column Names

Jul 25, 2013

I have the following piece of

CREATE OR REPLACE PROCEDURE COMP_RECORDS
IS
l_query VARCHAR2 (10000) := '';

CURSOR TBL1
IS
SELECT TABLE_NAME, COLUMN_NAME FROM COLS_TO_COMP WHERE TABLE_NAME='ACE_HIST';
TBL1_REC APP.COLS_TO_COMP%rowtype;

[Code]..

However I am getting an ORA-00923 exception with message as "FROM keyword not found where expected". know if I can/cannot use a cursor to fetch column names for a table?

View 9 Replies View Related

SQL & PL/SQL :: Passing Column Name To Cursor At Runtime?

Apr 15, 2013

in below program,i have to pass column name to cursor at runtime..but this giving error.

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
emp_record hr.employees%rowtYPE;
v_stmt_str VARCHAR2(200);
colname varchar2(10):='Last_name';
BEGIN

[code]....

dbms_output.put_line(emp_record);
*
ERROR at line 13:
ORA-06550: line 13, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 13, column 1:
PL/SQL: Statement ignored

View 2 Replies View Related

Passing Column In Cursor For Loop

Aug 8, 2012

How can we pass column index or column name in cursor for loop.

View 3 Replies View Related

Forms :: Put One Check Box To Check All The Check Boxes?

Jul 30, 2011

I want to put one check box to check all the check boxes.how can i do this?

View 5 Replies View Related

SQL & PL/SQL :: Ref Cursor As In Parameter / How To Fetch Column Values

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

Does Column V$SQL.CHILD_NUMBER Related On Number Of Current Cursor Only

Jan 11, 2013

Does the column V$SQL.CHILD_NUMBER related on number of current cursor only ? V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text enteredI see in V$SQL big numbers in V$SQL.CHILD_NUMBER ( HASH_VALUE and PLAN_ HASH_VALUE is in my quewry fixed ) , am I wrong that this column displayed not summary count of "objects"(in terminology of Steeve. URL....

View 0 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved