Home » SQL & PL/SQL » SQL & PL/SQL » Rowtype Declaration Problem Within Multiple Schemas (merged)
Rowtype Declaration Problem Within Multiple Schemas (merged) [message #350742] Fri, 26 September 2008 02:03 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

I have a problem in declaration of variable with
%ROWTYPE declarations used in multiple schema's.

I have one main schema "SCH1" where all my
packages are stored in it which were declared
by using AUTHID CURRENT_USER.

When i call the package from SCH2,
rowtype declaration is not taking tables description
from SCH2 which contains the actual table.
SCH1 may also contain the same table, so it
is taking from it.

Please help me in this regard so that calling
schemas description is taken.

Tx & Regards.
Natesh
Re: Rowtype Declaration Problem Within Multiple Schemas [message #350744 is a reply to message #350742] Fri, 26 September 2008 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case that we can reproduce and clearly show the issue you have.

Before 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 the "Preview Message" button to verify.

Regards
Michel
Re: Rowtype Declaration Problem Within Multiple Schemas [message #350766 is a reply to message #350742] Fri, 26 September 2008 04:59 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Mic,

Please find the updated scenario with a test case.

I have a problem in declaration of variable with %ROWTYPE declarations used
in multiple schema's.


I have one main schema "SCH1" where all my packages are stored in it which
were declared by using AUTHID CURRENT_USER.

When i call the package from "SCH2",rowtype declaration is not taking
tables description from SCH2 which contains the actual table. SCH1 may
also contain the same table,so it is taking from it.


Test Case:

Quote:
SCH1> CREATE OR REPLACE PROCEDURE p1
AUTHID Current_User
AS
eRow emp%ROWTYPE;
BEGIN
SELECT *
INTO eRow
FROM emp
WHERE empNo = 7788;
END;

SCH2> EXEC SCH1.P1;


Result: As the table is not existing in SCH2, Table or View
does not exist is the error.

Quote:
SCH2> CREATE TABLE emp (
empNo NUMBER,
eName VARCHAR2(2000));


Result: As the SCH2.emp is having only 2 columns which conflicts
with total no of columns in SCH1.emp it is raising following error
ORA-01007: variable not in select list.

Above problem can be solved by changing procedure p1 code as
select empno,ename into erow ..... But, it doesn't solve
my requirement completely. Because, from SCH3 schema, i may call
the same procedure where SCH3.emp table will have only ename column.


Please help me in this regard so that appropriate schema's Table description is taken.

Tx & Regards
Re: Rowtype Declaration Problem Within Multiple Schemas [message #350769 is a reply to message #350742] Fri, 26 September 2008 05:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Still not getting the Issue .

Here in your example

Result: As the table is not existing in SCH2, Table or View 
does not exist is the error.


But even if you created EMP table with 2 columns, this procedure will execute fine . ie, no need to change the select statement.

Thumbs Up
Rajuvan.

[Updated on: Fri, 26 September 2008 05:37]

Report message to a moderator

Re: Rowtype Declaration Problem Within Multiple Schemas [message #350775 is a reply to message #350769] Fri, 26 September 2008 05:34 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Raju

Tx for the reply. When i created emp table with two columns
and when procedure is having "select * into erow...", it is raising
variable not in select list error because i am using AUTHID Current_User option.

.....(copied from previous reply)
Result: As the SCH2.emp is having only 2 columns which conflicts
with total no of columns in SCH1.emp it is raising following error
ORA-01007: variable not in select list.

If i remove AUTHID Current_User option, it takes emp description
from SCH1 only. I dont need like that. It has to take emp description from the SCH2 only.

Please Help me in the above case.

Tx
Natesh

Re: Rowtype Declaration Problem Within Multiple Schemas [message #350836 is a reply to message #350775] Fri, 26 September 2008 11:25 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I think I understand your problem but correct me if I am wrong, you are trying to create a procedure which will select from the emp table in whichever schema it is called from and these tables do not have the same structure.

authid current_user is not going to do this for you. The current schema is only used for certain external resolutions with this option set. See Oracle documentation here.

The only way I can think of to do what you are trying to do would be to either:

1. Only select columns which appear in every one of the emp tables and declare the rowtype manually.
or
2. Use some kind of dynamic SQL solution.
Compilation of Procedure [message #351529 is a reply to message #350742] Wed, 01 October 2008 07:20 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

Can i compile a procedure without dependent table in
current schema?

Case

SCH1> Create procedure P1
erow emp%rowtype;
begin
null;
end;

Note: EMP table is not present in SCH1 schema. But, it is
present in SCH2 schema.

How can i successfully compile the above procedure?

Tx in Advance

Natesh
Re: Compilation of Procedure [message #351531 is a reply to message #351529] Wed, 01 October 2008 07:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
refer to the table in the SCH2 schems:
Create procedure P1
erow sch2.emp%rowtype;
begin
null;
end;
Re: Compilation of Procedure [message #351532 is a reply to message #351529] Wed, 01 October 2008 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
erow sch2.emp%rowtype;

Otherwise you can't or create a synonym sch2 table.

Regards
Michel

Re: Compilation of Procedure [message #351533 is a reply to message #351529] Wed, 01 October 2008 07:30 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Tx for the Reply...

I have a concern. Schema name is not static, it is dynamic.

Tx
Natesh
Re: Compilation of Procedure [message #351534 is a reply to message #351533] Wed, 01 October 2008 07:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well then, you've got a real problem.

What do you mean by 'Dynamic'?
Do you mean that there are several different schemas with an EMP table, and you don't know at compile time which one that is?
Or, do you mean that some total wuckfit has designed an application that creates and drops users and tables at runtime?
Re: Compilation of Procedure [message #351535 is a reply to message #351533] Wed, 01 October 2008 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is the same question as your previous topic, I merge the 2 to prevent from restarting the whole stuff from beginning.

Regards
Michel
Re: Compilation of Procedure [message #351549 is a reply to message #351534] Wed, 01 October 2008 08:49 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Yes bottom.

Your view is correct. I am having multiple schema's and i don't
know at compile time.

How should i continue in this case?

Tx in Advance
Natesh
Re: Compilation of Procedure [message #351551 is a reply to message #351549] Wed, 01 October 2008 08:50 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
create a synonym for sch2 table.

Regards
Michel

[Updated on: Wed, 01 October 2008 08:50]

Report message to a moderator

Previous Topic: Spool decimal value to csv file
Next Topic: UPDATE QUERY
Goto Forum:
  


Current Time: Sat Dec 10 14:31:55 CST 2016

Total time taken to generate the page: 0.29386 seconds