Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE - Create View
EXECUTE IMMEDIATE - Create View [message #589749] Wed, 10 July 2013 08:05 Go to next message
ledubs
Messages: 2
Registered: July 2013
Junior Member
Hi,

I'm trying to recreate all views in my DB through a PL/SQL script.

I tried something but it seems that the command EXECUTE IMMEDIATE doesn't launch my request. I mean that if i print the dynamic sql with "DBMS_OUTPUT.PUT_LINE", the request is correct (and if i execute the request 1 by 1, it works) but i didn't see any result in my BD after lauching my script. Do you see anything wrong ?

Set serveroutput on;
DECLARE
  -- Sélection de la définition des vues 
  CURSOR C IS SELECT VIEW_NAME, TEXT FROM ALL_VIEWS WHERE OWNER = 'MAXIMO';
  
  -- Selection des champs des vues
  CURSOR C2 (tbname varchar2) IS SELECT column_name FROM user_tab_columns WHERE table_name = tbname order by column_id;
  Req_full varchar2(32000);
  Req_champs varchar2(32000);
  BEGIN
    FOR rec IN C LOOP
      
      -- On concatene l'ensemble des champs de la vue
      Req_champs := '';
      FOR rec2 in C2(rec.VIEW_NAME) LOOP
        if (Req_champs is null) then 
          Req_champs := '"' || rec2.column_name || '"';
        else
         Req_champs := Req_champs || ', "' || rec2.column_name || '"';
        end if;
      END LOOP;
      
      -- Creation de la requete a executer
      Req_full := '';  
      Req_full := 'CREATE OR REPLACE VIEW ' || rec.VIEW_NAME || ' ('|| Req_champs||')' ||' AS ('||rec.text||')';
      
      -- Execution de la requete + recuperation des erreurs
      BEGIN EXECUTE IMMEDIATE Req_full; 
      EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( Req_full||' : ' ||sqlerrm ) ;
      END;
    END LOOP;
  END;


Thanks

Lesdubs


[mod-edit: code tags added by bb]

[Updated on: Fri, 02 August 2013 16:35] by Moderator

Report message to a moderator

Re: EXECUTE IMMEDIATE - Create View [message #589751 is a reply to message #589749] Wed, 10 July 2013 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Remove the EXCEPTION part, read WHEN OTHERS.
Then use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel
Re: EXECUTE IMMEDIATE - Create View [message #589752 is a reply to message #589751] Wed, 10 July 2013 08:19 Go to previous messageGo to next message
ledubs
Messages: 2
Registered: July 2013
Junior Member
Thank's for answering.

Quote:
Remove the EXCEPTION part

What do you mean ?
I tried to replace this :
BEGIN EXECUTE IMMEDIATE Req_full; 
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( Req_full||' : ' ||sqlerrm ) ;
END;


with that :
EXECUTE IMMEDIATE Req_full; 


But still doesn't work Sad
Re: EXECUTE IMMEDIATE - Create View [message #589753 is a reply to message #589752] Wed, 10 July 2013 08:24 Go to previous messageGo to next message
BlackSwan
Messages: 22537
Registered: January 2009
Senior Member
>But still doesn't work

while above likely 100% true, it is also 100% devoid of any actionable detail.

my car doesn't work.
tell me how to make my car go.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.
Re: EXECUTE IMMEDIATE - Create View [message #589759 is a reply to message #589752] Wed, 10 July 2013 09:31 Go to previous message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 10 July 2013 15:15
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.


Remove the EXCEPTION part, read WHEN OTHERS.
Then use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel

Previous Topic: How to use timeout in plsql
Next Topic: trying to get two values from case stmt (2 Merged)
Goto Forum:
  


Current Time: Wed Jul 30 17:51:18 CDT 2014

Total time taken to generate the page: 0.08795 seconds