| Pro *c [message #6971] |
Wed, 14 May 2003 04:50  |
Kajari
Messages: 1
Registered: May 2003
|
Junior Member |
|
|
Iwant to create and run a procedure in Pro *C in unix and NT Os.
In this i find the procedure doesnot recognise the replace function or replace procedure statement.
And how do we call the procedure in the same program.
I have made a anonymous PL/SQL block to do so but cannot achieve it.It says declare the identifier ie. the procedure name...
My procedure is as follows:
Plz try to give me a solution as soon as possible...
EXEC SQL CREATE OR REPLACE PROCEDURE proc_view AS
cursor view_cur is
select view_name , text from dba_views
where owner = 'INS';
l_start number ;
l_diff number ;
l_long long ;
l_var varchar2(4000) ;
begin
l_diff := 2000 ;
for c1 in view_cur
loop
l_long := c1.text ;
l_start := 0 ;
loop
l_var := '~' ;
l_var := substr(l_long,(l_start * l_diff + 1),l_diff),chr(10) ;
l_start := l_start + 1 ;
exit when nvl(l_var,'~') = '~' ;
Insert into All_view_Info_Old Values (c1.view_name, l_start , l_var) ;
end loop ;
end loop ;
end proc_view ;
END-EXEC;
EXEC SQL EXECUTE
begin
proc_view;
end;
END-EXEC;
|
|
|
|
| Re: Pro *c [message #6981 is a reply to message #6971] |
Wed, 14 May 2003 12:19  |
SaschaV
Messages: 18
Registered: February 2003
|
Junior Member |
|
|
Hi,
as far as I can see, you haven't declare c1 yet (also you say something about ie, which doesn't make much sense to me):
insert into the declaration block
c1 view_cur%ROWTYPE;
hope this helps.
Sascha
|
|
|
|
| Re: Pro *c [message #7028 is a reply to message #6971] |
Fri, 16 May 2003 02:29 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It looks like you have a typo in the following line:
l_var := substr(l_long,(l_start * l_diff + 1),l_diff)<b><font color="blue">,</font></b>chr(10) ;
I believe you meant ot use a concatentation symbol instead of the comma:
l_var := substr(l_long,(l_start * l_diff + 1),l_diff)<b><font color="blue">||</font></b>chr(10) ;
It is easier to find this sort of error based on error messages, if you test it first in SQL*Plus.
Your cursor loop is O.K., including your "for c1 in view_cur", which implicitly creates a c1 variable of view_cur%rowtype. I would probably also use an implicit cursor, rather than declaring the view_cur cursor, but either will work. Whatever user is creating the procedure will need privileges to select on sys.dba_views granted directly, rather than through a role.
Although I know pl/sql, I don't know much about Pro*C, so I am not sure if you should really do it this way, or if it would be better to create the pl/sql procedure from SQL*Plus, then just execute it from Pro*C, using your EXEC SQL.
|
|
|
|
| Re: Pro *c [message #7029 is a reply to message #6971] |
Fri, 16 May 2003 02:41 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
One more thing. If you are going to change the other line as I suggested, then you will also need to change your exit to something like:
exit when ltrim (l_var) = chr (10);
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
