I would like to use the Spool command to export data for other purposes within the application. We would like to use tab delimiter to seperate the fields but the client wants to know if the text datatype fields can be wrapped in double quotes along with the tab delimiter..
SQL>create table test (id number(2), first_name varchar2(15), last_name varchar2(15),var_no number(4), type varchar2(1),type_no number(12));
Table created.
SQL> insert into test values(1,'mary','ross',132,'S',12);
1 row created.
SQL> insert into test values(3,'Sue','Bill',432,'S',12);
1 row created.
I tried the below spool command to use tab delimited for all the fields but not sure how to wrap double quotes for only the text fields and also would to have the column names in the 1st row but don't seem to get the full column name in the csv file.
set echo off set feedback off set linesize 1000 set pagesize 4000 set trim on set headsep off set colsep '' (used tab between the quote)
spool test.csv select id,first_name,last_name,var_no,type,type_no from test; spool off
Up until today we assumed that compiling a package BODY-only would not cause invalidation of other code. However when we compiled the body of the HTP package, the OWA package became invalid and required re-compilation.
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production PL/SQL Release 10.2.0.4.0 - Production CORE10.2.0.4.0Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
I want to see a stored procedure definition which is inside the body of a package and the package is wrapped.So I am not able to see the content of the package body.I am new to oracle and I don't know how to decrypt the wrapped package.how to read the content of a wrapped package inside oracle.
I tried to create a UTL_SMTP package using '@?/rdbms/admin/utlmtp.sql' script but there are no package body for UTL_SMTP is created. There are only UTL_SMTP package is create.
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 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...
1- I do not have access to TOAD for Oracle yet 2- I can connect to SQLPLUS: Release 9.2.0.1.0 3- We have many stored procedures in packages that are available from the TOAD for Oracle interface 4- I need to be able to see the specs/body of some packages containing some procedures. 5- I am connected to the appropriate DataBase1 (for example), but from here what to do from SQLPLUS command prompt ?
For example: SchemaName1.PackageName1.ProcedureName1
I've compiled a package with warnings ON. Getting message SP2-0809: Package Body Created with Compilation Errors.When I checked the errors by issuing "Show Errors", it shows "No error".
SQL> ALTER PACKAGE PKG_ABC COMPILE PLSQL_WARNINGS = 'ENABLE:ALL'; SP2-0809: Package Body Created with Compilation Errors SQL> SHOW ERRORS No Errors
how to check errors for that, or oracle simply pointing that package might have performance issue.
I want to know how we can declare a Global Variable in Package body(Not Spec), So that i can use it in any procedures or function(Defined in same package).
I have a package which has couple of Procedures and functions. I use some constants between the functions and procedure and also functions r called from the other procedure and stuff...
So I declared all these in the header
Like this
CREATE OR REPLACE PACKAGE "PROCESS_HISTORY" IS type table_name_i is varray(100) of VARCHAR2(30); c_add constant number := 1; c_del constant number := 2; c_select_frm_tmp constant number := 1; [code]......
Now is there anyway I could hide the stuff that I don't want outside people to see? or can I declare them (constants and some functions) some where inside the body and use them?
ORA-31626: job does not exist ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_ LOGSTDBY" ORA-06512: at "SYS.KUPV$FT", line 949 ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"
CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT ( TEST_ID NUMBER(9), TEST_DESC VARCHAR(30) ) / CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE / [code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
CREATE OR REPLACE PACKAGE pkg_mkt_hub_load_collection AS PROCEDURE sp_final_load_mkt_hub; END pkg_mkt_hub_load_collection; / CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection AS c_default_limit CONSTANT PLS_INTEGER:=5000;
[code]....
show error
error code
SQL> @pkg_mkt_hub_load_collection.sql Package created. Warning: Package Body created with compilation errors. Errors for PACKAGE BODY PKG_MKT_HUB_LOAD_COLLECTION: LINE/COL ERROR -------- ----------------------------------------------------------------- 57/4 PL/SQL: Statement ignored PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL' SQL>
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 am try to import 4G dump in Oracle 11R2 version, in that we have around 9000+ Package Body which is taking huge time than other objects (about 8 to 12 hrs) and also it is expecting lots of system space (roughly about 10GB).
I have tried both parallel and non-parallel.how to improve speed of the package body import.
Details about the Schema & Import No. of objects in Schema
SQL> select object_type,count(1) from user_objects GROUP BY ROLLUP( object_type);
OBJECT_TYPE COUNT(1) ------------------- ---------- FUNCTION 248 INDEX 5161 JAVA CLASS 471 JAVA RESOURCE 1 JAVA SOURCE 16 LIBRARY 1
I am using expdp command to export the table by specifying Query parameter. But i am unable to export the table based on the condition.
Ex:EXPDP username/password dumpfile=employee.dmp logfile=emp.log directory=DATADIR_EXP TABLES=EMPLOYEE query=EMPLOYEE:"UPDATED_TIME >= '04-JUN-13' AND UPDATED_TIME >= '05-AUG-13'" Estimate in progress using BLOCKS method...Processing object type TABLE_ EXPORT/ TABLE/ TABLE_ DATATotal estimation using BLOCKS method: 3 GBORA-31693: Table data object "<username>"."EMPLOYEE" failed to load/unload and is being skipped due to error:ORA-00933: SQL command not properly endedMaster table "<username>"."EMPLOYEE" successfully loaded/unloaded...Dump file set for <username>.SYS_EXPORT_TABLE_01 is: E:IMPDPemployee.dmpJob "<username>"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 12:34:45 Oracle 11g,
I want to get all the column values in a table and save them into a text file.Beside UTL_FILE, is there any other method which will result better performance in writing to text file?
I have taken database backup using exp command and when I try to import in other pc the foreign keys are not imported. It saying error message that no matching unique key or primary key for this column.
how will i take backup including with primary keys?
Is it possible to take this schema export in windows command prompt mode and any guess how long it would take to complete the export ?Because based on the time it takes, i am going to perform the export in windows command prompt.
we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
