Fwd: Retrieving just one store procedure

 Harshan

If you have a relatively small export file (particularly if the , you could just open it in an editor and cut out what you need.

If it's larger, and you are in *nix, you could try using the strings command:

strings myfile.exp > expstrings.txt

and then work on that.

I have just opened a small (Windows, 10g XE) export using Wordpad and it looks to me like the procedures are included in plain text, but each line is preceded by a non-printing char (possibly the line length) like this (the look in Wordpad is a wingding at the start of each line; looks slightly different here):

CREATE FORMAT71 PACKAGE "COUNTER_PKG" ýÿ �CREATE �PACKAGE "COUNTER_PKG" AS

� g_counter integer := 0;

�

� procedure reset_counter;

� procedure increment_counter;

1� procedure print_counter(message in varchar2);

�END; If you use od -a to dump the exp file contents (thanks to cygwin for having that on Windows), here is a bit of it. You can see two octets at the start of each line (after nl). Look at the declaration of g_counter; the line us preceded with esc, nul. esc is decimal 27 - which is the length of the line up to and including the next nl (shown here in red, if it makes it through Oracle-L...

0301320 A G S sp = sp ' ' nl C R E A T E sp 0301340 F O R M A T 7 1 sp P A C K A G E 0301360 sp " C O U N T E R _ P K G " sp nl 0301400 } del bel nul C R E A T E sp em nul P A C 0301420 K A G E sp " C O U N T E R _ P K 0301440 G " sp A S nl esc nul sp sp sp g _ c o u 0301460 n t e r sp i n t e g e r sp : = sp 0301500 0 ; nl eot nul sp sp sp nl fs nul sp sp sp p r 0301520 o c e d u r e sp r e s e t _ c o 0301540 u n t e r ; nl sp nul sp sp sp p r o c 0301560 e d u r e sp i n c r e m e n t _ 0301600 c o u n t e r ; nl 1 nul sp sp sp p r 0301620 o c e d u r e sp p r i n t _ c o 0301640 u n t e r ( m e s s a g e sp i n 0301660 sp v a r c h a r 2 ) ; nl enq nul E N 0301700 D ; nl nul nul nl A L T E R sp S E S S

Hope that helps

Regards Nigel

2008/9/25 Harshan Vasudevan Eppurath <harshan.eppurath_at_capgemini.com>

  Hello,
>
>
>
> I would like to know whether it is possible to retrieve just one stored
> procedure from an export. We are currently running 10.2.0.3.0.
>
>
>
> Cheers
>
> Harshan
>
>
>
> Harshan Eppurath
>
> Financial Services Strategic Business Unit
> Team: Basel II, Database Administrator BI Practice /
> Office: +91.40.2312.5000 / Extn: 28445 /Mobile: +919392426435/
> Fax: +91.40.2312.5002 / http://www.in.capgemini.com /<http://www.in.capgemini.com%20/>
> harshan.eppurath_at_capgemini.com <dorababu.kottapalli_at_capgemini.com>
> Kanbay, A Capgemini Group Company , #5, Software Units Layout |
> Madhapur|Hyderabad-81.* *
>
> *Together: the Collaborative Business Experience *
>
>
>
>
>
> This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is
> intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to
> read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message
> in error, please notify the sender immediately and delete all copies of this message.
>
>

--
http://www.freelists.org/webpage/oracle-l