OraFAQ Forum: SQL & PL/SQL » pl/sql var is not getting captured urgent

Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql var is not getting captured urgent

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

pl/sql var is not getting captured urgent [message #41266]

Thu, 12 December 2002 09:45

Ravi
Messages: 251
Registered: June 1998

Senior Member

In both these scripts the username.tablename is not getting captured in the insert. could anybody can tell why it happens.

The following error has occurred:

PLS-00201: identifier 'V_DM.GL_TEST' must be declared
----------------------------

Create table gl_test (testing varchar2(1));

DECLARE
V_DM VARCHAR2(20);

BEGIN

SELECT USER INTO V_DM FROM USER_USERS;

insert into V_DM.GL_TEST (TESTING) values ('X');

DBMS_OUTPUT.PUT_LINE (V_DM);

end;

----------------------------
declare
cursor c1 is select user from user_users;
V_DM VARCHAR2(50);
begin
open c1;
fetch c1 into v_dm;
insert into V_DM.GL_TEST (TESTING) values ('X');
DBMS_OUTPUT.PUT_LINE (V_DM);
close c1;
end;

Report message to a moderator

Re: pl/sql var is not getting captured urgent [message #41269 is a reply to message #41266]

Thu, 12 December 2002 10:36

casanova
Messages: 4
Registered: December 2002

Junior Member

Pls change the table name i.e V_DM.GL_TEST ... U don't have a table with that name.

insert into V_DM.GL_TEST (TESTING) values ('X');

It should be something like this.

DECLARE
V_DM VARCHAR2(20);
BEGIN
SELECT USER INTO V_DM FROM USER_USERS;
insert into TABLE_NAME(u can specify ur tablename) values (v_dm);
DBMS_OUTPUT.PUT_LINE (V_DM);
end;

Report message to a moderator

casanova Re: pl/sql var is not getting captured urgent [message #41273 is a reply to message #41269]

Thu, 12 December 2002 15:35

Ravi
Messages: 251
Registered: June 1998

Senior Member

Hi Casanova
I created this table
Create table gl_test (testing varchar2(1));

and I wanted to know in the 'insert into V_DM.GL_TEST', why the V_DM.GL_TEST ie username.tablename was not captured. Any answers? should I only use dynamic sql..or any way out.

Thank you
Ravi

DECLARE
V_DM VARCHAR2(20);

BEGIN

SELECT USER INTO V_DM FROM USER_USERS;

insert into V_DM.GL_TEST (TESTING) values ('X');

DBMS_OUTPUT.PUT_LINE (V_DM);

end;

>From: "ORAFAQ plsql Message Board" <orafaq@orafaq.net>
>To: ravi_ora@hotmail.com
>Subject: Re: pl/sql var is not getting captured urgent
>Date: Thu, 12 Dec 2002 14:36:49 -0600
>
>Dear Oracle FAQ User,
>
>We just wanted to let you know that Casanova replied
>to your message on the Oracle FAQ plsql message forum.
>The complete message is available at:
>
> http://www.orafaq.net/msgboard/plsql/messages/11887.htm
>
>Message Text: Re: pl/sql var is not getting captured urgent
>
>Pls change the table name i.e V_DM.GL_TEST ... U don't have a table with
>that name.
>
>insert into V_DM.GL_TEST (TESTING) values ('X');
>
>It should be something like this.
>
>DECLARE
>V_DM VARCHAR2(20);
>BEGIN
>SELECT USER INTO V_DM FROM USER_USERS;
>insert into TABLE_NAME(u can specify ur tablename) values (v_dm);
>DBMS_OUTPUT.PUT_LINE (V_DM);
>end;
>
>
>

Report message to a moderator

Re: pl/sql var is not getting captured urgent [message #41277 is a reply to message #41266]

Fri, 13 December 2002 03:44

Venkat
Messages: 110
Registered: February 2001

Senior Member

hi Ravi,

GIve grnat from the original user like...

"grant insert on gl_test to user_name"

user_name is the user from where u r inserting...

try this out........

Report message to a moderator

Previous Topic:	trouble with 'group by'
Next Topic:	Hi MaheshRajendran

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 18:47:02 CDT 2024