| DBMS_SQL with clobs (Merged) [message #215272] |
Sat, 20 January 2007 06:36  |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi Can anyone give one example for DBMS_SQL with CLOB.
Simple example.
the clob is having more than 100 kb.
It tried using execute immediate. But it errored for one row the data is more than 100kb.
Here is the function.
CREATE OR REPLACE PROCEDURE test_populatesqls1( P_target_system VARCHAR2 DEFAULT 'ALL' ) as
sql2 clob;
l_str long;
begin
FOR sql1 IN (SELECT sql_string, target_system
FROM sys_nc_out_sqls where target_system=P_target_system)
LOOP
If (sql1.target_system='ORACLE' or sql1.target_system='ALL') then
sql2:=sql1.sql_string;
l_str:= dbms_lob.substr(sql2,32765,1);
dbms_output.put_line('entered');
Execute Immediate l_str ;
end if;
Commit;
end loop;
End test_populatesqls1;
Any help can reallly help me alot.
Thanks in advance
|
|
|
|
| Re: DBMS_SQL for clob's [message #215274 is a reply to message #215272] |
Sat, 20 January 2007 07:09  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
This is the n-th thread you start about clobs.
Apparently, you have a clob column containing a sql statement, which you want to execute at runtime.
First of all: sounds like a verrrrry bad design. Why would anyone want to do this?
(I think I know, considering your "target_system='ORACLE'" bit; you want 'database-independency? Even worse design!)
Secondly, and I really don't mean this in a negative way:
The way your posts, our answers and your replies have gone thusfar will not get you where you want to get. Somehow we don't completely understand you, and you don't seem to understand us.
Up till now this only resulted in you asking the same questions over and over.
The best solution to this: find yourself an experienced developer in the shop you work and ask him/her to be your tutor.
Maybe he/she can explain why this is designed the way it is and (s)he can help you along the way.
Of course you are always welcome to post your questions here, but (in my opinion) the problem you are working on now is way over your head.
|
|
|
|
| Re: DBMS_SQL for clob's [message #215280 is a reply to message #215274] |
Sat, 20 January 2007 07:32 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi Frank,
Thanks for your suggestion. I have made required changes in the procedure. I tried this with DBMS_SQL package in the following way.
CREATE OR REPLACE PROCEDURE test_genpop( P_target_system VARCHAR2 DEFAULT 'ALL' ) as
sql2 clob;
cursor_name INTEGER;
rows_processed INTEGER;
begin
FOR sql1 IN (SELECT sql_string, target_system
FROM sys_nc_out_sqls where target_system=P_target_system)
LOOP
If (sql1.target_system='ORACLE' or sql1.target_system='ALL') then
sql2:=sql1.sql_string;
cursor_name :=dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, sql2, DBMS_SQL.NATIVE);
rows_processed := dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name);
end if;
Commit;
end loop;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
End test_genpop;
The one I need is to execute the sql statement dynamically which is of size more than 100kb. It's not able to execute dynamically for that querry.
This is the one what I need. I never used clobs thats the reason I am getting lots of doubts. If you feel that it is reasonable then answer it else leave it.
I think you are much worried than me.
Anyway thanks for spending some time on my querry.
|
|
|
|
| Execute Immediate [message #215412 is a reply to message #215272] |
Mon, 22 January 2007 03:34 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi Can anyone help me in executing this querry dynamically. Its a column of one table called sys_nc_out_sqls
It is having 3 columns Table_name(varchar2), Target_name (varchar2), sqlstring(clob)
I have to execute dyanmically sqlstring by writing a procedure
This is the procedure which I have written. Its not giving any error and also not able to execute the querry dynamically.
[code]
CREATE OR REPLACE PROCEDURE test_genpop( P_target_system VARCHAR2 DEFAULT NULL ) as
sql2 clob;
cursor_name INTEGER;
rows_processed INTEGER;
begin
FOR sql1 IN (SELECT sql_string, target_system
FROM sys_nc_out_sqls)
LOOP
If (sql1.target_system=P_target_system or P_target_system is NULL) then
dbms_output.put_line(sql1.target_system);
sql2:=sql1.sql_string;
cursor_name :=dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, sql2, DBMS_SQL.NATIVE);
rows_processed := dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name);
end if;
Commit;
end loop;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
End test_genpop;
Thanks in advance
[Updated on: Mon, 22 January 2007 23:26] Report message to a moderator |
|
|
|
| Re: Execute Immediate [message #215482 is a reply to message #215412] |
Mon, 22 January 2007 07:58 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
You have GOT to be kidding. What kind of mumble jumble mish mosh is that? Do you really expect anyone to be able to read that? If you were at work and had to hand this to a manager, what do think they would do? Quite unprofessional.
As Littlefoot and Mahesh pointed out in an earlier post, I am thinking CODE tags will not help here either. A full reformat by the OP is needed is my guess.
[Updated on: Mon, 22 January 2007 08:42] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Execute Immediate [message #215587 is a reply to message #215571] |
Tue, 23 January 2007 00:22 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| vikram1780 wrote on Tue, 23 January 2007 06:28 | Hey I never thought of kidding anyone. I just want you people to replicate the same problem. Nothing more than that.
|
Well, maybe it is comfortable for you, but not for the repliers, as they follow a lot of threads with the same topic and get confused. I think I saw in some thread the goal you want to achieve (description of the problem), and thought of using PL/SQL rather than execute immediate SQL. However I cannot find it anywhere, so bad luck for you to create so many threads.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
