OraFAQ Forum: SQL & PL/SQL » Invalid value for query of REF CURSOR PARAMETER

Home » SQL & PL/SQL » SQL & PL/SQL » Invalid value for query of REF CURSOR PARAMETER

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Invalid value for query of REF CURSOR PARAMETER [message #182633]

Mon, 17 July 2006 07:41

sam_dinesh
Messages: 18
Registered: February 2006
Location: india

Junior Member

Hi
When i executed the procedure using DBMS_XMLGEN.newContext,i am getting

ORA-20222 ERROR ORA-10296 Invalid value for query of REF CURSOR PARAMETER

can you help me to solve the problem?

Report message to a moderator

Re: Invalid value for query of REF CURSOR PARAMETER [message #182638 is a reply to message #182633]

Mon, 17 July 2006 07:52

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

<AskTom>
My car wouldn't start last week. Can anyone help
</AskTom>

Can you give us a little more to work with?

A concise test case that contains everything we need to reproduce the problems would be good (Create table & insert statements if relevant, and a small pl/sql block that produces the error).

Failing that, you could at least show us the code that is produceing the error.

My guess is you'll go for the second of those choices, but I'm prepared to be suprised.

Report message to a moderator

Re: Invalid value for query of REF CURSOR PARAMETER [message #182674 is a reply to message #182633]

Mon, 17 July 2006 09:35

sam_dinesh
Messages: 18
Registered: February 2006
Location: india

Junior Member

When i try to use the following SQL query in procedure,i am getting
ORA-20222 ERROR ORA-10296 Invalid value for query or REF CURSOR PARAMETER

CREATE OR REPLACE PROCEDURE Prc_Get_Ref_Subject_old(v_get_result OUT CLOB) IS IS

v_sql VARCHAR2(4000);
qryCtx DBMS_XMLGEN.ctxHandle;
BEGIN

DBMS_APPLICATION_INFO.SET_MODULE
(module_name => 'REFERRAL SUBJECT LETTER',
action_name => 'SELECTING REFERRAL SUBJECT INFO');

v_sql := ' SELECT EEG.* FROM REFERRAL_EMP_GROUP_LINK REL,ENTP.EMPLOYER_GROUP EEG, '||
' ENTP.EMPLOYER_GROUP_ADDRESS EEGADDR WHERE EEG.EMP_GROUP_ID=REL.EMP_GROUP_ID '||
' AND eegaddr.EMP_GROUP_ID(+) = eeg.EMP_GROUP_ID AND EEG.EMP_GROUP_ID= 98 ';

qryCtx := DBMS_XMLGEN.newContext(v_sql);
DBMS_XMLGEN.setConvertSpecialChars(qryCtx, TRUE);
v_get_result := DBMS_XMLGEN.getXML(qryCtx);
DBMS_XMLGEN.closeContext(qryCtx);
DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20222, 'ERROR: ' || SQLERRM);
DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END Prc_Get_Ref_Subject_old;

Report message to a moderator

Re: Invalid value for query of REF CURSOR PARAMETER [message #182679 is a reply to message #182674]

Mon, 17 July 2006 09:51

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Ah well. No suprises for me today. I'd love to try runniong the code to reproduce the error myself, but as I don't have table definitions or test data, I'll just pluck a few suggestions out of the air

1)
I can find no reference anywhere to an ORA-10296. Can you just double check that that is the error returned.

2)
What happens if you run the query from SQL*Plus - does it return any data

3) What line is the code erroring at?

4) What version of Oracle are you using?

[Updated on: Mon, 17 July 2006 09:55]

Report message to a moderator

