Home » SQL & PL/SQL » SQL & PL/SQL » Cursor and variable issue. (Oracle 7.3)
Cursor and variable issue. [message #584962] Mon, 20 May 2013 15:03 Go to next message
CyberGoo
Messages: 7
Registered: May 2013
Location: Wisconsin
Junior Member
I am running this procedure but it will not compile. I get the error "PLS-00356: 'REC.XX' must name a table to which the user has access"

All of the query results from the cursor are correct.

Any ideas?????

Thank you!
create or replace procedure SWDCADMIN.Hard_Delete_Client( cltId IN number)
IS
cursor c1 IS
  select
        t1.table_name xx,
        t1.owner || '.' || t1.TABLE_NAME uu,
        t3.column_name zz
  from
        all_constraints t1, all_constraints t2, all_cons_columns t3
  where
        t1.constraint_type = 'R'
  and
        t2.constraint_name = t1.r_constraint_name
  and 
        t2.table_name = 'CLIENT' and t2.constraint_type = 'P'
  and
        t3.constraint_name = t1.constraint_name
  and
        t1.table_name != 'CLIENT';
        
  var varchar2(400);
  var1 varchar2(400);
BEGIN
      dbms_output.enable(1000000);
      --dbms_output.disable;      
      FOR rec in c1
        LOOP
          --dbms_output.put_line('table name:' || rec.xx );
          --dbms_output.put_line('column name:' || rec.zz );
          --dbms_output.put_line('full tbl name:' || rec.uu );
          delete from rec.xx where cltId = rec.zz;        
      END LOOP;
END;


[LF merged topics, deleted duplicate message & applied [code] tags]

[Updated on: Mon, 20 May 2013 15:06] by Moderator

Report message to a moderator

Re: Cursor and variable issue. [message #584965 is a reply to message #584962] Mon, 20 May 2013 15:08 Go to previous messageGo to next message
Littlefoot
Messages: 18847
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
DELETE works on tables. REC.XX is not a table, obviously.

By the way, what made you change useful column names (such as TABLE_NAME and COLUMN_NAME) to something useless (such as XX and ZZ)?
Re: Cursor and variable issue. [message #584966 is a reply to message #584965] Mon, 20 May 2013 15:12 Go to previous messageGo to next message
CyberGoo
Messages: 7
Registered: May 2013
Location: Wisconsin
Junior Member
Here is sample of dbms output

table name:CLIENTNOTES
column name:CLTN_CLT_ID
full tbl name:SWDCADMIN.CLIENTNOTES

rec.xx is indeed a table. No particular reason to change names, just during troubleshooting.
Re: Cursor and variable issue. [message #584967 is a reply to message #584966] Mon, 20 May 2013 15:16 Go to previous messageGo to next message
Littlefoot
Messages: 18847
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
rec.xx is indeed a table

I doubt that. Can you prove it?
Re: Cursor and variable issue. [message #584968 is a reply to message #584966] Mon, 20 May 2013 15:20 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
rec.xx is indeed a table.


It should a column of the query.

Regards
Michel
Re: Cursor and variable issue. [message #584969 is a reply to message #584967] Mon, 20 May 2013 15:20 Go to previous messageGo to next message
BlackSwan
Messages: 21982
Registered: January 2009
Senior Member
SQL must be complete & known at compile time.
To implement dynamic SQL you must (ab)use EXECUTE IMMEDIATE.
Re: Cursor and variable issue. [message #584972 is a reply to message #584969] Mon, 20 May 2013 15:40 Go to previous messageGo to next message
CyberGoo
Messages: 7
Registered: May 2013
Location: Wisconsin
Junior Member
I am working on the EXECUTE IMMEDIATE solution, can you show an example with below vars?
Re: Cursor and variable issue. [message #584973 is a reply to message #584972] Mon, 20 May 2013 15:42 Go to previous messageGo to next message
BlackSwan
Messages: 21982
Registered: January 2009
Senior Member
When all else fails, Read The Fine Manual

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#LNPLS01317
Re: Cursor and variable issue. [message #584974 is a reply to message #584973] Mon, 20 May 2013 15:47 Go to previous messageGo to next message
CyberGoo
Messages: 7
Registered: May 2013
Location: Wisconsin
Junior Member
Thank you for the link, I have been using the "Ant" book, but another source is always helpful.

Many Thanks!!!

-Goo
Re: Cursor and variable issue. [message #584975 is a reply to message #584974] Mon, 20 May 2013 15:50 Go to previous messageGo to next message
BlackSwan
Messages: 21982
Registered: January 2009
Senior Member
http://docs.oracle.com
Re: Cursor and variable issue. [message #585039 is a reply to message #584974] Tue, 21 May 2013 08:40 Go to previous messageGo to next message
CyberGoo
Messages: 7
Registered: May 2013
Location: Wisconsin
Junior Member
Still getting commpile error: PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:

:= . ( @ % ;


create or replace procedure SWDCADMIN.Hard_Delete_Client( cltId IN number)
IS
cursor c1 IS
select
t1.table_name table_name,
t1.owner || '.' || t1.TABLE_NAME full_table_name,
t3.column_name column_name
from
all_constraints t1, all_constraints t2, all_cons_columns t3
where
t1.constraint_type = 'R'
and
t2.constraint_name = t1.r_constraint_name
and
t2.table_name = 'CLIENT' and t2.constraint_type = 'P'
and
t3.constraint_name = t1.constraint_name
and
t1.table_name != 'CLIENT';

var varchar2(400);
var1 varchar2(400);
plsql_stat VARCHAR2(500);

BEGIN
dbms_output.enable(1000000);

plsql_stat := 'select :column_name into var from :table_name where :column_name = cltId;';

FOR rec in c1
LOOP

--dbms_output.put_line('table name:' || rec.table_name);
--dbms_output.put_line('column name:' || rec.column_name);
--dbms_output.put_line('full tbl name:' || rec.full_table_name );
EXECUTE IMMEDIATE plsql_stat
USING IN rec.column_name, rec.table_name, rec.column_name;

END LOOP;
END;
Re: Cursor and variable issue. [message #585040 is a reply to message #585039] Tue, 21 May 2013 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FORMAT your posts.

Regards
Michel
Re: Cursor and variable issue. [message #585041 is a reply to message #585039] Tue, 21 May 2013 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 21982
Registered: January 2009
Senior Member
The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
Then print the variable before passing it to EXECUTE IMMEDIATE.
COPY the statement & PASTE into sqlplus to validate its correctness.


Why do you issue SELECT & then totally ignore the result set?
Re: Cursor and variable issue. [message #585042 is a reply to message #585041] Tue, 21 May 2013 08:53 Go to previous messageGo to next message
CyberGoo
Messages: 7
Registered: May 2013
Location: Wisconsin
Junior Member
My problem is that I cannot compile the code so checking the statement is a next step, after code is compilied.

Just using the select to get a working example of this before using real statement.
Re: Cursor and variable issue. [message #585044 is a reply to message #585039] Tue, 21 May 2013 09:00 Go to previous messageGo to next message
flyboy
Messages: 1751
Registered: November 2006
Senior Member
You cannot bind identifiers (table and column names) - their names have to be hardcoded.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:227413938857
On the other hand CLTID should definitely be bound (especially as it is NOT in the dynamic scope).

However, as the procedure name (in the opposite to the implemented LOOP, which would be totally useless in that case - simple DELETE statement would do that job) leads to ON DELETE CASCADE emulation, you should save your time and redefine your foreign constraints with this option.
Re: Cursor and variable issue. [message #585049 is a reply to message #585044] Tue, 21 May 2013 09:32 Go to previous message
CyberGoo
Messages: 7
Registered: May 2013
Location: Wisconsin
Junior Member
Ah.........this says it all...Identifiers cannot be used as bind variables -- ever.

Have checked and ON DELETE CASCADE is on child tables so will use a delete statement.

Thanks All!!!!!

--Goo
Previous Topic: Variable is NULL
Next Topic: Order by in Date Column
Goto Forum:
  


Current Time: Thu Apr 24 22:21:36 CDT 2014

Total time taken to generate the page: 0.12810 seconds