SQL & PL/SQL :: How To Write LAST_INSERT_ID () In Oracle

Sep 25, 2013

I have a procedure in mysql that need to be converted to oracle.I tried to convert it ,everything looks good except the function LAST_INSERT_ID().

mqsql procedure

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_ac_message`(
IN in_process_run_id INT,
IN in_processID INT,
IN in_messagename VARCHAR(25),
IN in_message VARCHAR(200))
BEGIN
[code]......

oracle procedure

create or replace
PACKAGE body Mysql_To_Oracle
IS
Procedure Add_Ac_Message(In_Process_Run_Id In Number,
In_Processid In Number,
In_Messagename In Varchar2,
In_Message In Varchar2)
[code]....

Error:
ORA-00904: "LAST_INSERT_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"

View 6 Replies


ADVERTISEMENT

Can't Write SQL Scripts On Oracle G10

Apr 8, 2009

I just install the oracle g10 on my laptop, which runs windows XP and it seems to work fine BUT it will not allow me to write Scripts I go to the Script Editor, i can write on the "Script Name" but below i cant ever put the cursor to write the script!

View 3 Replies View Related

How To Write Trigger In Oracle 10g

Apr 12, 2011

I am using SQL*Plus to run this Oracle10g. Here is my problem I have to solve: After a new record is added to the task table, write a trigger that reports the new project balance to the user while updating the customer balance to reflect this additional task cost. Insert a new record to show the trigger works. I somewhat understand triggers,I think I have to combine tables to solve this. Here are my tables/attributes I can use to solve it:

CREATE TABLE customer
(
custnoNUMBER(3)CONSTRAINT ccustpk NOT NULL PRIMARY KEY,
custname CHAR(20) CONSTRAINT ccustnamenn NOT NULL,
phone CHAR(12),
curr_balance NUMBER(9,2) CONSTRAINT ccustbalancenn NOT NULL

[code]...

View 8 Replies View Related

To Write Generic Way Of Oracle 10g PL / SQL Code

Dec 14, 2010

I Would like to delete a records based on the parent / child relationship. So I would need to delete a record from parent table , first delete inner most child record then other child then master. To write generic way of Oracle 10g PL/SQL code.

View 2 Replies View Related

PL/SQL :: Oracle 10g - Write Select Statement With XML

Sep 26, 2012

I have Oracle 10g. In a table with just one record there is a clob with the following xml. In the following clob, there could be any number of Emp elements.

<?xml version="1.0"?>
<ROWSET>
<Emp>
<ENAME>SMITH</ENAME>
<EMPNO>7369</EMPNO>
<SAL>800</SAL>
</Emp>
<Emp>
<ENAME>ALLEN</ENAME>
<EMPNO>7499</EMPNO>
[code]...

I want to write a select statement which gives me result as follows (all ENAME and EMPNO from the xml)

ENAME EMPNO
SMITH 7369
ALLEN 7499
WARD 7521

View 4 Replies View Related

PL/SQL :: How To Write To Text File Using Oracle

Feb 23, 2013

How to write to text file using oracle? And how do I handle spaces/next line? (i was trying to use spaces(ch(32)), however it is just converted into squares in the text file.)

View 5 Replies View Related

Forms :: Read And Write File Using Oracle 6i?

Nov 16, 2011

I have a file studnet.txt in following order

JAMs|1231|PHYSICS
SAM|1232|PHYSICS
ALI|1233|CHEMISTRY

I want to read and write data from a file in | seprated mode. file READ and WRITE using Oracle Forms6i. I have a knowledge of file handling in C++ but not use it in Oracel Form before this.

View 5 Replies View Related

Connect To Oracle DB And Write SQL Results Into Excel File

Sep 27, 2013

I would like to connect to oracle database and would like to execute series of SQLs and write those SQL results into Excel file (sheet1).

my requirement is simple :
------------------------
db_user_id scott
db_user_pwd tiger
db_sid orcl

select count(*) from emp;
select count(*) from dept;

print the same SQLs in A column and result should be in B column in excel file.

column A column B
select count(*) from emp; 14
select count(*) from dept; 4

thats it.

View 1 Replies View Related

Precompilers, OCI & OCCI :: Write A Program Using Oci To Login Into Oracle Database?

Mar 28, 2006

I want write a program using oci to login into oracle database.Advance security option for the oracle server is set to kerberos authentication. Is it possible to login to the oracle database through olog() function.

Initial ticket is required in kerberos authentication. So is it also needed durinfg the login from my programme.

View 1 Replies View Related

Security :: Provide Read / Write Access To Oracle User?

Jun 2, 2011

I will have to provide read/write access to an oracle user. What privileges should i grant to the user so as to enable the user to read from and write to the files?

View 2 Replies View Related

Forms :: Write Trigger To Insert System Clock Time In Oracle?

Jan 4, 2013

how can i write a trigger to insert the system clock time in the oracle form?

View 15 Replies View Related

RAC & Failsafe :: Oracle 10g RAC Install On OEL5 Update 5 OUI Failing To Write On Tmp Filesystem

Nov 15, 2010

I am attempting to install Oracle 10g R2 on Oracle Enterprise Linux 5 update 5 but facing some errors. Can't believe i have done everything right as far as oracle docs n my understanding concern. when i try to invoke runInstaller for CRS it fails with following error:
------------------------------
[oracle@server01 clusterware]$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2 Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-11-15_04-36-47PM. Please wait ...[../stage/Components/oracle.swd.oui.core/10.2.0.1.0/1/DataFiles/filegroup1.jar]
End-of-central-directory signature not found. Either this file is not
a zipfile, or it constitutes one disk of a multi-part archive. In the
latter case the central directory and zipfile comment will be found on
the last disk(s) of this archive.
note: ../stage/Components/oracle.swd.oui.core/10.2.0.1.0/1/DataFiles/filegroup1.jar may be a plain executable, not an archive

Error in writing to directory /tmp/OraInstall2010-11-15_04-36-47PM. Please ensure that this directory is writable and has atleast 60 MB of disk space. Installation cannot continue.
: Success
[oracle@serverr01 clusterware]$
--------------------------------------------------

I am also attaching the cluster verfication result, if you wish to have a look at this.

View 35 Replies View Related

Server Administration :: Write Bangla Language In Oracle 10gR1 / 10gR2 And Express

May 17, 2012

I tried more to write Bangla in Oracle 10gR1, or 10gR2 or Express but failed. When I write bangla in the "emp_name" field for employee name this can be save, But when I execute to see the data again then the data comes with a big change like some symbol for some character.

View 3 Replies View Related

Forms :: Read / Write In Unix From Oracle Forms Installed In Windows

Nov 24, 2011

I have a requirement to read a file from windows m/c and write into Unix Server. How can i do that in Oracle forms.

I tried TEXT_IO package.But that is not allowing to access Unix path.

View 2 Replies View Related

Backup & Recovery :: Does Oracle Write Any Specify Value To Front Of Every Backup

Nov 7, 2011

When you create a backup in SQL Server, whether the backup is on disk or on tape, SQL Server writes the letters TAPE to the first four bytes of the backup. Does Oracle write any specify value to the front of every backup, and if so, what are the first four bytes?

View 2 Replies View Related

How To Write Exception Handler For PL / SQL

Jan 22, 2007

How to write an exception handler for the error "PL/SQL: ORA-01031: insufficient privilege"

View 2 Replies View Related

How To Write Audit Script

Oct 14, 2008

To write a audit script, that will analyze SQL usage and the performance of both individual statements and the overall memory utilization.

View 1 Replies View Related

SQL & PL/SQL :: How To Write Package For Deletion

Apr 21, 2012

how to write the package for deletion?

View 1 Replies View Related

SQL & PL/SQL :: How To Write Correlated Sub Query

Sep 13, 2010

I have employee, location, city tables, I have written following code to use joins

select ename from employee e, location l, city c
where e.c_locationid= l.locationid and l.cityid= c.cityid and c.cityname='XYZ';

The same can be written by using non- correlated sub query as follow..

select ename
from employee
where c_locationid in (select locationid from location where
cityid in (select cityid from city where cityname='XYZ'))

I need to implement the same concept using correlated sub query...

View 15 Replies View Related

SQL & PL/SQL :: How To Write Column To Row In A Query

Nov 17, 2005

How to write column to row...in a SQL query?

For example..

SQL> select empno,deptno from emp where empno = 7369;
EMPNO DEPTNO
---------- ----------
7369 20
7369 10
7369 40

The above output to be written in a single row like given below.

7369 20 10 40

1) Actually it may change dynamically...It may be 2 records for some values and there may be 10 records for some value and different for some other

2) It should be in SQL query only..Not in procedures or functions.

View 9 Replies View Related

PL/SQL :: Write Clob Or Xml To A File?

Jul 9, 2013

I have read the following article:[URL] 11070I want to know wherer if there exists a possibility of write a clob or an xml into a file on disk, if we do not have the CREATE ANY DIRECTORY privilege. Many functions, like UTL_FILE.FOPEN, or dbms_xslprocessor.clob2file, or dbms_xmldom.writetofile, need an Oracle directory to be created (with CREATE OR REPLACE DIRECTORY...). But if we don't have this privilege, is there a possibility to export a clob into a file as xml (the clob contains 100% xml, but this is the column data type, CLOB) if we don't have that privilege?The clob data contains 48200 characters.

View 8 Replies View Related

Multiple Processes To Write File?

Sep 10, 2012

In PL\SQL program, I am writing information from one table to file. In my current architecture, I am writing the information to approximately 1000 files.

If I put the database write operation in another package and in another method and call this method from my PL\SQL program asychronously, can that increase performance?

View 1 Replies View Related

SQL & PL/SQL :: Write A Trigger To Validate V_status For Each ID

Dec 30, 2010

I want to write a trigger to validate the v_status for each ID. below is the example

SEQ_NO ID V_STATUS

11 C
21 C
31 F
42 C
52 F
63 C
7 3 F
8 3 C

For each unique id there will be only one F .. if some one tries to update the 2nd F to the same ID it will show the error message that ID is already F.

SEQ_NO ID V_STATUS

11 C
21 C
31 F
42 C
52 F
63 C
7 3 F
8 3 F

View 12 Replies View Related

SQL & PL/SQL :: Write Log When Procedure Execution Is Successful

Aug 10, 2011

My need is to check whether procedure execution is successful and write sysdate and procedure name into log table.

Sure I can check target table which procedure uses to write result and count lines after execution. But I've got different procedures, some of them can write 0 lines and it's not error. I think Oracle should have something like return codes and some number means "ok".

View 12 Replies View Related

SQL & PL/SQL :: Write Trigger On Table Which Contain Lot Of Parameters?

Apr 17, 2012

I have to write a trigger on a table which contain lot of parameters.But i need to pick a specific row and check that without disturbing other stuffs.Is there a way to write Before update trigger on a particular rows filtering the unneccasary rows.

The requirement is when user update the date from front end (Java application) the trigger should check the date and validate that it should be month end date. For example.

1)04/21/2012 wrong date
2)04/30/2012 correct date
3)03/29/2012 Wrong date
4)03/31/2012 correct date

View 7 Replies View Related

How To Control File Parallel Write

Mar 2, 2011

We have a database that is accessed by ArcSDE, a product to modify maps. It uses BLOBs to store those maps.

We ran a load on the server and the response time was slow. By running the following query:

select event, total_waits, time_waited, avg_ms, round(ratio_to_report(time_waited) over () * 100) percent
from (select substr(event, 1, 30) event, total_waits, time_waited, round(time_waited_micro / total_waits / 1000, 2) avg_ms
from v$system_event
where wait_class in ('System I/O') union
select 'CPU' event, NULL, value, NULL
from v$sysstat
where statistic# = 12
order by 3 desc)
where rownum <= 10;

I get

EVENT TOTAL_WAITS TIME_WAITED AVG_MS PERCENT
--------------------------- -------------- ------------- -------- ---------
control file parallel write 127187 6354909 499.65 70
CPU 988274 11
db file parallel write 20461 886442 433.23 10
log file parallel write 14987 870672 580.95 10
log archive I/O 1557 18094 116.21 0
control file single write 149 10590 710.71 0
control file sequential read 136502 5219 .38 0
log file single write 56 2511 448.41 0
log file sequential read 489 492 10.05 0

BUG: 733426 says to change the event="10359 trace name context forever, level 1"

View 2 Replies View Related

Forms :: How To Write Query For ADD Button

Jul 23, 2010

Without using any templates, i created a customized sample form....now i want to add data thru that form....i dont know where to write code and what is code if i want to add data?

View 9 Replies View Related

SQL & PL/SQL :: Any Parser To Read And Write In Java

Mar 9, 2010

I want to know if there are any freeware's available to read and write .sql files. I hava a .sql file with set of insert statements and I need to identify the values that are pushed into table, the table name, the inner query clauses etc., from the sql statement.

After identifying, need to translate and push those changes back to the .sql file. Is there any way to do this with parsers rather reading the whole of file line by line.

I did come across few parsers like ZQL, sqljep etc

View 3 Replies View Related

SQL & PL/SQL :: How To Write Text At Specified Position In File

Apr 11, 2013

suppose i have a file named chk.txt and I have write 10 line in that file and i just want to write some text at line 5 or line first then how we can do this ?

View 3 Replies View Related

SQL & PL/SQL :: How To Write Query To Convert XML To Table

Oct 15, 2012

I have table xx_xml_test m which have row single entry

<logentry
revision="3">
<author>MA111300</author>
<date>2012-10-03T12:42:40</date>
<paths>
<path
[code].......

i want convert the table like below

Revision author date kind action path
3 MA111300 2012-10-03 12:42:40 file A /root.txt
3 MA111300 2012-10-03 12:42:40 file A /sample2/test_2.txt

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved