Advertisements I know there’s already a few posts on this error ( "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" is bug?), but I’ve been through them and I still can’t seem to figure out what’s wrong with my code. I can’t seem to call stored procedures with an OUT parameter in… Read More ORA-06502: PL/SQL: numeric or value error: character string buffer too small whenever I have an OUT Parameter

Advertisements I am trying to fetch all records from my table using cursor but it’s only returning the last record. Here is code DECLARE V_NAME1 Students.V_NAME%TYPE; N_CLASS1 Students.N_CLASS%TYPE; CURSOR C1 IS SELECT V_NAME,N_CLASS from students; BEGIN OPEN C1; LOOP FETCH C1 INTO V_NAME1,N_CLASS1; EXIT WHEN C1%NOTFOUND; END LOOP; dbms_output.put_line(V_NAME1||N_CLASS1); CLOSE C1; END; / Also below… Read More PL/SQL Cursor returning only last record

Advertisements I have a database with more than 100 tables. I want to find the table that contains the ‘ATMOSPHERIC’ data in the tables in this database. How can I do it. I wrote such a code myself but it did not work. I am waiting for your advice. DECLARE v_search_term VARCHAR2(100) := ‘ATMOSFERIK’; v_sql… Read More Oracle SQL Developer How to find one data all DB

Advertisements I’m looking to currently split a string in Oracle by more than 1 character. Currently I’m splitting a set of numbers by a space like so: SELECT regexp_substr(‘123 456’, ‘(^| )([^ ]*)’) from dual SELECT regexp_substr(‘123 456’, ‘(^| )([^ ]*)’, 1, 2, null, 2) from dual etc. which returns back 123 and then 456.… Read More Oracle Regex – Splitting a string by more than 1 delimiter

Advertisements I have been working hard how to create dynamic INSERT statement the table which as CLOB data Table – employee firstname CLOB, lastname CLOB, age CLOB, creation CLOB, description CLOB The above all the column data type is CLOB and as data in it I have one more table called main_employe firstname varchar2(255), lastname… Read More dynamic creation of insert statements | SQL |

Advertisements I’m trying to insert the data inside a forall loop. For this case, I cannot use a temporary variable and set result of the function beforehand. The function just maps a number to a string: create or replace function GetInvoiceStatus(status number) return nvarchar2 as begin case status when 0 then return ‘New’; when 200… Read More Function call as a parameter inside insert values statement

Advertisements I am learning PL SQL and running the below code — create or replace PROCEDURE PROCEDURE1 AS DECLARE c VARCHAR2(60 CHAR); BEGIN c := ‘abc ‘; END; END PROCEDURE1; However, this is giving error like below == Error(3,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype… Read More PLS-00103 caused by PL SQL Declare keyword

Advertisements I ma using PLSQL. I have json. But when i parse the json, it retun empty. Here is the sample PLSQL code: CREATE OR REPLACE PROCEDURE my_process IS myjson VARCHAR2 (32767):= ‘{"responseinfo":"<TRANSACTION><RESPONSE_CODE>100</RESPONSE_CODE><RESPONSE_MSG>Duplicate Request Found.</RESPONSE_MSG></TRANSACTION>"}’; l_json_obj JSON_OBJECT_T; requestinfo VARCHAR2 (32767); BEGIN BEGIN l_json_obj := JSON_OBJECT_T.parse (myjson); requestinfo := l_json_obj.get_string (‘requestinfo’); END; END; When I want… Read More Json parse return empty in plsql

Advertisements I want to load data from a csv file into a table in the oracle database using external tables or plsql. please considere that i connect remotly to the database using sql* plus. I have read that to use external tables or load data with plsql i need to create a directory first and… Read More Loading data from csv file into oracle database

Advertisements So I’m doing this script and at some point it worked. But I had to do it with bind variables so some of it changed. Once I did the changes and applied the bind variables isn’t working anymore and it throws these errors. SET SERVEROUTPUT ON; VAR B_NUMRUT_EMP NUMBER; EXEC :B_NUMRUT_EMP := &RUT; VAR… Read More Invalid number when the input is the right one and the table definition is also the right one?