Variable uses and placement

Posted on 16th Feb 2014 07:03 pm by admin

I'm new to PL/SQL and am trying to learn about variable valid uses/placement (any pointers to any docs is appreciated...)
All of the docs I've looked only mention declaration and assignment not valid placement.

When I use an input parameter to a cursor placement matters . I just don't understand why...

example... using the value "SCOTT" as an input parm to the c10 cursor replaces the "str_in" variable correctly and works...

1 declare
2 cursor c10(str_in IN varchar2) is
3 select username
4 from dba_users where username=str_in ;
5 begin
6 for r_c10 in c10('SCOTT') loop
7 dbms_output.put_line(r_c10.username);
8 end loop;
9* end;
SQL> /
SCOTT

PL/SQL procedure successfully completed.

However, when trying to replace another "word" it won't resolve the reference...

example... attempting to replace the from table_name as an input parm, no go...

1 declare
2 cursor c10(str_in IN varchar2) is
3 select username
4 from str_in where username='SCOTT' ;
5 begin
6 for r_c10 in c10('DBA_USERS') loop
7 dbms_output.put_line(r_c10.username);
8 end loop;
9* end;
SQL> /
from str_in where username='SCOTT' ;
*
ERROR at line 4:
ORA-06550: line 4, column 6:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 7, column 26:
PLS-00364: loop index variable 'R_C10' use is invalid
ORA-06550: line 7, column 5:
PL/SQL: Statement ignored

Why can't it resolve the string ?

Any help is appreciated
dumb newbie...

Your Answer:

Login to answer

Other forums

Chat Box in PHP
I was thinking in doing a Chat Box in PHP. For that I would use a form with two fields, Nick and Mes

Kill a process
I have a question - how can I kill a process from a command line or by using Oracle SQL Developer? I

Error Messaging not working
I changed the way my form tells the user of an error. But I can't seem to get it to work properly.

i need help with php header and footer
I have designed a header and footer for my site and they seem to be ok when they are running individ

Transform value 0 in no and 1 in yes help needed
Hello,
I have this in the table: "value" type int 0 to represent false and 1 to represe

Cant display mysql data
Hey all,

I am learning php and my first goal is to create a simple CMS. At the moment I am st

Preg_match question
I want to use preg_match to make sure a string is always 6 characters long and only contains 0-9 and

if statements problems
Hi. I'm trying to make a web form, but I kind of hit a dead end trying to figure out why it doesn't

Dynamic Array using glob?
Is there an easier way to do this?

I am trying to get create a dyamic array based on files wi

Breaking results into week blocks
I have a set of dates (and times), which are returned from a mySQL query.

These usually span