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
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;
I have table 'A' with column 'ID','NAME','IN_DATE','PHONE','EMAIL'
Now I have to create a trigger such that on every insert in the table 'A' the value of column 'IN_DATE' changes to sysdate.I m not good in PL/SQL
SQL> ALTER SESSION SET CURRENT_SCHEMA = CLA_T3;
Session altered.
SQL> select sys_context('USERENV','SESSION_USER') current_user,
2 sys_context('USERENV','SESSION_SCHEMA') current_schema
3 from dual
4 ;
CURRENT_USER
--------------------------------------------------------------------------------
CURRENT_SCHEMA
--------------------------------------------------------------------------------
CSR_ETL
CLA_T3
SQL> set linesize 300;
SQL> /
CURRENT_USER
----------------------------------------------------------------------------------------------------
CURRENT_SCHEMA
----------------------------------------------------------------------------------------------------
CSR_ETL
CLA_T3
SQL> create table cla_t3.test (r number, b char(2));
create table cla_t3.test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> create table test (r number, b char(2));
create table test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges
After Setting current schema to 'CLA_T3', I am unable to create table in cla_t3 schema.
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 RelatedI got a table table1 with 3 columns: id, name, value
im trying to create a procedure to update the table.
create or replace
PROCEDURE TEST1 (
x IN varchar,
y IN varchar,
z IN varchar
) AS
BEGIN
update table1 set value=x where name=y and id=z;
commit;
END TEST1;
that doesnt seem to work
I am trying to create a procedure that inserts parameters into a table and then returns the number of rows inserted back to calling block. the procedure is compiling fine but is not returning the number of rows inserted. My code is as follows;
HOST VARIABLE DECLARATION
VARIABLE g_CarMasterInsertCount NUMBER;
STORED PROCEDURE
CREATE OR REPLACE PROCEDURE CarMasterInsert_sp (
registration IN VARCHAR2,
model_name IN VARCHAR2,
car_group_name IN VARCHAR2,
date_bought IN DATE,
cost IN NUMBER,
miles_to_date IN NUMBER,
miles_last_service IN NUMBER,
status IN CHAR,
rowsInserted OUT NUMBER)
[code]....
I think im close just that my syntax is off.
How to create procedure for getting number of records of a table
View 24 Replies View RelatedI've to create a table every time a procedure is run, initially the table should be dropped and then created every time. From this procedure, the table is neither created nor dropped.
I cant track the error. Why is it so?
CREATE OR REPLACE PROCEDURE TESTPROC IS
S_SQL VARCHAR2(1000);
BEGIN
S_SQL := 'DROP TABLE MYTEST PURGE';
EXECUTE IMMEDIATE S_SQL;
[code]........
I have three tables fixtures, fixture_teams and team_tbl
fixtures consists of:
create table Fixture_tbl(
fixt_id varchar2(8),
fixt_date date,
fixt_time varchar2(10),
fixt_location location_t,
umpire_id varchar2(8),
player_of_match player_of_match,
home_team varchar2(20),
away_team varchar2(20),
[code]....
creating a stored procedure that updates the points column in the teams_tbl , the value that is updated in to the points column will be retrieved from the fixture_team table. so if team a has more goals than team b then the points column for team a will be increased by 6 else if the scores are equal they get 4 points each.
Can we create a table from a Select query ?
View 5 Replies View RelatedBelow query. Below is the DDL , DML and expected output.
create table tab_1 (col1 varchar2(10), col2 varchar2(10));
select * from tab_1
Output of query
col1 col2
12
23
34
[code]...
The above query is not giving below expected output.
Output:
4 4/3/2/1
3 3/2/1
7 7/6/5
8 8/7/6/5
11 11/10/9
Suppose you have the below table, same ID's occur for same month as well as different month
ID Month Value
--------------------------------------------------------------
226220 201203 100
1660 201204 200
26739 201204 1010
7750 201205 31.1
I need a query to determine the below laid result
ID Month Prior_month_value Prior_Month Value
----------------------------------------------------------------------------
1234 201203 10 201201 100
3456 201206 56.1 201204 78
I want to create table from query with passing parameters
create table as temp
select * from emp
where hiredate between :sdate and :edate
and deptno = :dpt
when I tried in Toad after passing parameters it gives me error
ORA-01036: illegal variable name/number
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 )
OBJECTIVE: To allow a user to change their own password after logging into an application.
BACKGROUND: I have a stored procedure where I pass the userid, old password, and new password. The stored procedure contains the following ALTER statement:
EXECUTE IMMEDIATE 'ALTER USER :uid
IDENTIFIED BY :npwd REPLACE :opwd'
USING IN v_user_id, v_new_pwd, v_old_pwd;
where v_user_id, v_new_pwd, and v_old_pwd are the arguments passed to the stored procedure.
FACTS:
(1) The procedure compiles fine;
(2) During execution, SQLCODE returns "0" (i.e. zero);
(3) My userid does have permission to change my password and execute the stored procedure;
(4) At the time I call the stored procedure, I am successfully logged into the application with my userid and old password.
PROBLEM: When I try to login with the new password I get the following error: "ORA-01017 invalid username/password; logon denied".
I have a procedure in this procedure i use.
EXECUTE IMMEDIATE 'alter user '||use||' identified by '||modp ||' replace '||oldp;
but when i execute it show insufficient privilages but i create for this procedure as public.and grant execute facility to the user.
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????
What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
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 RelatedWhenever there is a change in the c_flag, we need to identify the change and do some processing. How do I identify the change from either 1 to 0 or 0 to 1. The change can be either way. The point of importance is to identify the change.
CREATE TABLE tab1 ( id NUMBER(10) NOT NULL,
description VARCHAR2(50) NOT NULL,
Start_Date DATE,
id_num NUMBER(10),
c_flag varchar2(2));
[code]....
I have text item and it is assigned with lov namely 'Typelov' and that xlov assigned with 'Rgtype'. When I create the record group i just create with sql
like
select distinct type from x;
But at runtime i want to change the query
select distinct type from x where col1 = 'XXX';
and now the lov should display the above filtter condition item only.Is there any possible
how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me.
xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.
I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.
CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....
We are using APEX 4.2, 10GR2, RedHat5.
I have a report that comes from SQL Query (updateable report). I'm using the apex_item.text and apex_item.hidden on fields. I'm using a button to submit and after submit process to add some logic that I need.
There could be 1 - 10 records in the report. There is only 1 field that is needed to enter a value, but the value of this field determines the value of another field. I think that I can do this with a submit button and an after submit process where I loop through all the records. I think I have this handled.
This is the question
When the value of that field is changed then the value of another field in the same row changes immediately. All the examples I've seen so far are for a single record and that doesn't work for us.
I guess this is a MRU process but I haven't seen an example where a dynamic action is possible on a Multi Row Update.
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.
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]....
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
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.
iN MY DB SERVER (CALLED A),THERE IS A DBLINK TO ANOTHER DB SERVER (CALLED B). DBLINK'S IS LINKB.
----------
BY USING LINKB,I CAN CREATE,DROP TABLE;DELETE,UPDATE,INSERT DATAS IN SERVER B.
Now,i want to create procedure on server b(but i can only deal with b using dblink linkb).