plsql

SQL to generate a list of numbers from 1 to 100

Using the DUAL table, how can I get a list of numbers from 1 to 100? ...

Getting user data from Active Directory using PL/SQL

I had a discussion today regarding an Oracle procedure I wrote some time ago. I wanted to get 7500 user email addresses from Active Directory using PL/SQL. AD will return a maximum of 1000 rows and the LDAP provider used by Oracle will not support paging. Therefore, my solution was to filter on the last two characters of the sAMAccount...

using FUNCTION instead of CREATE FUNCTION oracle pl/sql

I see people writing a function with FUNCTION instead "CREATE FUNCTION". When I saw this usage in the web I thought it was a typo or something. But in Oreilly's "Oracle 11g PL/SQL Programming" by Steven Feurenstein, the author had used the same thing. But I get errors when I execute that. Could somebody explain is it legal usage or not?....

is versus as pl/sql

I thought that oracle treats both "is" and "as" same for functions and procedures.I tried googling with "pl/sql is vs as" and got the following link which says both are the same. http://stackoverflow.com/questions/2338408/is-vs-as-keywords-for-pl-sql-oracle-function-or-procedure-creation But I found http://www.adp-gmbh.ch/ora/plsql/col...

Reading from an write-only(OUT) parameter in pl/sql

When I tried writing to an read-only parameter(IN) of a function, Oracle complains with an error. But that is not the case when reading from an write-only(OUT) parameter of a function. Oracle silently allows this without any error. What is the reason for this behaviour?. The following code executes without any assignment happening to "s...

Oracle Training Providers

I'm looking to find an Oracle PL/SQL training course for a colleague of mine. I've had first-hand experience with Oracle University and was quite satisfied with my experience, but the courses currently available through Oracle University are limited to options out of our budget when considering travel and 5 days of hotel. I'm wonderi...

oracle call stored procedure inside select

Hi everybody. I'm working on a query (a SELECT) and I need to insert the result of this one in a table. Before doing the insert I have some checking to do, and if all columns are valid, I will do the insert. The checking is done in a stored procedure. The same procedure is used somewhere else too. So I'm thinking using the same procedu...

passing parameters from unix to pl sql

Hi. I have a dumb problem. From a ksh I'm connecting to sql plus to execute some query. I want to pass 2 parameters from unix to pl sql. I found some stuff but it does not works. UNIX: sqlplus -L $ORA_CONNECT @"$FIC_REQ" $1 $2 PLSQL: DECLARE param1 := $1; param2 := $2; BEGIN SELECT * from MYTABLE where field1=param1...

how retrieve the completely result from shell in Asynchronism process???

Hi all refer to these post : here1 and here2 at last I solve my problem by build a asynchronous solution,and it work well!!! but there is a problem that i face with it,now my code is like this: class MyProcessStarter { private Process process; private StreamWriter myStreamWriter; private static StringBuilder ...

Postgresql Schema Path permanently

I need to set schema path in Postgres so that I don't every time specify schema dot table e.g. schema2.table. Set schema path (SET SCHEMA PATH a,b,c) only seems to work for one query session on mac, after I close query window (on pg-admin) the path variable sets itself back to default. How can I make it perm ? Would greatly appreciate a...

Oracle SELECT query: collapsing null values when pairing up dates

I have the following Oracle query: SELECT id, DECODE(state, 'Open', state_in, NULL) AS open_in, DECODE(state, 'Not Open', state_in, NULL) AS open_out, FROM ( SELECT id, CASE WHEN state = 'Open' THEN 'Open' ELSE 'Not Open' END AS state, T...

Problem with UPDATE statement in stored-procedure in Oracle Database

Hello, I have stored-procedure in Oracle database like this: create or replace PROCEDURE EDYTUJ_PRACOWNIKA (PR_IMIE IN VARCHAR2, PR_NAZWISKO IN VARCHAR2, PR_PENSJA IN FLOAT, PR_PRZELOZONY IN NUMBER, PR_ODDZIAL IN NUMBER, PRAC_ID IN NUMBER) AS tmpPensja FLOAT := 0; tmpPrzel NUMBER := 0; BEGIN select przelozony into tmpPrzel from pr...

number of rows affected by rollback

Hi, I need to find out the number of rows affected by a rollback. How can I get this ? Please help. ...

Invoking a SOAP ( Web Services ) from ORACLE DB

Dears, Kindly note that I’m trying to invoke a SOAP (web services) from ORACLE DB using pl\sql , after I have done some investigations it says that I have to use the UTL_HTTP package but It didn't work with me !!! Kindly to advice me , where should I exactly place the following SOAP in pl\SQL to be invoked .... is it posible ? SOAP ...

continue statement in pl/SQl?

Possible Duplicate: CONTINUE keyword in Oracle 10g PL/SQL I am Using Oracle 9i and I want to use continue statement or its equivalent in PL/SQL. So is there any keyword called "continue" in oracle 9i. If not, please let me know the solution for this. ...

Passing an array of data as an input parameter to an Oracle procedure

I'm trying to pass an array of (varchar) data into an Oracle procedure. The Oracle procedure would be either called from SQL*Plus or from another PL/SQL procedure like so: BEGIN pr_perform_task('1','2','3','4'); END; pr_perform_task will read each of the input parameters and perform the tasks. I'm not sure as to how I can achieve th...

PL/SQL How return all attributes in ROW

Hi I don't know how I can return all attributes with the RETURNING clause I want something like this: DECLARE v_user USER%ROWTYPE BEGIN INSERT INTO User VALUES (1,'Bill','QWERTY') RETURNING * INTO v_user; END; RETURNING * INTO gets an error , how can I replace * ? Have you any idea ? Thanks for yo...

Can a sub-procedure procedure lock and modify the same rows FOR UPDATE that its calling procedure already locked?

Will the following code lead to a deadlock or should it work without any problem? I've got something similar and it's working but I didn't think it would. I thought the parent procedure's lock would have resulted in a deadlock for the child procedure but it doesn't seem to be. If it works, why? My guess is that the nested FOR UPDAT...

Ensure Oracle row represents a unique timespan

I have to make a process in Oracle/PLSQL. I have to verify that the interval of time between start_date and end_date from a new row that I create must not intersect other start_dates and end_dates from other rows. Now I need to check each row for that condition and if it doesn't correspond the repetitive instruction should stop and afte...

ORACLE: Parameter reference in WHERE doesn't work

Hello, I have created a simple static function in oracle 10g to get the reference of an object based on his pk. STATIC FUNCTION getRef(nome IN VARCHAR2) RETURN REF folder_typ IS fl_r REF folder_typ := null; BEGIN SELECT REF(fl) INTO fl_r FROM folder_tab fl WHERE fl.nome = nome; RETURN fl_r; END getRef; Thi...