I have a table TABLE1 which has following columns
proc_name
table_name
insert
update
delete.
The table contains a proc name, all tables used in that proc,operation is done on the tables. like sample data.
proc_name tab_name insert update del select
----------------------------------------------------------------------------------
Proc1 Table1 Y Y N Y
Proc1 Table2 y Y Y Y
I want to update this table throu running a generic procedure.Can it be possible to craete a procedure which parses through all procs of database and get above info and update TABLE1??
developing a procedure to create a partition every month passing in parms for the name and values, and also need to determine the appropriate tablespace that has adequate space available. Working within the constraints of the environment. I would like to pass in the table name as well. This code gives the general idea..
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.
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 )
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]....
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).
I would like to create a procedure which have 3 parameters. suppose in Date_birth, in Dept , out number_of_records
if i pass null value to one of the parameter it should return all values . if a pass value to the parameter it should return as per parameter values
Create or replace Procedure Emp_Test (P_dop in Date,P_Dept in number , P_noof_records out Number, p_recordset Out Sys_Refcursor) as Begin OPEN p_recordset FOR Select Emp_cd,Emp_name, Date_of_Birth,Dept,Count(emp_Cd) noof_records From Emp_Master Where Date_of_birth =P_date_of_Birth and Dept=P_dept ; End ;
************************* 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
I am trying to create a stored procedure in TOAD. I've found example code on the Internet to write a stored procedure, but it doesn't seem to work with Toad. I tried to write the procedure and execute it to save it to the DB, and instead I keep getting error messages like "an INTO clause is expected in this SELECT statement" when I used the
CREATE OR REPLACE PROCEDURE testing AS BEGIN SELECT * FROM CPK; END;
or "bad bind variable 'CPKS'" when I tried the following instead:
CREATE OR REPLACE PROCEDURE testing AS BEGIN SELECT * FROM CPK; Into :Cpks; END;
post some example code here with short explanations for me on how to write a stored procedure.
I 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;
insert into sample values('1','01-JAN-2005'); insert into sample values('1','1-JAN-2006'); insert into sample values('2','2-JAN-2007'); insert into sample values('3','4-JAN-2005'); insert into sample values('4','11-JAN-2004'); insert into sample values('3','2-JAN-2005'); insert into sample values('2','12-JAN-2006'); insert into sample values('1','21-JAN-2005'); insert into sample values('2','11-JAN-2005'); insert into sample values('7','01-JAN-2005'); [code]....
I want to create procedure who should run for both montly as well as yearly report for monthly report i will pass Year and Month as argument for year i will pass Year only
for annual report i a counting all the user, new entry for item, new enty for customer, new enty for admin for montly report i am counting new item, new group.
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;
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]....
create or replace procedure my_proc(p_user in varchar2) is l_cursor sys_refcursor; l_query constant varchar2(1000) := 'select a' || 'from ' || p_user || '.user_table' || 'where param0 = :x' || 'and param1 = :x' || 'and param2 = :x'
[Code]...
Suppose I execute my_proc many times and for multiple values of p_user. For performance reasons, will l_query be stored in the cache as I am using bind variables or it will not since I have the concatenation with p_user value ?
I'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]........
- To create one trigger on the table and make it call two unrelated procedures OR - To create 2 triggers and each trigger will call only one procedure.
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;
Reference: Accessing PL/SQL Stored Procedures using a Web Service (<i>Oracle® XML DB Developer's Guide</i>).
The feature works fine. Have tested a number of custom written PL/SQL procs this way - using it as a web service, and SoapUI and UTL_HTTP procedures as clients, calling the web service.
Question.
Can one support WSS (Web Services Security) with this feature?
I've added a WSSE to the SOAP envelope header when making the call - using standard password (no digest) and default addressing. The orawsv XDB servlet accepts the call, parses the SOAP envelope, and successfully executes the relevant PL/SQL procedure.
It however uses Basic Authentication (schema name and password). Not WSSE.
As the WSSE authentication data is part of the SOAP header (and not envelope body), the relevant PL/SQL procedure of course does/can not see the WSSE details. (also would not make sense ito how XDB abstracts orawsv as a web service interface and allows standard vanilla PL/SQL procedures and functions to serve as web service endpoints).
So if WSSE is to be supported, it would likely mean it needs to be supported in XDB itself. And that is outside my little area of Oracle expertise.
Not much on the net (lots about UTL_DBWS), and just a couple of basic orawsv supports notes on Metalink.
