After creating this procedure...we r not able to execute it...
SQL> ed
Wrote file afiedt.buf
1 create or replace procedure dyn_pro1
2 (tablename varchar2,colname varchar2, prodid number) is
3 cmd varchar2(100);
4 begin
5 cmd := 'delete ' || tablename || ' where'|| colname|| '=' ||prodid;
6 execute immediate cmd ;--using College_id;
7* end;
SQL> /
Procedure created.
SQL> exec dyn_pro1('stu_4','COLLEGE_ID',200);
BEGIN dyn_pro1('stu_4','COLLEGE_ID',200); END;
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "APPS.DYN_PRO1", line 6
ORA-06512: at line 1
am learning the Oracle PL/SQL language using CBT Nuggets tutorials and practicing what I learn. I seem to be getting confused with the ROUND function...For example, what am I doing wrong in this statement:
select avg(salary) from employees, ROUND (avg(salary) , 2;
Basically I am trying to round the result to two decimal places but get the error ORA - 00933 - SQL command not properly ended?
My requirement with one table which contains 20 columns. Now I want to convert 18 column into rows. I tried with the following query but its throwing sql error is given below.
select * from tmpl_pop_age_range_col_lvl UNPIVOT ( quantity FOR product IN (COUNT_0_TO_4 as 'a', COUNT_5_TO_9 as 'b', COUNT_10_TO_14 as 'c', COUNT_15_TO_19 as 'd', COUNT_20_TO_24 as 'e', COUNT_25_TO_29 as 'f',
[code].....
ERROR at line 4: ORA-00933: SQL command not properly ended
What I am trying to do is get my report to list every room in the table even if there is nothing scheduled in the room for the selected date. I add a command to the report to force the left outer join but I keep running into errors. This is how I have it worded:
SELECT "ROOM"."ROOM_ID", "PATIENT_CARE_EVENT"."OR_NUM" FROM "ROOM"."ROOM" LEFT OUTER JOIN "PATIENT_CARE_EVENT"."PATIENT_CARE_EVENT" ON "PATIENT_CARE_EVENT"."OR_NUM"="ROOM"."ROOM_ID" AND "PATIENT_CARE_EVENT"."PROCEDURE_DATE_DT" IN {?Start Date} TO {?End Date}
Someone else suggested that I change the IN/TO wording in the last line to BETWEEN/AND. When I do that it gives me an error stating that the table or view does not exist.
I am trying to execute the SQLPLUS command (CONNECT) from the stored procedure.It is throwing below error message.
Stored Procedure:
SQL> select user from dual 2 ; USER ------------------------------ SYS SQL> create or replace 2 PROCEDURE PROCEDURE1 AS 3 BEGIN 4 sqlplus sys/sys@D as sysdba; 5 --execute immediate 'create user 'kkk' identified by 'kkk'; 6 END PROCEDURE1; 7 /
Warning: Procedure created with compilation errors.
SQL> show err Errors for PROCEDURE PROCEDURE1: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/9 PLS-00103: Encountered the symbol "SYS" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "SYS" to continue. [code]....
How to dynamically execute a select command whether it is retrieved a single record or multiple record in oracle database 10g. i have tried with the command execute immediate but it was not successful.
is it possible can i delete or drop multiple tables in a single drop and delete statement.
I am getting errors while executing the following block.
create TYPE c_Rec as object(a VARCHAR2(1), b NUMBER); DECLARE -- TYPE c_Rec as object(a VARCHAR2(1), b NUMBER); TYPE c_collection IS TABLE OF c_Rec; l_coll c_collection := c_collection(); BEGIN [code]........
error
06530. 00000 - "Reference to uninitialized composite"
I'm trying to execute a procedure within a Declare/Begin/End statement and using variables as input parameters in my procedure but I keep on getting an Invalid SQL Statement Error. My code is below:
declare
START_dt VARCHAR2(30); END_DT VARCHAR2(30);
begin
SELECT '01-APR-2011' INTO END_DT FROM DUAL; [code]....
The table the procedure is pulling data from doesn't have proper date/time stamps but my procedure takes the varchar dates above and turns them into dates in the procedure so the input date parameters are left as just string characters.
How to execute Sql Server procedure from Oracle.I have created a dblink and when calling directly to that procedure it is throwing error.
--creationg sqlserver procedure create proc getRecords as print 'testing'; --creating db link create database link remote connect by ss identified by ss using 'remote'; [code]....
I have found DBMS_HS_PASSTHROUGH package is capable of doing it but not sure how to do. Nathan
I've the following package having a procedure with OUT parameter as refcursor .The following is the sample example having same structure with different table name,exception handling in my dev environment
CREATE OR REPLACE PACKAGE TEST_PACK IS Type refCursor is REF CURSOR; PROCEDURE TEST_PROC (out_data out refCursor); END; [code]...
i am getting the follwoing errror ORA-00932: inconsistent datatypes: expected - got.
I need to execute a procedure based on a value in a form. So the procedure name will be changing for value selected in a list.
I need to know a method where i could store the procedure name in a table and when ever i select a value from the list, the respective procedure needs to be executed.
I'm not attaching any tables / data..etc...I just want to know how to pass the record type to a procedure (which are actually obtained from a table) -- see ** below where I'm getting an error..Need to pass the whole record type "l_shl_order_msg"
CREATE OR REPLACE PROCEDURE CM_BUILD_MSG_PRC (P_IN_BLD_MSG_CURSOR IN SYS_REFCURSOR, P_OUT_BLD_MSG_CURSOR OUT SYS_REFCURSOR) IS l_shl_order_msg CRAE_INTERFACE.GLB_VAR_PKG.deid_SHELL_order_typ; V_MSG_SHELL_NAME VARCHAR2(1000); V_MESG_TEXT_SEGMENT VARCHAR2(1000); V_TEXT VARCHAR2(1000); V_MSG_TEXT VARCHAR2(4000); V_MSG_FINAL_TEXT VARCHAR2(4000); V_MSG_PROC VARCHAR2(1000); V_SQL VARCHAR2(4000); V_CNT NUMBER; L_STATUS VARCHAR2(100); L_REASON VARCHAR2(1000); [code]...
I get an error saying that "wrong number or types of arguments in call to ||"..Not sure how to pass record type dynamically...
Procedure: CREATE OR REPLACE procedure test (a number, b varchar2) is
begin dbms_output.put_line(a ||'->'||b); end;
Anyonymous Block:
begin exec test(1,'m'); end; / When i run this i am getting this error ORA-06550: line 2, column 7: PLS-00103: Encountered the symbol "TEST" when expecting one of the following [code]...
create or replace procedure test as stmt varchar2(2000); begin EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38)) ON COMMIT PRESERVE ROWS';
stmt := 'INSERT INTO tt_Local SELECT cardnumber FROM cards'; execute immediate stmt; end;
-- when am trying to execute this
begin test; end; -- showing ora-01031, insufficient privileges.
In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.
I would like to execute my stored procedures and all associated functions in database A on my data in database B.
Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.
Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...
I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.
CREATE OR REPLACE PROCEDURE test IS CURSOR cusers IS SELECT user_name, user_date FROM users; uname users.user_name%TYPE; udate users.user_date%TYPE; BEGIN OPEN cusers;
[code].....
When I try to execute this procedure I get following error:
ORA-00900: invalid SQL statement
Compilation of procedure is successful.
SQL code for creating the USERS table is here:
CREATE TABLE "USERS" ("USER_ID" NUMBER(10,0) NOT NULL ENABLE, "USER_NAME" VARCHAR2(50) NOT NULL ENABLE, "USER_EMAIL" VARCHAR2(50) NOT NULL ENABLE, "USER_PASS" VARCHAR2(50) NOT NULL ENABLE, "USER_DATE" DATE NOT NULL ENABLE, CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE )
I got to pull 30M data into local DB from a remote DB. As of now we have procedure to perform this task using an
Insert /*+append*/ table1 select * from rdb_view;
I observed using the copy command in sql plus has taken very less time than the procedure.Can we use the Copy command inside the PL/SQL block? I tried with dynamic SQL but It is not allowed I guess.
I am working on form 10g, here i am using post command in save procedure to apply some record in database temporary.
But Frm-40404 Message raise during saving record i need post commond but i dont want to this message should popup, is there any method to avoid the message only?
we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?
after a long time,(i never remember when was the last time i installed 9i)i was asked to install 9i.So, whether i install server or cilent i get this error <attachment>
after i ignore it and finished the installation,The server installation gets abruptly ended for the client i get TNS errors.
