SQL & PL/SQL :: Accept Input And Output To Screen - Getting ORA-06550 Error
Jan 18, 2011
I am trying to accept three numbers and one string as input, and then display the values on the screen. I don't understand why I am getting these errors.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 id_number NUMBER := &id_number_input;
3 trans_num NUMBER := &trans_num_input;
4 remar VARCHAR2(75) := &remark_input;
5 receipt NUMBER := &receipt_input;
[Code] .....
ERROR at line 4:
ORA-06550: line 4, column 24:
PLS-00201: identifier 'HELLO' must be declared
ORA-06550: line 4, column 8:
PL/SQL: Item ignored
ORA-06550: line 10, column 61:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 10, column 2:
PL/SQL: Statement ignored
<h4><font color="Blue"> CREATE OR REPLACE PROCEDURE SP_SSN_BY_PIN( V_REQUESTEDPIN IN VARCHAR2, V_SSN OUT CHAR) AS BEGIN SELECT
[code]....
As you can see the type of the SSN column is NUMBER(13,0). But I leftpad it with 0 and assign it to my output paramter V_SSN, whose type is CHAR. But I get 111196100099 instead of 0111196100099. I've tried TO_CHAR(LPAD(SSN,13,'0')) but still doesn't work. However, if I return the left padded SSN inside a SYS_REFCURSOR I get what I want.
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]...
I am using regexp_substr to break the pipe delimited string. Want to use the output as the NUMBER input to a cursor.Its not working and not getting any error also.
Here is an example.
initial input : '5545|4124|12456' using the sql below to break the string into columns. select regexp_substr('5545|4124|12456','[^|]+', 1, level)) from dual connect by regexp_substr(, '[^|]+', 1, level) is not null;
But when i try to pass the output of the above query to a cursor with input defined as number, its not accepting. I tried using to_number for the output of above query.
when i am calling stored procedure with input and output parameters from batch file .. I am getting the following message ..
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 11:48:51 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 14
PROCEDURE SP_SELCT_EMPLOYEE_DATA ( -- A_EMPLOYEE_ID IN VARCHAR2, --A_JOB_ID IN EMPLOYEES.JOB_ID%TYPE, P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE, P_EMPLOYEE_NAME IN EMPLOYEES.EMPLOYEE_NAME%TYPE, P_EMAIL IN EMPLOYEES.EMAIL%TYPE,
While I try to download a file using the below code; an HTTP 404 error is coming although the proc is being called and the parameter is correctly being passed.
create or replace procedure download_myfile1(p_id in number) as v_mime varchar2(255); v_length number;
[code]...
It shows a POP up window by which u can save or opens the file. But no pop up displayed in this case.
On Windows 7 I've created a DBCA from Script to automate the DB creation of identicall databases.I executed the bat from cmd.exe which I started as Administrator
On script xdb_protocol.sql when catqm.sql was called, the cmd stoped it asks for a value "Give a value for 4 one:" (In german actually "Geben Sie einen Wert f³r 4 ein:" ) http://img62.imageshack.us/img62/1145/tempin.jpg Its for *:user_opt_secfiles := '&4'*; shall be YES or NO it doesnt auto-set. What shall I put? [code]....
I trying to Assign XML content to the clob variable inside the pl/sql block, But i am getting the Below Error:
declare t clob; begin t := 'xml content exceeds 32000 characters'
update test clob_cloumn = t; where id =2;
exception when others then null; End;
ORA-06550: line 5, column 4: PLS-00172: string literal too long
I need to handle this exception, i know it length exceeds 32000 characters, but even though i need to handle the exception and to perform other operation after handling the exception.
I am trying to resolve by using a cursor C2 inside the already existing cursor C1, i will use a inner loop to get the select statement value to cursor c2 then i will assign the vlaues the variables, then once i read all the values of cursor c2 i will exit inner loop and go to outer loop to read the next value of c1 and then again go to cursor c2 and inner loop.
I have pasted the code below.
Declare temp1 source.source%type; temp2 QUOTE_LETTERS_MASTER%rowtype; Cursor c1 is Select * from Source;
I have a problem with the code below. I would like to define a variable (exrate1) which I would like to use in another script. Also, the variable should be different for every month(that's why timestamp is between some period).
The problem is that I receive some errors which I don't know haw to fix
I am learning Regular expression I am solving one problem with regular expression. I am creating a table while inserting I have to restrict punctuation character.
I am running rep_1, and before executing second rep_2 the user should enter department number, after accepting department no, the second rep_2 should be executed. But after accepting parameter the second rep_2 is not displaying any record.
i was asked to built a report file that will run from oracle form using web. show_document() built-in object to produce a data extract. the objective is to built a dynamic SQL and use that to a cursor.
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS TYPE array_t IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; rec_array array_t; BEGIN FETCH p_cursor BULK COLLECT INTO rec_array; FOR i IN rec_array.FIRST .. rec_array.LAST LOOP dbms_output.put_line(rec_array(i)); END LOOP; END pass_ref_cur; [code]....
I found out that the procedure can only accepts a single column. When I run this code it works fine because I am using a single column on the SQL statement.
DECLARE rec_array SYS_REFCURSOR; BEGIN open rec_array For 'select p.muni from chips.project p, chips.proj_type pt where p.type = pt.id'; pass_ref_cur(rec_array); CLOSE rec_array; END;
The objective is to use a multiple column. How can I revised the procedure pass_ref_cur so that it can accept multiple columns?
I have a plsql block construct where i want to use for loop dynamically , the query which for cursor for for loop will accept the table name from parameter and join them to return the result. the resultant data will iterate in loop and do the execution.
I'm trying to create a store procedure that will accept a username from a flat file but i don't know how to do read file into store procedure.
Below is a sample store procedure by itself i created to add user which created okay but when i execute I got the error displayed below.
create or replace procedure addUsers(userNam in varchar2) is begin EXECUTE IMMEDIATE 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERS'||'QUOTA "1M" ON USERS'|| 'PASSWORD EXPIRE'; end addUsers; /
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; -- TAKE ALL JOB TITLES FROM JOBS CURSOR JOBSCUR IS SELECT * -- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID FROM TARGET_UCM ; [code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
I have an form with a few canvases already in it. The problem is that I want to see a particular canvas which shows the toolbar at the top of the window but it dosent show up on the form. I checked the property palette and the canvas is visible. There is also an other canvas right at the top of the form. How do I move the other canvas down if this is covering the toolbar canvas. How do i move the canvas down as it dosent show any X position in its property palette.
i am not able to get whole form on the screen. some part of the part is missing and even there is no scroll bar to slide the form. I have also change the form applet parameter..my screen size is 1024 x 768 and this is same as of the size of form applet.
I migrate some forms&reports 6i to 11g, i have some issues on screens. they are
1.In some of the migrated screens the prompts are not properly aligned. In some cases the Prompts which were in bold are converted to regular fonts. 2.The font size have increased, due to this the data are not completely visible in the fields. Do we have any provision for reducing the font size so that this gets applied for all the screens instead of changing the font size in each screen? 3. In case of Push buttons the names of the buttons are not fully visible. 4.The reports are not getting generated if we access the Application using Google chrome.
