Home » SQL & PL/SQL » SQL & PL/SQL » Passing a parameter into a variable place holder
Passing a parameter into a variable place holder [message #437971] Wed, 06 January 2010 11:33 Go to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Hi,

Is it legal to pass a IN parameter into another variable placeholder?

For example,


I'm passing in the following parameters:


p_trm        VARCHAR2(6) :='&1';
p_elig       VARCHAR2(20) :='&2';

p_elig = an empty text field that users want to put in additional information.

These are the variable place holders

v_trm      VARCHAR2(6) := NVL(p_trm, 201);
v_elig     VARCHAR2(20) := p_elig;


I keep getting the following error message:


dbms_output.put(substr(py_rec.v_elig,1,20));
                                               *
ERROR at line 123:
ORA-06550: line 123, column 48:
PLS-00302: component 'V_ELIG' must be declared
ORA-06550: line 123, column 13:
PL/SQL: Statement ignored






V_elig is declared, so why would I get the following error message?


Re: Passing a parameter into a variable place holder [message #437972 is a reply to message #437971] Wed, 06 January 2010 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it legal to pass a IN parameter into another variable placeholder?

What does this mean?

Quote:
V_elig is declared

Where py_rec.v_elig is declared?

If you don't post the whole code (or code simulating your problem) how could we help?

Regards
Michel
Re: Passing a parameter into a variable place holder [message #437973 is a reply to message #437971] Wed, 06 January 2010 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
>V_elig is declared, so why would I get the following error message?

Oracle disagrees & I believe Oracle until proven otherwise.
py_rec.v_elig is only valid when cursor is open.
Re: Passing a parameter into a variable place holder [message #437975 is a reply to message #437972] Wed, 06 January 2010 11:50 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
DECLARE
--Parameters passed
p_trm       VARCHAR2(6) :='&1';
p_elig      VARCHAR2(20) :='&2';

--Variables declared
v_trm     VARCHAR2(6) := NVL(p_trm, 201);
v_elig    VARCHAR2(20) := p_elig;

--Main cursor
CURSOR get_py
IS
SELECT aa,ab,ac from dual
where trm_code = v_trm;
      
---    
 BEGIN

 FOR py_rec IN get_py
     LOOP
        
dbms_output.put(substr(nvl(py_rec.aa,' '),1,9));
dbms_output.put(substr(py_rec.ab,1,30));
dbms_output.put(substr(py_rec.ac,1,20));
dbms_output.put(substr(py_rec.v_elig,1,20));
dbms_output.new_line;
    END LOOP;

v_elig is not part of the main query because the users want to be able to enter in additional text information...which is why I used p_elig to pass into v_elig. Maybe I'm not capturing it right??????


So what is wrong?

[Updated on: Wed, 06 January 2010 12:06] by Moderator

Report message to a moderator

Re: Passing a parameter into a variable place holder [message #437977 is a reply to message #437975] Wed, 06 January 2010 11:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The error is coming from this line (unless I'm badly mistaken):
dbms_output.put(substr(py_rec.v_elig,1,20))


There is no column called v_elig returned by your cursor, and so trying to reference <cursor_record>.v_elig is not going to work.
Re: Passing a parameter into a variable place holder [message #437978 is a reply to message #437975] Wed, 06 January 2010 11:57 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
>SELECT aa,ab,ac from dual where trm_code = v_trm;
Above is utter nonsense.

explain in detail exactly what you hoped to accomplish by this SELECT.
Re: Passing a parameter into a variable place holder [message #437982 is a reply to message #437977] Wed, 06 January 2010 12:05 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
eventhough I'm passing the parameter p_elig into v_elig? what is the proper way to make what I'm trying to achieve work? p_elig is not referenced to a column in the database..because it's just for receiving additional information.

Re: Passing a parameter into a variable place holder [message #437983 is a reply to message #437975] Wed, 06 January 2010 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> DECLARE
  2  --Parameters passed
  3  p_trm       VARCHAR2(6) :='&1';
  4  p_elig      VARCHAR2(20) :='&2';
  5  
  6  --Variables declared
  7  v_trm     VARCHAR2(6) := NVL(p_trm, 201);
  8  v_elig    VARCHAR2(20) := p_elig;
  9  
 10  --Main cursor
 11  CURSOR get_py
 12  IS
 13  SELECT aa,ab,ac from dual
 14  where trm_code = v_trm;
 15        
 16  ---    
 17   BEGIN
 18  
 19   FOR py_rec IN get_py
 20       LOOP
 21          
 22  dbms_output.put(substr(nvl(py_rec.aa,' '),1,9));
 23  dbms_output.put(substr(py_rec.ab,1,30));
 24  dbms_output.put(substr(py_rec.ac,1,20));
 25  dbms_output.put(substr(py_rec.v_elig,1,20));
 26  dbms_output.new_line;
 27      END LOOP;
 28  /
Enter value for 1: a
Enter value for 2: b
    END LOOP;
            *
ERROR at line 27:
ORA-06550: line 27, column 13:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe

Please post something that shows the error you want to show.

Quote:
what is the proper way to make what I'm trying to achieve work?

If what is in p_elig is the name of a field, then the proper way is to use CASE or dynamic SQL.
But who knows what you really want to do as you don't clearly explain it.

Regards
Michel
Re: Passing a parameter into a variable place holder [message #437986 is a reply to message #437978] Wed, 06 January 2010 12:11 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
SELECT aa,ab,ac from dual where trm_code = v_trm;
Above is utter nonsense.

If you see as I have defined and declared my variables..from my previous post...the v_trm in essence is the parameter being passed in..


Re: Passing a parameter into a variable place holder [message #437987 is a reply to message #437986] Wed, 06 January 2010 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If you see as I have defined and declared my variables.

Not p_elig and you still don't say what it contains. A value? The name of a field? A hamburger?

Regards
Michel
Re: Passing a parameter into a variable place holder [message #437988 is a reply to message #437982] Wed, 06 January 2010 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
>what is the proper way to make what I'm trying to achieve work?
My basic problem is that I don't understand exactly what you are trying to achieve.

So what exactly are you trying to accomplish.
Specify inputs, outputs & internal transformations.
What needs to be done to inputs to achieve desired output/results?

Since anonymous PL/SQL, is "never" used in a Production environment, at a minimum I suggest you practice using a named PL/SQL procedure with formally declared parameters.
Re: Passing a parameter into a variable place holder [message #437989 is a reply to message #437986] Wed, 06 January 2010 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
>>SELECT aa,ab,ac from dual where trm_code = v_trm;

>If you see as I have defined and declared my variables..from my previous post...the v_trm in essence is the parameter being passed in..

DUAL knows nothing about TRM_CODE or V_TRM
What data is to obtained by SELECT above & from where?
Re: Passing a parameter into a variable place holder [message #437990 is a reply to message #437983] Wed, 06 January 2010 12:18 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Let me explain again....I have posted what my error meessage was previously. My objective is to not get the error message anymore and fix the problem...so I was hoping this forum would help me in that area of fixing the problem and possibly teach me something I probably don't know..and not be belittled or talked to in a condescending way.



Thank you,
Re: Passing a parameter into a variable place holder [message #437991 is a reply to message #437987] Wed, 06 January 2010 12:20 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
p_elig = values like LU123 or LU2009p.
Re: Passing a parameter into a variable place holder [message #437994 is a reply to message #437991] Wed, 06 January 2010 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have posted what my error meessage was previously. My objective is to not get the error message anymore and fix the problem

So the answer is:
Quote:
PLS-00302: component "string" must be declared
Cause: In a reference to a component (for example, in the name "A.B", "B" is a component of "A"), the component has not been declared. The component might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.
Action: Check the spelling and declaration of the component. Also confirm that the declaration is placed correctly in the block structure.


Quote:
so I was hoping this forum would help me in that area of fixing the problem and possibly teach me something I probably don't know

So we're hoping you will help to ehlp us and post what we ask: a code that shows when your problem occurs and what you are trying to achieve.

Quote:
_elig = values like LU123 or LU2009p.

And what is LU123? the name of a quark?

Regards
Michel

Re: Passing a parameter into a variable place holder [message #437996 is a reply to message #437991] Wed, 06 January 2010 12:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I apologise for some of the other posters here - they have a tendency to only attempt to answer the question after you've answered every single point of trivia about the situation that they can think of.

You say that:Quote:
V_elig is declared, so why would I get the following error message?
, but v_elig is only declared in the context of a stand alone pl/sql variable.

you are trying to reference the variable py_rec.v_elig - this is invalid, because the implicit record type py_rec only contains elements aa, ab and ac - the names of the columns returned by the cursor that py_rec contains the records of.

If you want to output the value of v_elig to dbms_output, you should replace this:
dbms_output.put(substr(py_rec.v_elig,1,20));
with this:
dbms_output.put(v_elig);
(you don't need to use Substr on it, as the string can't be more than 20 characters long).
Re: Passing a parameter into a variable place holder [message #437998 is a reply to message #437996] Wed, 06 January 2010 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you assume that "py_rec.v_elig" means "v_elig", well if you are right you have a far ebtter crystal ball than mine as I don't see that anywhere.

Regards
Michel
Re: Passing a parameter into a variable place holder [message #438119 is a reply to message #437998] Thu, 07 January 2010 03:37 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's definitely the py_rec.v_elig that's causing the error.

Beyond that, it's all informed guesswork.
I'd guess that the OP has posted a stripped down piece of code without checking the details (such as the fact that the cursor is totally invalid).

It's entirely possible that I'm wrong, but I reckon that's the most likely cause of the problem.

Hopefully the OP will let us know.
Previous Topic: pl/sql
Next Topic: sort data in date range
Goto Forum:
  


Current Time: Tue Sep 27 09:25:27 CDT 2016

Total time taken to generate the page: 0.25774 seconds