Home » SQL & PL/SQL » SQL & PL/SQL » Retrieve view definiton (Oracle 9.2.0.8, Win2003 server)
Retrieve view definiton [message #584890] Mon, 20 May 2013 06:09 Go to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Hi all,
How to retrieve a view definition to re-create it somewhere when its size is more than 32767 bytes ?
Here are some constraints :
- We are in Oracle 9.2.0.8, meaning that we cannot use the DBMS_METADATA package.
- We cannot concatenate char and long : things like
set long 100000
select 'create or replace view my_view as ' || text from dba_views where view_name = 'MY_VIEW';

are not possible.

Appreciate any help,
Amine
Re: Retrieve view definiton [message #584909 is a reply to message #584890] Mon, 20 May 2013 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create a function that will do it.

Regards
Michel
Re: Retrieve view definiton [message #584921 is a reply to message #584909] Mon, 20 May 2013 08:21 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

How ? Smile
Re: Retrieve view definiton [message #584924 is a reply to message #584921] Mon, 20 May 2013 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
SQL> create or replace function get_view (p_view in varchar2) return varchar2
  2  is
  3    l_text varchar(32600);
  4  begin
  5    select text into l_text from user_views where view_name=p_view;
  6    return 'create or replace view '||p_view||' as 
  7  '||l_text;
  8  end;
  9  /

Function created.

SQL> select get_view('V') from dual;
GET_VIEW('V')
-----------------------------------------------------------------------------
create or replace view V as
select "ID","VAL" from t1 where id between 1 and 100
union all
select "ID","VAL" from t2 where id between 101 and 200
union all
select "ID","VAL" from t3 where id between 201 and 300
union all
select "ID","VAL" from t4 where id between 301 and 400

1 row selected.

Regards
Michel
Re: Retrieve view definiton [message #584929 is a reply to message #584924] Mon, 20 May 2013 10:15 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

I have forgotten to add another constraint : view's text length is greater than 32767 bytes.
Re: Retrieve view definiton [message #584930 is a reply to message #584929] Mon, 20 May 2013 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
We have met the enemy & they is us!
It appears that somebody has dug a nice hole for themselves.
I hope the VIEW source code reside in a code repository.
Re: Retrieve view definiton [message #584931 is a reply to message #584930] Mon, 20 May 2013 10:44 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

didn't understand your poetry BS !
Re: Retrieve view definiton [message #584932 is a reply to message #584931] Mon, 20 May 2013 10:48 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
so have the function return a clob.
Re: Retrieve view definiton [message #584933 is a reply to message #584932] Mon, 20 May 2013 10:54 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

SQL> create or replace function get_view (p_view in varchar2) return clob
  2  is
  3  l_text clob;
  4  begin
  5  select text into l_text from user_views where view_name=p_view;
  6  return 'create or replace view '||p_view||' as
  7  '||l_text;
  8  end;
  9  /

Fonction crÚÚe.

SQL> select get_view('V')
  2  from dual;
ERROR:
ORA-00932: types de donnÚes incohÚrents ; attendu : NUMBER ; obtenu : LONG
ORA-06512: Ó "SYSTEM.GET_VIEW", ligne 5



aucune ligne sÚlectionnÚe

SQL>
Re: Retrieve view definiton [message #584935 is a reply to message #584933] Mon, 20 May 2013 11:07 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Fetch text into a long variable then concatenate it to a clob variable.
Re: Retrieve view definiton [message #584936 is a reply to message #584933] Mon, 20 May 2013 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is greater than 32K then you cannot retrieve it by SQL which is limited to 4K for varchar2 and you cannot do it in PL/SQL which is limited to 32K for varchar2.
So you have to do it with another language.

Regards
Michel
Re: Retrieve view definiton [message #584937 is a reply to message #584935] Mon, 20 May 2013 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cookiemonster wrote on Mon, 20 May 2013 18:07
Fetch text into a long variable then concatenate it to a clob variable.


LONG datatype in PL/SQL is limited to 32760 bytes (less than VARCHAR2 which is limited to 32767).

Regards
Michel

Re: Retrieve view definiton [message #584938 is a reply to message #584935] Mon, 20 May 2013 11:13 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

SQL> create or replace function get_view (p_view in varchar2) return clob
  2  is
  3  l_text    long;
  4  l_result  clob;
  5  begin
  6  select text into l_text from user_views where view_name=p_view;
  7  l_result := l_result || l_text;
  8  return 'create or replace view '||p_view||' as
  9  '||l_result;
 10  end;
 11  /

Fonction crÚÚe.

SQL> select get_view('V')
  2  from dual;
ERROR:
ORA-06502: PL/SQL : erreur numÚrique ou erreur sur une valeur
ORA-06512: Ó "SYSTEM.GET_VIEW", ligne 6



aucune ligne sÚlectionnÚe

SQL>
Re: Retrieve view definiton [message #584939 is a reply to message #584938] Mon, 20 May 2013 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See my previous answer(s) posted when you were writing.

Regards
Michel

[Updated on: Mon, 20 May 2013 11:14]

Report message to a moderator

Re: Retrieve view definiton [message #584940 is a reply to message #584939] Mon, 20 May 2013 11:28 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Quote:
So you have to do it with another language

Any idea how to do it with minimum cost ?
Re: Retrieve view definiton [message #584941 is a reply to message #584940] Mon, 20 May 2013 11:31 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can do it with dbms_sql. Use the column_long_value procedure to get the long in chunks.
I'm pretty sure it exists in 9i.
Re: Retrieve view definiton [message #584942 is a reply to message #584941] Mon, 20 May 2013 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but how do you return the LONG (with size > 32K)?

Regards
Michel
Re: Retrieve view definiton [message #584945 is a reply to message #584942] Mon, 20 May 2013 13:18 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
That procedure returns the long in varchar2 chunks of any size you like. So you get chunks of say 32767 length and then concatenate them to a clob.

9i documentation here: http://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_sql2.htm#1004679
Re: Retrieve view definiton [message #584947 is a reply to message #584945] Mon, 20 May 2013 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Doh! I have it in one of my scripts that creates a function named... get_view_ddl (and, of course, I no more use it since dbms_metadata exists)!
Here it is:
create or replace function get_view_ddl (
  p_view  all_views.view_name%type,
  p_owner all_views.owner%type default USER
  )
return clob
authid current_user
is

  l_ddl    clob;
  l_text   clob;
  l_cursor integer       := dbms_sql.open_cursor;
  l_query  varchar2(200) := 
           'select text from all_views where owner=:o and view_name=:v';
  l_lg     pls_integer   := 32767;
  l_pos    pls_integer   := 0;
  l_rc     pls_integer;
  l_retlg  pls_integer;
  l_sep    varchar2(1)   := ' ';
  l_cons   all_constraints.constraint_name%type;

begin

  -- Initialize DDL string
  l_ddl := 'CREATE OR REPLACE FORCE VIEW "'||p_owner||'"."'||p_view||'" (
';

  -- Add column names
  for rec in (
    select column_name from all_tab_columns
    where owner = p_owner and table_name = p_view
  ) loop
    l_ddl := l_ddl || '  ' || l_sep || '"' || rec.column_name || '"
';
    l_sep := ',';
  end loop;
  l_ddl := l_ddl || '  ) AS 
';

  -- Add text
  dbms_sql.parse (l_cursor, l_query, dbms_sql.native);
  dbms_sql.bind_variable (l_cursor, 'o', p_owner);
  dbms_sql.bind_variable (l_cursor, 'v', p_view);
  dbms_sql.define_column_long (l_cursor, 1);
  l_rc := dbms_sql.execute (l_cursor);
  if dbms_sql.fetch_rows (l_cursor) > 0 then
    loop
      dbms_sql.column_value_long (l_cursor, 1, l_lg, l_pos, l_text, l_retlg);
      l_pos := l_pos + l_retlg;
      exit when l_retlg = 0;
      l_ddl := l_ddl || l_text;
    end loop;
  end if;
  dbms_sql.close_cursor (l_cursor);

  -- Add optional constraint name
  begin
    select constraint_name into l_cons from all_constraints
    where owner=p_owner and table_name=p_view and constraint_type='V';
    if l_cons not like 'SYS_C%' then
      l_ddl := l_ddl || ' constraint '||l_cons;
    end if;
  exception when no_data_found then null;
  end;

  -- End
  l_ddl := l_ddl || '
/';
  return l_ddl;

exception 
  when others then 
    -- DEBUG (10g)
    -- dbms_output.put (dbms_utility.format_error_stack);
    -- dbms_output.put (dbms_utility.format_error_backtrace);
    -- End of DEBUG
    if dbms_sql.is_open (l_cursor) then
      dbms_sql.close_cursor (l_cursor);
    end if;
    raise;

end get_view_ddl;
/

Regards
Michel

[Updated on: Mon, 20 May 2013 13:30]

Report message to a moderator

Re: Retrieve view definiton [message #584949 is a reply to message #584947] Mon, 20 May 2013 13:37 Go to previous messageGo to next message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
It's been ages, but from what I recall you should be able to get the DDL using export/import.

exp scott/tiger owner=scott rows=n file=scott.dmp
imp scott/tiger file=scott.dmp show=y log=scott_ddl.txt

-- awk/gawk/nawk cleanup (for *nix users)
http://www.orafaq.com/scripts/awk/expdump.txt

[Updated on: Mon, 20 May 2013 13:39]

Report message to a moderator

Re: Retrieve view definiton [message #584951 is a reply to message #584949] Mon, 20 May 2013 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Indeed: or:
imp scott/tiger file=scott.dmp indexfile=ddl.sql full=y log=scott_ddl.txt

Regards
Michel
Re: Retrieve view definiton [message #584953 is a reply to message #584947] Mon, 20 May 2013 14:05 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Mon, 20 May 2013 19:27
Doh! I have it in one of my scripts that creates a function named... get_view_ddl (and, of course, I no more use it since dbms_metadata exists)!

Yes, I had a script like that too, though I can't find mine at all, and so had to go on (very vague) memory.
Re: Retrieve view definiton [message #584954 is a reply to message #584953] Mon, 20 May 2013 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so had to go on (very vague) memory.


which triggered mine. Smile

Regards
Michel
Re: Retrieve view definiton [message #584976 is a reply to message #584954] Mon, 20 May 2013 16:38 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Many thanks Michel for the share !
Re: Retrieve view definiton [message #585063 is a reply to message #584976] Tue, 21 May 2013 11:40 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Could you Michel please direct us to Quote:
one of your scripts that creates a function named... get_trigger_ddl (and, of course, you no more use it since dbms_metadata exists)!
???

Thanks in advance,
Amine
Re: Retrieve view definiton [message #585069 is a reply to message #585063] Tue, 21 May 2013 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I don't understand your question.
And your quote is wrong, it was "my scripts" not "your scripts".

Regards
Michel

[Updated on: Tue, 21 May 2013 12:48]

Report message to a moderator

Re: Retrieve view definiton [message #585078 is a reply to message #585063] Tue, 21 May 2013 17:20 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Amine wrote on Tue, 21 May 2013 17:40
Could you Michel please direct us to Quote:
one of your scripts that creates a function named... get_trigger_ddl (and, of course, you no more use it since dbms_metadata exists)!
???

Thanks in advance,
Amine

What do you think that big block of code above is?
It's the contents of a script that creates a function.
Re: Retrieve view definiton [message #585105 is a reply to message #585078] Wed, 22 May 2013 03:39 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

I just guessed that you have a script that do the same thing for triggers
Re: Retrieve view definiton [message #585107 is a reply to message #585105] Wed, 22 May 2013 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you could try to adapt the function to trigger by yourself (nothing difficult to do), you then learn how to do it instead of relying on others to do the job for you. What will you do when we'll all retire?
Once you'll have the function, post it; this will be your turn to share and help others.

Regards
Michel
Re: Retrieve view definiton [message #585109 is a reply to message #585107] Wed, 22 May 2013 03:55 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

To be honest, Orafaq makes me lazy. But promise I'll do it !
Re: Retrieve view definiton [message #585192 is a reply to message #585109] Wed, 22 May 2013 15:58 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Here it is :

create or replace function get_trigger_ddl (
  p_trigger  all_triggers.trigger_name%type,
  p_owner all_triggers.owner%type default USER
  )
return clob
authid current_user
is

  l_ddl                 clob;
  l_trigger_body        clob;
  l_cursor              integer       := dbms_sql.open_cursor;
  l_query               varchar2(200) := 
                        'select trigger_body from all_triggers where owner=:o and trigger_name=:t';
  l_lg                  pls_integer   := 32767;
  l_pos                 pls_integer   := 0;
  l_rc                  pls_integer;
  l_retlg               pls_integer;
  
  l_description         all_triggers.description%type;
begin
  
  -- Initialize DDL string
  l_ddl := 'CREATE OR REPLACE trigger' || chr(10);
  
  select description into l_description
  from all_triggers
  where owner = p_owner and trigger_name = p_trigger
  ;
  
  l_ddl := l_ddl || l_description || chr(10);
  
  -- Add trigger body
  dbms_sql.parse (l_cursor, l_query, dbms_sql.native);
  dbms_sql.bind_variable (l_cursor, 'o', p_owner);
  dbms_sql.bind_variable (l_cursor, 't', p_trigger);
  dbms_sql.define_column_long (l_cursor, 1);
  l_rc := dbms_sql.execute (l_cursor);
  if dbms_sql.fetch_rows (l_cursor) > 0 then
    loop
      dbms_sql.column_value_long (l_cursor, 1, l_lg, l_pos, l_trigger_body, l_retlg);
      l_pos := l_pos + l_retlg;
      exit when l_retlg = 0;
      l_ddl := l_ddl || l_trigger_body;
    end loop;
  end if;
  dbms_sql.close_cursor (l_cursor);

  l_ddl := l_ddl || chr(10) || '/';
  return l_ddl;

exception 
  when others then 
    -- DEBUG (10g)
    -- dbms_output.put (dbms_utility.format_error_stack);
    -- dbms_output.put (dbms_utility.format_error_backtrace);
    -- End of DEBUG
    if dbms_sql.is_open (l_cursor) then
      dbms_sql.close_cursor (l_cursor);
    end if;
    raise;

end get_trigger_ddl;
/
Re: Retrieve view definiton [message #585204 is a reply to message #585192] Thu, 23 May 2013 00:03 Go to previous message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.
It is almost this, WHEN clause is missing.

Regards
Michel
Previous Topic: please help,Error(15,22): PLS-00382: expression is of wrong type
Next Topic: Database Link Problem
Goto Forum:
  


Current Time: Wed Oct 01 19:25:08 CDT 2014

Total time taken to generate the page: 0.10290 seconds