1

I need to make a script that get all code source (specifcation + body) from every package and put in a text asked for my teacher.

Example: BOLETIN(PACKAGE IN MY BBDD) -> BOLETIN.TXT (WITH ALL THE SOURCE).

NOTE: I cant use the utl_file, and all this "imports" is for local backup.

I see something usefull here and i tried to adapt to my import:

set pagesize 0
set trimspool on  
set headsep off 
set feedback OFF
set echo OFF
set verify off
set timing off
set linesize 4000

-- Create an sql file that will create the individual result files
SET DEFINE OFF

SPOOL C:\temp\generar_fichero.sql
    PROMPT COLUMN nombe_dinamico NEW_VALUE fichero_dinamico
    PROMPT

    PROMPT SELECT 'C:\temp\BBDD\PACKAGE\package_'||&1||'.txt' nombe_dinamico 
    PROMPT FROM DUAL
    PROMPT /

    PROMPT SPOOL &fichero_dinamico
        PROMPT SELECT TEXT
        PROMPT FROM USER_SOURCE
        PROMPT WHERE NAME = ''||&1||''
        PROMPT /

    PROMPT SPOOL OFF

SPOOL OFF

SET DEFINE &

-- Buscar los nombres de los diferentes packages
--SELECT DISTINCT(NAME) nombre_fichero
--FROM USER_SOURCE
--WHERE TYPE = 'PACKAGE'
--ORDER BY NAME;

SPOOL c:\temp\run_all.sql
    SELECT '@C:\temp\asd\generar_fichero.sql'||nombre_fichero 
    FROM (
        SELECT DISTINCT(NAME) nombre_fichero
        FROM USER_SOURCE
        WHERE TYPE = 'PACKAGE'
        ORDER BY NAME
        )
    /

SPOOL OFF

@c:\temp\run_all.sql

The problem is I dont get create the .txt file and I dont know what I'm doing wrong.

Hope you can help me with this.

Thanks in advance.

Andres Garcia Rodriguez
  • 11
  • 2
  • I found why my script is not working, cause of try to concat with the doueble || in the select and, without understand, parse to a number, give me an error in my select, so I change to this code: C:\temp\BBDD\PACKAGE\&1..sql' and WHERE NAME = '&1' – Andres Garcia Rodriguez Jul 17 '17 at 10:58

0 Answers0