Re: Invalid value for query of REF CURSOR PARAMETER [message #182701 is a reply to message #182679]

Mon, 17 July 2006 11:39

sam_dinesh
Messages: 18
Registered: February 2006
Location: india

Junior Member

I am sorry for gave the wrong ora err no.

1.error no is 19206

2.Query is retuning rows in SQL Plus

3.Error at line 7

4.Version is 9.2.0.1.0

Thanks

Report message to a moderator

Re: Invalid value for query of REF CURSOR PARAMETER [message #182767 is a reply to message #182701]

Tue, 18 July 2006 02:23

sam_dinesh
Messages: 18
Registered: February 2006
Location: india

Junior Member

I thought ENTP.EMPLOYER_GROUP_ADDRESS EEGADDR is a long string.so for ENTP.EMPLOYER_GROUP_ADDRESS, I have created a view EE1.After using EE1, I found proc is working fine. Is my assumption correct?

Changed Query

v_sql := ' SELECT EEG.SUBS_EMP_GROUP_NAME AS "SUBJ_LNAME",EEGADDR.EMP_GROUP_ADD1 AS "SUBJ_ADD1", '||
' EEGADDR.EMP_GROUP_ADD2 AS "SUBJ_ADDR2",EEGADDR.EMP_GROUP_CITY AS "SUBJ_CITY", '||
' EEGADDR.EMP_GROUP_STATE AS "SUBJ_STATE",EEGADDR.EMP_GROUP_ZIP AS "SUBJ_ZIP" '||
' FROM ENTP.EMPLOYER_GROUP EEG,ENTP.EE1 EEGADDR '||
' WHERE EEG.EMP_GROUP_ID= 98 AND eegaddr.EMP_GROUP_ID(+) = eeg.EMP_GROUP_ID ';

Report message to a moderator

Re: Invalid value for query of REF CURSOR PARAMETER [message #182769 is a reply to message #182767]

Tue, 18 July 2006 02:40

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

As you still haven't bothered to give us any test data or table creation scripts, I have no idea why the changes you have made might have made a difference.

I would say that it is very unlikely to be the length of the table name that is causing the problem, but if you've got code that works, then go with it.

Next time you might want to consider actually providing us with the information we ask for, and that way you might get the help you want.

One other comment about you code - In the When Others block, your call to DBMS_APPLICATION_INFO will never happen, as it is after the RAISE_APPLICATION_ERROR call.

Report message to a moderator

Re: Invalid value for query of REF CURSOR PARAMETER [message #182776 is a reply to message #182769]

Tue, 18 July 2006 02:50

sam_dinesh
Messages: 18
Registered: February 2006
Location: india

Junior Member

I am not still happy with the changes i have made.could u tell exactly what dbms_xmlgen.newcontext() does?

Thanks

Report message to a moderator

Re: Invalid value for query of REF CURSOR PARAMETER [message #182779 is a reply to message #182776]

Tue, 18 July 2006 02:59

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

The answer to that question would be best found in the documentation

Report message to a moderator

Re: Invalid value for query of REF CURSOR PARAMETER [message #182781 is a reply to message #182776]

Tue, 18 July 2006 03:11

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Here's a thought.

Your code is specifying schema names for the table, so I'm guessing that the tables are in a schema other than the one owned by your current user.

Are your privileges on these tables granted via a role?
If they are, could you try granting them directly and seeing if you still get the problem occurring?

Report message to a moderator

Re: Invalid value for query of REF CURSOR PARAMETER [message #182782 is a reply to message #182781]

Tue, 18 July 2006 03:18

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Here's another thought, and one I should have had earlier.

The description of your error is :

Quote:

ORA-19206 Invalid value for query or REF CURSOR parameter
Cause: The queryString argument passed to DBMS_XMLGEN.newContext was not a valid query or REF CURSOR.

Action: Rewrite the query so that the queryString argument is a valid query or REF CURSOR.

The second query you posted is very different to the first (different columns, different number of tables etc etc).

Can you check that your first query (the one in the execute immediate in the OP) is a valid query and returns results.

If it wasn't valid, that would explain the problem quite nicely.

[Updated on: Tue, 18 July 2006 03:18]

Report message to a moderator

Re: Invalid value for query of REF CURSOR PARAMETER [message #182801 is a reply to message #182782]

Tue, 18 July 2006 04:52

sam_dinesh
Messages: 18
Registered: February 2006
Location: india

Junior Member

Thank you for your valuable suggestion JROW.I gave the grant permission from another schema(in my case ENTP) to current schema.Then procedure works fine.

Thank you once again

Thanks
Sam

Report message to a moderator

Previous Topic:	how can i do that in SQL server...
Next Topic:	Oracle 9 doesnt work with back date

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 05 06:36:24 CDT 2026