PL/SQL :: Create A Procedure For SELECT Query - Error?

Nov 1, 2013

how do I create a procedure for a SELECT query like the following?

When I create a procedure; I get an error "Error(80,1): PLS-00428: an INTO clause is expected in this SELECT statement" PROCEDURE  MyProcISBEGINselect 'Dakota' as ALIAS      ,A.StartDate      ,B.EndDatefrom Customer A    ,Clients bwhere  a.cType = b.cTypeand b.Active =0ORDER BY StartDate, EndDateEND  MyProc;

View 17 Replies


ADVERTISEMENT

SQL & PL/SQL :: Create A Select Procedure With Parameter?

Feb 2, 2011

I have a query like this:

*************************
SELECT vendor_id, summary_flag
FROM ap_suppliers
WHERE vendor_id = :param; --'4551'

The Results:

************
VENDOR_ID SUMMARY_FLAG
-----------------------
4551 N

Then I create the procedure:

*****************************
CREATE OR REPLACE PROCEDURE myproc4 (
p_vendor_id IN ap_suppliers.vendor_id,
p_summary_flag OUT ap_suppliers.summary_flag
)
AS
BEGIN

[code]....

Warning: compiled but with compilation errors.

I want to create a procedure that call vendor_id (parameter) and the output like the this:

VENDOR_ID SUMMARY_FLAG
-----------------------
4551 N

View 4 Replies View Related

Create Table From Select Query?

Sep 13, 2004

Can we create a table from a Select query ?

View 5 Replies View Related

SQL & PL/SQL :: Create A Function Which Can Be Used In A Select Query?

Jun 11, 2008

I have a table structure like :-

Create table test(A varchar2(50),B NUMBER);

The data in that table is like that:-

A B
----------------------
2*3
2*4*5
4*5
column B contain no data.

I want to create a function which can be used in a select query,and the output should come like that :-

A B
----------------------
2*3 6
2*4*5 40
4*5 20

Means column B contains the resultant value of column A.And the above output should come through a select statement.You can use any function inside the select statement.

View 20 Replies View Related

SQL & PL/SQL :: Create Nested Query Within Select Of Attributes

Dec 21, 2011

I'm trying to create a sort of nested-query within my select of attributes . i.e.

select A.a,
B.b,
(select count(C.*)
from C
where C. = B.d
group by C.y)
from A a,
B b
where A.d = B.d
and ...

Over-simplifying my query:

select B.desc "Location",
F.desc "Source",
A.amt "Amount",
sum(G.G_CNT) "No. Units",
c.desc "Status"
[code]....

I need to incorporate a count of the number of units from TableG that have a certain status. I tried the following but when I tried to run it, I get an error saying that it's not a Group By expression -the red part is highlighted in TOAD.

select B.desc "Location",
F.desc "Source",
A.amt "Amount",
sum(G.G_CNT) "No. Units",
(select count(*)
from TableG G2
where G2.D_ID = D.ID
and G2.status = 10
group by G2.D_ID)"Count",
c.desc "Status"
[code]....

Any thoughts how I can incorporate a query in my select of attributes?how to Group By something.

View 8 Replies View Related

Create Query On Procedure - Change Value In Table?

May 21, 2013

how can i create this query on a procedure:

Insert into COMPUSOFT.PESAJE@DB_2
(PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA,
PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD,
USR_COD, PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO)
select /*+ FULL(Tbl1) */

[code]..

plus i would like to insert also that when it runs the query also change a value in table pesaje column dmn_cod to "yes" default "no" in db_2

View 5 Replies View Related

SQL & PL/SQL :: Create Select Query That Will Get Data From 18 Months Ago Based On Sysdate?

Sep 9, 2013

I have a table with columns job_id, jan, feb, mar ... , and year

I need to create a select query that will get the data from 18 months ago based on sysdate.

So something like:

Select to_char(add_months(sysdate, -18),'MON') from table1 where job_id = 56947 and year = to_char(add_months(sysdate, -18),'YYYY');

