Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL runtime issue (oracle 9i/8i)
PL/SQL runtime issue [message #425061] Wed, 07 October 2009 06:28 Go to next message
sharathmpatil
Messages: 38
Registered: February 2008
Location: Bangalore
Member

Hi,

we are facing the problem when executing below procedure,
when we pass the value holding in the variable hold_select_query1 into the query we are ending up in exception , even though data is present.

CREATE OR REPLACE PROCEDURE vij(hold_query VARCHAR2)
IS
hold_select_query VARCHAR2(1000);
hold_select_query1 VARCHAR2(1000);
var VARCHAR2(1000);
v_party_cd varchar2(10) :='BANG';
v_CITY_CD varchar2(10):='BAN';


BEGIN
hold_select_query1:=v_party_cd ;
hold_select_query1 := hold_select_query1 ||' and CITY_CD='|| v_CITY_CD;

DBMS_OUTPUT.PUT_LINE(hold_select_query1);

hold_select_query:='select comp_name into var from sysparam where party_cd='||hold_select_query1 ;

DBMS_OUTPUT.PUT_LINE(hold_select_query);

begin
select comp_name into var from sysparam where party_cd=hold_select_query1;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('no data found');
end;

DBMS_OUTPUT.PUT_LINE(var);

END;
/
Re: PL/SQL runtime issue [message #425063 is a reply to message #425061] Wed, 07 October 2009 06:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What error are you getting, and what is the output?

Are you expecting that the query in hold_select_query1 is going to get executed at some point?

Re: PL/SQL runtime issue [message #425065 is a reply to message #425061] Wed, 07 October 2009 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: PL/SQL runtime issue [message #425115 is a reply to message #425061] Wed, 07 October 2009 10:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You do realize that hold_select_query1 just contains one large string, right?
If you execute
hold_select_query1 := hold_select_query1 ||' and CITY_CD='|| v_CITY_CD;
select comp_name into var from sysparam where party_cd=hold_select_query1;

the query that gets executed searches for a record where party_cd equals 'BANG and CITY_CD=BAN'
I doubt if you have any data resembling that in your database.
Re: PL/SQL runtime issue [message #425165 is a reply to message #425061] Wed, 07 October 2009 21:25 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
sharathmpatil

you got an error? Why did you not post the error stack in your post? How do you expect anyone to help you debug and error without knowing what the error is?

Kevin
Re: PL/SQL runtime issue [message #425302 is a reply to message #425165] Thu, 08 October 2009 09:20 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Kevin Meade wrote on Thu, 08 October 2009 03:25
sharathmpatil

you got an error? Why did you not post the error stack in your post? How do you expect anyone to help you debug and error without knowing what the error is?

Kevin


In fairness I think the OP is trying to say that the code ends up in the exception handler - no data found. The reason for which Frank has already given.
Re: PL/SQL runtime issue [message #425321 is a reply to message #425061] Thu, 08 October 2009 10:55 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
ah... thankyou for the clarification then.

Kevin x79427
Re: PL/SQL runtime issue [message #425345 is a reply to message #425321] Thu, 08 October 2009 12:38 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
Kevin Meade wrote on Thu, 08 October 2009 11:55
ah... thankyou for the clarification then.

Kevin x79427


Kevin, I called extension 79427, but a girl answered and didn't know who you were.
Re: PL/SQL runtime issue [message #425346 is a reply to message #425061] Thu, 08 October 2009 12:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
stupid me...

I put my work extension on the end of my emails at work. It is a habit. Lets my work cohorts call me without having to look my number up. Indeed, remote workers have special software that can recognize phone numbers and dial them just by clicking on them.

I have to force my self not to do it when replying to posts here. I forgot this time.

Why, did you call for a lark, or did you want to talk with me?

Sorry.

Kevin x7(oops)

[Updated on: Thu, 08 October 2009 12:41]

Report message to a moderator

Re: PL/SQL runtime issue [message #425348 is a reply to message #425346] Thu, 08 October 2009 12:45 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
Kevin Meade wrote on Thu, 08 October 2009 13:41

Why, did you call for a lark, or did you want to talk with me?


Nah, I was just playing with you. Sorry that you thought that I was serious. My company only has 4 digit extensions anyway Wink
Re: PL/SQL runtime issue [message #425350 is a reply to message #425061] Thu, 08 October 2009 12:51 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
hehe good one.
Previous Topic: Error inserting values in ORDBMS table
Next Topic: Return result of query into a 6 columns table
Goto Forum:
  


Current Time: Fri Sep 30 05:29:16 CDT 2016

Total time taken to generate the page: 0.11553 seconds