'HH2_SGH21_SS' is a schema I dont have access to. I have a whole bunch of schemas like this and would like to get versions of all those schemas putting it in a loop in pl/sql.
1: whats the best way get this output to a variable.
2: whats the best way to get the output to a file.
I use sqplus in oracle to output the command output to text file .
I use below set environment variable.
SQL> set echo off; SQL> set linesize 3999; SQL> set feedback off; SQL> set feedback off; SQL> set termout off; SQL> set pagesize 0; SQL> spool mapping.txt select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
In ouput file , it look like
SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN; 060010007 10007 : : : SQL> spool off;
any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"
I use sqplus in oracle to output the command output to text file .
I use below set environment varialble.
SQL> set echo off; SQL> set linesize 3999; SQL> set feedback off; SQL> set feedback off; SQL> set termout off; SQL> set pagesize 0; SQL> spool mapping.txt select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
In ouput file , it look like
SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN; 060010007 10007 : : : SQL> spool off;
any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"
I'm trying to return the number of records in my link table that contains the excursion_id I pass in by counting them. It doesn't seem to like the select count(*) into my output variable.
create or replace PROCEDURE BOOK_PASSENGER( EXCURSION_ID IN excursion_booking.excursion_id%TYPE, PASSENGER_ID IN excursion_booking.passenger_id%TYPE, NUMBER_BOOKED OUT NUMBER) AS BEGIN INSERT INTO EXCURSION_BOOKING VALUES(EXCURSION_ID, PASSENGER_ID, NULL); SELECT COUNT(*) INTO NUMBER_BOOKED FROM EXCURSION_BOOKING WHERE EXCURSION_ID = EXCURSION_ID; END BOOK_PASSENGER;
I tried BRK and COMPUTE commands myself after reading the documentation but its not working...The output of my script is correct but What I want is the information to be displayed on a different way:
Original script for the report:
set serveroutput on size 1000000 set pages 10000 set lines 1000 set arraysize 1 set trimspool on set heading off [code]....
The output should be exactly as above as the requirement is this should be in this particular order and how many is the count in each thoroughfare or locality, as shown in the final output, above.
after precompiling the cmpre.pc file, i got cmpre.c file. when i try compiling this .c file, am facing the following error...
servername:/.../home/usr/compile-> cc -g cmpre.c ld: 0711-317 ERROR: Undefined symbol: .sqlorat ld: 0711-317 ERROR: Undefined symbol: .sqlcxt ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.
CREATE OR REPLACE PROCEDURE A516907.proc_data_T_DTIA_TS_ADDR is cursor CSR1 IS SELECT * FROM T_DTIA_TS_ADDR ; v_file UTL_FILE.FILE_TYPE ; BEGIN v_file := UTL_FILE.FOPEN( 'DATA_PUMP_DIR' , 'T_DTIA_TS_ADDR.txt','w',32767); FOR CS1 IN CSR1
[Code]....
The procedure above is dynamically generated. It's simple enough in that I am calling "the parent procedure" by passing the table name in. After this the procedure for creating the output data file for that table is generated and that's what we have above. I am doing this to load data into DB2.
What I need is to be able to generate the output such that if the column is of type varchar, char (anything non numeric) then the data should have " around it. If the data is of type number it should not have ".
I suppose I could use something as follows in lieu of the procedure above
Declare cursor c1 is select column_name, data_type from user_tab_Cols where table_name = 'Table Name here';
-- I would like to know how to use the utl_file.put_line -- where if the column data type is varchar 2 -- I could get ' ||"|| CS1.TS_ADDR_TY_CD||" || ' -- and if it's a number -- || CS1.CB_TS_DTIS_ID ||
I am trying to get query output into textfile.The following procedure is working fine by creating directory as follows in sys user.The output is getting onto text file and it seen on server machine even.My question is I want to see the text file on local machine also instead of everytime connecting to server machine drive.How can I perform that?
create or replace directory INFO_DIR as 'D:Swapna'; grant read, write on directory INFO_DIR to fairpoint; CREATE OR REPLACE PROCEDURE FAIRPOINT.utl_file_test_write_xls(filename in VARCHAR2 ) IS output_fileutl_file.file_type; v_pathVARCHAR2(500); v_stringVARCHAR2(4000); v_sqlstrVARCHAR2(2000); BEGIN BEGIN [code]....
I am new to oracle which command do I use to set the file storage in oracle? I learnt the we have the option of specifying the storage pattern as per our queries.
I am in the process of recovering a database from an ASM environment to a non-ASM environment using RMAN. I've got a query to generate the command to RENAME the file:
When I run the query, this is an example of the result I get:
ALTER DATABASE RENAME FILE '+FRA/db1/onlinelog/group_3.259.766075171' TO '/u01/data/dev/group_3b.log'; ALTER DATABASE RENAME FILE '+DATA/db1/onlinelog/group_1.262.766075829' TO '/u01/data/dev/group_1a.log';
But I want to replace the "group" with "redo" and get the below result:
ALTER DATABASE RENAME FILE '+FRA/db1/onlinelog/group_3.259.766075171' TO '/u01/data/dev/redo_3b.log'; ALTER DATABASE RENAME FILE '+DATA/db1/onlinelog/group_1.262.766075829' TO '/u01/data/dev/redo_1a.log';
I'm running a sql file in Unix (using SQLPLUS command) and i want the file output to be sent to local windows directory. I specified the windows directory name in my Spool command (Spool C:/<Directory Name> ) , But it is not working
Our Unix server is a FTP server but i don't want to FTP the spool file from unix to Windows as the spool file is Huge and it takes hours for the transfer to complete (and we have to run the script multiple times).
Is there a way i can have the spool file created in Local windows directory when i run my sql script in Unix?
I am trying to import data in the following user : core_edb_20112_ct/local This user is already created , using the tablespace named C64_EDB_TS The dmp file resides in the location dir_core20112 ( e:\oracle)
I am getting the following error while i try to import
Import: Release 11.2.0.1.0 - Production on Mon May 2 12:47:54 2011
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc tion With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39002: invalid operation ORA-31694: master table "CORE_EDB_20112_CT"."SYS_IMPORT_FULL_01" failed to load/ unload ORA-02354: error in exporting/importing data ORA-02368: the following file is not valid for this load operation ORA-02369: internal number in header in file e:\oracle\core_edb_20112_ct_20110426.dmp is not valid.
The DMP is copied from a different network location into the local drive where the command is running.
I am calling a stored procedure from a shell script. This stored procedure is having a CLOB object as an OUT parameter. How to write the data in CLOB object a existing file which is there in my client system. Below is the shell and sql scripts.
Shell script ( echo "hello" ) > /root/file.txt sqlplus -s $user/$pass@$tns @/root/proc.sql /root/file.txt << EOF EOFSQL script set pages 0 set trimspool off
[Code]...
spool offThis code writes contents of the OUT variable to file.txt, but my existing data ('hello') is lost.
I figured the it...Use append in the spool command...
We are running a store procedure by calling it from shell script.The shell script is running in Server1 and the stored procedure is running in server2(which is the data base server.).
Functionality of the stored procedure:Generating a tab delimited file and place in a directory.
Requirement: The requirement is that the store should be modified in such a way that it should drop the output file to Server1 itself(The server from where it is getting called).
Attaching the store procedure for reference. mIn the store procedure,we have a variable "lv_file_dir" which currently refers to the directory location in Databse server(Server 2) We have to change this SP such that it drops the generated file in Server1 itself(From where the shell script is running).
Attached File(s) Procedure.txt ( 3.28K ) Number of downloads: 4
I'm calling myreport.rdf from a form using the rwservlet and destype=mail and desformat=pdf. The report file is attached to the email as myreport.pdf. However, I want to have the output file named myfilename.pdf. Is there any way to accomplish this short of using destype=file and then renaming it and sending that file?
I've noticed that the backup files produced by RMAN (Database is 11.2.0.2.0 Enterprise Edition) )on a RHEL5.5. server which is running ASM have group permissions assigned to ASMADMIN (so the file permissions are assigned to ORACLE:ASMADMIN) as opposed to OINSTALL or any other group.
While this is not an issue (it's not currently impacting us in any way) I'm curious as to how and why this happens. I suspect it is related to the fact that the oracle executable has these permissions also but I can't find any material to confirm my suspicion.
I need to take RMAN full backup by every Sunday night and Wednesday night . I have the netbackup script which will take the backup to media.
Question is:1. We have 15 - backup scripts for 15 DB in that server , so if i configure crontab for backup do i need to give all 15 scripts one by one or (*) will work . ie) 00 20 * * 3,7 /app/oracle/rman/scripts/hot_db_<sid>.sh
instead of this if i give : 00 20 * * 3,7 /app/oracle/rman/scripts/hot_db_*.sh then it will works fine or not ? 2.
Already my backup will create .output file in the same location , do i need to give output file location in crontab using ( > ) ? 3.
Is the above crontab timing is correct ? (3,7) for Wednesday and Sunday 8pm? so Sunday is 0(zero) or 7 ?
I executed a query which executed quickly (1.7 seconds) but since its output took time in displaying on the console the time shown by 'set timing on was 39.5 seconds
also I took trace (tkprof) for the same.My query is why the timings under 'Total Waited' (43.19 and 1.69) are not added to the elapsed time 1.83 seconds
I'm trying to write a bat file for the rman backups that can be used for any of our databases. I'm passing variables into the bat file and that is working fine (i.e. oracle_sid, drive letter, oracle_home), my problem is when the rman actually runs and calls the ctl file, the variables aren't being passed thru to that file.
