SQL & PL/SQL :: Insert Into Oracle Table Of UNIX Script Values
Dec 24, 2012
I want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table.
E.g. : insert into tbl_daily_status values(STAT,ENDTIME);
#!/bin/ksh
joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det"
for i in $joblist
do
STAT=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $6}'`
ENDTIME=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $4" " $5}'`
echo "$STAT"
echo "$ENDTIME"
done
View 2 Replies
ADVERTISEMENT
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
Jul 26, 2013
I have a table EMP having columns User_Id, ENO,Org_ID, Dept_ID.Now I would like to insert values into this EMP table using below conditions.Insert into EMP
(user_seq.nextval,(select empno from employees where empno in(....(empnumbers), (select org_id from organizations where org_name=' XXXXXXXXXX'), (select dept_id from DEPT where dname in ('MANAGER','ANALYST','SALESMAN') ))
provide me the query for the above requirement.
View 40 Replies
View Related
Apr 24, 2007
In MySQL I can insert the current timestamp by using MySQL's proprietary NOW() function.
Does Oracle have a similar function or method for adding a current timestamp with an INSERT?
View 1 Replies
View Related
Aug 26, 2011
Table Structure:
Name of Table: info
Column: ID, Name, Cell,Cell1,Ref
i want to insert these values in a table through ref (key next item).
-----------------------------------------------------------
Key next Item Triger
DECLARE
A NUMBER;
B NUMBER;
BEGIN
SELECT :CELL,:CELL1 INTO A,B FROM DUAL;
[code].......
-------------------------------------------------------
but when i press enter(ref Key_next_item) it will return error:
FRM-40735: KEY_NEXT_ITEM RAISED UNHANLEDED EXCEPTION ERROR: ORA 40102
View 12 Replies
View Related
May 15, 2013
Insert values into a table. The table is called PurchaseOrder_objtab. Here is the type for the table:
CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT
(
PONo NUMBER,
CUST_ref REF Customer_objtyp,
OrderDate DATE,
ShipDate DATE,
[code]...
The LineItemList_ntab is a nested table.
Here is the create code for LineItemList_ntabtyp:
CREATE TYPE LineItem_objtyp AS OBJECT (
LineItemNo NUMBER,
Stock_ref REF StockItem_objtyp,
Quantity NUMBER,
Discount NUMBER
)
[code]...
In the above code, the LineItemList_ntab is an empty LineItemList_ntabtyp. I am wanting to add values to this nested table rather than it being empty in the INSERT INTO code.
Here is some of the code I have tried to insert values with:
INSERT INTO PurchaseOrder_objtab
SELECT 1008, REF(C),
SYSDATE, '12-MAY-1999',
LineItemList_ntabtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004,
NULL
[code]...
The first insert statement above produces the following error:
SQL Error: ORA-00933: SQL command not properly ended
The second insert statement above produces the following error:
SQL Error: ORA-00936: missing expression
successfully insert values into the LineItemList_ntab nested table?
View 6 Replies
View Related
Oct 8, 2013
How to create this pl/sql process to add elements to a nested table or varray within a loop. Here's the scenario: I have an apex package that has some pl/sql processes and some stored procedures. I am dealing with Inspection Areas. An Inspection Area has several sectors. I already have the loop that lists all the Inspection Areas and a loop inside that loop that lists all the sectors. There is an if statement that determines whether or not the sector name gets stored in the varray or table. I am not sure how to correctly do this and am not sure whether to use a nested table or varray. I've posted somewhat of a pseudo coded example below
If (you_belong_in_table) then
variable := store_me_in_varray /* OR */
variable := array_type(sector.sector_name)
i := i + 1;
end if;
/* Now we output our varray or table */
start loop
output(sector names one by one)
end loop I hope this makes sense. I more so just need the syntax to be able to continually added values to a table or varray while I'm already inside a loop; and also how to output those values end the end as well.
View 7 Replies
View Related
Apr 19, 2010
My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.
I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.
View 2 Replies
View Related
May 4, 2012
I need to insert values fetched from an object type into an empty table.
-- >> table creation:
create table ASSET
(
ASSETID NUMBER(5),
ASSETTYPE VARCHAR2(20));
-- >> created an object_type
SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20),
2 v_ASSETTYPE varchar2(20));
3 /
Type created
--- >> create the package
SQL> create or replace package test_arr is
2 type nest_tab is table of obj1;
3 procedure insert_data(t_nest IN nest_tab);
4 end test_arr;
5 /
Package created
SQL> create or replace package body test_arr is
2 procedure insert_data(t_nest IN nest_tab) is
3 begin
4 for i in t_nest.first..t_nest.last loop
5 insert into asset(ASSETID,
6 ASSETTYPE) values (t_nest(i).v_ASSETID,
[code]....
-- >> a block to execute the above package:
SQL> declare
2 type ref_tab is table of obj1;
3 ref_tab1 ref_tab;
4 begin
5 ref_tab1 := ref_tab(1,'a');
6 test_arr.insert_data(ref_tab1);
7 end;
8 /
But I am getting the below error when executing the package:
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 1:
[code]....
View 6 Replies
View Related
Feb 22, 2010
I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :
CREATE SEQUENCE AAA_FOC.TRAINEE_ID INCREMENT BY 1 MINVALUE 0 MAXVALUE 999 NOCACHE NOCYCLE NOORDER
I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......
I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........
The code is given below:
CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
(sTraineeNo IN NUMBER,
nReturned_O OUT NUMBER)
IS
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
[code]......
View 2 Replies
View Related
May 19, 2007
I am using Oracle 9i and Unix on my system and trying to execute a UNIX shell command through external procedure in C.I created a shared lib (libextproc.so) for the following function.
int sysrun(char *command)
{
return system(command);
}
This function runs fine when caled through a driver function in C, meaning that the shared lib is fine.In PL/SQL, I have used the following method to invoke a UNIX command:-
create or replace library shell_lib as '/home/ECETRAonsite/oracle/OraHome1/lib/libextproc.so';
/
create or replace function sysrun (syscomm in varchar2)
return binary_integer
as language C
name "sysrun"
library shell_lib
parameters(syscomm string);
/
Now when I call this PL/SQL function to invoke the command, it is run succesfully but does not create the file.
SQL>
1 declare
2 rc number;
3 begin
4 rc := sysrun('/bin/touch /home/ECETRAonsite/oracle/OraHome1/test/sach');
5 dbms_output.put_line('Return Code='||rc);
6* end;
SQL> /
Return Code=0
PL/SQL procedure successfully completed.I have verified that the path for 'touch' is correct.Following are my configuration files.
listener.ora
-------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
[code]...
View 1 Replies
View Related
Dec 28, 2010
I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..
[URL].......
how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?
assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?
create materialized view my_view refresh fast as select * from t1;
create materialized view log on my_view;
and then schedule in a dbms_jobs?
View 1 Replies
View Related
Feb 23, 2010
I am trying to produce results in pivot format. I want to know how many entries per day per month. In other words how many entries on a Monday for each month, how many on a Tuesday for each month and so on.
The main problem I have is that the date column in the database uses UNIX time stamp. I have managed to get the results I want for a particular month. This code selects the entries for last month.
select
max(decode(pdayno, 1, cnt, null)) Sunday,
max(decode(pdayno, 2, cnt, null)) Monday,
max(decode(pdayno, 3, cnt, null)) Tuesday,
max(decode(pdayno, 4, cnt, null)) Wednesday,
[code].......
This code produces the result:
What I need is for another column at the start of the results to give the month so I end up with 12 rows, 1 for each month.
I did try to group by PENTERED(which is the unix time stamp column)
select pentered,
max(decode(pdayno, 1, cnt, null)) Sunday,
max(decode(pdayno, 2, cnt, null)) Monday,
max(decode(pdayno, 3, cnt, null)) Tuesday,
max(decode(pdayno, 4, cnt, null)) Wednesday,
[code]......
This gave me thousands of rows as each UNIX time stamp is unique. Is there a way of grouping on UNIX time stamp.
View 4 Replies
View Related
Aug 8, 2012
We would be moving oracle 11g unix sun solaries to oracle 11g Linux readhat OS. what would be the disadvantage and what are the item needs to be verified. Basically advantage of oracle 11g Linux readhat OS.
View 2 Replies
View Related
Jan 21, 2011
Iam planning to upgrade my Oracle Database from 10G to 11G .
Its on HP Unix Environment 11.23 and
Oracle CRS 10.2.0.4
Oracle ASM 10.2.0.4
Oracle RDBMS 10.2.0.4
Its in RAC env...and Active-Active DB running controlled by tnsnames.ora .
View 8 Replies
View Related
May 9, 2011
I want to insert some Chinese character in a row of my master table and also retrieve the same to display on web page along with others.
View 2 Replies
View Related
Aug 8, 2011
I'm trying to install Oracle 11gR2 RAC on AIX, do we need to turn on Multicasting, can we install without multicasting? Also can I have the ASM disk with external redundancy for OCR and Voting Disk?
View 3 Replies
View Related
Jun 14, 2010
I have a UNIX shell script as following,
#runs the selected process
${ORACLE_HOME}/bin/sqlplus -s ${USER_PASS} >${TMP_FILE} <<EOF
set pause off
set verify off
set pagesize 0
set linesize 2000
set timing off
[code].........
I am passing a Date to the Oracle Procedure in `date +'%b_%d-%H:%M:%S'` format.
rmsqa813.2 /home/ashlaksh> echo `date +'%b_%d-%H:%M:%S'`
Jun_14-02:26:37
The oracle Procedure's SQL is as following,
SELECT ffdh.fiscal_doc_id INVOICE_ID,
ffdh.location_id LOCATION_ID
FROM fm_fiscal_doc_header ffdh,
fm_schedule fs
[code]........
The above SQL is not getting the data as the Date format "AND ffdh.last_update_datetime <=i_last_update_datetime " is not matching..
Do I need to convert the Date ? ( But i_last_update_datetime is DATE)
View 1 Replies
View Related
Oct 17, 2013
I'm on Oracle 11g R1. I've a requirement where user will be putting CSV files on Unix server and I've to create a job which runs periodically to check if any new file is added by user in the folder. If it finds a new file (s), then it needs to identify it and insert its name in an Oracle table. Once file's name is noted, it has to move file from that directory to another one.
View 2 Replies
View Related
Nov 28, 2011
When I delete a table in Toad it takes only a one or two second until I receive success message.
But with SQL*Plus on UNIX I canceled the operation after 5 minutes:
$ sqlplus tmo323/IXQMISX8ttr@PROD
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 28 11:54:22 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> delete table_5
2 ;
^Cdelete table_5
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
exact the same SQL statement.
View 2 Replies
View Related
Feb 6, 2013
I have a Clob file as a in parameter in my PROC. . File is comma separated.need procedure that would parse this CLOB variable and populate in oracle table .
View 6 Replies
View Related
Mar 25, 2010
I installed a printer(HP Laserjet 3390) in UNIX(Solaris).Followed the instructions while installing as to the path it should be present for Reports to read it etc.,Also modified the uiprint.txt file with the desired .hpd file.Logging on to the UNIX machine, I am using the below command
rwrun.sh report=xyz.rdf userid=XXXX/XXX@xxx paramform=no p_orig_org='0825' desformat=pdf destype=printer desname=xxxx batch=yes
This command invokes the printer and
^[%12345X@PJL
@PJL SET RESOLUTION=300
@PJL SET PAGEPROTECT=OFF
@PJL SET ENTER LANGUAGE=PCL
^[%1A^[E^
@PJL RESET
^[%12345X
is what it prints.Realizing that this is because of the missing parameter,tried to change paramform=yes but I am not sure how to pass the parameter.
View 11 Replies
View Related
Jul 1, 2010
my problem is this. I created the java classes using itext to create a document library. Rtf, or. Odt. I integrated classes in Oralce form and everything works. I created a file. Odt application server on a folder and now only remains for me to open the file. The roads are two:
1. Directly from the server, you can?
2. From the client using the API or openoffice launching the executable with cmd?
Oracle Forms is a command to transfer files on the client and run the executable swriter of openoffice?
View 4 Replies
View Related
Jul 30, 2010
We have recently migrated from IAS 9.0 to IAS 10.1.2 in one of our development Server hosted in UNIX B.11.11. We are using Oracle forms as front end. We have re-compiled all the existing forms and libraries using IAS 10. There were no changes in the forms and libraries. We deployed the forms in our development box and configured it, since we are using our own environment specific copy of default.env file. With few of forms we are facing the FRM-92100 error mentioning the "Connection to Server was interrupted". Most of the forms are running fine but with few forms we are facing this error. Some of details are:-
Oracle 10.1.0.4
UNIX B.11.11
IAS 10.1.2
NLS_LANG parameter in default.env is set to "american_america.WE8ISO8859P1"..We have even tried copying the libraries in our local system and then opening and saving them and ftp'ng them back in to UNIX server and then recompiling there with full permissions but still unable to resolve this error.
View 2 Replies
View Related
Jun 13, 2013
i need a connectivity code to connect oracle DB in UNIX.pls send me a code exactly.
View 2 Replies
View Related
May 2, 2013
What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.
View 9 Replies
View Related
Jun 6, 2013
I would like to ask you if you know which built-in can I use for transferring a excel file from our Unix box to a table in oracle database, right now we are using webutil_file_transfer.Client_To_DB_with_progress using forms developer, but I need to run as an automatic process uploading form unix into oracle directly without using forms.
View 22 Replies
View Related
Nov 11, 2011
I have a file which is placed on a certain location on unix server. I want to write a code which picks up that file and emails it to certain recipients.
View 3 Replies
View Related
Mar 8, 2011
I am trying to Push the data from Oracle which is running in HP-UNIX to SQL Server. But don't know the efficient way to connect the SQL server from oracle which is running on HP-UNIX.I have heard about oracle Heterogeneous Connectivity but don't know exactly how to implement it in Unix environment.
View 1 Replies
View Related
Aug 16, 2013
I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records.
View 5 Replies
View Related