Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SQL with clobs (Merged)
DBMS_SQL with clobs (Merged) [message #215272] Sat, 20 January 2007 06:36 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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 #215571 is a reply to message #215482] Mon, 22 January 2007 23:28 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hey I never thought of kidding anyone. I just want you people to replicate the same problem. Nothing more than that.
Re: DBMS_SQL with clobs (Merged) [message #215574 is a reply to message #215272] Mon, 22 January 2007 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I just want you people to replicate the same problem.
OK. It is a problem for you.
Do you have any other questions that require answers?
With advice, you can have it good, fast or cheap. Pick any two.
Re: Execute Immediate [message #215587 is a reply to message #215571] Tue, 23 January 2007 00:22 Go to previous messageGo to next message
flyboy
Messages: 1832
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.
Re: Execute Immediate [message #215589 is a reply to message #215587] Tue, 23 January 2007 00:26 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I will lock this topic.
First continue in the other thread, describing the actual problem.
Please STOP opening new threads all the time.
Previous Topic: wat's i stands for in oracle 9i
Next Topic: Needed a simplified sql querry
Goto Forum:
  


Current Time: Sun Dec 04 08:20:13 CST 2016

Total time taken to generate the page: 0.04632 seconds