I am getting an error when trying to run this DECLARE statement and I'm not sure why. I'm fairly new to PL/SQL and have looked up how to format a DECLARE statement and this seems right to me.
The error is stating "encountered the symbol "end-of-file" when expecting one of the following... " and is refering to the beginning of this statement.
declare ret_val number; begin exec p_buildinfo('252657020001', to_date('20120820','YYYYMMDD'),to_date('20120928','YYYYMMDD'),ret_val, 0); DBMS_OUTPUT.PUT_LINE('Value Returned Is : '||ret_val) ; end;
I getting the below error
ORA-06550: line 3, column 10: PLS-00103: Encountered the symbol "P_BUILDINFO" when expecting one of the following:
:= . ( @ % ; The symbol ":=" was substituted for "P_BUILDINFO" to continue.
the procedure structure is
CREATE OR REPLACE PROCEDURE p_buildsinfo ( var_p_cod CHAR := NULL, var_p_dat_from DATE := NULL, var_p_dat_to DATE := NULL, po_var_l_nxt_seq IN OUT NUMBER, var_p_consol_flg NUMBER default 0 )
select iloan_code,inst_due_date,paid_flag,late_fee,case late_fee when sysdate-inst_due_date between 1 and 10 then 10 when sysdate-inst_due_date > 10 and late_fee <>10 then 5 when sysdate-inst_due_date > 10 and late_fee = 10 then 15 else 0 end as new_late_fee from st_il_schedule where paid_flag='N'; i am getting error
I'm trying to do is create 4 simple tables. This is my first project using SQL so I'm totally new to it, not just SQL itself but database design/management, including foreign/primary key concepts. I think that's why the errors are being generated due to a duplication of foreign key names (perhaps?) but I really don't where I'm going wrong with the design structure in terms of the FK's and the relationships.
Added the error report in the attachment.
Create Table Hotel (Hotel_No Char(4 Byte) Not Null, H_Name VarChar2(20 Byte) Not Null, H_Address VarChar2(30 Byte), Constraint Hotel_PK Primary Key (Hotel_No));
I have a question. If i insert some values to a table and then write a create statement. But if the create statement gives me error (eg: table name already exist). And without commiting if i come out the session will the insert commit?
I'm teaching myself to write stored procedures, working in TOAD 10.5 with Oracle 10g. I keep getting error ORA-00900: invalid SQL Statement. Here's the code, cut down to bare minimum sample size. I can't see where I'm doing anything wrong.
If I cut out the cursor (taking it down to just "Begin" and "End"), it does run, but I can't see anything wrong with the cursor.
CREATE OR REPLACE PROCEDURE IN_PROCESS_CASES_BOS IS V_HELLO VARCHAR2(10); CURSOR C_MAIN IS SELECT 'HELLO' FROM DUAL;
I am creating the following two tables...no issues here: CODECREATE TABLE COURSE_SECTION ( Csecid NUMBER(8) CONSTRAINT COURSE_SELECTION_NUMBER_pk PRIMARY Key, Cid NUMBER(6) NOT NULL CONSTRAINTS COURSE_SELECTION_Cid_fk REFERENCES COURSE, Termid NUMBER(5) NOT NULL CONSTRAINTS COURSE_SELECTION_Termid_fk REFERENCES TERM,
[code]...
The issue I am having is actually inserting data into the table: CODEINSERT INTO ENROLLMENT VALUES (100, 1000, 'A' ); INSERT INTO ENROLLMENT VALUES (100, 1003, 'A' );
[code]...
But I get an ORACLE error of
ORA-02291- integrity constraint (User1.ENROLLMENT_CSECID_FK) violated - parent key not foundHow can the parent key not be found when I have it declared/created in the above statement?
Can I declear a variable in PLSQL as the row type for a table, who's name is unknown during compile time, but will be determined when the PLSQL is runnning. The code is like following:
Procedure operTable( tableName IN VARCHAR2) IS TYPE ty_Row IS tableName%ROWTYPE v_Row ty_Row; CURSOR v_quey_cur
Error report: SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row" *Cause: *Action:
UPDATE XXX_CURR_EOM SET ID = (select CAPITALPLAN.ID from capitalplan, XXX_CURR_EOM where XXX_CURR_EOM.ID = CAPITALPLAN_id)
i just what to understand the differnce between the variable declared under the Begin and variable declared under Declared. to understand this i tried a sample plsql procedure as show below
create or replace PACKAGE BODY TEST_DEV AS PROCEDURE TESTING(I_NAME VARCHAR2, I_AGE NUMBER) AS INTEGE binary_integer := 100; [Code] .......
the Line L_nums1 NUMBER := 100/0; is throwing error(PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following:)
if i remove the line L_nums1 NUMBER := 100/0; and just declare the variable out side the begin , and using inside the same variable inside the begin works fine
Why question is why we are not able to decalre, initialize and use the same Number variable insde the begin
In package head I declare a variable , which reference 'level' type , but I don't know how to declare the variable , because I don't know what type of 'level'.
So if the truncate syntax goes like TRUNCATE { TABLE [ schema. ]table [ { PRESERVE | PURGE } MATERIALIZED VIEW LOG ] | CLUSTER [ schema. ]cluster } [ { DROP | REUSE } STORAGE
Where it is really not mandatory to add (DROP STORAGE) to the truncate statement knowing fully well the Watermark is dropped. Lately some of my Truncate statements that are part of Plsql package have been failing randomly with a ORA-03291 error. I iteratively walk through a list to truncate some tables and 1 or 2 out of 10 randomly fail. But work fine when I rerun. Its been a night mare for couple of weeks now. This code was working alright for last several years now in 10g and previously 9i. Do you believe some changes to settings on the Oracle database or to blame for ?
ORA-03291: Invalid truncate option - missing STORAGE keyword ORA-06512: at "xxxx.xxxxxxxxx", line 35 ORA-06512: at line 2
I have a procedure as below. To sum up the procedure in one line it dynamically forms a string to get the values of the type which is passed as an input to the procedure.
I call the procedure as
exec exec_imm( exec_imm_t(1,'asd','1/2-34'));
ERROR: Error starting at line 9 in command: exec exec_imm( exec_imm_t(1,'asd','1/2-34')) Error report: ORA-00904: "P_TYPE_DATA"."ADDRESS": invalid identifier ORA-06512: at "PTK_ADM.EXEC_IMM", line 26 ORA-06512: at line 1 00904. 00000 - "%s: invalid identifier"
NOTE: When I try to execute the procedure with the execute immediate statement I get the above error. But when I execute the select statement which is nothing but the value in v_type_data directly (as seen in the comments in the code below) there is no error. But when the same v_type_data is used in execute immediate, I get an error.
CREATE OR REPLACE procedure exec_imm(p_type_data exec_imm_t) AS v_type_str CLOB := NULL; v_type_data CLOB := NULL; v_type_name VARCHAR2(25) := NULL; BEGIN [code]......
how to play around with NDS dynamic sql and I'm trying to add a column on the fly.Basically the procedure is trying to take a table name, column name, and eventually a data type and adds it to a table.
It works fine without the bind variable for the column name, accepting the table name on the fly.As soon as it tries to use the column name I get an ORA-00904 invalid identifier exception.
Here is the procedure I'm using
CODEcreate or replace procedure test(tbl_name varchar2, col_name varchar2) IS qry varchar2(500); begin
I want to call a value from one from to another form in d2k forms 4.5. I think this can be done only by declaring a global variable. I don't know how to declare global variable and also calling the same in other forms.
I want to know how we can declare a Global Variable in Package body(Not Spec), So that i can use it in any procedures or function(Defined in same package).
I have a package which has couple of Procedures and functions. I use some constants between the functions and procedure and also functions r called from the other procedure and stuff...
So I declared all these in the header
Like this
CREATE OR REPLACE PACKAGE "PROCESS_HISTORY" IS type table_name_i is varray(100) of VARCHAR2(30); c_add constant number := 1; c_del constant number := 2; c_select_frm_tmp constant number := 1; [code]......
Now is there anyway I could hide the stuff that I don't want outside people to see? or can I declare them (constants and some functions) some where inside the body and use them?
I am getting some odd results from a Database Insert Function. The function receives an Array of elements. The elements are a defined structure (containing around 21 data items). I need to insert these records into an Oracle table.For each element of the Array the function reads the structure into a local c structure of the same type.
I then go through this local structure and get a copy of the data into local variables declared in the EXEC SQL BEGIN DECLARE SECTION of the function. I then use the local vars to do the insert, using null INDICATOR variable to handle those variables that could be empty. The local variables look like this....
EXEC SQL BEGIN DECLARE SECTION; int dbServiceTypeId; int dbRecordType; char dbRbmCustRef[MAX_CUST_REF_LEN]; int dbServiceSeq;
[code]...
BUT.. when I uncomment the dbCOS variable (even though I don't populate it or try to include it in the insert) I get the following in the table (The RATE value goes missing completely and the multiplier is wrong)...
ID SERVICE CODE R/T RATE MULTI 41325-SCODE-1084 1 542139762 11326-SCODE-1086 1 542139762 11326-SCODE-1086 2 542139762 21327-SCODE-1087 1 542139762 21327-SCODE-1087 2 542139762 21327-SCODE-1087 3 542139762 21327-SCODE-1087 3 542139762
However, a printf statement just before the insert based that returns the variables shows the following...
Service Type ID: '4' Record Type ID: '1' Service Cust Ref: '1325-SCODE-1084' Service Unit Rate: '1200.00'
Indeed, for this record the Multiplier doesn't even get populated. The other odd thing is if I recomment the dbCOS but remove the dbOraDateFmt variable definitions, it corrupts the data again, though different fields. I can't understand why individual local variables are behaving this way. Is this a problem with the way variables are declared in this section?
i want if user is not type the @ in email text box than from show the message "check your email address" but i cant do that
declare ok_flag number :=0; begin ok_flag :=instr('emailaddress','@'); if ok_flag < 1 then message('invalid email'); else message('thanks'); end if; end;
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 )