However I need the result of to_char(add_months(sysdate, -18),'MON') to actually act as a column name, not a string result.

View 23 Replies View Related

Application Express :: LOV Select List / How To Create Query With Begin And End

Jun 11, 2012

i am using Apex 3.2 ver.

i want to use below code in LOV select list

BEGIN
IF  UPPER(:P23_SERVICE_TYPE) like 'GUIDE%' THEN
SELECT NAME D, CODE R FROM SPECIAL_SERV_MAS
WHERE NVL(ACTIVE_FLG,'N') = 'Y'
AND NVL(GUIDE_FLAG,'N') = 'Y'
and CITY_CODE LIKE NVL (:P23_CITY_CODE, '%')

[code]....

When i put this code in my LOV Select list Section then display me Error

Not Found The requested URL /pls/apex/f was not found on this server.

Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server Server at tidevserv1 Port 7777

View 3 Replies View Related

Getting Deadlock Detected While Waiting For Resource Error For Select Query

Nov 4, 2013

i am getting a below error whenever executing the below select query. some times it will show dead lock detected while waiting for resource and terminated...some times it executes and gives result..but all the time it writes an alert to alert log 

Env: Linux / Oracle 11.2.0.3.3..Error from alert log:Errors in file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trc:ORA-00060: deadlock detected while waiting for resourceORA-10387: parallel query server interrupt (normal)  Trace file info... bdrdb_p017_6076.trc:Trace file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trcOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/oracle/oracle/product/11.2.0/dbhome_1System.
[code]....

View 11 Replies View Related

Ora-06502 - Create Generate Procedure That Create Dynamic Procedure Through DBMS_SQL

Mar 31, 2004

ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.

View 3 Replies View Related

Parenthesis Create Error In Query / How To Clean Text Before Use

Nov 10, 2008

i am binding a string to a CONTAINS search (oracle text) and its throwing an error because of characters within the string.

this causes the error.
Quote:
(A. A.)
if i take out ( and ) it works
Quote:
A. A.

i thought binding took care of these issues as well as sql injection?

example:

SELECT
score(1) the_score,
id
FROM
t_table
WHERE
contains(text_column, :BINDVAR1, 1) > 0

how can i get around this and other character errors on the fly?

View 4 Replies View Related

Create Procedure To Create User In Oracle

Jan 19, 2012

I need to create PROCEDURE to create user in oracle

CREATE OR REPLACE PROCEDURE "CREATE_USER_ORACLE8"
(
USER_ID in VARCHAR2,
PASSWORD in VARCHAR2,
ROLES in VARCHAR2,
nReturnCode OUT NUMBER
)
BEGIN
[code].......

Compilation errors for PROCEDURE NOG.CREATE_USER_ORACLE8

Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

; is with authid deterministic parallel_enable as
Line: 9
Text: BEGIN

i want that the customer execute PROCEDURE (user_id,password,PROCEDURE )

View 5 Replies View Related

PL/SQL :: Create A Procedure Inside A Procedure

Jun 25, 2012

DB version:11g

can we create a procedure inside a procedure...if not is there any alternative??

in db2 it is allowed so do oracle support this????

View 5 Replies View Related

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

Getting Top-N Query To Work As Sub-select In Larger Query?

Mar 10, 2012

Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?

Background:

We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.

The legacy HR system allows multiple phone numbers to be stored in a simple table structure:

SELECT emp_id, phone_type, phone_number
FROM employee_phones

emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333

The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)

From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.

That can be represented as:

SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333

Or similarly with Top N concept:

SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111

Or really what I want in my export:

SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

phone_number
-------------------
1111111111

However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.

(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)

1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?

2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?

Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.

View 3 Replies View Related

SQL & PL/SQL :: Single Select Statement Instead Of Procedure?

Oct 2, 2010

presenting the case as follows:There are two dates with time components like

26-sep-2010 13:00 and 29-sep-2010 19:00

In between these dates hours between 20.00 to 05.00 (night hours)need to be considered and In these night hours less than three hours can be ignored and between 3 to 9 hours should be treated as 1. Need the number of one's in between the dates.

