Home » SQL & PL/SQL » SQL & PL/SQL » Can we use CLOB for EXECUTE IMMEDIATE?
icon14.gif  Can we use CLOB for EXECUTE IMMEDIATE? [message #215119] Fri, 19 January 2007 06:51 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

How to execute dynamically a querry which is in sql.sql_string. Its a clob column

The table structure of sys_nc_out_sqls is
as follows

SYS_NC_OUT_SQLS

COLUMNS FIELD TYPE
TABLE_NAME VARCHAR2 (30 Byte)
TARGET_SYSTEM VARCHAR2 (15 Byte)
SQL_STRING CLOB

CREATE OR REPLACE PROCEDURE test_populatesqls( P_target_system VARCHAR2 := 'ALL') as
begin
FOR sql1 IN (SELECT sql_string, target_system
FROM sys_nc_out_sqls)
LOOP
If (sql1.target_system='ALL' or sql1.target_system='ORACLE') then
Execute Immediate 'sql.sql_string' ;
end if;
Commit;
end loop;
End test_populatesqls;


The sql1.sql_string has a huge queery to execute dynamically.

Can anyone help in doing this so.

Thanks in advance
Re: Dynamic sql for clob [message #215127 is a reply to message #215119] Fri, 19 January 2007 07:12 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


[Updated on: Sat, 20 January 2007 02:46]

Report message to a moderator

Re: Dynamic sql for clob [message #215131 is a reply to message #215127] Fri, 19 January 2007 07:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Try again, and this time with formatting Wink

MHE
Re: Dynamic sql for clob [message #215134 is a reply to message #215119] Fri, 19 January 2007 07:38 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
what formatting I have to do. If I am executing the same querry its able to produce proper results.

If I am executing the same querry though dynamic sql then its errored out.

Thanks
Can we use clob for execute immediate [message #215155 is a reply to message #215119] Fri, 19 January 2007 08:37 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Just can any one give one example
How to use clob with execute immedite.

can we conevrt clob to char is there any function in oracle.

As execute immediate works only for strings.

Any help really appreciated.

Thanks in advance
Re: Can we use CLOB for EXECUTE IMMEDIATE? [message #215249 is a reply to message #215119] Sat, 20 January 2007 02:46 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Any help how to execute a clob in execute immediate.

Any help really appreciated.

Thanks in advance
Re: Can we use CLOB for EXECUTE IMMEDIATE? [message #215502 is a reply to message #215249] Mon, 22 January 2007 11:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, I produced this little example in about 90 seconds:
SQL> declare
  2    v_sql  clob;
  3    v_date date;
  4  begin
  5    v_sql := 'select sysdate from dual';
  6    execute immediate v_sql into v_date;
  7  end;
  8  /
  execute immediate v_sql into v_date;
                    *
ERROR at line 6:
ORA-06550: line 6, column 21:
PLS-00382: expression is of wrong type
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored


SQL> ed
Wrote file afiedt.buf

  1  declare
  2    v_sql  varchar2(100);
  3    v_date date;
  4  begin
  5    v_sql := 'select sysdate from dual';
  6    execute immediate v_sql into v_date;
  7* end;
SQL> /

PL/SQL procedure successfully completed.


I'm guessing that CLOBs aren't allowed.
Re: Can we use CLOB for EXECUTE IMMEDIATE? [message #215567 is a reply to message #215502] Mon, 22 January 2007 23:19 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi
Thanks a lot for your reply.

I am having a huge querry of 30 pages. Which I have to execute dynamically. I tried with DBMS_SQL package. Its not giving any compilation error. But its not executing the querry dynamicaaly. Leaving that particular row its executing all other sql stmts dynamically.

Here is the dbms_sql(dynamic sql wriiten)

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
dbms_output.put_line(sql1.target_system);
dbms_output.put_line('inner bolck');
If (sql1.target_system=P_target_system or P_target_system is NULL) then
dbms_output.put_line(sql1.target_system);
dbms_output.put_line('inner bolck');
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
Re: Can we use CLOB for EXECUTE IMMEDIATE? [message #215680 is a reply to message #215567] Tue, 23 January 2007 08:19 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Would you please use CODE TAGS to make your code readable? If you handed this to your manager they would ask the same thing of you.

[Updated on: Tue, 23 January 2007 08:19]

Report message to a moderator

Re: Can we use CLOB for EXECUTE IMMEDIATE? [message #215689 is a reply to message #215567] Tue, 23 January 2007 09:33 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you add a 'RAISE;' statement into the when others, then you may see any errors that happen. At the moment, if an error occurrs, it will be caught by the 'When Others' block which will ignore it.
Previous Topic: Database transfer
Next Topic: connect by query
Goto Forum:
  


Current Time: Mon Dec 05 03:10:20 CST 2016

Total time taken to generate the page: 0.13527 seconds