Procedure, cursor solution is not needed and need single select statement.

View 5 Replies View Related

SQL & PL/SQL :: How To Make Procedure From Select Statement

Feb 8, 2011

i do have below query in oracle db just joining two views,

select docview.firstname as "First Name", docview.lastname as "Last Name", docview.mrn as "MRN", docview.physician as "Physician", docview.surgicalcong "Surgical Consent Missing", docview.admissionnotemissing "Admin Assessment Missing",
(case (docview.doctype) when 135 then 'Doctor Chart Checklist' else 'DS Chart Checklist' end) as doctypename,
docview.hpmissing "History & Physical Missing", docview.nursingassessment "Nursing Admission/Assessment",

[Code] ........


how can i make a procedure with below two steps,

1) load the result of above query to some temp table / or even a fixed table (which ever easier)
2) list all data from the temp table / fixed table created in first step

so in the final expecting result is

execute myprocnamehere -- will result same as running above query

View 3 Replies View Related

SQL & PL/SQL :: Create Table As Select On 10.2.0.5?

Oct 26, 2011

running on 10.2.0.5 and above satisfies the following:

having locally managed tablespace with assm enabled

create table t1 (n1 number) storage (next 1M);
insert into t1 values (1); commit;
create table t2 storage (next 2M) as select * from t1;

This will indeed set the storage option next to 2M;

The same situation in 10.2.0.4 will allways set next option for the ctas statement to the initial value of table t1.

Is this an impact of patch set 4 (10.2.0.5)? Why does it differ from 10.2.0.4 to newer releases?

View 3 Replies View Related

Execute AUDIT SELECT ON EMPLOYES From Procedure?

Dec 16, 2010

how can i execute "AUDIT SELECT ON EMPLOYES" from a procedure?

Ex:

create or replace procedure myfunc()
begin
AUDIT SELECT ON EMPLOYES;
end;

The above procedure will cause an error because AUDIT is no a DML statement.But... I still want to do that bebause i wanna call that function from PHP Application.With PHP, i can't execute AUDIT SELECT ON EMPLOYES.

View 3 Replies View Related

PL/SQL :: Procedure To Merge / SELECT From Table Of Record

May 28, 2013

I want to write a simple stored procedure and I want to keep it as simple as possible (no loop, the least amount of parameters ...etc.)

Basically, the procedure receives a Table Of Record as input parameters and needs to merge it with existing table, the table of record is of the rowtype of the existing table.

I have difficulties to merge these data. Below is what I tried

CREATE or REPLACE PACKAGE BIZ_xxx_MERGE
IS
TYPE xxx_ACTIVITE_Type IS TABLE OF MyTbl%RowType INDEX BY BINARY_INTEGER;
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
);
END BIZ_xxx_MERGE;

CREATE OR REPLACE PACKAGE BODY BIZ_xxx_MERGE AS
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
) AS
BEGIN
MERGE INTO MyTbl
[code].........

View 10 Replies View Related

Store Procedure In View With Select Statement

Aug 31, 2012

Need a trigger in view with select statement that means

CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TEST_TABLE;
CREATE OR REPLACE TRIGGER TEST_VIEW_TRG1
INSTEAD OF DELETE ON TEST_VIEW DECLARE
BEGIN
Dbms_Output.Put_Line('STATEMENT TRIGGER.');
END;

i wanted to use select statement instead of delete.How can i get that

View 1 Replies View Related

Create Sequence Using Subselect / Value From Select

Jul 8, 2008

CREATE sequence customer_id start with (select max(customer_id) from customer) increment by 1

i am trying to do that, can it be done or should i just find the max id myself and replace that select with a no?

View 2 Replies View Related

SQL & PL/SQL :: Create Table As Select With Index

Nov 10, 2010

Is it a possible to create table using clause below together with index ?

create table the_table
as
select col1, col2 from table2

I got procedure which create a table in the schema B. The procedure is called from schema A. But when I write into procedure query for create index
then I got a error:

ORA-01031: insufficient privileges when
...executing

Therefore I think about to create table together with index.

begin
B.proc.cre_table;
end;

View 6 Replies View Related

SQL & PL/SQL :: Select Into Temporary Variable On Stored Procedure For Second Insert?

Mar 30, 2011

the moment my 11g database is connecting to a php web front end. this following procedure is the one I'm having trouble with.

CREATE OR REPLACE PROCEDURE "BSISSONS"."CREATE_EXCURSION" (
min_places IN excursion.min_places%TYPE,
max_places IN excursion.max_places%TYPE,
additional_charge IN excursion.additional_charge%TYPE,

[code]...

I can select into an output variable to return the value of the primary key of the newly inserted row back into the webpage, but i need to be able to 'select into' a temp variable to insert this value into another table on the same procedure. I get complie errors when i try to 'DECLARE' a variable after the 'AS' keyword

View 2 Replies View Related

SQL & PL/SQL :: Procedure Containing Select Statement To Fill Page Items

Oct 24, 2011

I would like to fill items :P200_A and :P200_B and so on with the result of a SELECT which depends on the different values of many select lists.

E.G. :P200_list_alpha with the list of values
STATIC:less than 10;less,equal than 10;equal,above 10;above,indifferent;indiff

:P200_list_beta with the list of values
STATIC:active;active,passiv;passiv,excluded;excluded

How do I write the select statement ? I think it has to be executed in an anonymous PLSQL Procedure (after submit).

What is a convenient way to write the select statement ? I could imagine to use lots of IF , ELSIF, ELSE statements and in each branch (here 12 ) the whole/complet SELECT statement is written.

How to solve this problem in an elegant way ? In my opinion the CASE statement could be useful, but how to use it in the WHERE clause with this nested conditions ?

View 1 Replies View Related

SQL & PL/SQL :: Create DBMS-SQL Procedure?

Oct 7, 2010

I am creating the following dbms_sql procedure...

CREATE or replace PROCEDURE table_demo
(tabname IN varchar2)
IS
cursor_name INTEGER;
rows_processed INTEGER;

[code]...

There are no compilation errors.I call this code from the following anonymous block...

DECLARE
X CHAR:='T';
BEGIN
TABLE_DEMO(X);
END;

This also compiles successfully and without any errors. It runs properly as wellHowever when I run 'select * from T'. Then system throws up the error of table or view does not exist.

View 12 Replies View Related

SQL & PL/SQL :: Create A Trigger And Procedure To Go Along With DDL

May 11, 2010

I'm trying to create a trigger and procedure to go along with my DDL. Everything is created just fine, but when I try to execute an update on the table monitored by the trigger I get the following error:

update housing set group_num = 1 where room_num = 10

ERROR at line 1:
ORA-00937: not a single-group group function
ORA-06512: at "YANKEEFAN146.VIOLATION_CHK", line 6
ORA-04088: error during execution of trigger 'YANKEEFAN146.VIOLATION_CHK'

/* DDL */
create table violation_type
(violation_num number(1) primary key,
violation_def varchar2(100) not null)
organization index
tablespace mi257_data;
[code]....

View 9 Replies View Related

SQL & PL/SQL :: Create A Procedure Using EXECUTE IMMEDIATE?

Apr 14, 2013

I am trying to create a procedure using the EXECUTE IMMEDIATE. I have been having problems calling it from an anonymous block

My code

CREATE OR REPLACE PROCEDURE homework
(p_table_name VARCHAR2)
IS
v_department_id departments.department_id%TYPE;
BEGIN
EXECUTE IMMEDIATE

[code]....

I called the procedure from an anonymous block

BEGIN
homework('Employees');
END;

It gives me an error

ORA-00905: missing keyword

View 3 Replies View Related

SQL & PL/SQL :: How To Create Procedure In Script

May 23, 2011

I have 5 create package scripts.Rather than executing as one by one in SQLPLUS ,i want to run all 5 create statments in shell script in background.

For example... i want to create one file called bala.sql and execute all 5 create package statements in one shot as a script called bala.sql.

View 21 Replies View Related







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